Call: +44 (0)7759 277220 Call
Blog

Pete Finnigan's Oracle Security Weblog

This is the weblog for Pete Finnigan. Pete works in the area of Oracle security and he specialises in auditing Oracle databases for security issues. This weblog is aimed squarely at those interested in the security of their Oracle databases.

Oracle Security Blog Posts

I teach many training classes on Oracle security to lots of students worldwide both on-site and on-line and one area I often cover quote briefly is where can you find more information or keep up to date on Oracle security and of course one of those sources of information is obviously blogs.

In the old days; more than ten years ago there were quite a few people posting Oracle Security blogs but this stagnated and died mostly. I was the first and oldest Oracle security blog and was joined by Alex, David, Slavic, Steve and a few more who would blog regularly on the subject of Oracle Security. Most have stopped almost completely or post such a small amount of posts now and then. There are not really any new people who post about only Oracle security and none that do it exclusively like me. In fact myself and Steve Kost are probably the only regulars still. Some people like Laurent, Dennis, Kamil and Rodrigo Jorge do post on the subject of Oracle security from time to time as do others but there does not seem to be a ground swell of Oracle security posting as there was more than 10 years ago.

I decided to count up and see how my Oracle Security blogging has changed over the years. Here is a graph from 2004 to now.


Analysis of Oracle Security blog posts on PeteFinnigan.com Limited



As you can see i posted a lot in the early days and this curved off as time went on. Why? well there was a golden age of Oracle security when people were starting out to consider the problems and issues in securing Oracle. The start of this coincided with the start of the push for Oracle security patches; the start of finding SQL injection issues in Oracle built in packages and the overall buzz around securing Oracle. This tailed off as did the discussions around why Oracle didn't tell us exactly what was fixed in every CPU; people just got on and moved on. I still blogged but less so as i was just too busy. Now, i am even busier BUT i want to get back to talking about Oracle security in more details again.

The time of data security is now; we have more and more breaches of data, almost daily and two big ones in the last weeks; 500 Million records lost at Marriott (for sure an Oracle database was involved) and Quora forums also had a big breach; We have just had GDPR go live and there is a definite push towards people doing more around Oracle security and data security in general. Just recently Oracle made Privilege analysis free with EE and 18x XE also includes most of the security cost options for free. I discussed this very briefly in my recent post on super locking an Oracle database.

I do plan to post more going forwards on Oracle security. I have a big list of collected subjects that I want to cover here; and i will make it my new years resolution to try and post more. Well thats me and I think its write to talk more about securing Oracle in particular and securing data in general. We are for sure in an era of a heightened need to secure data.

Virtual Patching or Good Security Design instead?

I got an email from someone recently who asked me about virtual patching for Oracle as they were running an out of date version of Oracle and were thinking that virtual patching maybe a good solution to make their database more secure when they cannot get PSU / CPU Security patches for the database. I wrote them a reply and then thought actually this is a good topic for a blog post; so here is my reply a little edited to remove any identity.

Around 10 - 12 years ago a lot of companies were in the virtual patching space (BlueLane, Sentrigo, ....). I always believed this was a non-starter longer term and at best a very limited solution to a very specific problem (i.e. no patches). The problems at a high level are:

1 - Oracle does not publish what it fixes in each CPU (PSU from 12c). It only credits external people who follow their rules and does not credit internal fixes. Mary Ann Davidson the head of security many years ago said (I think in her blog or perhaps it was Eric Maurice; i am not certain now but the point is still valid) external report 20% of issues and internals 80%. So we have no fixed list of whats fixed in a CPU. The tool vendors must reverse engineer every CPU and then develop a check to block an attack. Everything fixed by Oracle has many possible attack vectors. A SQL Injection in a package such as DBMS_METADATA may be exploited to grant DBA to the user BUT it also the attack against the same issue could be completely different. Most of these virtual patch products focus on specific attacks and completely generic attacks are hard to match and detect. Also most of the VP products tended to focus only on remotely exploitable issues

2 - Securing data in an Oracle database falls into a number of grouped activities:

A - hardening and patching (what my emailer wanted to cover); these form a small part of securing data. In general if i can hack and steal card details from your database; then i can probably still do it even if you harden or patch. This is because the patch and the hardening does nothing to secure data; they are general counter-measures

B - Limit access to the database; this can be with simple tools such as firewalls, Oracle listener settings; logon triggers, users password security. This is the number one issue to solve; STOP people connecting to your database. If they cannot connect, they cannot hack or steal data

C - User Access controls; for the users that you do allow to connect then they should have least privilege; i.e. just exactly the right amount of privileges to do their job and no more. In my experience of performing security audits for a very long time for clients people have created the opposite; most privilege design; i.e. no granularity and no respect for what the purpose of a user actually is

D - Data access controls; we must understand where our data is and how its used and then how its secured. We must know who, what, why, when is allowed and design permissions on code and data accordingly. The main problem is that permissions at a table or procedure level are too granular. Therefore we need the next item (E)

E - We should use context based security around access controls to the database, around user rights and around data access controls. This can by use of Oracle VPD, OLS, DV, TSDP and more OR it can be done for free. For instance DV out of the box makes some limited hardening; changes some permissions on DBA, SYS and SYSTEM and creates separation of duties for privileged accounts. We can do a lot of this for free by simple working practice and good database security design; i.e. DV limits the system %ANY% privileges; instead we can not grant them to "our" DBA role; simple! We can seperate security DBA work from OPS DBA work. We can even create controls like realms by use of DML or SYSTEM triggers or by views with functions to control access. Don't just assume that these great products are only available if you have EE or money to license them; we can still simulate some of the "intent" of these products; not as perfect as DV BUT most databases in my experience are not secure anyway so a home grown method + some bits of code is fine; its better than no security; don't get hung up on perfection; security is always a compromise.

F - Finally we should implement a useful, succinct and easy to manage audit trail around all of the above. i.e. not just audit access to key business data but also audit the database engine itself for abuse; for changes to audit, for changes to security and also to detect possible attacks

