show user;
/*
USER is "SYS"
*/
show con_name;
/*
CON_NAME
------------------------------
CDB$ROOT
*/
# all roles in root are common
select * from dba_roles;
/*
*/
# you can not create local role in the root CDB
create role test_role ;
/*
Error starting at line : 17 in command -
create role test_role
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##
*/
create role c##test_role container=all;
/*
Role C##TEST_ROLE created.
*/
grant create session to c##test_role container=all ;
/*
Grant succeeded.
*/
grant create table to c##test_role container=all ;
/*
Grant succeeded.
*/
create user c##dark identified by dark ;
/*
User C##DARK created.
*/
grant c##test_role to c##dark container=all;
/*
Grant succeeded.
*/
# now test the connection using sql developer
show user;
show con_name;
create user c##kim identified by kim ;
/*
SQL> create user c##kim identified by kim ;
User created.
*/
alter session set container=orclpdb;
/*
Session altered.
*/
grant c##test_role to c##kim;
/*
Grant succeeded.
*/
# now c##kim only can create session in orclpdb
/*
SQL> conn c##kim/kim@orcl
ERROR:
ORA-01045: user C##KIM lacks CREATE SESSION privilege; logon denied
Warning: You are no longer connected to ORACLE.
SQL>
*/
select * from cdb_users;
/*
*/
