12c多租户架构里给Common/local user赋权的几种可能场景

 12c 多租户架构引入了common user、local user的概念后，对于用户权限的控制没有原先那么简单明了，本文列举了12c多租户架构中能够实现的八种赋权的场景，旨在更清晰的了解12c所带来的用户权限管理上的变化

首先在CDB$ROOT上创建Common user和 common role：

create user c##guser1 identified by chh;

select * from dba_roles;

create role c##grole1;   ----创建common role，不指定container，默认为container=ALL

 

1、 common role granted locally to common user only in CDB$ROOT，executed in CDB$ROOT

grant c##grole1 to c##guser1; ---不指定container，默认为container=current

 

2、 common role granted commonly to common user in CDB and all its PDBs，executed in CDB$ROOT

grant c##grole1 to c##guser1 container=all;

 

3、 privilege granted locally to common user/role only in CDB$ROOT，executed in CDB$ROOT

SQL> grant select any table to c##grole1;  –-不指定container，默认为container=current

 

Grant succeeded

 

select * from dba_sys_privs where grantee='C##GROLE1';

 

4、 privilege granted commonly to common user/role in CDB and all its PDBs，executed in CDB$ROOT

SQL> grant select any table to c##grole1 container=all;  

 

Grant succeeded

 

select * from dba_sys_privs where grantee='C##GROLE1'; ---和上一结果相比多了一行Common=yes

 

5、 privilege granted locally to common user/role in PDB，executed in PDB

grant insert any table to c##guser1;

 

 

6、 common role granted locally to local user/role in PDB，executed in PDB

grant c##grole1 to scott;

 

7、 local role granted locally to common user/role in PDB，executed in PDB

create role lrole1;

grant lrole1 to c##guser1;

 

8、 if a common role is granted commonly(grant … container=ALL) from CDB to all it PDBs, then it should only be revoked from CDB

 

select * from dba_role_privs where grantee='C##GUSER1'

 

SQL> revoke C##GROLE1 from C##GUSER1;   --- if revoked from PDB,ORA errors appears

 

revoke C##GROLE1 from C##GUSER1

 

ORA-01951: ROLE 'C##GROLE1' not granted to 'C##GUSER1'

 

SQL> revoke c##grole1 from c##guser1 container=ALL; ---should be revoked from CDB$ROOT with container=ALL

 

Revoke succeeded

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/53956/viewspace-1280016/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/53956/viewspace-1280016/