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