There are two problems to solve in securing Oracle; 1) hardening & patching and 2) data security design; my experience is that people are not brilliant at both BUT if they have done something then its a nod to patching maybe (and this will not secure your actual data) and a nod to some level of hardening (again in general this will not secure your data). Your money is better spent focusing on the actual problem; securing the data in your database. This is especially pertinent now that we have GDPR!

Using a virtual patch is a short term solution and flawed at best. Even if you cannot patch if you can limit the access to the listener completely and limit database access with controls and data access controls as well then even if you cannot patch it may be better to spend your money wisely on data security design to see if you can create controls on the access and data instead.

How can we help you with this? well in a number of ways:

1 - Scan all of your databases with PFLScan for security issues and understand the security currently implemented in all of your Oracle databases

2 - We provide a SAAS for help implementing a useful easy to manage audit trail in your databases. This is called PFCLATK. Currently this is a service based on us helping you design an audit plan and policy and map all of the events that you would like to capture and extended with events that we think that you should capture. We then help you implement this as a set of audit trail policies and events in your databases.

3 - We have lots of expert level training classes taught by me aimed at helping people secure your data in Oracle databases

Contact me to book a service or a training place or to see a demo of our products or to discuss how we can help you secure the data held in your Oracle database.

Talk to me if you want more details!!

Oracle Privilege Analysis Now Free in EE from 18c and back ported to all 12c

Wow!!, i just got an email from someone in Oracle to let me know that the Privilege Analysis feature of Database Vault has had its licensing changed from this week to now be free as part of an Enterprise Edition database (EE). This feature used to be a cost option and required a Database Vault license. To use privilege analysis you didn't actually need to enabled Database Vault but you needed the license. This is no longer the case from this week; Also i have been told that the licensing is also retroactive to all 12c versions.

The license details for 18c are here.

I wrote an article back in 2015 and also did a webinar for SANS and Oracle on a practical walk-through of Database Vault and Oracle 12c and this included using and testing Privilege Analysis. You can read about that here.

Great news; nice to see security features no longer being cost options. This is good to have; privilege analysis and reduction of privileges to least rights is also key to a super-locked Oracle database.

Super Lock an Oracle Database

I started this blog post a few weeks ago and kept adding to it from time to time but I have been incredibly busy helping people secure data in their Oracle databases that it has taken a long time to get it complete and ready to publish. The looming brexit here in the UK does not seem to have reduced the need to protect data held and used in Oracle databases and indeed the introduction of GDPR law in May has added extra teeth to the laws to protect peoples personal data. There seems to be an upsurge in the need for people to protect data held in Oracle databases.

I have been working on securing Oracle for almost 20 years and I was instrumental in some of the first lists on securing Oracle and also was the author of one of the first big papers ever written on securing Oracle. I have been teaching people to secure and protect data in Oracle databases and of course performing security audits of Oracle databases, locking down Oracle, helping people design audit trail solutions and of course all aspects of Oracle security design around things like audit, Database Vault, Label Security, VPD, TSDP, masking and obfuscation and all aspects of securing data in Oracle. I also have been selling our database security scanner for Oracle; PFCLScan as well as helping people protect PL/SQL with PFCLObfuscate and much more.

It is fair to say I live and breath Oracle security. So, after all these years of doing everything i can in the Oracle security world what is a "Super Locked Oracle Database"?.

I have seen many levels of security in Oracle databases over the many years that I have been helping secure them; some i have come to see as part of my work that are already secured to some level and some i have of course helped to become secure. I have never seen a database though that is truly truly locked down. I have to say that my definition of this statement may be (actually almost certainly it is) different to others views of the same statement. So for some people a well locked down and secured Oracle database may not be the same for me. Thats just my opinion and i can justify why in each case. One example, a simple example could be that someone thinks that their mediocre security is fine because in their view they have good network security and desktop security but my view is that you should not assume that the attacker does not get into your network or into your building and indeed the attacker may not be an attacker in the traditional sense of the word and it maybe that they work for you and are doing things outside of their realm. Then there is the case where the production Oracle database is pretty well secured BUT they copy the production data to a test or development system and because they reset the passwords that is fine... hmmm... not really; the data is still the data and now its easy to steal from the test system that no one is monitoring. I have also seen cases particularly on the west side of the Atlantic where the company has applied most of the CIS benchmark for Oracle or the Stig or the ISACA list; the problem here is that hardening does not protect your data generally.

This is a problem with Oracle and the traditional idea of hardening does not work. Imagine you apply all clauses of the CIS 12c document to a production Oracle database and then step back and pat yourself on the back that you are compliant with the CIS benchmark 12c. Well done; its a lot of work BUT is the data in the database any more secure than it was before you started from attack by a logged in user? well that depends of course on the design of your data model and the data security you have added. Oracle is a generic engine that allows you to specify the rules for the design of your data model (tables, views, synonyms.....) and also your code (PL/SQL, Java....) and you are supposed to also design security but my experience of in-house Oracle database applications and also third party pre-packaged applications is that security is usually an after thought and some are pretty bad; they put all of the data and code into one schema and then you connect the web server to the database as the same schema; why? well because its easy and the code works and you didn't need to think about security at all; it just functions; the most important job often is to get it functionally working and make sure its performant. I have hear the same stories many times; the new system goes live in one week and oh, by the way can we do security now? madness.

So, yes you may have applied all of the CIS benchmark and feel you will pass an external audit but how well secured is your actual data? Even if the whole of the CIS is applied it does nothing for the users, the grants on tables and views and procedures of the actual application. This is the difference between hardening and data security design. I have been teaching people and helping people in consulting work for many years to focus more of their effort and budget on the security of data. We are trying to secure data not trying to secure Oracle with no thought about the data thats processed in the database.

Back to the question; "What is a super locked Oracle database"?. For me this would be the ultimate secured database; focused on the task of ensuring that only the exact number of people can see the explicit data that they are supposed to see and only when they are supposed to see it. i.e. absolute least privilege and absolute data access controls.

In real life we never get to build truly secure Oracle databases like this because budgets, man power and timescales get in the way. Mostly because security of the data in the Oracle database is not considered until later in the project or maybe because you bought an application and it doesn't really do security of data very well.

A search on line for a very secure database or definition of one doesn't throw up such at all so i would have to conclude that i am not the only one who has never seen a truly truly locked down Oracle database out their in the wild. I am sure some people are now going to email me and tell me of their absolutely secured Oracle database and thats fantastic; i would love to hear but I am sure its not super locked.

