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
相关实践学习
数据库实验室挑战任务-初级任务
本场景介绍如何开通属于你的免费云数据库,在RDS-MySQL中完成对学生成绩的详情查询,执行指定类型SQL。
阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
云原生数据仓库AnalyticDB MySQL版是一种支持高并发低延时查询的新一代云原生数据仓库,高度兼容MySQL协议以及SQL:92、SQL:99、SQL:2003标准,可以对海量数据进行即时的多维分析透视和业务探索,快速构建企业云上数据仓库。 了解产品 https://www.aliyun.com/product/ApsaraDB/ads
相关文章
|
2月前
|
Oracle 关系型数据库
Oracle - 表操作语句
Oracle - 表操作语句
22 0
|
5月前
|
Oracle 关系型数据库
Oracle 11g和12c的主要区别
Oracle 11g和12c的主要区别
|
5月前
|
存储 SQL Oracle
Oracle数据库批量删除表、视图、序列、存储过程、函数脚本
Oracle数据库批量删除表、视图、序列、存储过程、函数脚本
55 0
|
5月前
|
Oracle 关系型数据库 数据库
Oracle数据库 查询所有表
Oracle数据库 查询所有表
99 1
|
6月前
|
SQL Oracle 关系型数据库
Oracle 19c 启动和关闭实例保存PDB状态
十年以上 MySQL Oracle DBA从业者,MySQL 5.7 OCP, 微信号: jinjushuke
159 0
|
7月前
|
Oracle 关系型数据库 索引
Toad Oracle Parttion表分析
当一个数据表的数据达到几十亿笔的时候,对整个表做表分析代价较大。
67 0
|
8月前
|
Oracle 关系型数据库
【Oracle】建立关联三个表的视图
【Oracle】建立关联三个表的视图
|
9月前
|
Oracle 关系型数据库
oracle 12c新功能 recover table恢复单个表
不支持sys用户和system表空间的表
|
9月前
|
Oracle 关系型数据库 数据库
|
9月前
|
Oracle 关系型数据库
常用oracle语句-查看表注释表的字段以及注释
常用oracle语句-查看表注释表的字段以及注释

热门文章

最新文章

相关实验场景

更多