12c, CDB, perl, Users

Local Users in Oracle Multitenant Databases

Leave a comment

In an Oracle 12c multitenant database, Local users are accounts created at the PDB level and they have no rights at the CDB level.   This article demonstrate the use of Local user accounts.   NOTE:  To create a local user you must be connected to that PDB and user is only valid in that PDB.   You can have same user name in multiple PDBs in the same

 

 

  1. Logon to your server as oracle software owner.

 

[larry@linux2 ~]$ su – oracle

Password:

Last login: Wed Oct 26 10:07:02 EDT 2016 on pts/2

[oracle@linux2 ~]$

 

  1. Logon to your CDB as an administrator, in this example we logon as CDB admin account c##cdb_dba.

 

SQL> connect c##cdb_dba/password as sysdba

Connected.

SQL>

 

  1. Open the PDB pdb1 and pdb2 with the alter pluggable database command.

 

SQL> alter pluggable database all open;

 

Pluggable database altered.

 

SQL>

 

  1. Connect to the PDB pdb1 to create local user in this single PDB.

 

SQL> alter session set container=pdb1;

 

Session altered.

 

SQL> create user user_pdb identified by password;

 

User created.

 

SQL>

 

  1. Grant resource, create session to user user_pdb.

 

 

SQL> grant resource to user_pdb;

 

Grant succeeded.

 

SQL> grant create session to user_pdb;

 

Grant succeeded.

 

SQL>

 

  1. Connect to pdb1 as the user user_pdb.

 

SQL> connect user_pdb/password@pdb1

Connected.

SQL>

 

  1. Currently user user_pdb is only a local user for the PDB pdb1 not for the CDB CDB$ROOT or the PDB pdb2. This can be show by attempting to connect to both will fail.

 

SQL> connect user_pdb/password@pdb2

ERROR:

ORA-01017: invalid username/password; logon denied

 

 

Warning: You are no longer connected to ORACLE.

SQL> connect user_pdb2/password@cdb1

ERROR:

ORA-01017: invalid username/password; logon denied

 

 

SQL>  connect user_pdb/password@pdb1

Connected.

SQL>

 

 

  1. As an admin user connect to PDB pdb2 and attempt to connect as the user ‘user_pdb’ NOTE:  That this fails because no user ‘user_pdb’ exists in this PDB.

 

SQL> alter session set container=pdb2;

 

Session altered.

 

SQL> show user

USER is “C##CDB_DBA”

SQL> connect user_pdb/password

ERROR:

ORA-01017: invalid username/password; logon denied

 

 

Warning: You are no longer connected to ORACLE.

SQL>

 

  1. Reconnect as the admin user and create a user ‘user_pdb’ in PDB pdb2

 

SQL> connect C##CDB_DBA/password

Connected.

SQL> show con_name

 

CON_NAME

——————————

CDB$ROOT

SQL> alter session set container=pdb2;

 

Session altered.

 

SQL> show con_name

 

CON_NAME

——————————

PDB2

SQL> create user user_pdb identified by password;

 

User created.

 

SQL> grant dba to user_pdb;

 

Grant succeeded.

 

SQL> grant create session to user_pdb;

 

Grant succeeded.

 

SQL> connect user_pdb/password@pdb2;

Connected.

SQL>

 

 

 

This completes local user creation in PDBs.

 

 

Larry Catt

OCP

 

 

Leave a Reply