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