Call: +44 (0)1904 557620 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.

[Previous entry: "Oracle Core Audit - Do you Audit your Core database engine for breach?"] [Next entry: "Oracle Privilege Analysis Now Free in EE from 18c and back ported to all 12c"]

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.