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/，如需轉載，請註明出處，否則將追究法律責任。