Managing Users / Roles / Privileges in CDB & PDB.

Users

Common Users  :-

a)    Common user is a user which is created on the root container.

b)    When the common user is created in root container it will get propagated to all the PDBS.

c)    If the PDB if closed then user will be synced when the PDB got opened.

d)    The container clause will be defaulted to ALL when the user is created in the root container.

e)    The common user should container SET CONTAINER privilege to switch to different container.

f)    Common user should be created with prefix as c##.

Eg.

SQL> select con_id, name, open_mode from V$pdbs;

    CON_ID NAME                           OPEN_MODE

---------- ------------------------------ ----------

         2 PDB$SEED                       READ ONLY

         3 PDB_PURCHASE                   READ WRITE

         4 PDB_HR                         READ WRITE

SQL> create user c##manzoor identified by ahamed;

User created.

SQL> select con_id, username, account_status from cdb_users where username = 'C##MANZOOR';

    CON_ID USERNAME               ACCOUNT_STATUS                 

---------- --------------------- ---------------

         4 C##MANZOOR            OPEN

         3 C##MANZOOR            OPEN

         1 C##MANZOOR            OPEN

The Common user C##MANZOOR is created in root container and it is automatically populated to all the PDB’s.

Local User :-

a)    Local user is a user which is created on the PDB, it is as similiar to the user belong to non-cdb.

b)    Local user cannot be created on root container.

c)    Local user is authorized to connect only to the pdbs on which he has the privileges.

SQL> create user manzoor identified by ahamed;

User created.

Common Roles

a) Roles are denoted as group of privileges, a common role can only be created in root container.

b) It has to be created with prefix as c##.

SQL> select con_id, name, open_mode from V$pdbs;

    CON_ID NAME                           OPEN_MODE

---------- ------------------------------ ----------

         2 PDB$SEED                       READ ONLY

         3 PDB_PURCHASE                   READ WRITE

         4 PDB_HR                         READ WRITE

SQL> create role c##admin_role ;

Role created.

SQL> select con_id, role, COMMON from cdb_roles where role = 'C##ADMIN_ROLE';

    CON_ID ROLE                                     COM

---------- ---------------------------------------- ---

         1 C##ADMIN_ROLE                            YES

         4 C##ADMIN_ROLE                            YES

         3 C##ADMIN_ROLE                            YES

When the common role is created is automatically syncronised to all the PDB's which are open read write or restricted.

If any pdb's is closed then it will get sync once the PDB is open.

Any conflicts will get reported in pdb_plug_in_violations views.

Local Roles

-----------

a)    Local role is a role which is created on the PDB, it is as similiar to the roles belong to non-cdb.

b)    Local roles cannot be created on root container.

c)    Local roles cannot be granted to users on other containers.

Privileges

----------

Common Privileges

-----------------

a) Common privileges define how the privileges is granted to the user/roles the CONTAINER caluse is the key.

   where the CONTAINER=ALL denotes the privilege should be granted as common and

           CONTAINER=CURRENT denotes the privilege should be granted to the local container.

Local privilege

---------------

a) Similar to non-cdb.

Scenario 1

----------

a) A User should be created who can able to connect to all the pdb's in the CDB, and select catalog role.

SQL> create user c##all_app identified by temp123#;

User created.

SQL> grant connect to c##all_app container=all;

Grant succeeded.

SQL> grant set container to c##all_app container=all;

Grant succeeded.

SQL> grant select_catalog_role to c##all_app container=all;

Grant succeeded.

SQL> conn c##all_app/temp123#

Connected.

SQL> alter session set container=pdb_purchase;

Session altered.

SQL> select count(*) from dba_tables;

  COUNT(*)

----------

      2369

SQL> alter session set container=pdb_hr;

Session altered.

SQL> select count(*) from dba_tables;

  COUNT(*)

----------

      2365

-- We have created a user and granted him the privileges accorss all containers.

or

SQL> drop user c##all_app ;

User dropped.

SQL> create user c##all_app identified by temp123#;

User created.

SQL> create role c##all_app_role;

Role created.

SQL> grant set container to  c##all_app_role container=all;

Grant succeeded.

SQL> grant connect to c##all_app_role container=all;

Grant succeeded.

SQL> grant select_catalog_role to c##all_app_role;

Grant succeeded.

SQL> grant c##all_app_role to c##all_app;

Grant succeeded.

SQL> conn c##all_app/temp123#

Connected.

