Peters-MacBook-Pro:____12_2 pxf$ sqlplus sys/oracle1@//192.168.56.95:1539/orcl.localdomain as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon May 22 20:26:25 2017
Copyright (c) 1982, 2012, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production
SQL> set serveroutput on
SQL> @check_parameter
Then check the settings of O7_DICTIONARY_ACCESSIBILITY:
check_parameter: Release 1.0.2.0.0 - Production on Mon May 22 20:36:23 2017
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.
PARAMETER TO CHECK [utl_file_dir]: O7_DICTIONARY_ACCESSIBILITY
CORRECT VALUE [null]: FALSE
OUTPUT METHOD Screen/File [S]:
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:
Investigating parameter => O7_DICTIONARY_ACCESSIBILITY
====================================================================
Name : O7_DICTIONARY_ACCESSIBILITY
Value : FALSE
Type : BOOLEAN
Is Default : DEFAULT VALUE
Is Session modifiable : FALSE
Is System modifiable : FALSE
Is Modified : FALSE
Is Adjusted : FALSE
Description : Version 7 Dictionary Accessibility Support
Update Comment :
-------------------------------------------------------------------------
value is correct
PL/SQL procedure successfully completed.
For updates please visit /tools.htm
SQL>
This script check_parameter.sql is available from my website by clicking the link. The parameter is not removed after all in 12c R2 but only deprecated. Also this document does not state when this parameter will actually be removed. This is one of the key security parameters that has been included in most Oracle security guides for many years. If this parameter is set too TRUE then system privileges that include the word %ANY% will also apply to the SYS schema. So SELECT ANY TABLE with this set to TRUE will allow you to read SYS.USER$ for instance. It also controls other privileges with ANY such as EXECUTE ANY PROCEDURE to allow you to execute SYS owned packages. Of course it defaults to FALSE and should remain FALSE.
The other parameter of interest is utl_file_dir which controls access to directories on the host file system.
check_parameter: Release 1.0.2.0.0 - Production on Tue May 23 16:24:27 2017
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.
PARAMETER TO CHECK [utl_file_dir]:
CORRECT VALUE [null]:
OUTPUT METHOD Screen/File [S]:
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:
Investigating parameter => utl_file_dir
====================================================================
Name : utl_file_dir
Value :
Type : STRING
Is Default : DEFAULT VALUE
Is Session modifiable : FALSE
Is System modifiable : FALSE
Is Modified : FALSE
Is Adjusted : FALSE
Description : utl_file accessible directories list
Update Comment :
-------------------------------------------------------------------------
value is correct
PL/SQL procedure successfully completed.
For updates please visit /tools.htm
SQL>
This also has been in security guides and standards since the SANS Step-by-Step. In the old days we recommended not setting to * or . or .. or \ or / or many other things such as system locations but since the arrival of DIRECTORY objects you should not use utl_file_dir for anything. utl_file_dir is global and affects any user with just CREATE SESSION so is dangerous if dangerous locations are specified. The DIRECTORY object alternative is much better as the controls are at the user level as grants can be made against specific DIRECTORY objects so allowing fine grained controls. Again utl_file_dir is deprecated but the removal version/date is not stated.
utl_file_dir is more relevant to us as users as there is more likely to be an application impact but O7_DICTIONARY_ACCESSIBILITY should be left set to FALSE no matter what.
Good luck!!
