Oracle 12c CDB环境管理用户和角色

一、管理用户

1、本地用户

本地用户只能在PDB内创建
SYS@dgpri>select con_id,name,dbid,open_mode from v$pdbs;

CON_ID NAME		     DBID OPEN_MODE
------ --------------- ---------- --------------------
     2 PDB$SEED        4064618166 READ ONLY
     3 PDB01	       3794412324 READ WRITE

SYS@dgpri>alter session set container = pdb01;

Session altered.

SYS@dgpri>show pdbs;

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 3 PDB01			  READ WRITE NO

SYS@dgpri>create user local_test identified by local_test;

User created.

SYS@dgpri>select username,account_status from dba_users where username = 'LOCAL_TEST';

USERNAME	      ACCOUNT_STATUS
--------------------- ----------------------
LOCAL_TEST	      OPEN

SYS@dgpri>grant create session to local_test;

Grant succeeded.

SYS@dgpri>conn local_test/local_test@TNS_PDB01;
Connected.

LOCAL_TEST@TNS_PDB01>show con_name;

CON_NAME
------------------------------
PDB01
LOCAL_TEST@TNS_PDB01>show user;
USER is "LOCAL_TEST"

2、公共用户

根容器下创建的用户是公共用户，会自动传递到PDB中，但是权限需要PDB去授予。

"""CDB公共用户前缀，这个前缀可以进行修改"""
SYS@dgpri>show parameter common_user_prefix;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
common_user_prefix		     string	 C##

不添加前缀不能创建用户
SYS@dgpri>create user public_test1 identified by public_test1;
create user public_test1 identified by public_test1
            *
ERROR at line 1:
ORA-65096: invalid common user or role name


SYS@dgpri>create user c##public_test1 identified by public_test1;

User created.

查询公共用户C#PUBLIC_TEST1
SYS@dgpri>select con_id,username,account_status from cdb_users where username like '%TEST%';

CON_ID USERNAME 	     ACCOUNT_STATUS
------ --------------------- ----------------------
     1 C##PUBLIC_TEST1	     OPEN
     3 LOCAL_TEST	     OPEN
     3 C##PUBLIC_TEST1	     OPEN

SYS@dgpri>show con_name;

CON_NAME
------------------------------
CDB$ROOT
SYS@dgpri>grant create session to c##public_test1;

Grant succeeded.

SYS@dgpri>conn c##public_test1/public_test1;
Connected.
C##PUBLIC_TEST1@dgpri>show con_name;

CON_NAME
------------------------------
CDB$ROOT

"""需要在PDB01中授予登录权限后才使用C##PUPBLIC_TEST1能登录到PDB01"""
C##PUBLIC_TEST1@dgpri>conn c##public_test1/public_test1@TNS_PDB01;
ERROR:
ORA-01045: user C##PUBLIC_TEST1 lacks CREATE SESSION privilege; logon denied


Warning: You are no longer connected to ORACLE.
@>conn / as sysdba;
Connected.
SYS@dgpri>conn sys/oracle@TNS_PDB01 as sysdba;
Connected.
SYS@TNS_PDB01>grant create session to c##public_test1;

Grant succeeded.

SYS@TNS_PDB01>conn c##public_test1/public_test1;
Connected.
C##PUBLIC_TEST1@dgpri>show con_name;

CON_NAME
------------------------------
CDB$ROOT
C##PUBLIC_TEST1@dgpri>conn c##public_test1/public_test1@TNS_PDB01;
Connected.
C##PUBLIC_TEST1@TNS_PDB01>show con_name;

CON_NAME
------------------------------
PDB01

SYS@dgpri>select con_id,grantee,privilege,admin_option from cdb_sys_privs where grantee = 'C##PUBLIC_TEST1';

CON_ID GRANTEE		    PRIVILEGE		 ADM
------ -------------------- -------------------- ---
     1 C##PUBLIC_TEST1	    CREATE SESSION	 NO
     3 C##PUBLIC_TEST1	    CREATE SESSION	 NO

SYS@dgpri>show con_name;

CON_NAME
------------------------------
CDB$ROOT
SYS@dgpri>revoke create session from c##public_test1;

Revoke succeeded.

SYS@dgpri>select con_id,grantee,privilege,admin_option from cdb_sys_privs where grantee = 'C##PUBLIC_TEST1';

CON_ID GRANTEE		    PRIVILEGE		 ADM
------ -------------------- -------------------- ---
     3 C##PUBLIC_TEST1	    CREATE SESSION	 NO

二、管理角色

1、公共角色

"""CDB中只能创建公共角色"""
SYS@dgpri>create role c##public_role1;

Role created.


SYS@dgpri>conn sys/oracle@TNS_PDB01 as sysdba;
Connected.

SYS@TNS_PDB01>conn / as sysdba;
Connected.

SYS@dgpri>select con_id,role,role_id from cdb_roles where role = 'C##PUBLIC_ROLE1';

CON_ID ROLE			    ROLE_ID
------ ------------------------- ----------
     1 C##PUBLIC_ROLE1			107
     3 C##PUBLIC_ROLE1			111


SYS@TNS_PDB01>grant c##public_role1 to local_test;

Grant succeeded.

SYS@TNS_PDB01>select grantee,granted_role,admin_option from dba_role_privs where grantee = 'LOCAL_TEST';

GRANTEE    GRANTED_ROLE 		  ADM
---------- ------------------------------ ---
LOCAL_TEST C##PUBLIC_ROLE1		  NO

该公用角色被授予给pdb01中的local_test用户

2、本地角色

SYS@TNS_PDB01>show con_name;

CON_NAME
------------------------------
PDB01
SYS@TNS_PDB01>create role local_role1 ;

Role created.

SYS@TNS_PDB01>conn / as sysdba;
Connected.
SYS@dgpri>show con_name;

CON_NAME
------------------------------
CDB$ROOT
SYS@dgpri>select con_id,role,role_id from cdb_roles where role = 'LOCAL_ROLE1';

CON_ID ROLE			    ROLE_ID
------ ------------------------- ----------
     3 LOCAL_ROLE1			113

总结：
       CDB中只能创建公共用户及公共角色且公共用户和角色默认都会传递到所有pdb中，创建公共用户和角色时需要添加common_user_prefix(这个前缀可以手动修改)，如果要在PDB中使用公共用户则需要登录PDB进行授权，PDB中只能创建本地用户和角色。