This idea first intrigued me about 2 years ago when a client approached me and asked me to help them create what he described as the most secure database on the planet. This was a small island government almost half way around the world from me in the UK. We discussed at a high level his requirements and some of his ideas and also my proposals on what we could do. As a teaser he wanted to obfuscate everything; tables, views, code, everything; he wanted to encrypt everything, network, at rest (TDE), backups, and in motion; i.e. data held in every table had to be encrypted at rest, in motion and in storage; so TDE was just the start, data actually held was then encrypted and the network was to use ASO or OpenSSH. There had to be the most locking of the database we could come up with; so much more than CIS; there had to be context based security around every user and every piece of data so Database Vault, VPD etc. Absolutely every action must be audited and the audit trails could not contain actual data; hmmm. There had to be no excess users or functionality at all in the database. The DBA mist not be able to see or change any data at all through any means; i.e. he cannot select data from v$sql or other views as well as of course from the actual tables. And many many more ideas...

This was an interesting project and in the end it went ahead in a much stripped down way from the initial discussions; for two reasons; It is hard to design and implement and second its hard to use the system after its in such a locked down state. Testing some things shows that absolute security like they imagined that they needed creates operational issues that must also be resolved. Also application development and deployment has issues; audit trails are difficult if we want to capture everything but not leak anything via the audit trail. There were many issues to cover and resolve and a truly truly locked database is hard to use. This is really like the idea that every single person in the organisation must have specific tasks and the data that they access and use has to be known fully and rules must be created that allow them to only use that data and no more and that data must only become visible at the right point in time exactly when they are supposed to see it.

If we wanted to create a super locked database we must start with the data and ignore Oracle and we must essentially make a matrix that shows for each user (real person) what exact data they are allowed to see and why and how and when and is it encrypted and is it masked and how is it audited. The n we must lay that over the Oracle database.

One of the most obvious problems with the Oracle database is its massive numbers of PUBLIC grants and the (tens of) thousands packages and procedures that are available either publicly direct or via other packages where the function is not directly accessed but is used via another public interface. No one knows what all this code does and whether its vulnerable to an exploit OR its use can abuse data in the database without an actual exploit such as SQL Injection. This is a major hurdle and is one that is a massive problem to solve.

We have two approaches (actually more approaches exist as well but...) ; the first is we de-install the default schemas that we don't use and which own some of this code; this works fine if we can de-install and if we don't need the code exposed by these users. This is a difficult problem as no one knows what these 30 to 50 default users do exactly in every case. (around 35 default accounts in a 10g, 11g, 12c, 18c legacy database and a cumulative total of more than 50 if we build a multi tenant database). Big issue. The second issue is that some of the functionally is installed into the SYS schema - i.e. packages owned by SYS so that the de-install of default schemas does not remove the risky code from the database.

Related to this issue is the PUBLIC grants. We could remove all public grants. There are a small number of posts online about people who have tried to do this and an unclear situation as to whether Oracle would support you and also what happens every time you install a patch or run catproc (i.e. a lot of the grants will be added back again). My view is that you can remove all the PUBLIC grants and it can be made to work; be aware that multienant makes this harder to do as a lot of it has to be done in the root container or the catcon.pl script has to be considered. This would only remove the PUBLIC grants and the invalid objects can be fixed by granting back to any remaining schema that needs the grant (if you already removed most default schemas then this is easier and also as part of that if you remove Java and XDB then again its easier). BUT this means any dynamic use of a PUBLIC grant is going to fail at run time and any patch will put the grants back so you need to script the removal.

If we truly want a super locked database then we must remove default schemas and PUBLIC grants BUT any inter-package exposure that is not PUBLIC would still be there and could potentially be used.

In principal for a super locked database we need to at a high level:


  • Block access to the database unless explicitly needed for each explicit action; this means any none explicit access can be blocked

  • Least privilege for all users

  • Data access controls around all data



These can be supplemented by hardening, patching, context based security around access, users and data, obfuscation and masking and encryption, audit trails, removal of public grants, removal of all defaults, network controls and much more. We should also add secure coding and security of all PL/SQL; i.e. we can use context based security again either by hand with who_called_me or accessible lists. We can include firewalls and intrusion detection or prevention and its possible to build that yourself into the database for free (at least in terms of license costs).

This subject of how far can we go in securing Oracle and what are the effects of that security intrigues me; i.e. does it make working harder; I have agreed to do two talks at the Belgian tech days conference next year in February and one of the talks I will give is on this very subject. How far can we go in securing data and what are the effects.

One last point of note is that 18c XE has just been released and it includes most of the security cost options for free such as database vault; fantastic!!

If anyone needs help to really secure their data either in terms of training; consulting or in terms of automated vulnerability scanning for their Oracle database talk to me on info@petefinnigan.com and we can help you protect your data.

Oracle Core Audit - Do you Audit your Core database engine for breach?

Oracles core database audit is a useful tool to monitor activity of the core database engine or applications and detect potential abuses. It seems to be a sad fact that with a lot of companies that i visit and from people i talk to at conferences very few have an audit trail in the database to monitor the database engine itself for possible unauthorised activity and even fewer of those that do actually use the audit trail that they have in a pro-active way - i.e. react to an event or alert detected by the audit trail in real or semi-real time.

I often take a show of hands at conference talks that I do or trainings and I ask the room - How many people have audit enabled in the database? - Usually between 10% and 30% of the room will put their hands up. I then say - Those of you with your hands up keep them up if you actually do anything with the audit trails except store it and never look at it? - Again 10% to 30% may keep their hands up. In a room of more than 100 people I have seen one or two people keep their hand up. I have asked these questions for years and in different settings; countries and more. The answer is always similar and a fact is that its not changing over the years. My guess (not a complete guess but based only on people I have had chance to ask!) is that 5% to maybe 15% may have auditing of the database engine itself.

