# connect as sys
show con_name
/*
CON_NAME
------------------------------
CDB$ROOT
*/
# all users in root are common
select * from dba_users;
/*
*/
# you can not create local user in the root
create user jane identified by jane;
/*
Error starting at line : 16 in command -
create user jane identified by jane
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.
*/
show parameter common_user_prefix
/*
NAME TYPE VALUE
------------------ ------ -----
common_user_prefix string C##
*/
# note 'container=all' is optional
create user c##jane identified by jane container=all;
/*
User C##JANE created.
*/
# now let us give create session local only in the root
# it will work only for the root, "orcl"
grant create session to c##jane ;
/*
Grant succeeded.
SQL> conn c##jane/jane@orclpdb
ERROR:
ORA-01045: user C##JANE lacks CREATE SESSION privilege; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn c##jane/jane@orcl
Connected.
SQL> show user
USER is "C##JANE"
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL>
*/
# now let us give create session common as "container=all"
grant create session to c##jane container=all ;
/*
Grant succeeded.
SQL> conn c##jane/jane@orclpdb
Connected.
SQL> show user
USER is "C##JANE"
SQL> show con_name
CON_NAME
------------------------------
ORCLPDB
SQL>
*/
