UTL_FILE revoke from PUBLIC

From: Charles J. Fisher <cfisher_at_rhadmin.org>
Date: Fri, 22 Apr 2005 20:56:35 GMT
Message-ID: <Pine.BSO.4.58.0504221542380.25071@bart.rhadmin.org>

I was recently asked to revoke UTL_FILE from PUBLIC by a colleague in IT security, following recommendations from NGSS (and their automated "squirrel" scanner).

Supposedly, Oracle recommends revoking this privilege:

http://oraclelon1.oracle.com/docs/cd/A91202_01/901_doc/server.901/a90117/secure.htm#8738

However, I've noticed that several sys-owned objects in the data dictionary go invalid (and stubbornly remain so until utlrp.sql is executed). One of my Oracle 7 instances went into a tailspin with a circular dependency between DBMS_UTILITY and DBMS_DDL, and I was forced to run CATALOG and CATPROC.

What is everyones' experience with revoking UTL_FIL?

Also, how serious is utlrp's suggestion to run in startup restrict?

I've attached an example of this behavior on a fresh 8i instance below.

   / Charles J. Fisher     | "If Tyranny and Oppression come to this land,   /
  /  cfisher_at_rhadmin.org   |   it will be in the guise of fighting a        /
 /   http://rhadmin.org    |   foreign enemy." - James Madison             /


---------------------------------------------------------------------------

SVRMGR> connect internal
Connected.

SVRMGR> startup
ORACLE instance started.

Total System Global Area                          4919456 bytes
Fixed Size                                          73888 bytes
Variable Size                                     4562944 bytes
Database Buffers                                   204800 bytes
Redo Buffers                                        77824 bytes

Database mounted.
Database opened.

SVRMGR> select distinct status from dba_objects; STATUS

VALID
1 row selected.

SVRMGR> revoke execute on utl_file from public; Statement processed.

SVRMGR> select distinct status from dba_objects; STATUS

INVALID
VALID
2 rows selected.

SVRMGR> select owner, object_name, object_type from dba_objects where status='INVALID';

OWNER                          OBJECT_NAME                      OBJECT_TYPE


-----------------------------------------------------------------------------

SYS                            DBMS_LOGMNR_D                    PACKAGE BODY
SYS                            DBMS_SUMREF_UTIL                 PACKAGE BODY
SYS                            DBMS_SUMREF_UTIL2                PACKAGE BODY
SYS                            UTL_FILE                         PACKAGE BODY

4 rows selected.

SVRMGR> alter package dbms_logmnr_d compile; Statement processed.

SVRMGR> alter package dbms_sumref_util compile; Statement processed.

SVRMGR> alter package dbms_sumref_util2 compile; Statement processed.

SVRMGR> alter package utl_file compile;
Statement processed.

SVRMGR> select owner, object_name, object_type from dba_objects where status='INVALID';

OWNER                          OBJECT_NAME                      OBJECT_TYPE   


------------------------------ -------------------------------- ------------------

SYS                            DBMS_LOGMNR_D                    PACKAGE BODY  
SYS                            DBMS_SUMADV                      PACKAGE BODY  
SYS                            DBMS_SUMMARY                     PACKAGE BODY  
SYS                            DBMS_SUMREF_CHILD                PACKAGE BODY  
SYS                            DBMS_SUMREF_PARENT               PACKAGE BODY  
SYS                            DBMS_SUMREF_UTIL                 PACKAGE BODY  
SYS                            DBMS_SUMREF_UTIL2                PACKAGE BODY  
SYS                            DBMS_SUMVDM                      PACKAGE BODY

8 rows selected.

SVRMGR> execute dbms_utility.compile_schema('sys');--no effect Statement processed.

SVRMGR> select owner, object_name, object_type from dba_objects where status='INVALID';

OWNER                          OBJECT_NAME                      OBJECT_TYPE   


------------------------------ -------------------------------- ------------------

SYS                            DBMS_LOGMNR_D                    PACKAGE BODY  
SYS                            DBMS_SUMADV                      PACKAGE BODY  
SYS                            DBMS_SUMMARY                     PACKAGE BODY  
SYS                            DBMS_SUMREF_CHILD                PACKAGE BODY  
SYS                            DBMS_SUMREF_PARENT               PACKAGE BODY  
SYS                            DBMS_SUMREF_UTIL                 PACKAGE BODY  
SYS                            DBMS_SUMREF_UTIL2                PACKAGE BODY  
SYS                            DBMS_SUMVDM                      PACKAGE BODY

8 rows selected.

SVRMGR> @OraHome1/rdbms/admin/utlrp.sql
Statement processed.

SVRMGR> select owner, object_name, object_type from dba_objects where status='INVALID';

OWNER                          OBJECT_NAME                                                                      OBJECT_TYPE   


------------------------------ -------------------------------------------------------------------------------- ------------------

0 rows selected. Received on Fri Apr 22 2005 - 15:56:35 CDT