This is not good enough. I spend quite a lot of my time helping companies create useful audit trails in their database so that they know whats happening and who is trying to do wrong to their data. I do this with a toolkit i have created called PFCLATK and this toolkit helps map a policy/event driven plan into settings in the actual databases and includes management and audit of audit and audit of security and even audit of security of audit. GDPR has created an interest in getting suitable audit trails up and running quickly as these are a core benefit to helping with GDPR; i.e. to know who accessed what and to know if you have been breached and more.

With GDPR and other existing data laws and data breach laws we need to rely more these days on audit trails than not. I said to someone in the last weeks; whats the point of planning for a database breach and creating an incident response process and a plan to deal with forensics to allow you to swiftly, calmly and objectively deal with a data breach if/when it happens IF you take no steps now to protect the data in your database. I went on to discuss that you can create an Oracle database security policy ( A document that describes what secure data in an Oracle database looks like to you) and implement it across all databases. You should still create your incident response process BUT also start to plan and implement Oracle security in your databases at the same time; no point as i say to plan for the breach if you do not bolt down the database.

As part of that bolting down of the database it makes absolute sense for one of the first countermeasures to be a rich and useful audit trail.

Audit trails in Oracle have a lot of nuances and issues and strange ways. I will cover some of these here in this blog over the coming weeks in details and show you what I mean; keep a watch for new posts.

You may say what about Unified Audit in 12c; its been around now since 2013 BUT; i have literally come across a small amount of people who use Unified audit and not the standard core audit - bear in mind the amount of people who actually have an audit trail set up and react to it; see above!!. As part of the room show of hands I always also ask if any of the people with their hand up use Unified audit; usually its a resounding no; or perhaps one person says they use it. On the face of it Unified Audit seems to offer advantages; policies; filtered audit BUT it also turns off some existing audit features if you enable in PURE mode; syslog, writing to the OS; it also seemed to have some teething issues in 12.1 but the move back to a real table instead of a secure file in 12.2 maybe makes that better.

PFCLATK also has policies and filtered results and it had them a few years before 12cR1 was released and i was not on the beta of that so i didn't know. I presented at UKOUG many years ago about audit trails and i mused there about policies and context based audit in the database for the core audit.

There are naysayers who say that auditing in the database with its core audit presents two main risks; 1) - The DBA or someone else with power can delete or turn off the audit trails and 2) - There is a massive performance impact to using database audit.

Both of these are not true (well a caveated not true). The attacker (the DBA) can turn off audit or delete or update audit records BUT as part of the audit trail we include audit triggers and also audit of audit so that we can react if that event happens. We can always get back deleted audit from the redo via flashback query or using LogMiner; If the DBA turned off audit trail creation we can also react swiftly and extract using the Incident process we created current actions and also use Flashback or LogMiner to see what changes he made. I believe the risk to having audit in the database (for a short time - I do advocate moving it to a central storage as quick as possible) is outweighed by the benefits of the use of SQL and PL/SQL to report and process the audit rather than terse file search tools and consolidating thousands of files. Also if we compare to the alternatives; network sniffer based technologies; then these also have issues, firstly they only see what flying past on the wire. If you decide to monitor the CREDIT_CARD table and its accessed in a SQL and this is in the TNS network packets then fine BUT if you access the table via a package called SCOTT.CARDS then either you have to know what SCOTT.CARDS does in advance and analyse all code to find access or you place a tap on the database server to try and get that detail from the database!. Secondly these network based technologies may syphon off details that could include data and now this data is stored in a box outside of the database. Database level audit can also include SQL that includes data BUT this is still stored in dictionary tables accessible to SYS and if deleted we can get to back from REDO.

The second issue people always site for not turning on audit in the database as i said is performance and again this can also be site as a reason to use a network blade / software TNS sniffer solution although if you have a lot of network traffic this causes different issues. My answer to the performance issue of enabling audit is that we should only audit that which should not happen; i.e. things that do not matter for performance; if someone accesses static configuration data then we audit DELETE and UPDATE (perhaps INSERT dependant on the design of the data) bit not SELECT as that is likely to be the major action performed thousands of times a day BUT delete and update should hardly ever happen.

We should start our audit trail design / policy with a list of events - "I want to know!" items and then flesh that out - a table is a good structure. The table should include a list of audit events and information on whether we just capture it and hold or we react. If we react how quickly and finally do we create a report (or include in a report) or do we raise and alert and escalate.

This design drives the technical solution; PFCLATK is deliberately written to encase these ideas and to use policy and event based audit that is easy to define and implement quickly in a database. That was the reason to create it; to allow customers to design their events and THEN to translate those into events and alerts in the toolkit.

Back to the performance issue for a second. Often people site performance as a major issue to not implement database level audit BUT at the same time they have triggers creating BEFORE and AFTER images of data changes at the application level. These can create 300% or even 500% impacts on the original action; this heavily depends on the code written into triggers. I have seen some monsters in my time!. Core database audit to audit things that should not happen has a tiny tiny impact.

OK, I will return to audit trail events and nuances and issues very soon as I have a big list of things I would like to talk about here.

Oracle Security Training by Pete Finnigan in 2018

The Merchant Adventurers Hall, York

Are you worried about the data in your databases being stolen? GDPR has just become law across the EU and the UK and affects business in other countries that process EU citizens data. Maybe you store and process credit card details and you fear card details being stolen and cloned. The core fear is that your business processing data in an Oracle database can be at the minimum tarnished by bad media over a breach or even completely ruined if the GDPR is brought to bear on a business.

Securing data in an Oracle database is not complex or rocket science if the task is approached in a methodical manner. You can get one over on the attackers and hackers by being one step ahead of them by finding out all of the key recommendations and pitfalls to fix in advance of an attack.

Oracle is a complex product; probably actually one of the most complex software packages available; it contains literally tens or even hundreds of thousands of objects and each can have their one security settings and parameters and techniques to secure them. It can, at first seem like a minefield BUT it doesn't have to be if you treat securing Oracle in a planned and structured way.

Often Oracle databases and the applications that they host are deployed in a completely insecure way; this is not usually intended but is usually a fact of performance and SLA's come first and security comes last; this hinders the process of securing data as adding security later is not actually so much harder than designing it in (after all Oracle has settings for security on most anything and they can be changed) its just a compromise or its a risk; in terms of perhaps breaking a working database.

