如下举例:让C##TEST用户查询v$session的公共数据,但这些数据只能来自CDB(CON_ID=0)、CDB$ROOT(CON_ID=1)和orclpdb2(CON_ID=4):
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB1 READ WRITE NO
4 ORCLPDB2 READ WRITE NO
alter user语句可以在跟级别限制对v$session的查询,从而控制用户只能访问哪些容器的数据,如下:
SQL> conn system/oracle
Connected.
SQL> alter user c##test set container_data=(CDB$ROOT,orclpdb2) for v$session container=current;
User altered.
验证:
SQL> select * from dba_container_data where username='C##TEST';
USERNAME D OWNER OBJECT_NAME A CONTAINER_NAME
--------------- - --------------- --------------- - ---------------------
C##TEST N SYS V_$SESSION N CDB$ROOT
C##TEST N SYS V_$SESSION N ORCLPDB2
使用sys用户执行报错如下:
SQL> conn / as sysdba
Connected.
SQL> alter user c##test set container_data=(CDB$ROOT,orclpdb2) for v$session container=current;
alter user c##test set container_data=(CDB$ROOT,orclpdb2) for v$session container=current
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views
使用sys用户需查询v_$session:
SQL> alter user c##test set container_data=(CDB$ROOT,orclpdb2) for v_$session container=current;
User altered.
使用SYSTEM连接,统计所有容器的会话连接数:
SQL> conn system/oracle
Connected.
SQL> select con_id,type,count(*) from v$session group by con_id,type;
CON_ID TYPE COUNT(*)
---------- ---------- ----------
1 USER 1
0 BACKGROUND 51
3 USER 1
使用c##test用户连接,就无法查看到orclpdb1(con_id=3)的信息,因为它不在授权列表中:
SQL> conn c##test/test
Connected.
SQL> select con_id,type,count(*) from v$session group by con_id,type;
CON_ID TYPE COUNT(*)
---------- ---------- ----------
1 USER 1
0 BACKGROUND 51
注意:这种访问限制只能作用在CDB$ROOT中执行的查询,并且当在orclpdb2中授予c##test执行set container的权限时,该用户就可以切换到orclpdb2中并执行查询会话信息:
SQL> conn / as sysdba
Connected.
SQL> alter session set container=orclpdb2;
Session altered.
SQL> select con_id,type,count(*) from v$session group by con_id,type;
CON_ID TYPE COUNT(*)
---------- ---------- ----------
4 USER 1
0 BACKGROUND 51