Oracle AI Database 26ai — Dictionary Protection

Source: Dev.to

System‑wide “ANY” privileges and the Data Dictionary

When a user is granted a system‑wide ANY privilege (e.g., SELECT ANY TABLE, DROP ANY TABLE), the user still cannot see objects that belong to the SYS schema or the internal 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

---

Granting SELECT ANY DICTIONARY

If the restriction is lifted for SELECT ANY TABLE, the SELECT ANY DICTIONARY system privilege can be granted:

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

---

Oracle 12c and later – Some dictionary objects stay hidden

Starting with Oracle 12c, even SELECT ANY DICTIONARY does not give access to a number of internal tables, for example:

USER$, ENC$, DEFAULT_PWD$, LINK$, USER_HISTORY$, CDB_LOCAL_ADMINAUTH$, XS$VERIFIERS

Note: The role SELECT_CATALOG_ROLE also grants access to many Data Dictionary views.

---

The historic O7_DICTIONARY_ACCESSIBILITY parameter

A more powerful (now‑removed) mechanism was the initialization parameter O7_DICTIONARY_ACCESSIBILITY.
When set to TRUE, any user that possessed a system‑wide ANY privilege could read the Data Dictionary at the same privilege level.

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

Removed: The parameter was completely eliminated in Oracle 19c.

---

Dictionary Protection in Oracle AI Database 26ai

Oracle AI Database 26ai extends the restriction to a set of Oracle‑maintained users. These users are marked with DICTIONARY_PROTECTED = 'YES'.

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

Consequently, even a user with SELECT ANY TABLE cannot see objects that belong to any of the schemas above.

Example – SYSDG schema (Oracle AI Database 26ai)

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

Same scenario on Oracle 21c (no protection)

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

Dictionary Protection cannot be enabled for arbitrary users; the target must be an Oracle‑maintained account and must use NO AUTHENTICATION.

SQL> ALTER USER C##vahid ENABLE DICTIONARY PROTECTION;
ORA-40374: Dictionary protection feature is supported for only Oracle maintained users.

SQL> ALTER USER SYSTEM ENABLE DICTIONARY PROTECTION;
ORA-40369: Only NO AUTHENTICATION user can be marked as dictionary protected.

A user that satisfies both conditions is DGPDB_INT:

SQL> SELECT username,
           dictionary_protected,
           authentication_type,
           oracle_maintained
      FROM dba_users
     WHERE username = 'DGPDB_INT';

USERNAME   DICTIONARY_PROTECTED AUTHENTICATION_TYPE ORACLE_MAINTAINED
---------- -------------------- ------------------- --------------------
DGPDB_INT  NO                   NONE                Y

Now the protection can be turned on:

SQL> ALTER USER DGPDB_INT ENABLE DICTIONARY PROTECTION;
User altered.

---

Summary

Understanding these mechanisms helps DBAs control who can see internal metadata and prevents accidental exposure of critical system objects.

Timestamp
23:29 2024

---

Create a table in the DGPDB_INT schema

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

Note: User c##vahid can access tables in this schema.

---

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.