Oracle 12c之后CDB上创建公共用户无法访问PDB 上v$session表内容

一、问题描述

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/[email protected]: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/[email protected]: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/[email protected]: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/[email protected]: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/[email protected]: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

Oracle 12c之后CDB上创建公共用户无法访问PDB 上v$session表内容

一、问题描述

二、操作复现

1、创建公共账号

2、登陆新创建的账号，查看权限

3、账号c##test权限测试

3.1、模式PDB业务用户登陆

3.2、登陆c##test到CDB查看session信息

3.3、登陆sys到CDB查看信息

3.4、尝试系统自带的dbsnmp用户是否能正常查看

3.5、问题处理

三、参考网址

继续阅读

ASP编程中20个非常有用的例子

龙珠训练营task04

阿里云天池龙珠计划SQL训练营打卡

阿里云天池龙珠计划SQL训练营day1

赋予用户dba权限--解释以上：

实验楼sql进阶之成绩管理系统的数据操作(window)

Oracle的基本操作

为什么要选择UniDAC

SQL优化SQL语句优化的目的

JAVA高效编程指南

windows不能在本地计算机上运行oracleDbConsoleorcl

关于SQL语言

SQL语言基础：常用的数据查询语句

neo4j之cypher使用文档

sqlServer根据经纬查距离

Oracle 批量查询传入List 返回List