I have taught a design pattern and used it for years that is similar to what Oracle have done with AUDSYS and AUD$UNIFIED where we create a schema to own something dangerous and then lock that schema to prevent access to it and also then create an API that allows only the access that we need to allow and add extra protections to prevent bypass or removal of the security.
As we found out with AUDSYS and the unified audit trail we don't know for sure exactly how Oracle does it as it is likely hard coded or some sort of internal security policy implemented in the C or simply flags we cannot set ourselves easily or even if we could we could not access these flags in our code.
If we use any number of standard database features to do the same thing then there are two risks; the first is that the owner of the object could manipulate the object itself if someone were to access the schema; and second that a DBA can disable enough of the security to allow access to the object or schema.
This is the big issue with standard features and why Oracle brought in Database vault to allow this type of security to be added to the database where the DBA cannot simply disable the Database Vault settings themselves. The problem then shifts to the database vault administrators as they could then remove the security or enough of it to allow access again to the object. Yes, its better than standard features as its harder to get around but shows how a problem with security in a database is the number of users accessing it and protecting it and the complexity of needed solutions.
As an anecdote I have seen many times where companies have implemented Database Vault and the same people have access to the DV admin/owner accounts, Oracle OS account and sometimes root and also of course access to SYS/SYSTEM etc. If you do this you are wasting the money you paid for database vault.
Even with standard features we could remove the threat of a DBA from un-doing the security if we didn't allow the DBA to access SYSDBA or any other powerful role or privilege that would allow that security to be bypassed. Oracle Autonomous Database does something along these lines already where the tenant of the ATP or ADW database does not have access to the CDB or SYS or standard Oracle roles. So yes a solution could be done provided we can change the way people work.
As part of this discussion we are not going to use cost options like Database Vault or Label Security or Enterprise Edition features such as Row Level Security. This means anyone can use these ideas with the caveats from above; i.e. reduce access from powerful users and schemas.
NOTE: what I said above, these ideas are not perfect and if someone does have access to SYSDBA or a DBA like privileges they could get around this solution. But it is better than doing nothing!! and if we combine with audit events we can minimise the risks by reacting in real or semi-time if someone disables our security. Its the best we can do without access to internal c code and without cost options
What do we want to achieve here as an example. I want to have a table in as schema that cannot be changed or DML performed on it outside of a fixed API. I want to have an API that is allowed to delete blocks of rows of data and also new rows can be added but only through the API. I want anyone allowed access to be able to read the data in this table.
This is essentially what AUDSYS and AUD$UNIFIED do now. To do this I need to do or consider these things:
- We should create a schema that owns the table
- The schema should have no password and be locked
- Create the table that we will protect in the schema
- No grants to be made on the table
- Create a READ ONLY view based on the table and allow grants for READ to be made on this view
- Create the API that will allow insert and delete on the table
- It should not be possible to log into the schema
- We should not allow the schema to be proxied to
- We can create DML triggers for INSERT, UPDATE and DELETE
- UPDATE should be prevented completely
- INSERT should only be allowed via the API
- DELETE should only be allowed via the API
This will give us the basis of protecting a table. Now we need to think about how we might protect the schema and the table protection mechanisms. When we create a security mechanism in Oracle we need to now protect the security mechanism. This is the same by the way if we used Database Vault. If we used Database vault then we need to protect all of the code and schemas and user used in Database Vault otherwise someone could bypass it.
In general we need security of security and we need audit of security. So we could add the following features to our design:
- Create a DDL/ALTER trigger to prevent changing of the schema password
- Create a DDL/ALTER trigger to prevent unlocking the schema
- Create a LOGON trigger to prevent logon as the schema
- Create a LOGON trigger to prevent logon via proxy to the schema
- Create a DDL trigger to prevent creation of any object in the schema
- Create a GRANT trigger to stop any grants to ATK_SEC
- In the DML triggers ensure that no one can do DML unless the call comes from the specific API
- Detect if any trigger is dropped or disabled either through another DDL trigger or audit trails
- Because someone can turn off system triggers with ALTER SYSTEM and this is not caught by a DDL trigger, create an audit event
- Create audit events for non standard access to the table
- Create audit events for changes to any part of the security
- Create audit events for any changes to the security settings
We could even go further and detect changes to the above detections and add auditing ad-infinitum.
In summary we need to detect if someone tried to change the security and block it if possible and if someone succeeds in changing security then create an audit event to detect that fact and we can also verify all the security is valid by checking settings on a regular basis - if not raise an audit alert.
Also think about performance; we should not do things that would slow down the system noticeably.
In summary we can create a similar mechanism to AUDSYS and AUD$UNIFIED using standard features of the database; it is not as perfect as Oracles solution but we can come close.
#oracleace #sym_42 #oracle #security #audit #unified #protect #audittrail #databreach #readonly #grants #protection
