Oracle 12c之后CDB上创建公共用户无法访问PDB 上v$session表内容-阿里云开发者社区

开发者社区> 数据库> 正文

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

简介: Oracle 12c CDB模式下,创建公共用户,登录到CDB,无法查看PDB下一些表的信息,如v$session,登录到具体PDB才能看到,这篇文章将介绍如何给公共用户授权解决这个问题。

一、问题描述

        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

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章