The Problem:Dan asked me a question about whether the DBA can be stopped from accessing views such as V$SQL or V$SQL_BIND_CAPTURE with Database Vault because these views can be used to read data from the SGA.
I have covered the issue of reading data from outside of the planned data model - i.e. access outside of SELECT, INSERT, UPDATE and DELETE grants on data tables or via PL/SQL execute privileges - many many times in the past on this site blog and in my trainings and consulting.
A blog entry here called
Accessing data outside of the data model is a good example. I cover this very issue in my two day class "
How to perform a security audit of an Oracle Database" and also "
Secure coding in PL/SQL" from the view point of stealing data and I also cover the SGA and binds from the other side of needing that information to track a hacker in my "
Oracle Incident response and forensics class".
The Problem Stated Even More:Dans point that a DBA can read data by simply sampling SQL statements and PL/SQL statements or bind data is very valid and indeed this is the problem he wants to solve. If a DBA has access to the database then they need to do DBA like work (we haven't defined what that is exactly) and of course the DBA should not be able to see data; why in this data breach age should the DBA have carte blanche to read data along side his normal job. Business people are limited in what data they can see so why not limit the DBA as well.
So the first thoughts are to limit access to all tables and views and code so that the DBA cannot select data from business tables or views. Then the next thought is to limit PL/SQL procedures for the same reason so that they cannot run business code and indirectly access data. The permissions on data are not very granular; they are at the table, view procedure level. This is why Oracle brought out tools such as Virtual Private database to limit data by row or column or combinations if you care to do the setup and write the rules. It is possible to also do a lot with the old fashioned methods of using views and triggers and PL/SQL code to limit and enforce rules but these can be by passed if you can access the tables directly for select and not via the views that have had the security implemented in them. Of course Oracle also has Label Security, TSDP etc.
Then there is Database Vault which can be used to protect data, commands and roles via realms, command rules and secure application roles. The de-facto position is to block so called SYSTEM ANY rights and this is the next logical thing to mention for DBAs they tend to get the DBA role, The IMP_FULL_DATABASE or DATAPUMP versions or the CATALOG roles or worse the SYSDBA privilege. Included in all of this mess is usually most SYSTEM ANY rights. This includes SELECT ANY TABLE so the DBA can bypass the direct grants that he doesn't have now against business data or business code and access tables, views and code again. Now Database Vault out of the box stops the SYSTEM ANY rights when a realm is made around data. This is fine BUT as i said in my presentation "
You don't have database vault; what can you do instead?" a simpler fix is possible without buying database vault. That is do not have the SYSTEM ANY grants in the first place.
Why does a DBA need SYSTEM ANY grants? in general for three main reasons:
- Access to data to trouble shoot application issues: this is the fundamental issue we are trying to resolve. the DBA should not have these ANY rights and if the DBA needs to trouble shoot then create a role with the specific direct grants needed and grant it only when completing that task.
- Access to deploy code/applications:This is normally because the DBA is also the release team and they then have the choice of connecting to the schema and running code that says create table ... or connecting as a DBA with CREATE ANY like rights and running create schema.table.... There is no need for the DBA to connect direct to the schema or to have CREATE ANY rights. The better approach is to use proxy and connect as the DBA account and proxy to the schema. In this way we can also have a log on trigger that executes AUDIT ALL IN SESSION CURRENT and AUDIT ALL STATEMENTS IN SESSION CURRENT and this will create a log of all actions as the DBA as a proxy to the schema during a release.
- Create other objects or changes: For instance create a directory object in another schema. The approach here is to create these objects once and a good approach is to isolate these rights into a shared security manager and access this via proxy - so no direct logins - and audit in the same way
So already it is complex and we have not talked about sneakily accessing data via lots of other means:
- Select code and data embedded in it from the SGA; i.e. v$SQL, v$SQLAREA et al plus all of the other methods such as via stats pack or tuning pack or Quest products....the list is very big of possible ways
- Use tools such as Log Miner, CDC, Streams, Goldengate to extract data from redo or archive logs
- Dump data with ALTER SESSION and ALTER SYSTEM in many formats and ways
- Access data via export files
- Access data files
- The list goes on to many many ways to get at data without selecting it from tables
There are then more subtle ways to read data outside of the areas we have just discussed:
- Utilise ALTER USER, change the SYS password and connect as SYS and read data!
- Do SQL Injection as a database user on vulnerable business logic written in PL/SQL and then use that avenue to read data!
- Utilise CREATE ANY PROCEDURE and overwrite the F procedure of APEX (its usually installed in my experience) and steal the ALTER USER privilege of APEX and change the SYS password, log in as SYS and read the data!
- There are many, many more ways...
The Real Problem; What is a DBA and what can he do:All of this leads us to the conclusion that we need to solve two problems:
- What is the DBA and what should he be allowed to do (in the database or on the server)?
- What data are we protecting and just how many ways are there to access it?
So Dans question of how to protect access to V$SQL and V$SQL_BIND_CAPTURE is a good one but it must be extended to include all possible ways that data can be read outside of normal select statements but firstly it must be defined what is the DBA, what access should he have and for what purposes and when. In my view all DBAs should have their own accounts in the database and they must not use any built in Oracle roles such as DBA or the IMPORT roles or grants of all rights or SYSDBA. A DBA account must be designed for purpose and to design for purpose we must understand what the DBA is allowed to do and then we can start to make plans to limit access to only that which is necessary.
At a simple level that would mean no direct grants on tables, views, procedures etc, no SYSTEM ANY rights including DML and DDL rights. No rights to bypass the restrictions in place; i.e. Dans example of no access to V$SQL but also no access to GRANT roles, no access to ALTER USER and many more as we have stated these could be used to bypass the rules already in place.
This is an oxymoron waiting to happen though.
As soon as we start to limit some rights that clearly also have DBA like needs then we have problems. For instance ALTER USER is a good example. A DBA would need access to this BUT we would not want the DBA accessing SYS or SYSTEM on a daily basis and we may limit their access so we would need to also add a DDL trigger that stops the DBA from changing the SYS or SYSTEM password. This then brings in a next layer. We also dont want the DBA to be able to disable the DDL trigger so he should not have ALTER TRIGGER and ADMINISTER DATABASE TRIGGER; that means our DDL trigger must be a database wide system trigger so he needs ADMINISTER DATABASE TRIGGER to disable it so we dont grant this to the DBA. But also the parameter _system_trig_enabled could be turned off preventing system triggers to fire so the DBA should not have ALTER SYSTEM or access to stop and start the database and edit the parameters file. Stopping him have ALTER SYSTEM would be an issue; stopping him getting to the box on a daily basis to edit files can be managed.
12c brought LOCK DOWN PROFILES and this is a fantastic tool to limit things like ALTER SYSTEM in a fine grained way. So we could stop him turning on trace and stop him setting the _system_trig_enabled parameter.
We can also use layered automated security; we can test all these key elements in a regular way externally so that we can determine if the security of the database has been compromised.
PFCLScan is a good tool to allow you to monitor this. A video showing a
quick demo of PFCLScan is here.
We could even go as far as automatically re-applying the security if it is found to have been reduced. This can be tricky and needs to be managed properly but this is the start of the path towards adaptive security and we can also do adaptive auditing.. i.e. we can have a database go from defcon 5 to defcon 1.
Other Issues:So far it seems complex and it gets worse. We also have since 12c the problem of multitenant databases - i say problem purely in the sense that we now need to manage at least two databases the CDB and the PDB; where it works out well is when we have multiple PDB in a single CDB; then the administration can be centralised. This brings in the issue of COMMON or LOCAL users, privileges, profiles and the side issue of COMMON grants to LOCAL and LOCAL grants to COMMON.
In this scenario of stopping the DBA team looking at data then in one sense having a centralised DBA (a COMMON user) makes sense and indeed Oracle provides some tools to assist in fine grained security around this. So imagine that the COMMON DBA only accesses the CDB and looks at data in V$SQL from the CDB then we can use CONTAINER_DATA to limit access to individual PDBs and also views within those. So we can stop the DBA from viewing the contents of V$SQL from the CDB BUT we have the secondary problem that the DBA is a COMMON user and we would then need to stop him accessing the PDB directly. This could be achieved via lack of CREATE SESSION for instance BUT as a DBA he may need to be in the PDB at some point to do something.
Clearly if a DBA has his own account and its not SYSDBA we can probably limit his access to data with some careful design and also layered security such as DDL triggers - but not limited to those - and maybe tools like Database vault if you have licensed it. Also we must make good use of audit trails to capture all actions performed by the DBA. We must also accept that in some circumstances he/she may need to be logged in as SYSDBA BUT not all day every day; and we should audit that access and actions and control it with proper processes.
Securing Oracle properly and limiting access to a DBA can also use tools like VPD, OLD and of course Database Vault but we can do a lot with normal rights and ideas and audit trails and of course management of people and processes and knowing what people are supposed to do and not do.
We need to analyse privileges of all users and they must be least rights for all jobs and that includes the DBA. Oracles Privilege Analysis is
now free for 18c and above and back ported to 12c so thats a useful tool. You can also use all of my scripts, who_has_priv.sql, who_can_access.sql and find_all_privs.sql available on my
Oracle security tools page.Limit core grants such as Oracle roles, ANY rights, SELECT ANY DICTIONARY and more. We clearly need lots of thought and careful design and need layered security. I did a post a while ago about
super locking an Oracle database and this post enhances that post in part.
I will return to this discussion as time goes on here in this blog as i have just created a new 18c XE database to form the new basis of my training sample database - most of the cost options such as Database Vault are available for free in 18c XE - and I have started to create a simple application (A CRM) as a sample for my training classes and I am creating a new two or possibly three day class where I want to super lock a database as a step by step process and show whats possible in a short period of time utilising many many layers and tools and techniques in an Oracle database.
This would be great as a follow along (hands on) class as i plan to use XE and everyone can download and use it.
More later on this and if anyone is interested to register an interest in attending the first sitting of this class later this year contact me via email or my social media accounts
