Manage Users and Privileges:
When connected to a multitenant database the management of users and privileges is a little different to traditional Oracle environments. In multitenant environments there are two types of user.
- Common User : The user is present in all containers (root and all PDBs).
- Local User : The user is only present in a specific PDB. The same username can be present in multiple PDBs, but they are unrelated.
Likewise, there are two types of roles.
- Common Role : The role is present in all containers (root and all PDBs).
- Local Role : The role is only present in a specific PDB. The same role name can be used in multiple PDBs, but they are unrelated.
Some DDL statements have a container clause added to allow them to be directed to the current container or all containers.
Create Common Users :
While creating a common user the following requirements must all be met.
- Must be connected to a common user with the create user privilege.
- The current container must be the root container.
- The username for the common user must be prefixed with “C##” or “c##” and contain only ASCII or EBCDIC characters.
- Username must be unique across all containers.
- The default tablespace, temporary tablespace , quota and profile must all reference objects that exist in all containers.
- You can either specify the container=all clause, or omit it, as this is the default setting when the current container is the root.
now we can see how to create common users with and without the container clause from the root container.
[oracle@ram ~]$ ps -ef | grep pmon oracle 37524 34096 0 18:42 pts/0 00:00:00 grep pmon [oracle@ram ~]$ export ORACLE_SID=dbwr [oracle@ram ~]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Tue Oct 16 18:42:58 2018 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 2516582400 bytes Fixed Size 8795904 bytes Variable Size 671090944 bytes Database Buffers 1828716544 bytes Redo Buffers 7979008 bytes Database mounted. Database opened. SQL> conn / as sysdba Connected.
Create the common user using the CONTAINER clause
SQL> create user c##user1 identified by password1 container=all; User created. SQL> grant create session to c##user1 container=all; Grant succeeded.
Create the common user using the default CONTAINER setting
SQL> create user c##user2 identified by password2; User created. SQL> grant create session to c##user2; Grant succeeded.
Create Local Users:
While creating a local user the following requirements must all be met.
- Must be connected to a user with the create user privilege.
- Username for the local user must not be prefixed with “C##” or “c##”.
- Username must be unique within the PDB.
- You can either specify the container=all clause, or omit it, as this is the default setting when the current container is a PDB.
Switch container while connected to a common user
SQL> conn / as sysdba Connected. SQL> alter pluggable database all open; Pluggable database altered. SQL> alter session set container =pdb5; Session altered.
Create the local user using the CONTAINER clause
SQL> create user user3 identified by password3 container=current; User created. SQL> grant create session to user3 container=current; Grant succeeded.
Connect to a privileged user in the PDB
SQL> conn system/oracle@pdb5 Connected.
Create the local user using the default CONTAINER setting
SQL> create user user4 identified by password4; User created. SQL> grant create session to user4; Grant succeeded.
Create Common Roles:
Roles can be common or local. All Oracle-supplied roles are common and therefore available in the root container and all PDBs. Common roles can be created, provided the following conditions are must.
- You must be connected to a common user with create role and the set container privileges granted commonly.
- The current container must be the root container.
- The role name for the common role must be prefixed with “C##” or “c##” and contain only ASCII or EBCDIC characters.
- The role name must be unique across all containers.
- The role is created with the container=all clause
Now we can see how to create a common role and grant it to a common and local user.
Create the common role
SQL> conn / as sysdba Connected. SQL> create role c##role1; Role created. SQL> grant create session to c##role1; Grant succeeded.
Grant it to a common user
SQL> conn / as sysdba Connected. SQL> grant c##role1 to c##user1 container=all; Grant succeeded. SQL> alter pluggable database all open; Pluggable database altered.
Grant it to a local user
SQL> alter session set container =pdb5; Session altered. SQL> grant c##role1 to user3; Grant succeeded. SQL>
Create Local Roles:
Local roles are created in a similar manner to pre-12c databases. Each PDB can have roles with matching names, since the scope of a local role is limited to the current PDB. The following conditions are must.
- Must be connected to a user with the create role privilege.
- If you are connected to a common user, the container must be set to the local PDB.
- Role name for the local role must not be prefixed with “C##” or “c##”.
- Role name must be unique within the PDB.
we can see now how to create local a role and grant it to a common user and a local user.
SQL> conn / as sysdba Connected. SQL>
Switch container
SQL> alter session set container = pdb5; Session altered.
Alternatively , we can connect pluggable database through local or common user with PDB service.
Create the common role
SQL> create role role1; Role created. SQL> grant create session to role1; Grant succeeded.
Grant it to a common user
SQL> grant role1 to c##user1; Grant succeeded.
Grant it to a local user
SQL> grant role1 to user3; Grant succeeded.
Granting Roles and Privileges to Common and Local Users:
The basic difference between a local and common grant is the value used by the container clause.
Common grants
SQL> conn / as sysdba Connected. SQL> grant create session to c##user1 container=all; Grant succeeded. SQL> grant create session to c##role1 container=all; Grant succeeded. SQL> grant c##role1 to c##user1 container=all; Grant succeeded. SQL>
Local grants
SQL> conn system/oracle@pdb5 Connected. SQL> grant create session to user3; Grant succeeded. SQL> grant create session to role1; Grant succeeded. SQL> grant role1 to user3; Grant succeeded.
Catch Me On:- Hariprasath Rajaram Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/ Facebook:https://www.facebook.com/HariPrasathdba FB Group:https://www.facebook.com/groups/894402327369506/ FB Page: https://www.facebook.com/dbahariprasath/? Twitter: https://twitter.com/hariprasathdba
