Oracle AI Database 26ai — 字典保护

It looks like the text you’d like translated isn’t included in your message. Could you please paste the content you want translated (excluding the source line you already provided)? Once I have the text, I’ll translate it into Simplified Chinese while preserving the formatting, markdown, and any code blocks.

系统范围的 “ANY” 权限与数据字典

当用户被授予系统范围的 ANY 权限（例如 SELECT ANY TABLE、DROP ANY TABLE）时，用户 仍然无法 查看属于 SYS 模式或内部数据字典表的对象。

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

授予 SELECT ANY DICTIONARY

如果已取消对 SELECT ANY TABLE 的限制，则可以授予 SELECT ANY DICTIONARY 系统权限：

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 及以后 – 某些字典对象仍然隐藏

从 Oracle 12c 开始，即使 SELECT ANY DICTIONARY 也 不能 访问若干内部表，例如：

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

注意： 角色 SELECT_CATALOG_ROLE 也授予对许多数据字典视图的访问权限。

历史 O7_DICTIONARY_ACCESSIBILITY 参数

一种更强大（现已移除）的机制是初始化参数 O7_DICTIONARY_ACCESSIBILITY。
当将其设为 TRUE 时，任何拥有系统范围 ANY 权限的用户都可以在相同的权限级别下读取数据字典。

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: 参数已在 Oracle 19c 中完全删除。

Oracle AI Database 26ai 中的字典保护

Oracle AI Database 26ai 将此限制扩展到一组 Oracle 维护的 用户。这些用户的 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

因此，即使拥有 SELECT ANY TABLE 权限的用户，也无法看到上述任何模式下的对象。

示例 – SYSDG 模式（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

在 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

启用字典保护

字典保护 不能 为任意用户启用；目标必须是 Oracle 维护的账户 且 必须使用 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.

同时满足这两个条件的用户是 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

现在可以打开保护：

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

摘要

理解这些机制有助于 DBA 控制谁可以查看内部元数据，并防止关键系统对象意外泄露。

Timestamp
23:29 2024

在 DGPDB_INT 模式下创建表

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

注意： 用户 c##vahid 可以访问此模式中的表。

启用字典保护

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

---

手动创建 Oracle‑维护的模式

使用隐藏参数 _oracle_script，我们也可以创建一个 Oracle‑维护的模式并为其启用字典保护。

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.