在PDB中赋予公共用户create session权限，默认该公共用户仅能连接该PDB，无法连接其他PDB

最新推荐文章于 2023-09-21 11:56:55 发布

lk_db

最新推荐文章于 2023-09-21 11:56:55 发布

阅读量879

点赞数

CC 4.0 BY-SA版权

分类专栏： Oracle 文章标签： 12c PDB CREATE SESSION CDB

本文链接：https://blog.youkuaiyun.com/lk_db/article/details/89447107

Oracle 专栏收录该内容

120 篇文章

订阅专栏

本文介绍了如何在12c的PDB中为公共用户C##TEST授予CREATE SESSION权限，使其只能连接特定的PDB。默认情况下，C##TEST无法连接其他PDB，通过在CDB$ROOT中执行GRANT语句并指定CONTAINER=ALL，用户将能够连接到CDB及其所有PDB。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

创建公共用户：
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