Revoke PUBLIC Grants on CIS Recommended PL/SQL Packages

Objective

In section 5.1.1, CIS Oracle Database Benchmark v3.0 recommends revoking PUBLIC grants on six categories of PL/SQL packages: Network, File System, Encryption, Java, Job Scheduler and SQL Injection Helper packages. In addition, CIS also recommends not granting EXECUTE privilege on few additional packages as listed in section 5.1.2. Let’s call these PL/SQL packages publicly-granted-packages.

The primary rationale for this recommendation is that publicly-granted-packages are powerful PL/SQL packages. Any database user with minimal privileges such as CREATE SESSION could potentially leverage PUBLIC grants to perform unauthorized tasks or even to escalate privileges to exploit the database.

Oracle grants EXECUTE privilege on these powerful packages to PUBLIC. Oracle-maintained users such as DBSNMP, XDB, etc. rely on the publicly-granted-packages. In addition, you may have a situation where your applications are using these publicly-granted-packages through PUBLIC grants. Revoking PUBLIC grants without performing dependency analysis could very well make your PL/SQL packages INVLAID, causing issues with your applications.

This blog describes the procedures that we successfully implemented to revoke PUBLIC grants on these packages.

Plan

Given that our applications relied heavily on the publicly-granted-packages, we prepared a plan in collaboration with the application owners. The plan included appropriate application testing and validation procedures. We started this task with the test database, validated our applications and then progressed to the production environment.

Prepare Scripts

Create a Table with List of Objects

There are approximately 40 PL/SQL packages that, per CIS Oracle Database Benchmark, should not be granted to PUBLIC. When there are 40 objects, the list is obviously quite long. I would like to create a table to maintain a list of these objects. It is quite convenient to use this table in a JOIN clause, as you would see later. Otherwise, the WHERE clause of your SQLs may become quite long. This is just a step for convenience.

create table revoke_public_grant_list

(object_name     varchar2(32),

 owner           varchar2(32),

 object_type     varchar2(32),

 privilege       varchar2(32) );

insert all

into revoke_public_grant_list (object_name,owner,object_type,privilege) values('DBMS_AW', 'SYS','PACKAGE','EXECUTE')

into revoke_public_grant_list (object_name,owner,object_type,privilege) values('DBMS_XMLSAVE', 'SYS','PACKAGE','EXECUTE')

into revoke_public_grant_list (object_name,owner,object_type,privilege) values('DBMS_XMLSTORE', 'SYS','PACKAGE','EXECUTE')

into revoke_public_grant_list (object_name,owner,object_type,privilege) values('DBMS_REDACT', 'SYS','PACKAGE','EXECUTE')

into revoke_public_grant_list (object_name,owner,object_type,privilege) values('DBMS_ADVISOR', 'SYS','PACKAGE','EXECUTE')

into revoke_public_grant_list (object_name,owner,object_type,privilege) values('DBMS_CRYPTO', 'SYS','PACKAGE','EXECUTE')

into revoke_public_grant_list (object_name,owner,object_type,privilege) values('DBMS_JAVA', 'SYS','PACKAGE','EXECUTE')

into revoke_public_grant_list (object_name,owner,object_type,privilege) values('DBMS_JAVA_TEST', 'SYS','PACKAGE','EXECUTE')

into revoke_public_grant_list (object_name,owner,object_type,privilege) values('DBMS_JOB', 'SYS','PACKAGE','EXECUTE')

into revoke_public_grant_list (object_name,owner,object_type,privilege) values('DBMS_LDAP', 'SYS','PACKAGE','EXECUTE')

into revoke_public_grant_list (object_name,owner,object_type,privilege) values('DBMS_LOB', 'SYS','PACKAGE','EXECUTE')

into revoke_public_grant_list (object_name,owner,object_type,privilege) values('DBMS_OBFUSCATION_TOOLKIT', 'SYS','PACKAGE','EXECUTE')

into revoke_public_grant_list (object_name,owner,object_type,privilege) values('DBMS_RANDOM', 'SYS','PACKAGE','EXECUTE')

into revoke_public_grant_list (object_name,owner,object_type,privilege) values('DBMS_SCHEDULER', 'SYS','PACKAGE','EXECUTE')

into revoke_public_grant_list (object_name,owner,object_type,privilege) values('DBMS_SQL', 'SYS','PACKAGE','EXECUTE')

into revoke_public_grant_list (object_name,owner,object_type,privilege) values('DBMS_XMLGEN', 'SYS','PACKAGE','EXECUTE')