SQL> alter session set container=pdb_hr;

ERROR:

ORA-01031: insufficient privileges

-- Here we have created a common role and granted the privileges commonly, but the common role is assigned to the common

user c##all_app locally. i.e. container=all is not mentioned which means the default value container=current will be taken

effect. Hence the user can able to user the privileges only to that particular container.

Now,

SQL> grant c##all_app_role to c##all_app container=all;

Grant succeeded.

SQL> conn c##all_app/temp123#

Connected.

SQL> alter session set container=pdb_hr;

Session altered.

Scenario 2

----------

a) A common user should be created, he need to have only the select privielges (read only) on pdb_purchase database and

   he need write privilege on the pdb_hr database.

SQL> create user c##hr_app identified by hradmin;

User created.

SQL> grant connect, set container to c##hr_app container=all;

Grant succeeded.

SQL> grant select_catalog_role to c##hr_app container=all;

Grant succeeded.

SQL> grant select any table to c##hr_app container=all;

Grant succeeded.

SQL> alter session set container=pdb_hr;

Session altered.

SQL> grant resource to c##hr_app container=current;

Grant succeeded.

Verify the privileges.

SQL> conn c##hr_app/hradmin

Connected.

SQL> show con_name;

CON_NAME

------------------------------

CDB$ROOT

SQL> alter session set container=pdb_purchase;

Session altered.

SQL> select count(*) from dba_tables;

  COUNT(*)

----------

      2369

SQL> create table emp (empid number) tablespace users;

create table emp (empid number) tablespace users

*

ERROR at line 1:

ORA-01031: insufficient privileges

--Write privilege not given to the user.

SQL> alter session set container=pdb_hr;

Session altered.

SQL> select count(*) from dba_tables;

  COUNT(*)

----------

      2365

SQL> create table emp (empid number) tablespace users;

Table created.

SQL> drop table emp;

Table dropped.

User is able to write. Working as per the expectation. The same can be achevied via below.

SQL> create user c##hr_app identified by hradmin;

User created.

SQL> create role c##hr_app_role;

Role created.

SQL> grant connect, set container to c##hr_app_role container=all;

Grant succeeded.

SQL> grant c##hr_app_role to c##hr_app container=all;

Grant succeeded.

SQL> alter session set container=pdb_purchase;

Session altered.

SQL> grant select_catalog_role to c##hr_app_role;

Grant succeeded.

SQL> alter session set container=pdb_hr;

Session altered.

SQL> grant select_catalog_role to c##hr_app_role;

Grant succeeded.

SQL> grant resource to c##hr_app_role;

Grant succeeded.

SQL> conn c##hr_app/hradmin

Connected.

SQL> alter session set container=pdb_purchase;

Session altered.

SQL> select count(*) from dba_tables;

  COUNT(*)

----------

      2369

SQL> create table emp (empid number) tablespace users;

create table emp (empid number) tablespace users

*

ERROR at line 1:

ORA-01031: insufficient privileges

SQL> conn c##hr_app/hradmin

Connected.

SQL> alter session set container=pdb_hr;

Session altered.

SQL> select count(*) from dba_tables;

  COUNT(*)

----------

      2365

SQL> create table emp (empid number) tablespace users;

Table created.

SQL> drop table emp;

Table dropped.

-- Common user c##hr_app created.

-- Common role c##hr_app_role created.

-- Connect and set container privileges are granted to c##hr_app_role commonly (across all containers)

-- Common user c##hr_app_role granted to common user c##hr_app commonly (accross all containers).

-- In pdb pdb_purchase, select_catalog_role granted to common role c##hr_app_role locally (container=current which is the default).

-- In pdb pdb_hr , select_catalog_role and resource are granted to common role c##hr_app_role locally (container=current which is the default).

Chanding Password

-----------------

SQL> select dbms_metadata.get_ddl('USER','C##HR_APP') FROM DUAL;

DBMS_METADATA.GET_DDL('USER','C##HR_APP')

------------------------------------------------------------------------------

   CREATE USER "C##HR_APP" IDENTIFIED BY VALUES 'S:2580217D87120EE705E62B33614

8A4FEC384A018C83820489A33DBCB4F80;H:0E769A3AF02A8264325333529E833F35;T:5600C32

26720421ECE811B0468E081E54540F5A05959B01DA45DC2D31C6CA9F3D376B00FD556C5D570D92

