iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Training

    Home      .Services     Login       Start Learning     Certification      .                 .Share your BELIEF(s)...

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       Email2aFriend    |

 

Oracle 12c New Features

Online Oracle Training

LESSON 17

How to manage roles in a CDB (Multitenant Container) and PDBs (Pluggable Databases)

 

The same as users, you can have common roles or local roles in your pluggable databases. A common role has to start with c## or C## prefix. All roles in the root container are common. You cannot have local role in the root container. A common role will be created in the root container and can be used in all PDBs. They can be modified at a PDB level. A common role cannot be created in a pluggable database. A local role can only be created in PDBs.

 

Let us list the DBA common role in CDB or the root container.

SQL> SELECT role, common, con_id

   2   FROM cdb_roles

   3   ORDER by 3;

 

ROLE

COMMON

CON_ID

DBA

YES

1

DBA

YES

2

DBA

YES

3

DBA

YES

4

DBA

YES

5

Notice that you cannot have any local role in the root container (only common role in the root container).

 

Example of creating a common role in the root container

SQL> CREATE ROLE c##myrole CONTAINER=ALL;

Role created.

 

Granting the system privileges to it in the root container

SQL> GRANT CREATE TABLE, UNLIMITED TABLESPACE TO c##myrole CONTAINER=ALL;

 

You are not allow to create a local role in the root container

SQL> SQL> CREATE ROLE myrole CONTAINER=CURRENT;

ERROR at line 1:

ORA-65049: creation of local user or role is not allowed in CDB$ROOT

 

List the roles in the mypdb1 container

Connect to mypdb1

SQL> CONNECT sys/mypass@mypdb1 as sysdba

 

SQL> SELECT role, common, con_id

   2   FROM cdb_roles;

--or--

SQL> SELECT role, common

   2   FROM dba_roles;

 

You will find the same output.

 

Create a local role

SQL> CREATE ROLE local_role4mypdb1 CONTAINER=CURRENT;

Role created.

 

Examples of granting roles or system privileges to it

SQL> GRANT connect, resource TO local_role4mypdb1;

SQL> GRANT CREATE ANY SEQUENCE TO local_role4mypdb1 CONTAINER=CURRENT;

SQL> GRANT CREATE VIEW TO c##myrole CONTAINER=CURRENT;

SQL> GRANT CREATE VIEW TO c##myrole CONTAINER=ALL;

You should be the root container to use ALL.

You will get the following error

ORA-65050: Common DDLs only allowed in CDB$ROOT

 

Revoke it

SQL> REVOKE connect FROM local_role4mypdb1;

 

Good Luck!

 

 

Google
 
Web web site