SEO

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...ecure.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@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.

On Fri, 22 Apr 2005 20:56:35 GMT, "Charles J. Fisher"
<cfisher@rhadmin.org> wrote:

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

Oracle 7 is desupported and Oracle 8i too, so a *fresh* 8i instance is
sheer madness.


--
Sybrand Bakker, Senior Oracle DBA

Charles, when you see an invalid package body if you just recompile the
body and not the specification then you can generally resolve the
circular dependency chain: alter package owner.packagename compile
body.

Oracle has a white paper or two on the side-effects of removing PUBLIC.
You may want to hunt them up for future reference.

HTH -- Mark D Powell --

Charles J. Fisher wrote:

> 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...ecure.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?

My suggestions in no particular order.
1. Never grant anything to public without very careful review
2. Never grant UTL_FILE to public no matter the review
3. Never had a problem running UTLRP.SQL no matter the conditions
though not on a production system with current users. That would
be sheer madness.
4. Don't do a new install with anything less than 9.2.0.4: Preferably
10.1.0.4.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)