CODEWITHSUNDEEP
databaseoracle-databasesql-grantsystem-tables
Alexander
Alexander

Reputation: 1

Oracle: Grants for select from SYS.DBMS_LOCK_ALLOCATED

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.

Upvotes: -1

Views: 468

Answers (1)

Littlefoot
Littlefoot

Reputation: 142705

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>

Upvotes: 0

Related Questions