6 Replies Latest reply: Aug 20, 2013 9:25 AM by Artem S. Tsygankov RSS

    Revoked grants from PUBLIC

    Artem S. Tsygankov

      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

          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

            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

              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

                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

                  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

                    Ok, Srini,

                    Thanks a lot!