一、问题描述
Oracle 12c CDB模式下，在CDB中创建一个公共用户想要监控所有PDB的信息，发现在授予了DBA、CDB_DBA，SELECT ANY DICTIONARY 等权限后仍然不可以查看，只有登陆到具体的PDB上才能查看，这样在一些场景就有可能受到约束，下面内容将实验如何授权解决这个问题。
二、操作复现
本次测试环境是Oracle 12.1.0.2.0单机。
1、创建公共账号
[oracle@vbox66 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Mon Sep 21 21:57:45 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> create user c##test identified by test container=all;
User created.
SQL> grant dba,cdb_dba,resource,connect to c##test container=all;
Grant succeeded.
SQL> grant SELECT ANY DICTIONARY to c##test container=all;
Grant succeeded.
SQL>
2、登陆新创建的账号，查看权限
SQL> SELECT GRANTED_ROLE FROM dba_role_privs a WHERE a.GRANTEE = upper('c##test');
GRANTED_ROLE
--------------------------------------------------------------------------------
DBA
CDB_DBA
RESOURCE
CONNECT
SQL> select * from user_sys_privs order by 1,2;
USERNAME PRIVILEGE ADM COM
--------------- ---------------------------------------- --- ---
C##TEST SELECT ANY DICTIONARY NO YES
C##TEST UNLIMITED TABLESPACE NO YES
SQL> 3、账号c##test权限测试
3.1、模式PDB业务用户登陆
[oracle@vbox66 ~]$ sqlplus wrtest/wrtest@192.168.3.66:1521/wrtest
SQL*Plus: Release 12.1.0.2.0 Production on Mon Sep 21 22:05:06 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Mon Sep 21 2020 22:04:28 +08:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> select userenv('sid') from dual;
USERENV('SID')
--------------
75
SQL> 3.2、登陆c##test到CDB查看session信息
[oracle@vbox66 ~]$ sqlplus c##test/test@192.168.3.66:1521/orcl
SQL*Plus: Release 12.1.0.2.0 Production on Mon Sep 21 22:06:40 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Mon Sep 21 2020 22:01:05 +08:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> set line 150
SQL> col USERNAME for a15
SQL> col SCHEMANAME for a15
SQL> col MACHINE for a20
SQL> select SID,SERIAL#,USERNAME,STATUS,SCHEMANAME,MACHINE,to_char(LOGON_TIME,'YYYY-MM-DD HH24:MI:SS') from v$session where USERNAME!='SYS';
SID SERIAL# USERNAME STATUS SCHEMANAME MACHINE TO_CHAR(LOGON_TIME,
---------- ---------- --------------- -------- --------------- -------------------- -------------------
48 1264 C##TEST ACTIVE C##TEST vbox66 2020-09-21 22:06:40
SQL> 从上面信息可以发现，这里没有之前wrtest这个用户的信息，下面登陆sys用户查看信息。
[oracle@vbox66 ~]$ sqlplus c##test/test@192.168.3.66:1521/wrtest
SQL*Plus: Release 12.1.0.2.0 Production on Mon Sep 21 22:17:52 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Mon Sep 21 2020 22:06:40 +08:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> set line 150
SQL> col USERNAME for a15
SQL> col SCHEMANAME for a15
SQL> col MACHINE for a20
SQL> select SID,SERIAL#,USERNAME,STATUS,SCHEMANAME,MACHINE,to_char(LOGON_TIME,'YYYY-MM-DD HH24:MI:SS') from v$session where USERNAME!='SYS';
SID SERIAL# USERNAME STATUS SCHEMANAME MACHINE TO_CHAR(LOGON_TIME,
---------- ---------- --------------- -------- --------------- -------------------- -------------------
75 6431 WRTEST INACTIVE WRTEST vbox66 2020-09-21 22:05:06
90 43582 C##TEST ACTIVE C##TEST vbox66 2020-09-21 22:17:52
SQL>
当c##test登录到具体PDB的时候是可以看到wrtest这个用户的session信息的。
3.3、登陆sys到CDB查看信息
[oracle@vbox66 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Mon Sep 21 22:09:18 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> set line 150
SQL> col USERNAME for a15
SQL> col SCHEMANAME for a15
SQL> col MACHINE for a20
SQL> select SID,SERIAL#,USERNAME,STATUS,SCHEMANAME,MACHINE,to_char(LOGON_TIME,'YYYY-MM-DD HH24:MI:SS') from v$session where USERNAME!='SYS';
SID SERIAL# USERNAME STATUS SCHEMANAME MACHINE TO_CHAR(LOGON_TIME,
---------- ---------- --------------- -------- --------------- -------------------- -------------------
48 1264 C##TEST INACTIVE C##TEST vbox66 2020-09-21 22:06:40
75 6431 WRTEST INACTIVE WRTEST vbox66 2020-09-21 22:05:06
SQL> 从上面信息可以看出，sys用户是可以看到wrtest登录到PDB的信息。
3.4、尝试系统自带的dbsnmp用户是否能正常查看
[oracle@vbox66 ~]$ sqlplus dbsnmp/dbsnmp@192.168.3.66:1521/orcl
SQL*Plus: Release 12.1.0.2.0 Production on Mon Sep 21 22:14:21 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Fri Sep 18 2020 14:57:56 +08:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> SELECT GRANTED_ROLE FROM dba_role_privs a WHERE a.GRANTEE = upper('dbsnmp');
GRANTED_ROLE
--------------------------------------------------------------------------------
DBA
CDB_DBA
OEM_MONITOR
DBA
DV_MONITOR
SQL> set line 150
SQL> col USERNAME for a15
SQL> col SCHEMANAME for a15
SQL> col MACHINE for a20
SQL> select SID,SERIAL#,USERNAME,STATUS,SCHEMANAME,MACHINE,to_char(LOGON_TIME,'YYYY-MM-DD HH24:MI:SS') from v$session where USERNAME!='SYS';
SID SERIAL# USERNAME STATUS SCHEMANAME MACHINE TO_CHAR(LOGON_TIME,
---------- ---------- --------------- -------- --------------- -------------------- -------------------
48 1264 C##TEST INACTIVE C##TEST vbox66 2020-09-21 22:06:40
62 46829 DBSNMP ACTIVE DBSNMP vbox66 2020-09-21 22:14:21
75 6431 WRTEST INACTIVE WRTEST vbox66 2020-09-21 22:05:06
SQL> dbsnmp在和c##test权限类似的情况下却可以查看wrtest登录到PDB的session信息，之后尝试导出dbsnmp这个用户，解析dmp文件查看dbsnmp的授权语句，结果发现导出失败，原因是oracle内置账号不可以导出。
3.5、问题处理
如上面信息所示，当创建监控账号监控当前CDB下所有PDB信息的时候这个c##test就不能满足要求，查看相关资料，发现通过container_data可以满足要求，操作如下：
sys用户登陆CDB，执行如下语句：
SQL> alter user c##test set container_data=(CDB$ROOT,wrtest) for v_$session container=current;
User altered.
SQL>
wrtest用户退出重新登陆，查看v$session信息
[oracle@vbox66 ~]$ sqlplus c##test/test@192.168.3.66:1521/orcl
SQL*Plus: Release 12.1.0.2.0 Production on Mon Sep 21 22:27:05 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Mon Sep 21 2020 22:17:52 +08:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> set line 150
SQL> col USERNAME for a15
SQL> col SCHEMANAME for a15
SQL> col MACHINE for a20
SQL> select SID,SERIAL#,USERNAME,STATUS,SCHEMANAME,MACHINE,to_char(LOGON_TIME,'YYYY-MM-DD HH24:MI:SS') from v$session where USERNAME!='SYS';
SID SERIAL# USERNAME STATUS SCHEMANAME MACHINE TO_CHAR(LOGON_TIME,
---------- ---------- --------------- -------- --------------- -------------------- -------------------
48 44688 C##TEST ACTIVE C##TEST vbox66 2020-09-21 22:27:05
62 46829 DBSNMP INACTIVE DBSNMP vbox66 2020-09-21 22:14:21
75 6431 WRTEST INACTIVE WRTEST vbox66 2020-09-21 22:05:06
90 43582 C##TEST INACTIVE C##TEST vbox66 2020-09-21 22:17:52
SQL>
此时，c##test账号已经可以满足需求，当c##test需要访问很多表时该怎么处理呢，需要执行多次上面那个语句吗？，可以通过执行下面语句实现：
ALTER USER c##test SET CONTAINER_DATA=ALL CONTAINER=CURRENT;
注意，也是sys登陆CDB执行
三、参考网址
https://docs.oracle.com/database/121/SQLRF/statements_4003.htm#i2058207