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!
|