Oracle 12c User Administration and Connectivity

ORACLE 12 VIEWS
--------------

1)cdb_users,
2)cdb_tables;
3)dba_tablespaces;
4)dba_users;
5)all_users

root container and privelge user

ALTER SESSION SET CONTAINER=<CONTAINER_NAME>;

SELect sys_context('userenv','con_id')
      sys_context('userenv','con_name')
from dual;


SQL>show con_id con_name

COMMON USERNAME

prefix : C## or c##
at CDB$ROOT  LEVEL


LOCAL USERNAME
no prefix
at PDB level

date


cat /etc/oratab

ps -ef|grep -i smon

. oraenv
lsnrctl status
lsnrctl status | grep instance

tnsping cdb_welsfargo
tnsping pdb_sale
tnsping pdb_hr
tnsping pdb_mktg



COMMANDS
=========
CDB
sqlpplus / as sysdba

CONN / AS SYSDBA                    
  check current container name and ID
  SQL>SHOW CURRENT CON_ID,CON_NAME
                            OR

 SQL>SELect sys_context('userenv','con_id')
      sys_context('userenv','con_name')
      from dual;
       CON_ID     CON_NAME
      ==================
         1         CDB$ROOT
         

SELECT b.con_id, a.pdb_name, a.status,b.open_mode
from dba_pdbs a, v$pdbs b
where a.pdb_name=b.name
order by b.con_id;



CON_ID     PDB_NAME     STATUS   OPEN_MODE
=========================================
  2           PDB$SEED     NORMAL   READ ONLY
  3           PDB$HR       NORMAL   READ WRITE
  4           PDB_SALES    NORMAL   READ WRITE
  5           PDB_MKTG     NORMAL   READ WRITE


SELECT b.con_id, a.pdb_name, a.status,b.open_mode
from dba_pdbs a, v$pdbs b
where a.pdb_name=b.name
order by b.con_id;

SQL>SELECT USERNAME,COMMON COMMON_USER , CON_ID FROM CDB_USERS
WHERE ACCOUNT_STATUS ='OPEN'
AND USERNAME LIKE 'SYS%'
ORDER BY CON_ID,USERNAME;


USERNAME  COMMON_USER  CON_ID
=================================
SYS           YES       1
SYSTEM        YES       1
SYS           YES       2
SYSTEM        YES       2
SYS           YES       3
SYSTEM        YES       3
SYS           YES       4
SYSTEM         YES      4
SYS            YES      5
SYSTEM         YES      5


QUERY FOR ALL LOCAL USERS

  SQL>SELECT USERNAME,COMMON COMMON_USER , CON_ID
      FROM CDB_USERS
      WHERE ACCOUNT_STATUS ='OPEN'
        AND USERNAME NOT  LIKE 'SYS%'
          ORDER BY CON_ID,USERNAME;


USERNAME          COMMON_USER     CON_ID
=======================================
PDB_HR_ADMIN                NO                  3
PDB_SALES_ADMIN          NO                  4
PDB_MKTG_ADMIN           NO                 5


EXIT


CONNECTING TO CONTAIER USER USING COMMON USER SYSYEM



[oracle@Welsfargo] sqlplus system/oracle@cdb_welsfargo

SQL>SHOW CURRENT CON_ID,CON_NAME

SQL>SELECT b.con_id, a.pdb_name, a.status,b.open_mode
from dba_pdbs a, v$pdbs b
where a.pdb_name=b.name
order by b.con_id;



CON_ID     PDB_NAME     STATUS   OPEN_MODE
=========================================
  2           PDB$SEED     NORMAL   READ ONLY
  3           PDB$HR          NORMAL   READ WRITE
  4           PDB_SALES    NORMAL   READ WRITE
  5           PDB_MKTG     NORMAL   READ WRITE


PDB

CHANGING CURRENT CONTAINER TO PDB SALES



SQL> ALTER SESSION SET CONTAINER = pdb_sales;

 SQL>SHOW CURRENT CON_ID,CON_NAME

                 OR

 SQL>SELect sys_context('userenv','con_id')
      sys_context('userenv','con_name')
      from dual;

       CON_ID     CON_NAME
      ==================
       4              PDB_SALES

SQL>SELECT USERNAME,COMMON COMMON_USER ,
       CON_ID FROM CDB_USERS
       WHERE ACCOUNT_STATUS ='OPEN'
       ORDER BY CON_ID,USERNAME;


USERNAME         COMMON_USER  CON_ID
=======================================
PDB_SALES_ADMIN    NO       4
SYS                                  YES      4
SYSTEM                           YES      4

       4        PDB_SALES
SQL>SELECT USERNAME,COMMON COMMON_USER ,
     CON_ID FROM DBA_USERS
      WHERE ACCOUNT_STATUS ='OPEN'
      ORDER BY CON_ID,USERNAME;


USERNAME         COMMON_USER  CON_ID
=======================================
PDB_SALES_ADMIN    NO       4
SYS                                  YES      4
SYSTEM                          YES      4



CHANGING PDB HR USING ALTER SESSION CONTAINER
SQL> ALTER SESSION SET CONTAINER = pdb_hr;
 SQL>SHOW CURRENT CON_ID,CON_NAME

CHANGING CURRENT CONTAINER TO cdb$root


SQL> ALTER SESSION SET CONTAINER = cdb$hr;
SQL>SHOW CURRENT CON_ID,CON_NAME


COMMAND TO CHECK LOCAL USER IN CONTAINER
=======================================

SQL>SELECT USERNAME,COMMON COMMON_USER , CON_ID FROM CDB_USERS
    WHERE ACCOUNT_STATUS ='OPEN'
    AND USERNAME NOT  LIKE 'SYS%'
       ORDER BY CON_ID,USERNAME;


