20 - [Oracle 19C] Common users VS local users

 

 

• A CDB common user is a database account that is created in the root container and is inherited by

all PDBs in the CDB, including future PDBs

• A common user can not have the same name as any local user across all the PDBs.

• Oracle supplied administrative accounts such SYS user and system user are common user.

• You can create common user , but you need user defined prefix C## , example C##KHALED

• The user defined prefix can be defined in parameter called common_user_prefix

• A Local user is DB user created in a specific PDB ( example: HR user in PDB orclpdb )

• You can create same local account and password in more than one PDB

example:

HR user in PDB called pdb1

HR user in PDB called pdb2

HR user in PDB called pdb3

 

 

 

show con_name
/*
CON_NAME 
------------------------------
CDB$ROOT
*/

--make sure that all pluggable is opned
select con_id,name,open_mode from v$pdbs;
/*
    CON_ID NAME 			  OPEN_MODE
---------- --------------- ------------------------------
        2	   PDB$SEED			  READ ONLY
        3	   ORCLPDB			  READ WRITE
*/

alter pluggable database all open;



select username,common,con_id from cdb_users
order by username;
/*
USERNAME   COM	   CON_ID
---------- --- ----------
ABC	        NO  3
ANONYMOUS   YES 1
ANONYMOUS   YES 3
APPQOSSYS   YES 1
APPQOSSYS   YES 3
*/

select distinct(username) from cdb_users
where common='YES';
/*
USERNAME
----------
SYS
DBSNMP
APPQOSSYS
MDDATA
DBSFWUSER
REMOTE_SCH
EDULER_AGE
NT
.....
*/


select username,common,con_id from cdb_users
where common='NO'
order by username;
/*
USERNAME   COM	   CON_ID
---------- --- ----------
ABC	        NO  3
HR	        NO  3
PDBADMIN    NO  3
*/


SHOW PARAMETER common_user_prefix;
/*
NAME               TYPE   VALUE 
------------------ ------ ----- 
common_user_prefix string C##   
*/

CREATE USER t1 IDentified by t1;
/*
Error starting at line : 29 in command -
CREATE USER t1 IDentified by t1
Error report -
ORA-65096: invalid common user or role name
65096. 00000 -  "invalid common user or role name"
*Cause:    An attempt was made to create a common user or role with a name
           that was not valid for common users or roles. In addition to the
           usual rules for user and role names, common user and role names
           must consist only of ASCII characters, and must contain the prefix
           specified in common_user_prefix parameter.
*Action:   Specify a valid common user or role name.
*/

CREATE USER C##t1 IDentified by welcome;
/*
User C##T1 created.
*/

select distinct(username) from cdb_users
where common='YES' order by username;
/*
USERNAME
----------
ANONYMOUS
APPQOSSYS
AUDSYS
C##T1
CTXSYS
DBSFWUSER
DBSNMP
...
*/

alter session set container=orclpdb
/*
Session altered.
*/

show con_name
/*
CON_NAME 
------------------------------
ORCLPDB
*/

create user abc identified by abc ;
/*
User ABC created.
*/

select username,common,con_id from cdb_users
where common='NO'
order by username;
/*
USERNAME   COM	   CON_ID
---------- --- ----------
ABC	        NO  3
HR	        NO  3
PDBADMIN    NO  3

*/