一、管理用户
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中只能创建本地用户和角色。