When we grant a system ANY privilege such as SELECT ANY TABLE, DROP ANY TABLE, and so on to a user, that user still cannot access objects in the SYS schema or Data Dictionary tables:
SQL*Plus: Release 10.1.0.4.2 - Production on Tue Jan 9 10:38:31 2024
SQL> show user
USER is "SYS"
SQL> create table sys.tb as select * from dual;
Table created.
SQL> create user vahid identified by a;
User created.
SQL> grant create session,select any table to vahid;
Grant succeeded.
SQL> conn vahid/a
Connected.
SQL> select * from sys.tb;
ORA-00942: table or view does not exist
SQL> select * from v$datafile;
ORA-00942: table or view does not exist
If we decide to remove this restriction — at least for the SELECT ANY TABLE privilege—we can grant the SELECT ANY DICTIONARY privilege to the user:
SQL> grant SELECT ANY DICTIONARY to vahid;
Grant succeeded.
SQL> conn vahid/a
Connected.
SQL> select * from sys.tb;
D
-
X
SQL> select file# from v$datafile where rownum=1;
FILE#
----------
1
However, starting from Oracle 12c, access to some Data Dictionary tables is not possible even with the SELECT ANY DICTIONARY privilege. Some of these tables include:
USER$, ENC$ , DEFAULT_PWD$, LINK$, USER_HISTORY$, CDB_LOCAL_ADMINAUTH$, XS$VERIFIERS
Note: the SELECT_CATALOG_ROLE role can also grant access to Data Dictionary views.
A more powerful mechanism than SELECT ANY DICTIONARY was the parameter o7_dictionary_accessibility. When this parameter was set to TRUE, any user who had a system ANY privilege could access Data Dictionary tables at the same privilege level (the default value was FALSE):
SQL*Plus: Release 10.1.0.4.2 - Production on Tue Jan 9 11:26:57 2024
SQL> show parameter o7_dictionary_accessibility
NAME TYPE VALUE
------------------------------------ ----------- -------------------------
O7_DICTIONARY_ACCESSIBILITY boolean FALSE
SQL> alter system set O7_DICTIONARY_ACCESSIBILITY=true scope=spfile;
System altered.
SQL> startup force;
SQL> create user vahid identified by a;
User created.
SQL> grant create session,select any table to vahid;
Grant succeeded.
SQL> conn vahid/a
Connected.
SQL> select file# from v$datafile where rownum=1;
FILE#
----------
1
SQL> select * from sys.tb;
D
-
X
This parameter has been completely removed starting from Oracle 19c.
Dictionary Protection in Oracle AI Database 26ai
In Oracle AI Database 26ai, Oracle has expanded the scope of these restrictions and enabled Dictionary Protection for the following users as well:
SQL> select username,ORACLE_MAINTAINED from dba_users where dictionary_protected='YES';
USERNAME ORACLE_MAINTAINED
------------------ --------------------
SYSRAC Y
XS$NULL Y
LBACSYS Y
CTXSYS Y
DVF Y
DVSYS Y
AUDSYS Y
GSMADMIN_INTERNAL Y
GGSHAREDCAP Y
XDB Y
SYSBACKUP Y
SYSKM Y
SYSDG Y
13 rows selected.
Therefore, even if a user has a system ANY privilege, they cannot access tables in any of the schemas listed above solely based on those privileges.
For example, we create a table in the SYSDG schema. Since Data Dictionary Protection is enabled for this schema, its tables—similar to Data Dictionary tables—will not be visible to the user c##vahid, even though this user has the SELECT ANY TABLE privilege:
SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Tue Jan 9 13:25:56 2024
SQL> create user c##vahid identified by a;
User created.
SQL> grant create session,select any table to c##vahid;
Grant succeeded.
SQL> grant unlimited tablespace to SYSDG;
Grant succeeded.
SQL> create table SYSDG.tb as select * from dual;
Table created.
SQL> conn c##vahid/a
Connected.
SQL> select * from SYSDG.tb;
ORA-00942: table or view does not exist
However, the same scenario does not fail in Oracle 21c:
SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Tue Jan 9 13:25:56 2024
SQL> create user c##vahid identified by a;
User created.
SQL> grant create session,select any table to c##vahid;
Grant succeeded.
SQL> grant unlimited tablespace to SYSDG;
Grant succeeded.
SQL> create table SYSDG.tb as select * from dual;
Table created.
SQL> conn c##vahid/a
Connected.
SQL> select * from SYSDG.tb;
D
-
X
Enabling Dictionary Protection
The Data Dictionary Protection feature cannot be enabled for arbitrary users. The user must be Oracle-maintained:
SQL> alter user C##vahid enable dictionary protection;
ORA-40374: Dictionary protection feature is supported for only Oracle maintained users.
Additionally, not all Oracle-maintained users qualify. The schema must also use NO AUTHENTICATION:
SQL> alter user SYSTEM enable dictionary protection;
ORA-40369: Only NO AUTHENTICATION user can be marked as dictionary protected.
The user DGPDB_INT meets these requirements:
SQL> select USERNAME,dictionary_protected,AUTHENTICATION_TYPE,ORACLE_MAINTAINED from dba_users where username=’DGPDB_INT’;
USERNAME DIC AUTHENTI ORACLE_MAINTAINED
---------- --- -------- ------------------
DGPDB_INT NO NONE Y
Before enabling Dictionary Protection, we test access for user c##vahid:
SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Tue Jan 9 14:23:29 2024
SQL> create table DGPDB_INT.tb as select * from dual;
Table created.
SQL> conn c##vahid/a
Connected.
SQL> select * from DGPDB_INT.tb;
D
-
X
User c##vahid can access tables in this schema. Now we enable Dictionary Protection:
SQL> alter user DGPDB_INT enable dictionary protection;
User altered.
SQL> conn c##vahid/a
Connected.
SQL> select * from DGPDB_INT.tb;
ORA-00942: table or view does not exist
Creating an Oracle-Maintained Schema Manually
Using the hidden parameter _oracle_script, we can also create an Oracle-maintained schema and enable Dictionary Protection for it:
SQL> show con_name
CON_NAME
--------------------
CDB$ROOT
SQL> alter session set "_oracle_script"=true;
Session altered.
SQL> create user vahid;
User created.
SQL> select USERNAME,dictionary_protected,AUTHENTICATION_TYPE,ORACLE_MAINTAINED from dba_users where username='VAHID';
USERNAME DIC AUTHENTI ORACLE_MAINTAINED
---------- --- -------- --------------------
VAHID NO NONE Y
SQL> alter user VAHID enable dictionary protection;
User altered.
Top comments (0)