When attempting to make their Oracle database as secure as possible, many organizations turn on Oracle’s auditing feature. Oracle has a very robust auditing feature that enables us to log every action taken in the database. We can audit connections, object creation, data updates, deletes, and many other database activities.
Some organizations turn on auditing in order to comply with regulations or corporate security policy and may not actually review the logs that are produced until there is suspected breach of security.
These organizations may be in for a surprise if they use a shortcut that Oracle provides for configuring auditing. Shortcuts are an easy and fast way to configure auditing. Instead of specifying each individual activity to be audited, they can be used to specify a group of them at one time. These shortcuts can be deceiving, however. One of these shortcuts is: Audit All.
Despite the comprehensive sound of Audit All, there are some important activities that Audit all does not capture. These include renaming and altering tables, and other system activities. They also do not audit any operations on the audit tables themselves.
So when configuring auditing, be sure to capture everything you want by specifying the activities that you wish to audit individually.
The following list from Oracle Documentation describes the activities that are not audited when you specify Audit All:
- ALTER SEQUENCE
- ALTER TABLE
- COMMENT ON TABLE table, view, materialized view
- COMMENT ON COLUMN table.column, view.column, materialized view.column
- DELETE FROM table, view
- Execution of any procedure or function or access to any variable, library, or cursor inside a package.
- GRANT privilege ON directory
- REVOKE privilege ON directory
- GRANT privilege ON procedure, function, package
- REVOKE privilege ON procedure, function, package
- GRANT privilege ON sequence
- REVOKE privilege ON sequence
- GRANT privilege ON table, view, materialized view
- REVOKE privilege ON table, view, materialized view
- GRANT privilege ON TYPE
- REVOKE privilege ON TYPE
- INSERT INTO table, view
- LOCK TABLE table, view
- Any statement containing sequence.CURRVAL or sequence.NEXTVAL
- SELECT FROM table, view, materialized view
- UPDATE table, view
It may not be appropriate to enable auditing on all of the above actions. Auditing some of these actions, such as inserts and updates, can have a negative impact on system performance, so be sure to consider that when deciding whether to audit those activities.
Also, be sure to audit the audit trail itself as follows:
- Audit Insert,Update,Delete on sys$aud$ by access;
The potential misunderstanding caused by the misleading name of the audit shortcut is one more good reason to review your audit trail periodically even before there is a suspected breach. By examining the audit trail you can be sure about what activities actually being tracked.
Download our database security roadmap to secure your database.
Audit configuration should also be checked during annual security audits. Contact Buda Consulting for more information about database auditing.
