Create role and Manage the Common and Local Roles on CDB and PDB
- Query to check roles in CDB database
connect / as sysdba
col role format a30
SELECT ROLE, COMMON, CON_ID
FROM CDB_ROLES
ORDER BY ROLE, CON_ID;
- Connect to CDB database and create role on CDB container ALL
oracle@srv3:/u01/app/oracle/product/12.2.0/dbhome_1/network/admin> sqlplus sys/system123@SMRCDB as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Thu Aug 13 18:59:43 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> CREATE ROLE C##ROLE1 CONTAINER=ALL;
Role created.
- Create ROLE having name ROLE_PDB2 CONTAINER is CURRENT mode on PLM2 pluggable database
oracle@srv3:/u01/app/oracle/product/12.2.0/dbhome_1/network/admin> sqlplus sys/system123@PLM2 as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Thu Aug 13 19:09:55 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> CREATE ROLE LROLE_PDB2 CONTAINER=CURRENT;
Role created.
SQL> SELECT ROLE FROM DBA_ROLES WHERE COMMON ='NO' ORDER BY ROLE;
ROLE
------------------------------
LROLE_PDB2
SQL>
- Redirect to CDB database grant common role to common user
SQL> connect / as sysdba
Connected.
SQL> GRANT C##ROLE1 TO C##USER1;
Grant succeeded.
- Check status below :
SQL> col grantee format A16
col granted_role format A16
SELECT GRANTEE, GRANTED_ROLE, COMMON, CON_ID FROM CDB_ROLE_PRIVS WHERE GRANTEE='C##USER1';SQL> SQL>
GRANTEE GRANTED_ROLE COM CON_ID
---------------- ---------------- --- ----------
C##USER1 C##ROLE1 NO 1
SQL> CONNECT C##USER1/oracle
Connected.
SQL> SELECT * FROM SESSION_ROLES;
ROLE
------------------------------
C##ROLE1
- Grant role privilege to all container pdb
SQL> GRANT C##ROLE1 TO C##USER1 CONTAINER=ALL;
Grant succeeded.
SQL> SELECT GRANTEE, GRANTED_ROLE, COMMON, CON_ID
FROM CDB_ROLE_PRIVS WHERE GRANTEE='C##USER1'; 2
GRANTEE GRANTED_ROLE COM CON_ID
---------------- ---------------- --- ----------
C##USER1 C##ROLE1 NO 1
C##USER1 C##ROLE1 YES 1
C##USER1 C##ROLE1 YES 5
C##USER1 C##ROLE1 YES 6
C##USER1 C##ROLE1 YES 8
SQL> CONNECT C##USER1/oracle
Connected.
SQL> SELECT * FROM SESSION_ROLES;
ROLE
------------------------------
C##ROLE1
- Check status using PLM2 PDB database
SQL> CONNECT C##USER1/oracle@PLM2;
Connected.
SQL> SELECT * FROM SESSION_ROLES;
ROLE
------------------------------
C##ROLE1
- Revoke Role for all container pdb database
SQL> CONNECT / as sysdba
Connected.
SQL> REVOKE C##ROLE1 FROM C##USER1 CONTAINER=ALL;
Revoke succeeded.
- Check the status below:
SQL> SELECT GRANTEE, GRANTED_ROLE, COMMON, CON_ID
FROM CDB_ROLE_PRIVS WHERE GRANTEE='C##USER1'; 2
GRANTEE GRANTED_ROLE COM CON_ID
---------------- ---------------- --- ----------
C##USER1 C##ROLE1 NO 1
SQL> REVOKE C##ROLE1 FROM C##USER1;
Revoke succeeded.
SQL> SELECT GRANTEE, GRANTED_ROLE, COMMON, CON_ID
FROM CDB_ROLE_PRIVS WHERE GRANTEE='C##USER1'; 2
no rows selected
SQL> CONNECT C##USER1/oracle
Connected.
SQL> SELECT * FROM SESSION_ROLES;
no rows selected
SQL> CONNECT C##USER1/oracle@PLM2
Connected.
SQL> SELECT * FROM SESSION_ROLES;
no rows selected
- Grant common role to local user on PLM2 PDB database only
SQL> connect system/system123@PLM2
Connected.
SQL> GRANT C##ROLE1 TO LUSER2;
Grant succeeded.
- Check the status
SQL> SELECT GRANTEE, GRANTED_ROLE, COMMON, CON_ID
FROM CDB_ROLE_PRIVS WHERE GRANTEE='LUSER2'; 2
GRANTEE GRANTED_ROLE COM CON_ID
---------------- ---------------- --- ----------
LUSER2 C##ROLE1 NO 5
SQL> CONNECT LUSER2/oracle@PLM2
Connected.
SQL> SELECT * FROM SESSION_ROLES;
ROLE
------------------------------
C##ROLE1
SQL> exit
- Check the session roles below:
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
oracle@srv3:/u01/app/oracle/product/12.2.0/dbhome_1/network/admin> sqlplus sys/system123@PLM2 as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Thu Aug 13 20:10:58 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> connect system/system123@PLM2
Connected.
SQL> SELECT * FROM SESSION_ROLES;
ROLE
--------------------------------------------------------------------------------
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_SELECT_ROLE
EXECUTE_CATALOG_ROLE
HS_ADMIN_EXECUTE_ROLE
CAPTURE_ADMIN
EXP_FULL_DATABASE
IMP_FULL_DATABASE
DATAPUMP_EXP_FULL_DATABASE
DATAPUMP_IMP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
ROLE
--------------------------------------------------------------------------------
OPTIMIZER_PROCESSING_RATE
EM_EXPRESS_ALL
EM_EXPRESS_BASIC
SCHEDULER_ADMIN
XDBADMIN
XDB_SET_INVOKER
WM_ADMIN_ROLE
JAVA_ADMIN
JAVA_DEPLOY
OLAP_XS_ADMIN
OLAP_DBA
ROLE
----------------------------
SQL> SELECT GRANTEE, GRANTED_ROLE, COMMON, CON_ID FROM CDB_ROLE_PRIVS WHERE GRANTEE='LUSER2';
GRANTEE
--------------------------------------------------------------------------------
GRANTED_ROLE
--------------------------------------------------------------------------------
COM CON_ID
--- ----------
LUSER2
LROLE_PDB2
NO 5
LUSER2
C##ROLE1
NO 5
GRANTEE
--------------------------------------------------------------------------------
GRANTED_ROLE
--------------------------------------------------------------------------------
COM CON_ID
--- ----------
SQL> CONNECT LUSER2/oracle@PLM2
Connected.
SQL> SELECT * FROM SESSION_ROLES;
ROLE
--------------------------------------------------------------------------------
C##ROLE1
LROLE_PDB2
No comments:
Post a Comment