Revoking UTL_* execute from PUBLIC and ACLs

Version -- Oracle 11.2.0.4

In most of the oracle production dbs public executes are revoked as per the security requirements.
But doing so can cause some of the functionalities to fail.

Here we will discuss the effect on ACLs by revoking public execute on UTL_* packages.

Revokes -->

'revoke execute on UTL_TCP from public'
'revoke execute on UTL_HTTP from public'
'revoke execute on UTL_SMTP from public'

now the ACL will not work since the users assigned to ACL do not have the required privileges on ULT packages.
But this also triggers another event.

select * from dba_network_acls will return a ora-00600 as below.

 
ERROR at line 1:
ORA-00600: internal error code, arguments: [qmxqtmChkXQAtomMapSQL:2], [], [], [], [],
[], [], [], [], [], [], []

10046 trace for the session shows below,

 XDB.DBMS_CSX_INT is invalid.

Querying dba_objects show lots of invalid objects owned by XDB and SYS.

To resolve,

First UTL pacakage privileges were granted to XDB user,

grant execute on utl_http to xdb;

grant execute on utl_smtp to xdb;

grant execute on utl_tcp to xdb;

Then run  utlrp.sql to validate all invalids.

Above will allow to query on XDB and ACL realted views including dba_network_acls.

To get ACLs working the related users need to be provided with execute privileges on the UTL packages.
Eg. If the ACL principle is aclusr then,

grant execute on utl_http to aclusr;

grant execute on utl_smtp to aclusr;

grant execute on utl_tcp to aclusr;