4AEE39CEEB7C5F1560AC003367E98DC9B919EC8A1F16711D7C4061B61067BA25673DD19EAAE;49

3303D55FB4AFF2'

      DEFAULT TABLESPACE "USERS"

      TEMPORARY TABLESPACE "TEMP"

Common user should change their password only from the root container.

SQL> conn c##hr_app/hradmin

Connected.

SQL> alter session set container=pdb_hr;

Session altered.

SQL> alter user C##HR_APP identified by hr_admin1;

alter user C##HR_APP identified by hr_admin1

*

ERROR at line 1:

ORA-65066: The specified changes must apply to all containers

SQL> alter user C##HR_APP identified by hr_admin1 container=all;

alter user C##HR_APP identified by hr_admin1 container=all

*

ERROR at line 1:

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

The common DDLi.e. container=all should always be executed only from the root container.

SQL> alter session set container=cdb$root;

Session altered.

SQL> alter user C##HR_APP identified by hr_admin1;

User altered.

Reverting back to the old password.

SQL> set long 10000;

Current password of c##HR_APP user is hr_admin1

SQL> select dbms_metadata.get_ddl('USER','C##HR_APP') FROM DUAL;

DBMS_METADATA.GET_DDL('USER','C##HR_APP')

------------------------------------------------------------------------------

   CREATE USER "C##HR_APP" IDENTIFIED BY VALUES 'S:56C7759CBC0C4503F20027692C1

1D13572EDB5B9A271EC2D5CCC92155E5E;H:60C2D5FFCA882CB48BFA906DCBFD24E4;T:E64E3F8

6B0AFA19643150958542B1CCD17CD2C089D31FFC34159C829E5CE5CF7E1C301865CD7CEC82021B

036D09CC03906DA489B49B944E59C2F6459C48824AE5DDC30A3E388725B6BFFAABA880284A6;48

8A838A0DC512F6'

      DEFAULT TABLESPACE "USERS"

      TEMPORARY TABLESPACE "TEMP"

SQL> select name, password, SPARE4 from user$ where name = 'C##HR_APP';

NAME

------------------------------------------------------------------------------

PASSWORD

------------------------------------------------------------------------------

SPARE4

------------------------------------------------------------------------------

C##HR_APP

488A838A0DC512F6

S:56C7759CBC0C4503F20027692C11D13572EDB5B9A271EC2D5CCC92155E5E;H:60C2D5FFCA882

CB48BFA906DCBFD24E4;T:E64E3F86B0AFA19643150958542B1CCD17CD2C089D31FFC34159C829

E5CE5CF7E1C301865CD7CEC82021B036D09CC03906DA489B49B944E59C2F6459C48824AE5DDC30

A3E388725B6BFFAABA880284A6

Take the backup using below command.

SQL> select 'alter user c##hr_app identified by values ' || '''' || SPARE4 || ';' || password || '''' || ';' from user$ where name = 'C##HR_APP';

'ALTERUSERC##HR_APPIDENTIFIEDBYVALUES'||''''||SPARE4||';'||PASSWORD||''''||';'

------------------------------------------------------------------------------

alter user c##hr_app identified by values 'S:56C7759CBC0C4503F20027692C11D13572EDB5B9A271EC2D5CCC92155E5E;H:60C2D5FFCA882CB48BFA906DCBFD24E4;T:E64E3F86B0AFA19643150958542B1CCD17CD2C089D31FFC34159C829E5CE5CF7E1C301865CD7CEC82021B036D09CC03906DA489B49B944E59C2F6459C48824AE5DDC30A3E388725B6BFFAABA880284A6;488A838A0DC512F6';

SQL> alter user c##hr_app identified by hr_new_password;

User altered.

SQL> conn c##hr_app/hr_new_password

Connected.

-- Lets revert back to the old password using the backup, execute the above command.

SQL> alter user c##hr_app identified by values

    'S:56C7759CBC0C4503F20027692C11D13572EDB5B9A271EC2D5CCC92155E5E;H:60C2D5FFCA882CB48BFA906DCBFD24E4;T:E64E3F86B0AFA19643150958542B1CCD17CD2C089D31FFC34159C829E5CE5CF7E1C301865CD7CEC82021B036D09CC03906DA489B49B944E59C2F6459C48824AE5DDC30A3E388725B6BFFAABA880284A6;488A838A0DC512F6';

User altered.

SQL> conn c##hr_app/hr_new_password

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

SQL> conn c##hr_app/hr_admin1

Connected.