Pete Finnigan (Me!) is a sought after speaker and trainer and of course consultant to companies world wide to help them secure their databases. Pete is a pragmatist and favours security solutions that are cost effective and work and do not break existing code. So you can learn from me how to prioritise your security plans to lock and harden your databases without risk. I love to focus on solutions that are possible to 100% implement and that are aimed at least cost for the best increase in security of your data in your Oracle databases. Pragmatic and cost effective.

I will be teaching a range of my companies Oracle Security training classes in the coming months both in-person and also online live teaching via webex. I have a range of Oracle Security classes that are available as public classes on these dates.

In particular I have just agreed to a 3 day training with Oracle University in Istanbul, Turkey on the 15th to 17th October. You can find details on the public training dates page. This event sees me teach my very popular 2 day class - How to perform a security audit of an Oracle database and also my one day class - Oracle Incident Response and Forensics. This is a unique combination of content for this event in Turkey.

I am also teaching my one day class - Secure Coding in PL/SQL in York, UK on the 28th August and also on the 29th my Oracle Incident Response and Forensics. This is my home city and its a great place to visit besides to learn about Oracle Security

I will also be adding dates in Prague, Holland, France and the UK as well for more classes later in the year.

Getting started with Oracle Security does not need to feel daunting and it is always possible to increase the security of your data no matter what you start with. Learning about Oracle security also gives you the insight to work in a better and more secure way going forwards.

To book your place on one of my classes to come and learn from my many years of practical experience in securing data in Oracle databases either visit the public training dates page and click the register links or send me an email and i will be glad to assist.

Oracle Can Generate 6 Password Hashes When a User is Added or Password Changed in 12.1.0.2 and Above

In a 12.2.0.2 database it's possible that Oracle generates 6 different password hashes for one password for one user under certain circumstances when a password is changed or created (user is created). I will layout the 6 different ones first and then for the rest of this post I will focus on one of them; i will tell you which one i want to focus on shortly after i have laid out all six generally.

The normal wisdom in a 10g database would have been that Oracle generates a single password hash for each user when a user is created or it would update that password hash when a users password was changed. This was the password hash stored in SYS.USER$.PASSWORD column and this uses the DES based HASH. The username is the SALT and is concatenated with the PASSWORD and then encrypted with DES CBC using a standard key and once the first round is complete the process starts again using the last input vector as the new key. This means that whilst an encryption algorithm is used (DES CBC) it is actually a one way HASH as there is no way to decrypt the password. The standard method to crack this is to use a cracker and either use a dictionary based approach - i.e. test each password generated from a dictionary of words and compare the generated hash with the one stored in the database. Another possibility is to use brute force; i.e. try a password of A, then B and then C and then AA, AB, AC... and AAA, AAB, AAC and so on until all possible passwords have been tried for the keyspace. A simple PL/SQL password cracker is available here. This is an older version of my PL/SQL cracker; version 1.5; if you have been on two day my training class - How to perform a security audit of an Oracle database - then you would have the latest version which is faster and has more default passwords and more features - A benefit of attending my classes is to get all the latest scripts and tools. I will be teaching this class in my home city of York at the beginning of July; come along! I may release the later version of this cracker on here soon; so watch out for a blog post on that.

So, 10g database, thats one password hash. When Oracle database 11.1 was released Oracle added case sensitive passwords and the new (At the time) SHA1 password hash. This is stored in SYS.USER$.SPARE4 and is identified as the "S:" string. This is just SHA1 on the actual password and with the addition of a SALT to make the password that is stored different to other hashes of the same password. So if i set the SYS password to TEST and then the SYSTEM password to TEST even though the hash is only of the password the hash is different as Oracle generates a random SALT each time a password is hashed. This SALT is also stored in SYS.USER$.SPARE4 with the hashed password. I created a simple SHA1 hash test back in 2007 when 11.1 was released. This script is sha1.sql and is here.

In 11.2 this situation continued; so that DES and the SHA1 hashes are available in SYS.USER$. When 12.1.0.1 was released an additional password hash was added in SYS.USER$.SPARE4 - this one identified by the "H:" - This is an HTTP Digest for use in web based applications authentication. In 12.1.0.1 this could not be turned off; so you got a HTTP digest whether you liked it or not. In 12.1.0.2 Oracle changed the HTTP digest slightly if a multi-tennent database were created. In 12.1.0.1 the hash for common users (default users as well as ones created by you) was visible in the PDB; in 12.1.0.2 this is now only visible in the CBD for common users; in a PDB the HTTP digest is still available for LOCAL users. In 12.2.0.2 the HTTP digest is not created by default anymore BUT can be created with a new additional syntax - I will come back to this in a later post just about this hash. So as of Oracle 12.1.0.1 there were 3 core password hashes.

In 12.1.0.2 added the new SHA2 password algorithm. This is a combination of the PBKDF2 multiple rounds and then a final SHA2 hash. As with the SHA1 in 11.1 this password hash is stored in SYS.USER$.SPARE4 and is the "T:" password hash. Also as with SHA1 in 11g the SALT used is also stored in the same column with the hashed password. More in a later post about this algorithm. So as of 12.2.0.1 whilst the HTTP Digest is normally turned off and the default is that the DES hash is not generated it is possible to get all 4; either through an upgrade or because you use the syntax to create the HTTP digest or because you have set the network authentication in the sqlnet.ora to default to an earlier setting to get the DES hash also.

OK, but thats just 4 password hashes what about the other two. Well the 5th is generated by Oracle internally and not stored in a public place where you can view it. If an account is a default account then every time a password changes (CREATE or ALTER) then Oracle creates an un-salted SHA1 hash and uses it to compare with an internal list to see if the account is a default and whether the password is a default password. Oracle does this to detect default passwords. The hash is not stored as far as I can see BUT the accounts status is updated to indicate that the password is a default. I will come back to this in a more detailed review in another post soon. So this is 5 password hashes generated in 12.1.0.2 and above.

The one i want to focus on for the rest of this blog is the 6th password hash created for a user. This hash is only created if you run a DDL trigger on CREATE and / or ALTER or generally an un-constrained DDL trigger. If you collect the ORA_DES_ENCRYPTED_PASSWORD trigger attribute in the DDL trigger for a password change then this trigger attribute is populated. As the name implies it should be a DES hash or DES encrypted password. If it is indeed encrypted then that also implies decryption. Lets have a look and test the ORA_DES_ENCRYPTED_PASSWORD trigger attribute.

