You can use Oracle Grid Control for checking policy violations, to keep compliance with company security or configuration standards. Grid Control comes with pre-installed set of policies and recommendations of best practices for databases.
With having that in mind I checked Critical Policy Violations for my database and noticed this critical violations:
... Execute Privileges on DBMS_JOB to PUBLIC Restricted Privilege to Execute UTL_TCP Restricted Privilege to Execute UTL_HTTP Restricted Privilege to Execute UTL_SMTP Execute Privileges on DBMS_LOB to PUBLIC Execute Privileges on UTL_FILE To PUBLIC ...
It is recommended to remove excessive privileges from some users to prevent possible attacks for happening in the first place. So based on recommendations from Grid Control I’ve decided to revoke noted privileges from PUBLIC and check what will happen after.
As SYS user I executed:
REVOKE EXECUTE ON utl_tcp FROM public; REVOKE EXECUTE ON utl_smtp FROM public; REVOKE EXECUTE ON utl_http FROM public; REVOKE EXECUTE ON utl_file FROM public; REVOKE EXECUTE ON dbms_job FROM public; REVOKE EXECUTE ON dbms_lob FROM public;
Immediately after revoking those grants I checked database status noticed invalid objects in sys schema, mdsys, ordsys, wmsys, etc.
My next move was rollback:
GRANT EXECUTE ON dbms_job TO public; GRANT EXECUTE ON utl_file TO public; GRANT EXECUTE ON utl_tcp TO public; GRANT EXECUTE ON utl_http TO public; GRANT EXECUTE ON utl_smtp TO public; GRANT EXECUTE ON dbms_lob TO public; -- Compile SQL> shutdown immediate; SQL> startup restrict; SQL> @$ORACLE_HOMEfirstname.lastname@example.org SQL> shutdown immediate; SQL> startup;
After granting privileges back and recompiling invalid objects everything was valid again.
This test was very educational for me.
My next step was searching for Metalink notes that explain this situation and I’ve found very useful notes - [ID 247093.1] [ID 1165830.1] [ID 797706.1]
Important lesson learned in this experiment was - do not blindly follow EM/GC policy recommendations without consulting Oracle support or (at least) skimming through Metalink notes.
From Metalink note:
“Oracle highly recommends keeping DBMS_SQL, DBMS_JOB, DBMS_LOB granted to PUBLIC just to keep user’s database running. Otherwise lots of things break including some of the utilities like Import/Export, Datapump expdp/impdp, SQL*Loader, etc.”
PUBLIC is pseudo user/schema used by database for its internal functionality. Privileges are granted to PUBLIC with CATALOG and CATPROC components for 10g database instance.
If you still want to revoke privileges from PUBLIC make sure that you re-grant privileges to user that require them and recompile possible invalidated objects.