创建公共用户:
SQL> create user C##TEST identified by test container=all;
User created.
SQL> select username,common from dba_users where oracle_maintained='N';
USERNAME COM
------------------
C##TEST YES
测试公共用户默认无法连接PDB:
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB1 MOUNTED
4 ORCLPDB2 READ WRITE NO
SQL> alter session set container=orclpdb2;
Session altered.
SQL> select username,common from dba_users where oracle_maintained='N';
USERNAME COM
------------ ---
PDBADMIN NO
C##TEST YES
TEST NO
SQL> conn C##TEST/test@//localhost/orclpdb2
ERROR:
ORA-01045: user C##TEST lacks CREATE SESSION privilege; logon denied
Warning: You are no longer connected to ORACLE.
连接PDB orclpdb2后,赋权create session权限给C##TEST:
SQL> conn / as sysdba
Connected.
SQL> alter session set container=orclpdb2;
Session altered.
SQL> grant create session to C##TEST;
Grant succeeded.
此时,用户C##TEST可以正常连接PDB orclpdb2:
SQL> conn C##TEST/test@//localhost/orclpdb2
Connected.
SQL> show con_id
CON_ID
------------------------------
4
但是用户C##TEST无法连接PDB orclpdb1:
SQL> conn / as sysdba
Connected.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB1 MOUNTED
4 ORCLPDB2 READ WRITE NO
SQL> alter session set container=orclpdb1;
Session altered.
SQL> startup
Pluggable Database opened.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 ORCLPDB1 READ WRITE NO
SQL> conn / as sysdba
Connected.
SQL> conn C##TEST/test@//localhost/orclpdb1
ERROR:
ORA-01045: user C##TEST lacks CREATE SESSION privilege; logon denied
Warning: You are no longer connected to ORACLE.
用户C##TEST连接CDB也需要单独赋权:
SQL> conn / as sysdba
Connected.
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
SQL> conn C##TEST/test@//localhost/orcl
ERROR:
ORA-01045: user C##TEST lacks CREATE SESSION privilege; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba
Connected.
SQL> grant create session to C##TEST ;
Grant succeeded.
SQL> conn C##TEST/test@//localhost/orcl
Connected.
SQL> show con_id
CON_ID
------------------------------
1
但是如果在CDB$ROOT中执行grant语句时添加了container=all,则C##TEST用户可以连接该CDB及其他所有的PDB:
SQL> conn / as sysdba
Connected.
SQL> grant create session to C##TEST container=all;
Grant succeeded.
SQL> conn C##TEST/test@//localhost/orclpdb1
Connected.
SQL> show con_id
CON_ID
------------------------------
3
SQL> show con_name
CON_NAME
------------------------------
ORCLPDB1