大河向东流就是这么牛牛牛牛牛

書名： Oracle Database 12c Security Cookbook
作者名： Zoran Pavlovi? Maja Veselica
本章字數： 708字
更新時間： 2021-07-02 16:43:16

Granting privileges and roles commonly

The common privilege is a privilege that can be exercised across all containers in a container database. Depending only on the way it is granted, a privilege becomes common or local. When you grant a privilege commonly (across all containers) it becomes a common privilege. Only common users or roles can have common privileges. Only common role can be granted commonly.

Getting ready

For this recipe, you will need to connect to the root container as an existing common user who is able to grant a specific privilege or existing role (in our case, create session, select any table, c##role1, c##role2) to another existing common user (c##john). If you want to try out examples in the How it works section, you should open pdb1 and pdb2.

You will use the following:

Common users c##maja and c##zoran with the dba role granted commonly
Common user c##john
Common roles c##role1 and c##role2

How to do it...

You should connect to the root container as a common user who can grant these privileges and roles (for example, c##maja or system user):
```
SQL> connect c##maja@cdb1
```
Grant a privilege (for example, create session) to a common user (for example, c##john) commonly:
```
c##maja@CDB1> grant create session to c##john container=all;
```
Grant a privilege (for example, select any table) to a common role (for example, c##role1) commonly:
```
c##maja@CDB1> grant select any table to c##role1 container=all;
```
Grant a common role (for example, c##role1) to a common role (for example, c##role2) commonly:
```
c##maja@CDB1> grant c##role1 to c##role2 container=all;
```
Grant a common role (for example, c##role2) to a common user (for example, c##john) commonly:
```
c##maja@CDB1> grant c##role2 to c##john container=all;
```

How it works...

Figure 16

You can grant privileges or common roles commonly only to a common user. You need to connect to the root container as a common user who is able to grant a specific privilege or role.

In Step 2, system privilege, create session is granted to the common user c##john commonly by adding a container=all clause to the grant statement. This means that the user c##john can connect (create session) to the root or any pluggable database in this container database (including all pluggable databases that will be plugged in in the future).

Note

Note that the container = all clause is NOT optional even though you are connected to the root. Unlike during the creation of common users and roles (if you omit container=all, the user or role will be created in all containers commonly), if you omit this clause during the privilege or role grant, the privilege or role will be granted locally and it can be exercised only in root container.

SQL> connect c##john/oracle@cdb1 
 Connected. 
 c##john@CDB1> connect c##john/oracle@pdb1 
 Connected. 
 c##john@PDB1> connect c##john/oracle@pdb2 
 Connected. 
 c##john@PDB2>

In step 3, system privilege select any table is granted to the common role c##role1 commonly. This means that the role c##role1 contains the select any table privilege in all containers (root and pluggable databases):

c##zoran@CDB1> select * from role_sys_privs where role='C##ROLE1'; 
 ROLE PRIVILEGE ADM COM
----------------- ------------------ --- ---
C##ROLE1 SELECT ANY TABLE NO YES 
 c##zoran@CDB1> connect c##zoran/oracle@pdb1 
 Connected. 
 c##zoran@PDB1> select * from role_sys_privs where role='C##ROLE1'; 
 ROLE PRIVILEGE ADM COM
----------------- ------------------ --- ---
C##ROLE1 SELECT ANY TABLE NO YES 
 c##zoran@PDB1> connect c##zoran/oracle@pdb2 
 Connected. 
 c##zoran@PDB2> select * from role_sys_privs where role='C##ROLE1'; 
 ROLE PRIVILEGE ADM COM
----------------- ------------------ --- ---
C##ROLE1 SELECT ANY TABLE NO YES

In Step 4, the common role c##role1 is granted to another common role c##role2 commonly. This means that the role c##role2 has granted the role c##role1 in all containers:

c##zoran@CDB1> select * from role_role_privs where role='C##ROLE2'; 
 ROLE GRANTED_ROLE ADM COM
----------------- ---------------------- ---- ---
C##ROLE2 C##ROLE1 NO YES 
 c##zoran@CDB1> connect c##zoran/oracle@pdb1 
 Connected. 
 c##zoran@PDB1> select * from role_role_privs where role='C##ROLE2'; 
 ROLE GRANTED_ROLE ADM COM
----------------- ---------------------- ---- ---
C##ROLE2 C##ROLE1 NO YES 
 c##zoran@PDB1> connect c##zoran/oracle@pdb2 
 Connected. 
 c##zoran@PDB2> select * from role_role_privs where role='C##ROLE2'; 
 ROLE GRANTED_ROLE ADM COM
----------------- ---------------------- ---- ---
C##ROLE2 C##ROLE1 NO YES

In step 5, the common role c##role2 is granted to the common user c##john commonly. This means that the user c##john has c##role2 in all containers.

Consequently, the user c##john can use the select any table privilege in all containers in this container database:

c##john@CDB1> select count(*) from c##zoran.t1; 
 COUNT(*)
----------
 4 
 c##john@CDB1> connect c##john/oracle@pdb1 
 Connected. 
 c##john@PDB1> select count(*) from hr.employees; 
 COUNT(*)
----------
 107 
 c##john@PDB1> connect c##john/oracle@pdb2 
 Connected. 
 c##john@PDB2> select count(*) from sh.sales; 
 COUNT(*)
----------
 918843

官术网_书友最值得收藏!

Oracle Database 12c Security Cookbook

Granting privileges and roles commonly

Getting ready

How to do it...

How it works...

Note