into revoke_public_grant_list (object_name,owner,object_type,privilege) values('DBMS_XMLQUERY', 'SYS','PACKAGE','EXECUTE')

into revoke_public_grant_list (object_name,owner,object_type,privilege) values('UTL_FILE', 'SYS','PACKAGE','EXECUTE')

into revoke_public_grant_list (object_name,owner,object_type,privilege) values('UTL_INADDR', 'SYS','PACKAGE','EXECUTE')

into revoke_public_grant_list (object_name,owner,object_type,privilege) values('UTL_TCP', 'SYS','PACKAGE','EXECUTE')

into revoke_public_grant_list (object_name,owner,object_type,privilege) values('UTL_MAIL', 'SYS','PACKAGE','EXECUTE')

into revoke_public_grant_list (object_name,owner,object_type,privilege) values('UTL_SMTP', 'SYS','PACKAGE','EXECUTE')

into revoke_public_grant_list (object_name,owner,object_type,privilege) values('UTL_DBWS', 'SYS','PACKAGE','EXECUTE')

into revoke_public_grant_list (object_name,owner,object_type,privilege) values('UTL_ORAMTS', 'SYS','PACKAGE','EXECUTE')

into revoke_public_grant_list (object_name,owner,object_type,privilege) values('UTL_HTTP', 'SYS','PACKAGE','EXECUTE')

into revoke_public_grant_list (object_name,owner,object_type,privilege) values('HTTPURITYPE', 'SYS','TYPE','EXECUTE')

into revoke_public_grant_list (object_name,owner,object_type,privilege) values('DBMS_SYS_SQL', 'SYS','PACKAGE','EXECUTE')

into revoke_public_grant_list (object_name,owner,object_type,privilege) values('DBMS_BACKUP_RESTORE', 'SYS','PACKAGE','EXECUTE')

into revoke_public_grant_list (object_name,owner,object_type,privilege) values('DBMS_AQADM_SYSCALLS', 'SYS','PACKAGE','EXECUTE')

into revoke_public_grant_list (object_name,owner,object_type,privilege) values('DBMS_REPACT_SQL_UTL', 'SYS','PACKAGE','EXECUTE')

into revoke_public_grant_list (object_name,owner,object_type,privilege) values('INITJVMAUX', 'SYS','PACKAGE','EXECUTE')

into revoke_public_grant_list (object_name,owner,object_type,privilege) values('DBMS_STREAMS_ADM_UTL', 'SYS','PACKAGE','EXECUTE')

into revoke_public_grant_list (object_name,owner,object_type,privilege) values('DBMS_AQADM_SYS', 'SYS','PACKAGE','EXECUTE')

into revoke_public_grant_list (object_name,owner,object_type,privilege) values('DBMS_STREAMS_RPC', 'SYS','PACKAGE','EXECUTE')

into revoke_public_grant_list (object_name,owner,object_type,privilege) values('DBMS_PRVTAQIM', 'SYS','PACKAGE','EXECUTE')

into revoke_public_grant_list (object_name,owner,object_type,privilege) values('LTADM', 'WMSYS','PACKAGE','EXECUTE')

into revoke_public_grant_list (object_name,owner,object_type,privilege) values('WWV_DBMS_SQL', 'SYS','PACKAGE','EXECUTE')

into revoke_public_grant_list (object_name,owner,object_type,privilege) values('WWV_EXECUTE_IMMEDIATE', 'SYS','PACKAGE','EXECUTE')

into revoke_public_grant_list (object_name,owner,object_type,privilege) values('DBMS_IJOB', 'SYS','PACKAGE','EXECUTE')

into revoke_public_grant_list (object_name,owner,object_type,privilege) values('DBMS_FILE_TRANSFER', 'SYS','PACKAGE','EXECUTE')

select * from dual;

commit;

Identify Objects that have been Installed

Although CIS Oracle Database Benchmark recommends revoking PUBLIC grants on 40 publicly-granted-packages, you may not have all these objects installed/configured in your database. Identify the objects that have been installed in your database AND have been granted to PUBLIC by running the following query:

select decode(t.owner,null,'No','Yes') installed,

       r.owner, r.object_name, r.object_type, r.privilege,

       t.grantee

from revoke_public_grant_list r, dba_tab_privs t

where r.object_name = t.table_name (+)

and r.owner = t.owner (+)

and r.privilege = t.privilege (+)

and ( t.grantee(+) = 'PUBLIC')

order by 1,2,3;

Identify Dependencies

Run the following query to identify dependent objects and their owners which are currently relying upon the PUBLIC grants. You may also notice the table that we created previously is included in the join clause.

