12c, CDB, Multitenant, PDB, privileges, Users

Privileges of Local Users in Oracle Multitenant Database

Leave a comment

In Oracle Multitenant Databases, local users only have access and privileges to a single PDB and not the CDB.    You can have the same user name across multiple PDBs housed in the same CDB, but the users share no privileges and only shares a common name.    This procedure give a brief overview of local user privileges in Oracle 12c Multitenant Databases.  Listed below are the stated restriction on local user privileges.

 

  1. A privilege granted locally can only be used in that container, even if granted by common user.
  2. Both common and local users can grant privileges locally.
  3. Both common and local users can grant privileges to other common or local users.
  4. Grantor must connect to container and specify CONTAINER=CURRENT clause.
  5. Common and Local user can grant privileges locally if they have appropriate privileges.

 

 

  • A privilege granted locally can only be used in that container, even if granted by common user.

 

  1. As the common user c##dba logon to pdb1 and pdb2 creating users user_local.

 

SQL> connect c##dba/password@pdb1

Connected.

SQL> create user user_local identified by password;

User created.

SQL> connect c##dba/password@pdb2

Connected.

SQL>  create user user_local identified by password;

User created.

 

SQL>

 

  1. Now connect to pdb1 as c##dba and grant the user user_local the DBA role. Connect as user_local and  create an additional local user user_test.    NOTE:   The user user_local can successfully connect to pdb1 and create an additional local user in this PDB.

 

SQL> connect c##dba/password@pdb1

Connected.

SQL> grant dba to user_local;

Grant succeeded.

 

SQL> connect user_local/password@pdb1

Connected.

 

SQL> create user user_test identified by password;

User created.

 

SQL>

 

  1. Now attempt to connect to pdb2 with local user user_local. NOTE:   The user user_local does not even have permission to connect to pdb2, even though the user exists in pdb2.

 

SQL> connect user_local/password@pdb2

ERROR:

ORA-01045: user USER_LOCAL lacks CREATE SESSION privilege; logon denied

 

Warning: You are no longer connected to ORACLE.

SQL>

 

  • Both common and local users can grant privileges locally.

 

  1. Using local_user created above from pdb1, you can grant privileges to the newly created user user_test logon as local_user.

 

SQL> connect user_local/password@pdb1

Connected.

SQL> grant resource to user_test;

Grant succeeded.

 

SQL>

 

 

  • Both common and local users can grant privileges to other common or local users.

 

  • Using local_user created above from pdb1, you can grant privileges to the newly created user user_test logon as local_user.

 

SQL> connect user_local/password@pdb1

Connected.

SQL> grant resource to user_test;

Grant succeeded.

 

SQL>

 

 

  • Grantor must connect to container and specify CONTAINER=CURRENT clause or specify no CONTAINER clause. CONTAINER=ALL is not available in PDB.

 

  1. As the user_local grant resource to user_test without a container clause and CONTAINER=ALL clause.

 

SQL> connect user_local/password@pdb1

Connected.

SQL> grant resource to user_test;

Grant succeeded.

 

SQL> grant resource to user_test container=all;

grant resource to user_test container=all

*

ERROR at line 1:

ORA-65029: a Local User may not grant or revoke a Common Privilege or Role

 

SQL>

 

  • Common and Local user can grant privileges locally if they have appropriate privileges.

 

 

  1. Local user_local can create additional users in PDB pdb1 once the user has been granted the DBA role.

 

SQL> connect c##dba/password@pdb1

Connected.

SQL> grant dba to user_local;

Grant succeeded.

 

SQL> connect user_local/password@pdb1

Connected.

 

SQL> create user user_test identified by password;

User created.

 

SQL>

 

 

This completes the overview of privileges for local users in Oracle 12c Multitenant Database.

 

 

Larry Catt

OCP

Leave a Reply