First I installed PFCLATK our Oracle audit trail toolkit into my Oracle 12.2.0.1 database:


PFCLATK: Release 1.7.2.0 - Production on Wed Jun 13 18:07:26 2018
Copyright (c) 2009 - 2018 PeteFinnigan.com Limited. All rights reserved.

SECTION-[1] - Remove existing schemas and users
[1-1] Drop The ATK Admin user
[1-2] Drop the ATK Reports User
[1-3] Run schema drop script
SECTION-[2] - Create the Schema owner ATK (Functional owner)
[2-1] Create ATK Schema
[2-2] Perform ATK Grants
SECTION-[3] - Create schema owner ATKD (Data owner)
[3-1] Create ATKD schema
[3-2] Perform ATKD Grants
...
SECTION-[17] - Install the user policies
SECTION-[18] - Install the user factors
SECTION-[19] - Install the PUL jobs/policies
SECTION-[20] - Clean up...
[20-1] Purge the PFCLAudit Audit table
[20-2] Print out existing audit, except OLS and DV
SQL>

This gives me a very detailed policy driven audit trail in my database that allows certain actions to be raised as exceptions and alerts fired. We are not going to explore the audit trail in this blog post but i will come back and discuss this toolkit in a future blog post. We use it to help customers through consulting engagements develop and deploy a rich policy driven audit trail with alerts and escalations quickly and easily. Currently it's a PL/SQL based command line tool but a GUI is coming soon to allow customers to manage, choose and design their own policies or to amend or simply use ours. There will be an admin GUI and a dashboard to allow you to monitor multiple databases. The toolkit can be deployed to a target database to audit it or it can be be deployed to a central database so that all target databases can send their audit trails to it for centralised storage and reporting. More soon here in the blog on the toolkit; but if you are interested now to have us help you design and deploy very useful audit trails email info@petefinnigan.com for availability and costs.

We installed PFCLATK because it already knows how to collect the ORA_DES_ENCRYPTED_PASSWORD field when a user is created (password first set) or when the password is changed. So lets create a user called TEST and set his password to A:


SQL> create user TEST identified by A;

User created.

SQL> col err_sql for a40
SQL> col ora_dict_obj for a40
SQL> col ora_des for a32
SQL> set lines 220
SQL> select err_sql,ora_dict_obj,ora_des from atkd.pfclatk_audit where ora_des is not null;

ERR_SQL ORA_DICT_OBJ ORA_DES
---------------------------------------- ---------------------------------------- --------------------------------
create user TEST identified by * TEST a0fe3035825f3637aad3b435b51404ee

SQL>

This doesn't tell us much so far. Lets change the password to B, then C, then D and see what happens:

SQL> alter user TEST identified by B;

User altered.

SQL> alter user TEST identified by C;

User altered.

SQL> alter user TEST identified by D;

User altered.

SQL> select err_sql,ora_dict_obj,ora_des from atkd.pfclatk_audit where ora_des is not null;

ERR_SQL ORA_DICT_OBJ ORA_DES
---------------------------------------- ---------------------------------------- --------------------------------
create user TEST identified by * TEST a0fe3035825f3637aad3b435b51404ee
alter user TEST identified by * TEST a6b47b9609ea127baad3b435b51404ee
alter user TEST identified by * TEST ce0beda1542358c2aad3b435b51404ee
alter user TEST identified by * TEST 5a7c34dd04a4c3adaad3b435b51404ee

SQL>

Now we are getting somewhere. As you can see the first 16 characters of the DES encrypted password change as each new password is set BUT the last 16 characters do not. This implies that a short password is only encrypted into the first 16 characters (8 bytes) and a longer one into the next. But before we explore that rollover what if we set the same 4 passwords for the user ORABLOG:

SQL> alter user ORABLOG identified by A;

User altered.

SQL> alter user ORABLOG identified by B;

User altered.

SQL> alter user ORABLOG identified by C;

User altered.

SQL> alter user ORABLOG identified by D;

User altered.

SQL> select err_sql,ora_dict_obj,ora_des from atkd.pfclatk_audit where ora_des is not null;

ERR_SQL ORA_DICT_OBJ ORA_DES
---------------------------------------- ---------------------------------------- --------------------------------
create user TEST identified by * TEST a0fe3035825f3637aad3b435b51404ee
alter user TEST identified by * TEST a6b47b9609ea127baad3b435b51404ee
alter user TEST identified by * TEST ce0beda1542358c2aad3b435b51404ee
alter user TEST identified by * TEST 5a7c34dd04a4c3adaad3b435b51404ee
alter user ORABLOG identified by * ORABLOG a0fe3035825f3637aad3b435b51404ee
alter user ORABLOG identified by * ORABLOG a6b47b9609ea127baad3b435b51404ee
alter user ORABLOG identified by * ORABLOG ce0beda1542358c2aad3b435b51404ee
alter user ORABLOG identified by * ORABLOG 5a7c34dd04a4c3adaad3b435b51404ee

8 rows selected.

SQL>

The same 4 passwords were set for ORABLOG as i set for the user TEST. The same 4 DES encrypted passwords were created. This means that the encryption is just for the password and the username is not involved and also it means that there is no SALT. What if we now set the passwords using lower case a, b, c and d:

SQL> alter user TEST identified by a;

User altered.

SQL> alter user TEST identified by b;

User altered.

SQL> alter user TEST identified by c;

User altered.

SQL> alter user TEST identified by d;

User altered.

SQL> select err_sql,ora_dict_obj,ora_des from atkd.pfclatk_audit where ora_des is not null;