select d.owner, d.name, d.type, d.referenced_owner, d.referenced_name, d.referenced_type

from dba_dependencies d

where (  d.referenced_name in ( select object_name from revoke_public_grant_list))

and d.owner != d.referenced_owner

and d.type != 'SYNONYM'

and not exists (select 'x'

                from dba_tab_privs t

                where t.table_name = d.referenced_name

                and t.grantee = d.owner

                and t.privilege =  'EXECUTE'

                )

-- order by 1,2 ;

order by 5,2 ;

Dependencies not Identified

Please note that the above SQL identifies dependent stored PL/SQL objects (packages, procedures, functions and triggers). The above SQL does NOT identify dependency sources such as ad-hoc PL/SQL scripts (which are created through declare/begin/end PL/SQL blocks) or dynamic SQLs.

You may have these publicly-granted-packages being called using dynamic SQLs. Fortunately, we don’t have such dependencies in our environment. If you do, query DBA_SOURCE view to identify such dependencies.

Prepare Direct Grant Script

If you revoke PUBLIC grants without granting EXECUTE privilege on these objects to the owners of the dependent objects, the dependent objects will become INVALID, causing issues with the applications that rely on these objects. This step generates a SQL script that would grant EXECUTE on the required packages to the owners of the dependent objects. This is the most important step.

Spool direct_grants.sql
select distinct 'grant execute on ' || d.referenced_name || ' to ' || d.owner || ';' Stmt

from dba_dependencies d

where (  d.referenced_name in ( select object_name from revoke_public_grant_list))

and d.owner != d.referenced_owner

and d.type != 'SYNONYM'

and not exists (select 'x'

                from dba_tab_privs t

                where t.table_name = d.referenced_name

                and t.grantee = d.owner

                and t.privilege =  'EXECUTE'

                )

order by 1;
spool off

Prepare Revoke Script

This step generates a script to revoke PUBLIC grants.

Spool revoke_public_grants.sql
select distinct 'revoke execute on ' || table_name || ' from public ;' Stmt

from dba_tab_privs

where (  table_name in ( select object_name from revoke_public_grant_list))

and grantee = 'PUBLIC'

order by 1;
spool off

Prepare Rollback Script

What if you revoked PUBLIC grants which caused havoc? Let’s prepare a rollback script!

Spool rollback_public_grants.sql
select distinct 'grant execute on ' || table_name || ' to public ;' Stmt

from dba_tab_privs

where (  table_name in ( select object_name from revoke_public_grant_list))

and grantee = 'PUBLIC'

order by 1;

Prepare Audit Script

Although this is an optional step, it could be quite handy if you face any issues after revoking PUBLIC grants. This step prepares a script to enable auditing on these objects so that you could get more information about the failures and errors.

spool enable_auditing.sql
select 'audit execute on ' || table_name || ' whenever not successful; ' Stmt

from dba_tab_privs

where (  table_name in ( select object_name from revoke_public_grant_list))

and grantee = 'PUBLIC'

order by 1;
spool off

Implement

Please note that when you revoke PUBLIC grants, dependent objects will become INVALID. The time to recompile INVALID objects depends on the number of INVALID objects. It is recommended that this task be performed during maintenance window to prevent any application issues or outages. Run the scripts generated above as follows:

@?/rdbms/admin/utlrp

@direct_grants.sql

@revoke_public_grants

@?/rdbms/admin/utlrp

@enable_auditing

The number of INVALID objects should be zero, but if it is not, then the number should be the same as before and after the revoke.

Validate/Test Applications

With PUBLIC grants replaced by DIRECT grants to the owners of the dependent objects, there should not be any issues with your database or applications. But, and there is always But, we should test our applications.

Identify Grantees of these Objects

Although you have revoked PUBLIC grants or ensured that PUBLIC grants don’t exist on these objects, we should make sure that these powerful packages have been granted to those authorized users who need access. Run the following query to see who have access on these packages:  

select table_name,

       listagg(grantee, ', ') within group (order by grantee) "Grantees"

from dba_tab_privs

where (owner, table_name) in ( select owner, object_name

                               from revoke_public_grant_list )

group by table_name ;

Monitor Audit Logs

Continue to monitor audit logs to identify issues, if any.

Other Considerations

Oracle may grant EXECUTE privilege again to PUBLIC during upgrade or patching. Re-run the dependency script to make sure that no additional direct grants are needed, and then revoke PUBLIC grants if needed.

There was a concern that the future DB upgrade or patching could run into issues as we had revoked PUBLIC grants, but we didn't face any such issues during 12.2 and 19c upgrades.