He wanted to know if there is any trick to set object audit in Unified Audit so that instead of "select on orablog.table" could we do "select on schema.*" or similar for all privileges he wants to track on the objects such as insert, update, delete, select and execute.
With this type of requirement we can quickly see that the unified audit "when clause" or "...with granted roles..." is not as flexible as we would like it to be. For instance it would be great to do "schema.*" as a rule and it would also be great to access the fields of the current audit record being written (for all records of course) so that we could say in the when clause "...current_object_owner=audit.owner we want..." for instance. This would be very flexible if we could do this.
In terms of the requirement we have the following points:
- There is no audit object for schema.* - If there was this option it would be a simple case then to do "with granted roles DBA..."
- We could audit “all” for ISDBA and post filter the audit trail to see only those actions relevant to the schema that we are interested in
- We could audit “all” for “users with granted roles” DBA and post filter the audit trail to see only those actions relevant to the schema we are interested in
- From the other side of thinking; we could instead audit all object actions such as “actions select” for the and post filter to see if the access was by a DBA or SYSDBA etc. We would also need to enable for DBA and also SYSDBA etc. We would need to individually enable for every object needed and privileges on those objects
- The current “when clause” is extremely limited in its possibilities. If we audit all actions; “action select” for instance then it would be nice to access the current audit record being created in the “when clause” and then check if the schema of the object is in the list to limit the output BUT this is not possible
- The sys_context('userenv','current_sql') would be useful to get the SQL and parse out the schema (**if it was listed in the SQL as it may not be even if we get the SQL!**) but this is not possible from unified audit, only from FGA
So, as with all things audit trail over the years; whether that is using Oracle Unified Audit or Oracle standard audit, or a commercial third party solution we must satisfy our own requirements and the businesses auditing needs, especially the security department requirements. We must not simply start with a simple list of shipped policies from someone else including from Oracle with its built in policies or simple lists or the CIS recommendations. These are not designed for your circumstances and you must design your audit events at a high level (in words) and then work out how you would implement these in whatever technical solution you decide.
The best solution using Unified Audit for this example would be that we could script the action list for all actions on all objects in the schema we want and combine with “users with granted roles” to limit to DBA" create a second policy with the same object list and use a when clause with SYSDBA, SYSOPER etc - i.e. sys_context('userenv','isdba') or simply audit everything as a system user such as SYDBA or SYSDG etc.
Audit trail design like everything in Oracle security ends up being a compromise.
#oracleace #dbsec #oracle #security #audit #events #databreach
