Pete Finnigan's Oracle Security Weblog

Yesterday I made a short video to talk about my two day class "How to Perform a Security audit of an Oracle database" and added the video to YouTube. This class is going to be delivered at a few public events this year (so far); the next being in Paris on the 13th and 14th June with EasyTeam.

I am also going to teach this class online for those that cannot or do not want to travel. I will teach two classes on USA timezones, one on New York time and one on Los Angeles times. Each class includes tuition by myself, around 50 demos and lots of practical experience. You will get a download of all the class materials including pdfs of the slides, all the demo code and over 100 free tools and scripts including simple security scanner written in PL/SQL

If you would like to take part in any of these trainings then please review the public trainings page and register.

I was not in the beta program for Oracle database 12c release 2 but when I was discussing security changes in the new release with some people who were in the beta they told me that O7_DICTIONARY_ACCESSIBILITY and utl_file_dir parameters had gone in 12cR2. This is not the case:

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

I am going to be teaching my two day class "How to perform a security audit of an Oracle database" in Athens, Greece on the 30th and 31st May 2017. This is advertised on Oracle University website and you can register there or contact me and I will put you in touch with the Oracle team on the ground.

I have also just agreed two new dates with Oracle University to teach my one day classes. The first is on the 28th June and is my one day class "Secure and lock down Oracle". This is a great class and we spend a whole day starting with an open default database with two applications, we attack that database and then lock down most aspects of it and finally hack it again at the end of the day to show that we in fact secured the data in it. The second class to be taught on Oracles LVC on the 5th July 2017 is my new one day class "An Appreciation of Oracle Security". This class takes some elements from my other 5 days of Oracle security training plus some new material on subjects such as incident response to an attack in an Oracle database and also forensic analysis of an Oracle database.

I am also teaching my two day class "How to perform a security audit of an Oracle database" in person in Paris on the 13th and 14th of June 2017.

Finally I will be teaching my "How to perform a security audit of an Oracle database" online via the webbed platform on EST timezone (i.e. New York) on the 26th to 27th June and again on PST timezone (i.e. Los Angeles) and finally on London timezone on the 10th to 11th July 2017.

The details for each of these classes are on my public training dates page along with links to my class outlines. To register follow the register links or if in doubt email me and if the class is with us I will take your booking or if with EasyTeam or Oracle University i will be able to direct you to the right people to book your places.

I have spent some of my spare time (not a lot available unfortunately) researching Oracle 12c Release 2 security changes. This research has looked at the big changes announced by Oracle. There is nothing really major like with previous releases but still quite a lot of security related changes. I have also looked at the little details that I have spotted so far that have changed; interestingly as I was not in the Beta some information gleaned from those that were seems to have either been incorrect or has changed. For instance I was told that O7_DICTIONARY_ACCESSIBILITY was removed in 12.2.0.1 but its still there and is in fact only deprecated. Some research has followed removed and deprecated items and some followed the major change of application root containers and other changes in Multitenant in 12cR2 such as the ability now to formally add metadata links and object links. Some things are the same of course; OPS$ is still OPS$; some things are expected such as more new default users and roles. Interestingly when we first tried PFCLScan our database security scanner against a 12cR2 database it worked with no errors and no changes needed (to support 12cR2) but many years of Oracle made me suspect that would be the case; Oracle rarely changes historic interfaces and rarely removes anything critical that would affect applications and tools. We are of course adding new checks to PFCLScan for 12cR2.

In the latest version of PFCLScan we have also just added our first E-Business Suite policy to check some of the Security basics of E-Business Suite. We will also add an Oracle APEX security scan policy soon as well; its in development.

Our company has also just become an Oracle Gold Partner - we have yet to update the logo on our site to Gold but that will be done soon.

