CREATE LOCAL USERS IN 12C DATABASE

******************** ********************************** **************
CREATE LOCAL USERS IN 12C PDB DATABASE:
******************** ********************************** **************

When connected to a multi-tenant database the management of users and privileges is a little different to traditional Oracle environments. In multi-tenant 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.


******************** **************
Create Local Users
******************** **************

When creating a local user the following requirements must all be met.

You must be connected to a user with the CREATE USER privilege.
The username for the local user must not be prefixed with "C##" or "c##".
The username must be unique within the PDB.
You can either specify the CONTAINER=CURRENT clause, or omit it, as this is the default setting when the current container is a PDB.
The following example shows how to create local users with and without the CONTAINER clause from the root container.

CONN / AS SYSDBA

-- Switch container while connected to a common user.

ALTER SESSION SET CONTAINER = pdborcl1;

-- Create the local user using the CONTAINER clause.

CREATE USER pdborcl1_usr1 IDENTIFIED BY pdborcl1_usr1 CONTAINER=CURRENT;
GRANT CREATE SESSION TO pdborcl1_usr1 CONTAINER=CURRENT;

[oracle@Linux03 ~]$ sqlplus pdborcl1_usr1/pdborcl1_usr1@pdborcl1

SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 7 10:52:50 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing option

SQL> sho user
USER is "PDBORCL1_USR1"

******************** ------ ******************** ----- **************
-- Connect to a privileged user in the PDB.
******************** ------ ******************** ----- **************

CONN system/password@pdb1

-- Create the local user using the default CONTAINER setting.

SQL> CREATE USER pdborcl1_usr2 IDENTIFIED BY pdborcl1_usr2;
SQL> GRANT CREATE SESSION TO pdborcl1_usr2;

SQL> exit

[oracle@Linux03 ~]$ sqlplus pdborcl1_usr2/pdborcl1_usr2@pdborcl1

SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 7 10:56:29 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options


SQL> show con_name

CON_NAME
------------------------------
PDBORCL1

SQL> show user
USER is "PDBORCL1_USR2"

If a local user is to be used as a DBA user, it requires the PDB_DBA role granted locally to it.
This dba role will be only specific to prdborcl1 local PDB database.

SQL> grant pdb_dba to PDBORCL1_USR2;

Grant succeeded.


SQL> create table test (name varchar2(20));
create table test (name varchar2(20))
*
ERROR at line 1:
ORA-01031: insufficient privileges

As per above doc (https://docs.oracle.com/database/121/DBSEG/authorization.htm#DBSEG4414) PDB_DBA Granted automatically to the local user that is created when you create a new PDB from the seed PDB. No privileges are provided with this role.

IF I try to use a dba user in pdborcl1 and try to connect onto pdborcl2. It wont work

oracle@Linux03 ~]$ sqlplus pdborcl1_usr2/pdborcl1_usr2@pdborcl2

SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 7 11:13:09 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied

NOW I LOGGED IN AS SUPER USER AND GRANTED DBA TO PDBORCL1_USR2;

[oracle@Linux03 ~]$ sqlplus /"as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 7 11:32:01 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> ALTER SESSION SET CONTAINER = pdborcl1 ;

Session altered.

SQL> grant dba to PDBORCL1_USR2;

connect back as PDBORCL1_USR2 user and tried to create table :

SQL> show con_name

CON_NAME
------------------------------
PDBORCL1

SQL> show user
USER is "PDBORCL1_USR2"

SQL> create table test (name varchar2(20));

Table created.

SQL> select table_name from tabs;
TABLE_NAME
--------------------------------------------------------------------------------
TEST

SQL> insert into test values ('arvind') ;

1 row created.

SQL> commit;

SQL> select * from test;

NAME
--------------------
arvind

SQL> create view test1 as select * from test;

View created.

Note: I was able to have a great deal of priv as dba but only till "pdborcl1". I still do not have any priv on "prdorcl2" or any other DB's in CDB.

~~~~~~~~~~~~~
NOW LETS TRY LOGGEING INTO PDBORCL2 (Nope still doest work), as this dba role has been granted to pdborcl1_usr2 only for PDB "pdborcl1"
~~~~~~~~~~~~~

[oracle@Linux03 ~]$ sqlplus pdborcl1_usr2/pdborcl1_usr2@pdborcl2

SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 7 11:35:11 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied



*************************** **********************************
Now lets create same user in OTHER PDB "PDBORCL2"
*************************** **********************************

[oracle@Linux03 ~]$ sqlplus /"as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 7 11:48:56 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> ALTER SESSION SET CONTAINER = pdborcl2;

Session altered.

SQL> CREATE USER pdborcl1_usr2 IDENTIFIED BY pdborcl1_usr2;

User created.

SQL> grant CREATE SESSION,dba to pdborcl1_usr2;

Grant succeeded.

SQL> conn pdborcl1_usr2/pdborcl1_usr2
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL> exit
[oracle@Linux03 ~]$ sqlplus pdborcl1_usr2/pdborcl1_usr2@pdborcl2

SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 7 11:50:33 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show con_name

CON_NAME
------------------------------
PDBORCL2
SQL> show user
USER is "PDBORCL1_USR2"
SQL> select username from dba_users where username like 'PDB%';

USERNAME
--------------------------------------------------------------------------------
PDBORCL2
PDBORCL1_USR2

Conclusion :

1) You can have same users with same username and pass in two diff PDB's as local user.Yet they have no permissions on each other.
2)  Youe will need CDB_DBA -- it provides the privileges required for administering a CDB, such as SET CONTAINER, SELECT ON PDB_PLUG_IN_VIOLATIONS,   and SELECT ON CDB_LOCAL_ADMIN_PRIVS