UTL_FILE revoke from PUBLIC
08/01/2019 11:47
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@rhadmin.org | it will be in the guise of fighting a / / http://rhadmin.org | foreign enemy. - James Madison /--------------------------------------------------------------------------- SVRMGR> connect internalConnected.

SVRMGR> startupORACLE instance started.

Total System Global Area 4919456 bytesFixed Size 73888 bytesVariable Size 4562944 bytesDatabase Buffers 204800 bytesRedo Buffers 77824 bytesDatabase mounted.

Database opened.

SVRMGR> select distinct status from dba_objects;STATUS-------VALID1 row selected.

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

SVRMGR> select distinct status from dba_objects;STATUS-------INVALIDVALID2 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 BODYSYS DBMS_SUMREF_UTIL PACKAGE BODYSYS DBMS_SUMREF_UTIL2 PACKAGE BODYSYS UTL_FILE PACKAGE BODY4 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 effectStatement 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.sqlStatement processed.

SVRMGR> select owner, object_name, object_type from dba_objects where status='INVALID';OWNER OBJECT_NAME OBJECT_TYPE ------------------------------ -------------------------------------------------------------------------------- ------------------0 rows selected.


Source is Usenet: comp.databases.oracle.server
Sign in to add a comment

Answer score: 5
08/01/2019 11:47 - Charles, when you see an invalid package body if you just recompile thebody and not the specification then you can generally resolve thecircular dependency chain: alter package owner.packagename compilebody.

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

Source is Usenet: comp.databases.oracle.server
Sign in to add a comment

Answer score: 5
08/01/2019 11:47 - On Fri, 22 Apr 2005 20:56:35 GMT, Charles J. Fisher Oracle 7 is desupported and Oracle 8i too, so a *fresh* 8i instance issheer madness.


Source is Usenet: comp.databases.oracle.server
Sign in to add a comment

eDiscover
Helpforce eDiscover provides technical articles updated each dayHelpforce eDiscover RSS feed contains the latest technical articles in RSS
Click the logo to go back to the main page
Search eDiscover
  
Categories

Click an icon to go to that category

Helpforce eDiscover contains articles about Microsoft Windows Helpforce eDiscover contains articles about Apple products and MacOS Helpforce eDiscover contains articles about Linux and POSIX operating systems Helpforce eDiscover contains articles about Helpforce Helpforce has a large variety of technical information and articles for you to read Helpforce eDiscover contains articles about databases, MYSQL, SQL Server Oracle Helpforce eDiscover contains articles about Java, JVM and the JRE Helpforce eDiscover contains articles about the QNX operating system Helpforce eDiscover contains articles about Oracle Solaris and Open Solaris Helpforce eDiscover contains articles about RISC OS, Acorn and the BBC Micro Helpforce eDiscover contains articles about Amiga and AmigaOS

Type your comment into the box below