Pete Finnigan's Oracle Security Weblog

I am keen to reduce grants made in any customers database. One area we can focus on is this curios state of affairs that the creator of a role in the Oracle database is also granted that role as part of the create. We can see this in my 19c database in a PDB. First lets create a role:

C:\>sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jun 6 09:46:13 2022

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

SQL> @cs
Connected.
USER is "SYS"

'CONNECTEDAS'||USER
--------------------------------------------------------------------------------
CONNECTED AS SYS

SQL> create role fred;

Role created.

This is a CREATE privilege and use BUT Oracle also makes a silent GRANT. We can see this by checking who has been granted the ROLE FRED in this case:

SQL> @print 'select * from dba_role_privs where granted_role=''''FRED'''''
old  33:        lv_str:=translate('&&1','''','''''');
new  33:        lv_str:=translate('select * from dba_role_privs where granted_role=''FRED''','''','''''');
Executing Query [select * from dba_role_privs where granted_role='FRED']
GRANTEE                       : SYS
GRANTED_ROLE                  : FRED
ADMIN_OPTION                  : YES
DELEGATE_OPTION               : NO
DEFAULT_ROLE                  : YES
COMMON                        : NO
INHERITED                     : NO
-------------------------------------------

PL/SQL procedure successfully completed.

SQL>

Because I created the role as SYS, then SYS has also been granted the role FRED BUT not explicit GRANT was made. This needs to be managed in each database and unless the creator of the role actually needs to have the role then these grants should be revoked as they are unnecessary rights.

We should also think carefully about who grants roles. If we decide that the application schema needs to grant roles then the schema needs the ability to create roles. This means that after we create all the needed application roles we should remove this grant also from the schema. It maybe makes more sense to have the application create its roles as then all of the application including roles is contained within the application.

As you can see a database often can end up with privileges and security objects such as roles that are not needed at run time. We must always strive for least rights in all parts of the database.

There is a second slight glitch in the creation of roles if we do this in a CDB. Lets create a role in the CDB:

C:\>sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jun 6 09:54:37 2022

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

SQL> @cs
Connected.
USER is "SYS"

'CONNECTEDAS'||USER
--------------------------------------------------------------------------------
CONNECTED AS SYS

SQL>

This is still a 19c database. Lets create the role:

SQL> create role c##fred;

Role created.

SQL>

Now we can check to see if SYS has also been granted C##FRED in the CDB:

SQL> @print 'select * from dba_role_privs where granted_role=''''C##FRED'''''
old  33:        lv_str:=translate('&&1','''','''''');
new  33:        lv_str:=translate('select * from dba_role_privs where granted_role=''C##FRED''','''','''''');
Executing Query [select * from dba_role_privs where granted_role='C##FRED']
GRANTEE                       : SYS
GRANTED_ROLE                  : C##FRED
ADMIN_OPTION                  : YES
DELEGATE_OPTION               : NO
DEFAULT_ROLE                  : YES
COMMON                        : YES
INHERITED                     : NO
-------------------------------------------

PL/SQL procedure successfully completed.

SQL>

So, as with the PDB and for the same reasons I would like to revoke C##FRED from SYS as it is not a needed grant:

SQL> revoke c##fred from sys;
revoke c##fred from sys
*
ERROR at line 1:
ORA-01951: ROLE 'C##FRED' not granted to 'SYS'


SQL>

When we created the role we didn't specify which pluggable databases we would like the role created in so Oracle uses a default of "container=all" behind the scenes this is why the revoke needs "container=all":

SQL> revoke c##fred from sys container=all;

Revoke succeeded.

SQL>

The role still exists in the PDB of course as well as the CDB:

SQL> alter session set container=orclpdb;

Session altered.

SQL> select role from dba_roles where role like '%FRED%';

ROLE
--------------------------------------------------------------------------------
FRED
C##FRED

SQL>
Don't create roles in the CDB as by default they are created in each PDB and also granted to the creator in each PDB.:

SQL> @cs
Connected.
USER is "SYS"

'CONNECTEDAS'||USER
--------------------------------------------------------------------------------
CONNECTED AS SYS

SQL> create role c##test;

Role created.

SQL> alter session set container=orclpdb;

Session altered.

SQL> @print 'select * from dba_role_privs where granted_role=''''C##FRED'''''
old  33:        lv_str:=translate('&&1','''','''''');
new  33:        lv_str:=translate('select * from dba_role_privs where granted_role=''C##FRED''','''','''''');

PL/SQL procedure successfully completed.

SQL> set serveroutput on
SQL> @print 'select * from dba_role_privs where granted_role=''''C##TEST'''''
old  33:        lv_str:=translate('&&1','''','''''');
new  33:        lv_str:=translate('select * from dba_role_privs where granted_role=''C##TEST''','''','''''');
Executing Query [select * from dba_role_privs where granted_role='C##TEST']
GRANTEE                       : SYS
GRANTED_ROLE                  : C##TEST
ADMIN_OPTION                  : YES
DELEGATE_OPTION               : NO
DEFAULT_ROLE                  : YES
COMMON                        : YES
INHERITED                     : YES
-------------------------------------------

PL/SQL procedure successfully completed.

SQL>

Do not create roles in the CDB for applications use, create then in each PDB and also ensure that you "manage" who creates them and revoke the hidden grants and also revoke the CREATE ROLE if granted to a schema after the creation.