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

本文介绍了如何在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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值