I blogged about old course manuals for our training courses a few weeks ago and these were snapped up straight away and despatched across the world. I have now one extra 2015 printed manual for my one day class "Designing Practical Audit Trails" that we returned to me a week or so ago. A printer who prints our manuals for classes it seems sent it to someone else in 2015 just before our class in York and the organisation that it was sent to posted it to me - So this manual is now also available; its pristine condition and I will take £30 GBP + Postage + VAT (if applicable). If anyone is interested then please email me and we will arrange it - obviously first come; first served.

We are also considering to sell the current class materials to people who would like to buy them. They will be higher price than the old ones we sold recently and the 2015 manual mention here. You would get the scripts and tools from the class and paper manuals BUT you would not benefit from actually attending the training and listening to the delivery but we realise that some people may want just the manuals. If you are interested contact me as we will decide whether its worth the effort to set this up based on the interest levels.

We have also now just purchased our first real server as we continue to grow as we have relied on various office level machines and a RAID storage for many years but we have just bit the bullit and bought a real server with multi-CPU, multi hot swappable SFF disks, hot swappable PSU's massive RAM and storage etc. This will fortify our development and testing regimes for PFCLScan and PFCLObfuscate as soon as we get it live. It took two people to lift it into place..:-)

OK, that wraps it up for this post

I downloaded Oracle 12cR2 from Oracle when it became available in March and installed a legacy SE2 database and also a single PDB multitenant database and started some investigations to discover and look at the new security features added in Oracle 12cR2 and also more importantly to investigate the subtle changes made to the database that affect Oracle security and also the bigger non-security changes added to Oracle 12cR2 that could have a security angle.

I have made a lot of notes and I will make some blog posts around some of the new security features / changes in 12cR2 as time allows. The last few weeks have been extremely busy due to client work, teaching training classes, product development on PFCLScan and also company year end so blogging has taken a back seat for a few weeks.

I wanted to make this first post on Oracle 12cR2 Security a simple and short one about the fact that when you install 12cR2 and choose to have a sample database the installer now did not choose by default the port 1521 for the listener and instead in my case it chose 1539 - well, at least in my case. This is not massively different from 1521 but it was nice to see that Oracle did not choose 1521 as its default. I checked with netstat and nothing else was using 1521 to force it to use 1539 instead. I did not find any documentation (yet) that states that Oracle use 1539 instead and a search of Google shows one post from 2016 where someone with 12.1 had Oracle change the port to 1539.

So I decided on Friday evening to try this again and installed a new Oracle Linux 7.3 VM and then installed Oracle 12cR2 SE2 database (My company is a Silver partner currently so we are limited to SE and SE2 at this time). I used the same settings as I did in March and chose defaults except to choose SE2 and also to choose non-CDB. Everything ran correctly except the necta failed and I looked at the logs and it claimed that port 1521 was in use - This was not true and I checked with netstat. So i clicked try again and it succeeded so the choice of port 1521 / 1539 could be related to the fact that necta failed and not a better security setting. The install in March did not fail in nectar and 1539 was also used as i noted it but have now deleted the VM so cannot look further into the install logs.

After the installation completed the listener is not running:



Then I changed the current_listener to LISTENER and then started the listener BUT no services; this is to be expected as the auto registration works only in 1521. Here is the listener running but no services:



The listener is up and running so lets add the local_listener database parameter and then register the services and see what happens:



Finally we can now get into the database via the listener:



In summary, its a good thing that Oracle chose a non 1521 port for my listener; whether its intended as a new security feature I am not yet certain as I need to install the database software again as there is no evidence that i can find that this is normal as the installer said necta failed and then allowed me to try again. Irrespective of this you should not run your database on 1521 as its not just a known default but some things work because of 1521; i.e. alter system register; or connect to the listener and not set the current_listener if the listener is running on port 1521.

As an aside the default port of 5500 for the database manager website is still used and the XDB service is also still enabled by default in 12cR2!!

Changing the port will not stop any determined person as a port scanner would find the database listener anyway but its a default and defaults sometimes make it easier for a script kiddie type attacker who doesn't "know"