This discussion is archived
6 Replies Latest reply: Aug 20, 2013 7:25 AM by Artem S. Tsygankov RSS

Revoked grants from PUBLIC

Artem S. Tsygankov Newbie
Currently Being Moderated

Dear colleagues,

Our audit has demanded revoke next grants from PUBLIC.

DBMS_LDAP

DBMS_SCHEDULER

UTL_FILE

DBMS_RANDOM

DBMS_LOB

DBMS_XMLGEN

UTL_SMTP

DBMS_JAVA_TEST

I have done this on test database and this led to crash of majority system objects.

If it is possible can I do this recommendations from our audit?

Variant "it is impossible" not satisfied our audit

  • 1. Re: Revoked grants from PUBLIC
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated

    Pl post details of OS and database versions. What exactly do you mean by "crash of majority system objects" ?

     

    Pl also see these MOS Docs

     

    1165830.1

    247093.1

     

    HTH
    Srini

  • 2. Re: Revoked grants from PUBLIC
    Artem S. Tsygankov Newbie
    Currently Being Moderated

    Hi Srini,

     

    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

    PL/SQL Release 11.2.0.3.0 - Production

    CORE    11.2.0.3.0      Production

    TNS for Linux: Version 11.2.0.3.0 - Production

    NLSRTL Version 11.2.0.3.0 - Production

     

    Oracle Linux Server release 6.4

     

    >>crash of majority system objects

    Sorry for my English, Too many invalid objects on sys, xdb, olapsys and other schemas. But after execute $ORACLE_HOME/rdbms/admin/utlrp.sql many objects compiled.

    Now I have 48 invalid objects:

    SYS.DBMS_CUBE_EXP

    DBSNMP.DM_FMTLIB

    EXFSYS.DBMS_EXPFIL_IR

    CTXSYS.DRIDOC

    CTXSYS.DRIREP

    CTXSYS.DRIXMD

    CTXSYS.DRVDDL

    CTXSYS.DRVDOC

    CTXSYS.DRVIMR

    CTXSYS.CTX_DOC

    CTXSYS.CTX_REPORT

    XDB.DBMS_XMLDOM

    XDB.DBMS_XMLPARSER

    XDB.DBMS_XSLPROCESSOR

    XDB.DBMS_XDBRESOURCE

    XDB.DBMS_XDB

    XDB.DBMS_XDBUTIL_INT

    XDB.DBMS_CSX_INT

    XDB.DBMS_XMLSCHEMA

    XDB.XIMETADATA_PKG

    XDB.DBMS_XDBT

    MDSYS.SDO_CS

    MDSYS.SDO_UTIL

    MDSYS.SDO_GEOR

    MDSYS.SDO_GEOR_INT

    MDSYS.SDO_ROUTER_PARTITION

    MDSYS.SDO_NET_PARTITION

    MDSYS.SDO_NET_PARTITION

    MDSYS.SDO_NET

    MDSYS.SDO_NETWORK_MANAGER_I

    MDSYS.SDO_OLS

    MDSYS.SDO_PC_PKG

    MDSYS.SDO_TIN_PKG

    OLAPSYS.CWM$EXPORT

    OLAPSYS.CWM2_OLAP_MANAGER

    OLAPSYS.CWM2_OLAP_UTILITY

    OLAPSYS.CWM2_OLAP_EXPORT

    OLAPSYS.CWM2_OLAP_PC_TRANSFORM

    OLAPSYS.CWM2_OLAP_AW_AWUTIL

    OLAPSYS.OLAPFACTVIEW

    OLAPSYS.OLAPDIMVIEW

    OLAPSYS.DBMS_ODM

    OLAPSYS.CWM2_OLAP_OLAPAPI_ENABLE

    OLAPSYS.DBMS_AWM

    APEX_030200.WWV_FLOW_LDAP

    APEX_030200.WWV_FLOW_LDAP

    APEX_030200.WWV_FLOW_CUSTOM_AUTH_LDAP

    APEX_030200.WWV_FLOW_DATALOAD_XML

  • 3. Re: Revoked grants from PUBLIC
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated

    What errors do you get when you try to compile these objects manually ? have you reviewed the two MOS Docs ? It is expected that functionality will be broken when grants from PUBLIC are revoked.

     

    HTH
    Srini

  • 4. Re: Revoked grants from PUBLIC
    Artem S. Tsygankov Newbie
    Currently Being Moderated

    Errors such as:

    identifier 'DBMS_RANDOM' must be declared

    identifier 'DBMS_LOB' must be declared

    identifier 'UTL_FILE' must be declared

    identifier 'DBMS_SCHEDULER' must be declared

    etc

     

    And Yes, I have reviewed the two MOS Docs and understood that functionality will be broken.

    But why I cannot do following? (for example):

    1. Create some role, e.g. MY_PUBLIC.

    2. Give privileges to role MY_PUBLIC:

    grant execute on UTL_FILE to MY_PUBLIC;

    grant execute on DBMS_XMLGEN to MY_PUBLIC;

    grant execute on DBMS_LOB to MY_PUBLIC;

    grant execute on DBMS_RANDOM to MY_PUBLIC;

    grant execute on DBMS_JAVA_TEST to MY_PUBLIC;

    grant execute on DBMS_SCHEDULER to MY_PUBLIC;

    grant execute on DBMS_LDAP to MY_PUBLIC;

    3. Give role MY_PUBLIC to all users and roles of DB.

    4. Revoke these privileges from PUBLIC.

    5. Recompile invalid objects.

     

    It doesn't work and I don't understand why. May be PUBLIC uses how hard code in system objects?

  • 5. Re: Revoked grants from PUBLIC
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated

    Pl identify exactly what privileges you have revoked from PUBLIC. Have you followed the 5 steps in MOS Doc 247093.1 ? Be aware that some privileges need to be granted directly, and not thru a role - see 234551.1

     

    HTH
    Srini

  • 6. Re: Revoked grants from PUBLIC
    Artem S. Tsygankov Newbie
    Currently Being Moderated

    Ok, Srini,

    Thanks a lot!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points