ERR_SQL ORA_DICT_OBJ ORA_DES
---------------------------------------- ---------------------------------------- --------------------------------
create user TEST identified by * TEST a0fe3035825f3637aad3b435b51404ee
alter user TEST identified by * TEST a6b47b9609ea127baad3b435b51404ee
alter user TEST identified by * TEST ce0beda1542358c2aad3b435b51404ee
alter user TEST identified by * TEST 5a7c34dd04a4c3adaad3b435b51404ee
alter user ORABLOG identified by * ORABLOG a0fe3035825f3637aad3b435b51404ee
alter user ORABLOG identified by * ORABLOG a6b47b9609ea127baad3b435b51404ee
alter user ORABLOG identified by * ORABLOG ce0beda1542358c2aad3b435b51404ee
alter user ORABLOG identified by * ORABLOG 5a7c34dd04a4c3adaad3b435b51404ee
alter user TEST identified by * TEST a6b09e2de7820bedaad3b435b51404ee
alter user TEST identified by * TEST 94326650a42f2bd8aad3b435b51404ee
alter user TEST identified by * TEST e6ea6b41dedd8560aad3b435b51404ee

ERR_SQL ORA_DICT_OBJ ORA_DES
---------------------------------------- ---------------------------------------- --------------------------------
alter user TEST identified by * TEST cd961c7f88ea81c7aad3b435b51404ee

12 rows selected.

SQL>

This is interesting as the encrypted passwords are not the same as the upper case ones. This means that the encryption used must be CASE SENSITIVE unlike the DES algorithm used in the database password hash from Oracle 6 upwards; actually the algorithm supports upper and lower case and specials etc but the double quote syntax was needed to access that. You will have noticed that the last 16 characters (8 bytes) of the DES encrypted password is always the same; before we play with that lets test if this also works for SYS.

This is interesting as the reason I first looked into this area was to see if there was any way I could simulate password management in any other way than the password management built into Oracle; this built in password management does not work for SYS; so i was looking to see if i could simulate it in any other way. More on this in another blog post and i will show you what I worked out for SYS. So lets try this for SYS:

SQL> alter user sys identified by a;

User altered.

SQL> alter user sys identified by b;

User altered.

SQL> alter user sys identified by c;

User altered.

SQL> alter user sys identified by d;

User altered.

SQL> select err_sql,ora_dict_obj,ora_des from atkd.pfclatk_audit where ora_des is not null;

ERR_SQL ORA_DICT_OBJ ORA_DES
---------------------------------------- ---------------------------------------- --------------------------------
create user TEST identified by * TEST a0fe3035825f3637aad3b435b51404ee
alter user TEST identified by * TEST a6b47b9609ea127baad3b435b51404ee
alter user TEST identified by * TEST ce0beda1542358c2aad3b435b51404ee
alter user TEST identified by * TEST 5a7c34dd04a4c3adaad3b435b51404ee
alter user ORABLOG identified by * ORABLOG a0fe3035825f3637aad3b435b51404ee
alter user ORABLOG identified by * ORABLOG a6b47b9609ea127baad3b435b51404ee
alter user ORABLOG identified by * ORABLOG ce0beda1542358c2aad3b435b51404ee
alter user ORABLOG identified by * ORABLOG 5a7c34dd04a4c3adaad3b435b51404ee
alter user TEST identified by * TEST a6b09e2de7820bedaad3b435b51404ee
alter user TEST identified by * TEST 94326650a42f2bd8aad3b435b51404ee
alter user TEST identified by * TEST e6ea6b41dedd8560aad3b435b51404ee

ERR_SQL ORA_DICT_OBJ ORA_DES
---------------------------------------- ---------------------------------------- --------------------------------
alter user TEST identified by * TEST cd961c7f88ea81c7aad3b435b51404ee
alter user sys identified by * SYS a6b09e2de7820bedaad3b435b51404ee
alter user sys identified by * SYS 94326650a42f2bd8aad3b435b51404ee
alter user sys identified by * SYS e6ea6b41dedd8560aad3b435b51404ee
alter user sys identified by * SYS cd961c7f88ea81c7aad3b435b51404ee

16 rows selected.

SQL>

So, yes it works for SYS and the results are the same as the passwords for TEST. So next, lets truncate the audit trail and lets try extending the password from A to 16 A's and see what happens:

SQL> truncate table atkd.pfclatk_audit;

Table truncated.

SQL> alter user TEST identified by A;

User altered.

SQL> alter user TEST identified by AA;

User altered.

SQL> alter user TEST identified by AAA;

User altered.

SQL> alter user TEST identified by AAAA;

User altered.

SQL> alter user TEST identified by AAAAA;

User altered.

SQL> alter user TEST identified by AAAAAA;

User altered.

SQL> alter user TEST identified by AAAAAAA;

User altered.

SQL> alter user TEST identified by AAAAAAAA;

User altered.

SQL> alter user TEST identified by AAAAAAAAA;

User altered.

SQL> alter user TEST identified by AAAAAAAAAA;

User altered.

SQL> alter user TEST identified by AAAAAAAAAAA;

User altered.

SQL> alter user TEST identified by AAAAAAAAAAAA;

User altered.

SQL> alter user TEST identified by AAAAAAAAAAAAA;

User altered.

SQL> alter user TEST identified by AAAAAAAAAAAAAA;

User altered.

SQL> alter user TEST identified by AAAAAAAAAAAAAAA;

User altered.

SQL> alter user TEST identified by AAAAAAAAAAAAAAAA;

User altered.

SQL> select err_sql,ora_dict_obj,ora_des from atkd.pfclatk_audit where ora_des is not null;

ERR_SQL ORA_DICT_OBJ ORA_DES
---------------------------------------- ---------------------------------------- --------------------------------
alter user TEST identified by * TEST a0fe3035825f3637aad3b435b51404ee
alter user TEST identified by * TEST e59f3bfac2082d77aad3b435b51404ee
alter user TEST identified by * TEST f984b70b43584870aad3b435b51404ee
alter user TEST identified by * TEST 00629bf7623f9c45aad3b435b51404ee
alter user TEST identified by * TEST 5a52d459aeb64441aad3b435b51404ee
alter user TEST identified by * TEST 8ee7deffee86298baad3b435b51404ee
alter user TEST identified by * TEST 1e3528942decf14daad3b435b51404ee
alter user TEST identified by * TEST 1e3528942decf14da0fe3035825f3637
alter user TEST identified by * TEST 1e3528942decf14de59f3bfac2082d77
alter user TEST identified by * TEST 1e3528942decf14df984b70b43584870
alter user TEST identified by * TEST 1e3528942decf14d00629bf7623f9c45