USERNAME          COMMON_USER        CON_ID
=======================================
PDB_HR_ADMIN                 NO          3
PDB_SALES_ADMIN           NO          4
PDB_MKTG_ADMIN            NO          5


EXIT


CONNECTING PDB_HR USING LOCAL ADMIN USER
========================================
sqlplus pdb_hr_admin/password@pdb_hr

SQL> ALTER SESSION SET CONTAINER = pdb_hr;
 SQL>SHOW CURRENT CON_ID,CON_NAME
                 OR

 SQL>select sys_context('userenv','con_id')
      sys_context('userenv','con_name')
      from dual;
       CON_ID     CON_NAME
      ==================
       3        PDB_HR


BY DEAULT PDB_DBA ROLE ARE GRANTED TO  PDB_ADMIN USER

HOST CLEAR

SQL> SELECT username,granted_role from user_role_privs;


USERNAME                   GRANTED_ROLE
=========================================
PDB_HR_ADMIN               PDB_DBA


CHECK CURRENT USER SESSION PRIVLEGES
==================================


SQL>select privilege from session_privs;


THESE ARE DERFAULT TO PDB_ADMIN


WILL NOT HAVE DATA DICTIONARY PRIVS

once user have privelege on data dictionary
SQL>select con_id,name,open_mode  from v$pdbs




Connecting PDB_hr container

SQL>sqlplus  sys/oracle@pdb_hr as sysdba
CHECK CURRENT CONTAINER NAME
SQL>show con_name
CHECKING PRIVELAGES IN CURRENT SESSION
SQL>select privilege from session_privs;

SQL>select con_id,name, open_mode from v$pdbs

TO CLOSE PLUGGABLE DATABASE
SQL>shutdown immediate;


CHECK STATUS AFTER CLOSING

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

it will show you mounted

to open PLUGGABLE DATABASE

SQL>startup;
Exit

connecting to container database using
sqlplus / as sysdba
SQL>select role role_ name,common common_role from dba_roles where role='PDB_DBA';
SQL>select role,privilege from role_sys_privs where role='PDB_DBA'

PDB_DBA ROLES ARE GRANTED ADMINS USERS BY DEFAULT BY WHEN CREATING PLUGGABLE DATABASES
SQL>GRANT create user  TO pdb_dba CONTAINER=ALL;

SQL>GRANT create tablespace to pdb_dba CONTAINER=ALL;

SQL>GRANT select any dictionarry to pdb_dba CONTAINER=ALL;

SQL>SELECT  role,privilege from role_sys_privs where role='PDB_DBA'

EXIT


connect to pdb_hr using Local Admin user

sqlplus pdb_hr_admin/password@pdb_hr

SQL>show con_name

check current user session privileges
SQL>SELECT privilege from session_privs;

to check status of current pluggable database status
SQL>SELECT con_id, name, open_mode from v$pdbs;

query to see all open accounts in current container both local and common
SQL>SELECT username,common common_user ,con_id from cdb_user where account_status='OPEN';

SQL>Select username,common from dba_users where account_status=open;

QL>select tablespace_name from dba_tablespaces;
CREATE TABLESPACE IN CURRENT PLUGGABLE DATABASE

SQL>CREATE TABLESPACE user_indexes_tbs DATAFILE SIZE 5G;
SQL>select tablespace_name from dba_tablespaces;
SQL>selec username, common common_user from dba_users;
  where account_status=open;

create user in current container
SQL>create user scott identfied by tiger  container=current;
SQL>select username, common common_user from dba_users;
SQL>create user scott2 identfied by tiger  container=all;
this is allowed only in root container
exit
sqlplus / as sysdba

SELECT b.con_id, a.pdb_name, a.status,b.open_mode
from dba_pdbs a, v$pdbs b
where a.pdb_name=b.name
order by b.con_id;


to check local users created
SQL>select username, common common_user,con_id  from cdb_users;
where username like '%SCOTT%';

CHECK pdp_hr in cdb database;

SQL>select tablespace_name from cdb_tablespaces where con_id=3;

to check  current container
SQL>SHOW CON_NAME;


CREATE COMMON USER
SQL>create user c##syed identified by india123 container=all;
SQL>grant create session to c##syed container=all;
He will be able to login in root container as well as all pluggable databases

 for common user you have to specify container=all
SQL>create user c##syed identified by india123 container=current;
will result error




below command  will also result error

SQL>create user c##syed identified by india123 container=pbd_sales;

SQL>select username, con_id from cdb_users where account_status='OPEN'
and common ='YES'  order by username, con_id;


USERNAME                CON_ID
==============================
C##SYED                   1
C##SYED                   3
C##SYED                   4
C##SYED                   5
SYS                       1
SYS                       2
SYS                       3
SYS                       4
SYS                       5
SYESTEM                   1
SYESTEM                   2
SYESTEM                   3
SYESTEM                   4
SYESTEM                   5

pdb seed database is container ID 2
so user C##SYED  is not created there pdb seed with only have oracle supplied common user

when you try to create local role in root container it will error out
SQL>create role localrol container=current;
errot
You can only create common roles at root container level

common role should be prefix with c##  symbol
SQL>create role c##powerole CONTAINER=all;

SQL>select role ,common, con_id from cdb_roles where role LIKE '%#%';

ROLE                      COM   CON_ID
=======================================
C#POWERROLE                YES    1
C#POWERROLE                YES    3
C#POWERROLE                YES    4
C#POWERROLE                YES    5

NOTE:pdb seed will have only oracle supplied common roles


connecting to container database using newly created common user
sqlplus c##syed/India123@cdb_welsfargo
my root container is welsfargo


connect to pdb_sales with common user syed
sqlplus c##syed/india123@pdb_sales