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_HOME/rdbms/admin/@utlrp.sql 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.
Have you ever try to connect as public user to oracle?
ReplyDeleteI haven't till now.
ReplyDeleteIt's nice to know that when user connects as SYSOPER he becomes PUBLIC... thx ;)
$ sqlplus /nolog
SQL*Plus: Release 10.2.0.4.0 - Production on Sat Nov 20 22:44:37 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
SQL> connect sys/password as sysoper
Connected.
SQL> sho user
USER is "PUBLIC"
Great summary. I too learned this the hard way by breaking things and having to re-grant the privileges.
ReplyDeleteArg me too... it took me so long to find this too.
ReplyDeleteGreat post.
ReplyDeleteThe EM policy warning gave me nightmare....
Thanks for your great and simply solution.
Hi,
ReplyDeleteI have same problem .. this is what i did as per standard:
REVOKE execute ON SYS.DBMS_RANDOM from public;
REVOKE execute ON SYS.UTL_FILE from public;
REVOKE execute ON SYS.DBMS_JOB from public;
REVOKE execute ON SYS.DBMS_LOB from public;
REVOKE execute ON SYS.UTL_SMTP from public;
REVOKE execute ON SYS.UTL_TCP from public;
REVOKE execute ON SYS.UTL_HTTP from public;
this invalidated many objects:
when i regrant back do i need to regrand like this:
grant execute ON SYS.DBMS_RANDOM to public;
grant execute ON SYS.UTL_FILE to public;
grant execute ON SYS.DBMS_JOB to public;
grant execute ON SYS.DBMS_LOB to public;
grant execute ON SYS.UTL_SMTP to public;
grant execute ON SYS.UTL_TCP tto public;
or withou SYS option like :
grant execute ON DBMS_RANDOM to public;
please do let me know when you get time.
Regards
Hello Firdous,
ReplyDeletehave you tried to recompile invalid objects?
-- Compile
SQL> shutdown immediate;
SQL> startup restrict;
SQL> @$ORACLE_HOME/rdbms/admin/@utlrp.sql
SQL> shutdown immediate;
SQL> startup;
Do you still have any problems?
Regards,
Marko
The only way you can ensure in advance that you won't screw things up is if Oracle would give up the ddl they've wrapped so you could see all the calls made to these packages.
ReplyDeleteI did it, but had to regrant as follows:
ReplyDeletegrant execute on sys.UTL_FILE to ORACLE_OCM;
grant execute on sys.DBMS_SQL to ORACLE_OCM;
grant execute on sys.DBMS_LOB to WMSYS;
grant execute on sys.DBMS_LOB to XDB;
grant execute on sys.UTL_FILE to XDB;
grant execute on sys.UTL_FILE to WMSYS;
grant execute on sys.DBMS_SQL to XDB;
grant execute on sys.DBMS_SQL to SYSTEM;
grant execute on sys.DBMS_SQL to WMSYS;
grant execute on sys.DBMS_SQL to DBSNMP;
grant execute on sys.DBMS_SQL to GSMADMIN_INTERNAL;
grant execute on sys.DBMS_JOB to DBSNMP;
grant execute on sys.DBMS_OBFUSCATION_TOOLKIT to DBSNMP;
Thank you for the article. The Metalink notes will be very helpful in providing documentation on why not to revoke some of these from PUBLIC as recommended by a security audit by non-DBA's
ReplyDelete