Oracle数据库容器模式下cdb,pdb环境中，公共与本地 用户权限管理

1.1 公用用户和本地用户

vi $ORACLE_HOME/sqlplus/admin/glogin.sql

– 增加登陆用户提示
define _editor=‘vi’
set sqlprompt "_user’@'_connect_identifier> "

SYS@sgcedudb> select username,common,con_id from cdb_users where username in ('SYS','SYSTEM','HR');

USERNAME                       COMMON        CON_ID
------------------------------ --------- ----------
SYS                            YES                3
SYSTEM                         YES                3
HR                             NO                 3
SYS                            YES                1
SYSTEM                         YES                1

SYS@sgcedudb> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 SGCEDUPDB                      READ WRITE NO
         4 MESUPPDB                       READ WRITE YES

COMMON 列显示 YES 为公共用户，在所有容器包括将来创建的 PDB 中均存在
COMMON 列显示 NO 为本地用户，仅在当前容器中存在

1.1.1 cdb 中创建公用用户

在根容器中尝试创建用户名 cdbuser 报错
SYS@cdb1> create user cdbuser identified by oracle;

默认参数执行了公共用户的前缀 C##

SYS@cdb1> show parameter common_user_prefix
NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
common_user_prefix                   string                            C##

需求如下
Creating the following users
A. USER1,with the following specifications:
i. USER1 must be defined in PDBPROD1, PDBPROD2, PDBPROD3, PDBPROD4, PDBPROD5.
ii. USER1 must also be defined in any future PDB in PRODCDB
iii. Adjust the name of the user.USER1 ,if required
B. USER2 , which is defied only in the PDBPROD1 database.

SYS@PRODCDB> show parameter prefix 
NAME TYPE VALUE 
------------------------------------ ----------- ------------------------------ 
common_user_prefix string C## 
os_authent_prefix string ops$


-- 修改取消公共用户的前缀
SYS@PRODCDB> alter system set common_user_prefix='' scope=spfile; 
System altered. 

SYS@PRODCDB> shutdown immediate 
SYS@PRODCDB> startup 
SYS@PRODCDB> create user user1 identified by oracle; 
User created. 
SYS@PRODCDB> select username,con_id,common from cdb_users where username='USER1'; 
USERNAME 	CON_ID 		COM 
-------------------- ---------- -------
USER1	 	1 			YES 
USER1 		5		 	YES 

SYS@PRODCDB> show pdbs 
CON_ID CON_NAME    OPEN MODE RESTRICTED 
--- ----------    --------    -----------
2 PDB$SEED 			READ ONLY NO 
3 PDBPROD1 			MOUNTED 
4 PDBPROD2 			MOUNTED 
5 PDBPROD3 			READ WRITE NO 
6 PDBPROD4 			MOUNTED 
7 PDBPROD5 			MOUNTED 

SYS@PRODCDB> alter pluggable database PDBPROD1 open; 
Pluggable database altered. 

SYS@PRODCDB> alter session set container=PDBPROD1; 
Session altered. 

SYS@PRODCDB> show pdbs CON_ID
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
     3 		PDBPROD1 					  READ WRITE   NO 
SYS@PRODCDB> create user user2 identified by oracle; User created. 
SYS@PRODCDB> select username,con_id,common from cdb_users where username like 'USER%'; 
USERNAME CON_ID COM 
------ ---- ----------
USER1 3 YES 
USER2 3 NO

SYS@sgcedudb> show parameter common_user_prefix

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
common_user_prefix                   string
-- VALUE C## 表示默认
-- ocm 考试设置默认为空，创建用于时可以不加C##

创建公共用户 c##user01,设置密码为 oracle

SYS@cdb1> create user c##user01 identified by oracle;
SYS@cdb1> select username,common,con_id from cdb_users where username in ('SYS','HR','C##USER01');
SYS@cdb1> col USERNAME format a30
SYS@cdb1> run
  1* select username,common,con_id from cdb_users where username in ('SYS','HR','C##USER01')

USERNAME                       COMMON        CON_ID
------------------------------ --------- ----------
SYS                            YES                1
HR                             YES                3
C##USER01                      YES                4

1.1.2 pdb 中创建本地用户

pdb 中创建本地用户 user02

SYS@pdb1> create user user02 identified by oracle;
SYS@pdb1> select username,common,con_id from cdb_userswhere username in ('SYS','HR','C##USER01','USER02');
cdb 中查看
SYS@cdb1> select username,common,con_id from cdb_userswhere username in ('SYS','HR','C##USER01','USER02');

1.2 公用和本地权限和角色

1.2.1 cdb 中给公用用户授权

[oracle@PRODCDB ~]$ sqlplus c##user01/oracle@cdb1
[oracle@PRODCDB ~]$ sqlplus c##user01/oracle@pdb1
尝试使用前面新创建的公共用户 c##user01 登录 cdb1 和 pdb1,默认没有权限登录

1.2.1.1container=current

在 cdb 中指定 container=current 进行授权

SYS@cdb1> grant connect to c##user01 container=current;

[oracle@PRODCDB ~]$ sqlplus c##user01/oracle@cdb1
[oracle@PRODCDB ~]$ sqlplus c##user01/oracle@pdb1

cdb 有权限登录，pdb 没有权限

1.2.1.2container=all

在 cdb 中授权指定 container=all

SYS@cdb1> grant connect to c##user01 container=all;

cdb pdb 都有权限

1.2.2 pdb 中给本地用户授权

pdb 中对本地用户授权，指定 container=all 报错，指定 container=current 成功
SYS@pdb1> grant connect to user02 container=all;
SYS@pdb1> grant connect to user02 container=current;

1.3 pdb 中解锁 hr 用户方案

[oracle@PRODCDB ~]$ sqlplus sys/oracle@pdb1 as sysdba

SYS@pdb1> alter user hr identified by hr account unlock;
SYS@pdb1> conn hr/hr@prodpdb;
HR@pdb1> select table_name from user_tables;

1.4 pdb 中创建 scott 用户

修改脚本 $ORACLE_HOME/rdbms/admin/utlsampl.sql 可以重建 SCOTT 用方案
但在 PDB 中执行此脚本，无法正常创建表，需要修改脚本内容

[oracle@PRODCDB ~]$ cd $ORACLE_HOME/rdbms/admin

复制生成一个新的脚本

[oracle@PRODCDB admin]$ cp utlsampl.sql utlsampl.sql.pdb1

[oracle@PRODCDB admin]$ vi utlsampl.sql.pdb1

在 pdb 中使用 connect 命令切换用户时，需要使用 tnsnames.ora 中网络服务别名修改脚本内容，在 connect 命令后添加@pdb1

CONNECT SCOTT/tiger@pdb1