-1

I'm wondering do I need just SELECT FROM grant to select from SYS.DBMS_LOCK_ALLOCATED or there is some special role for it?

I'm unable to grant SELECT FROM SYS.DBMS_LOCK_ALLOCATED to my test user, so I've tried to grant SELECT FROM ANY TABLE. But it didn't worked.

Alexander
  • 1
  • 1
  • Either grant it or obtain the SELECT ANY DICTIONARY priv. But is this really necessary? Rarely is there need to go behind Oracle's views and hit SYS tables directly. – Paul W Apr 12 '23 at 20:01
  • Granting SELECT ANY DICTIONARY did the trick. Thanks! It's for testing purposes only. I can select everythinng I need from V$LOCK but I also want to see name of the lock, not only ID. – Alexander Apr 12 '23 at 20:33

1 Answers1

0

Grant it is; at least, I think so.

SQL> connect sys@pdb1 as sysdba
Enter password:
Connected.
SQL> select * from dbms_lock_allocated where rownum <= 3;

NAME                               LOCKID EXPIRATIO
------------------------------ ---------- ---------
ORA$_SQLSET$630476433          1073741824 03-APR-22
ORA$_SQLSET$4096254864         1073741825 08-OCT-21
ORA$KU$DATAPUMP_SW_UPDATE      1073741844 25-MAR-23

SQL> grant select on dbms_lock_allocated to scott;

Grant succeeded.

SQL> connect scott@pdb1
Enter password:
Connected.
SQL> select * from sys.dbms_lock_allocated where rownum <= 3;

NAME                               LOCKID EXPIRATIO
------------------------------ ---------- ---------
ORA$_SQLSET$630476433          1073741824 03-APR-22
ORA$_SQLSET$4096254864         1073741825 08-OCT-21
ORA$KU$DATAPUMP_SW_UPDATE      1073741844 25-MAR-23

SQL>

As of USER_TAB_PRIVS:

SQL> show user
USER is "SCOTT" 
SQL> select owner, table_name, privilege
  2  from user_tab_privs
  3  where grantor = 'SYS';

OWNER                TABLE_NAME           PRIVILEGE
-------------------- -------------------- --------------------
SYS                  DBMS_LOCK_ALLOCATED  SELECT      --> here it is
SYS                  DBMS_SCHEDULER       EXECUTE
SYS                  DBMS_STATS_LIB       EXECUTE
SYS                  EXT_DIR              READ
SYS                  EXT_DIR              WRITE
SYS                  SYS                  INHERIT PRIVILEGES

6 rows selected.

SQL>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • Well, I've checked USER_TABS_PRIV for my another user which is able to select from DBMS_LOCK_ALLOCATED: select * from user_tab_privs where owner = 'SYS' and table_name = 'DBMS_LOCK_ALLOCATED' 0 row selected. So I thinks it's something else. – Alexander Apr 12 '23 at 20:14
  • Well, I've checked USER_TAB_PRIVS as well. Grant is there (see edited answer). So, did you follow steps I did, or are you still looking for another way to do that? – Littlefoot Apr 12 '23 at 20:21