ERR_SQL ORA_DICT_OBJ ORA_DES
---------------------------------------- ---------------------------------------- --------------------------------
alter user TEST identified by * TEST 1e3528942decf14d5a52d459aeb64441
alter user TEST identified by * TEST 1e3528942decf14d8ee7deffee86298b
alter user TEST identified by * TEST 1e3528942decf14d1e3528942decf14d
alter user TEST identified by * TEST 1e3528942decf14d1e3528942decf14d
alter user TEST identified by * TEST 1e3528942decf14d1e3528942decf14d

16 rows selected.

SQL>

This shows some interesting information and facts:

  • First the known string in the second 16 characters (8 bytes) aad3b435b51404ee remains the same until the password is 8 characters long. This means that all passwords 7 characters and less are encrypted into the first 16 characters (8 bytes) and the second 16 characters (8 bytes) remain the standard blank value. This is odd, why 7 characters and not 8 which is a standard input to DES? possibly the password is a standard C string with a NULL terminator and the terminator is also encrypted?

  • After and including the 8 character password the second 16 characters change. This is interesting as if we take the first 16 characters of the password A and the second 16 characters of the password AAAAAAAA (8 A's) the they are the same a0fe3035825f3637. So the second half of the algorithm used is the same as the first half. So it does not encrypt AAAAAAAA (8 A's) as one string; it takes the first 7 A's and encrypts to 1e3528942decf14 and then encrypts the 8th A on its own as a0fe3035825f3637 so is the same password DES string as a single A. So the password is definitely split before encryption and the same encryption is used on each half

  • Passwords 7 (7 A's) and onwards all have the same first half no matter what as they are all the same password half (AAAAAAA).

  • When we get to a 14 character password the second 16 characters of the encrypted password are the same as the first i.e. we have AAAAAAA + AAAAAAA = 1e3528942decf14d + 1e3528942decf14d

  • A password that is 15 characters or higher; its clear that the characters above 14 are ignored!!


If i repeat the output with the passwords also its a little clearer:

alter user TEST identified by * TEST a0fe3035825f3637aad3b435b51404ee 1 - (A)
alter user TEST identified by * TEST e59f3bfac2082d77aad3b435b51404ee 2 - (AA)
alter user TEST identified by * TEST f984b70b43584870aad3b435b51404ee 3 - (AAA)
alter user TEST identified by * TEST 00629bf7623f9c45aad3b435b51404ee 4 - (AAAA)
alter user TEST identified by * TEST 5a52d459aeb64441aad3b435b51404ee 5 - (AAAAA)
alter user TEST identified by * TEST 8ee7deffee86298baad3b435b51404ee 6 - (AAAAAA)
alter user TEST identified by * TEST 1e3528942decf14daad3b435b51404ee 7 - (AAAAAAA)
alter user TEST identified by * TEST 1e3528942decf14da0fe3035825f3637 8 - (AAAAAAAA) (2nd 16 chars change)
alter user TEST identified by * TEST 1e3528942decf14de59f3bfac2082d77 9 - (AAAAAAAAA)
alter user TEST identified by * TEST 1e3528942decf14df984b70b43584870 10 - (AAAAAAAAAA)
alter user TEST identified by * TEST 1e3528942decf14d00629bf7623f9c45 11 - (AAAAAAAAAAA)
alter user TEST identified by * TEST 1e3528942decf14d5a52d459aeb64441 12 - (AAAAAAAAAAAA)
alter user TEST identified by * TEST 1e3528942decf14d8ee7deffee86298b 13 - (AAAAAAAAAAAAA)
alter user TEST identified by * TEST 1e3528942decf14d1e3528942decf14d 14 - (AAAAAAAAAAAAAA) (2nd 16 = 1st 16 chars)
alter user TEST identified by * TEST 1e3528942decf14d1e3528942decf14d (15 - (AAAAAAAAAAAAAAA)
alter user TEST identified by * TEST 1e3528942decf14d1e3528942decf14d (16 - (AAAAAAAAAAAAAAAA)

16 rows selected.

So the passwords seem to be split into 7 character strings and the separately DES encrypted or hashed? and then concatenated again as a HEX string. The testing that i have done above is in 12.2.0.1. Lets try the same 16 passwords in 11.2.0.4:

SQL> create user test identified by A;

User created.

SQL> col err_sql for a40
SQL> col ora_dict_obj for a30
SQL> col ora_des for a32
SQL> set lines 220
SQL> select err_sql,ora_dict_obj,ora_des from atkd.pfclatk_audit where ora_des is not null;

ERR_SQL ORA_DICT_OBJ ORA_DES
---------------------------------------- ------------------------------ --------------------------------
create user test identified by * TEST a0fe3035825f3637aad3b435b51404ee

SQL>

In 11.2.0.4 the hash is exactly the same as in 12.2.0.1 for a password of A. We can therefore rely on the values to analyse the passwords of users as they are changed. As we can do this in a much better way with a password verify function and the password management settings this is sort of redundant but as i said the reason i started to look into this was to do password management of sorts for SYS; more of that in a later post.

In summary we could easily tell if a password is not at least 8 characters long as the last 16 characters of the ORA_DES_ENCRYPTED_PASSWORD are known if its length is seven or less. We can see that the password is split into 7 character blocks so in rare cases we might be able to detect repeat patterns or passwords all of the same character. There is no SALT but there is case sensitivity.

If we could test the actual algorithm and detect its implementation then we could perhaps decrypt a password up to 14 characters. But if it were decrypted at 14 characters but the password were 16 then it would be simple to test that the password is not cracked so therefore must be longer. Therefore we could brute force the extra characters easily - so a 16 character password would be like cracking a 2 character password if we knew the first 14 only.

The testing implies the same key is always used as the first 16 characters were the same as the second so it implies its not a random key used but a fixed key; knowing Oracle thats probably 0123456789ABCDEF. A bit of testing and trial and error could possibly reveal the algorithm used so that the password could be decrypted but i have not done this.

So, 6 password hashes for each user in some circumstances in 12.2.0.1. There are other cases as well of encrypted passwords such as LINKS.