Friday, November 19, 2010

Invalid objects after Revoking Execute On DBMS_JOB, DBMS_LOB, UTL_TCP, UTL_HTTP, UTL_SMTP, UTL_FILE from PUBLIC

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_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.


10 comments:

  1. Have you ever try to connect as public user to oracle?

    ReplyDelete
  2. I haven't till now.

    It'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"

    ReplyDelete
  3. Great summary. I too learned this the hard way by breaking things and having to re-grant the privileges.

    ReplyDelete
  4. Arg me too... it took me so long to find this too.

    ReplyDelete
  5. Great post.
    The EM policy warning gave me nightmare....
    Thanks for your great and simply solution.

    ReplyDelete
  6. Hi,
    I 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

    ReplyDelete
  7. Hello Firdous,

    have 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

    ReplyDelete
  8. 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.

    ReplyDelete
  9. I did it, but had to regrant as follows:

    grant 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;

    ReplyDelete
  10. 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