ITPub博客

首页 > 数据库 > Oracle > 12c多租户架构里给Common/local user赋权的几种可能场景

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

原创 Oracle 作者:oliseh 时间:2014-09-25 00:01:09 0 删除 编辑

 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$ROOTexecuted 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 PDBsexecuted in CDB$ROOT

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

 

3、 privilege granted locally to common user/role only in CDB$ROOTexecuted 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 PDBsexecuted 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 PDBexecuted in PDB

grant insert any table to c##guser1;

 

 

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

grant c##grole1 to scott;

 

7、 local role granted locally to common user/role in PDBexecuted 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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
不仅仅专注Oracle database技术, member of SHOUG

注册时间:2014-04-06

  • 博文量
    128
  • 访问量
    1617267