In this article, I will continue with Oracle Database Security and I will present some important facts about standard database auditing, audit triggers, and audit policies in Oracle. Database auditing has two components: monitoring and persistent registration of established database activity sets and events. The purposes of database auditing are non-repudiation, investigation of suspicious activities, detection of problems generated by configurations regarding authorization (resources access), compliance with actual legislation and control.
Standard Auditing
What activities do we audit? Database starting and stopping as well as the connections made by the database administrator are implicitly audited by Oracle and data is automatically stored in the operating system. The table below shows other activities that can be monitored:
Where do we keep the audited activities?
- in the database, using database audit trail, where we have two possibilities:
-
- audit_trail =DB
which can be done with the following code:alter system set audit_trail=db scope=spfile;
- audit_trail =DB
-
- audit_trail =DB,EXTENDED
using the following code:alter system set audit_trail= db, extended scope=spfile;
The difference between DB and DB,EXTENDED is that the second populates the SQLBIND and SQLTEXT CLOB columns of the SYS.AUD$ table.
- audit_trail =DB,EXTENDED
- external, using operating system audit trail, with the following possibilities:
- audit_trail = OS
and the code used is:alter system set audit_trail=os scope=spfile;
- audit_trail = XML and the AUDIT_FILE_DEST = file path (implicitly is $ORACLE_BASE/admin/$ORACLE_SID/adump)
with the code:alter system set audit_trail=xml scope=spfile;
- audit_trail = OS
In order to find the current configuration of the stored audited activities, one can run the following query, written with lowercase:
select value from v$parameter where name='audit_trail';
More useful commands:
[table id=43 /]
Now let’s have some examples of database auditing.
Here is an example of a standard audit with audited information stored in the database:
The audited information consists of the SELECT statements executed on the database tables.
In SQL Developer, execute the following script:
alter system set audit_trail= db, extended scope=spfile; AUDIT SELECT TABLE;
Then, the database must be restarted. In order to do that, in the SQLPlus terminal, connect with username sys as sysdba / password and run the following statements:
SHUTDOWN IMMEDIATE STARTUP
Note that the above sizes differ from one system to another.
To verify if the audit trail is set correctly, run the following query:
select value from v$parameter where name='audit_trail';
or:
show parameter audit_trail;
When you want to stop the audit, execute:
NOAUDIT SELECT TABLE;
To view what statements were recorded by the audit, you can use:
select dbms_lob.substr( sqltext, 4000, 1 )
from SYS.AUD$
where OBJ$NAME IN ('EMPLOYEES','DEPARTMENTS','JOBS','LOCATIONS');
or
select count(*), OBJ$NAME, USERID
from SYS.AUD$
where OBJ$NAME IN ('EMPLOYEES','DEPARTMENTS','JOBS','LOCATIONS')
group by rollup (OBJ$NAME, USERID);
Another example is for standard audit with audited data stored in an XML file, in the standard path.
alter system set audit_trail=xml scope=spfile; AUDIT SELECT, INSERT, UPDATE, DELETE ON employees WHENEVER NOT SUCCESSFUL;
Again, restart the database: connect to the SQLPlus terminal with username sys as sysdba / password and run SHUTDOWN IMMEDIATE and STARTUP commands.
Then, each time a select, insert, update and delete query fails on employees table, it should be recorded in the XML file.
When we want to stop the audit, we run the following commands in the database development environment:
NOAUDIT ALL; NOAUDIT ALL ON DEFAULT;
And restore the default audit trail:
alter system set audit_trail=db scope=spfile;
Below is an example of a part of an XML audit file:
How do we delete the audited information?
The volume of the audited data can become very big due to the number of the audited activities and their frequency. This is why it is recommended to periodically archive the audited data and delete it from the production system.
If the audited data are stored in the database (database audit trail), then we can use delete statements (but only after we have archived the data!):
DELETE FROM SYS.AUD$;
You can choose to delete the audited information for a specific database object, for example, a table called products:
DELETE FROM SYS.AUD$ WHERE OBJ$NAME='PRODUCTS';
Audit Triggers
A trigger is a PL/SQL block or the CALL statement of a PL/SQL procedure that is automatically executed each time an event occurs. There are two types of triggers: at the database level (database statements) and at the application level (for example pushing a button on an Oracle Form). The triggers used for auditing are the database level triggers. They classify into the following categories:
- DML triggers – where a DML statement is triggered on a table. Those triggers can be executed once at the command level regardless of the number of records (triggers at the statement level) or they can be executed FOR EVERY ROW (triggers at the record level). Types of record level triggers: BEFORE STATEMENT, AFTER STATEMENT, BEFORE EACH ROW, AFTER EACH ROW;
- INSTEAD OF triggers – where a DML statement is triggered on a view;
- SYSTEM triggers – triggered by events such as starting/stopping the database, DDL statements, user login/logout. Types of system triggers: AFTER EVENT, BEFORE EVENT.
Querying the SYS.TRIGGER$ table or the ALL_TRIGGERS view offers information about all the database level triggers. For example, the distinct trigger type from the database can be found as follows:
SELECT DISTINCT TRIGGER_TYPE FROM ALL_TRIGGERS;
The DBA_TRIGGERS view offers information about the triggers automatically created by the Oracle products at installation. If we want to find information about the SYSTEM triggers (‘BEFORE EVENT’ and ‘AFTER EVENT’), we can run the following statement:
SELECT SUBSTR(OWNER,1,20) OWNER ,SUBSTR(TRIGGER_NAME,1,30), TRIGGER_NAME, SUBSTR(TRIGGERING_EVENT,1,30) TRIGGERING_EVENT, TRIGGER_TYPE FROM DBA_TRIGGERS WHERE TRIGGER_TYPE='BEFORE EVENT' OR TRIGGER_TYPE='AFTER EVENT' ORDER BY TRIGGER_TYPE DESC;
Also, at installation, DML triggers are automatically created on the HR user schema:
SELECT SUBSTR(TABLE_NAME,1,20) TABLE_NAME, SUBSTR(TRIGGER_TYPE,1,30) TRIGGER_TYPE,TRIGGER_BODY FROM DBA_TRIGGERS WHERE OWNER='HR';
For auditing, we can create customized triggers to record the desired information, but one should create a special table to store the audited information.
It is important to make sure that the developed triggers don’t influence the normal database activity. The purpose of auditing is to passively monitor the normal, day-to-day database activity and to store it for later analyze. Consequently, it is not recommended to create INSTEAD OF triggers to return the results from the target tables to the audit table.
DML triggers at the statement level can coexist with DML triggers at the record level. The order of call is:
- trigger BEFORE statement
- for each record affected
- trigger BEFORE record
- current DML action
- trigger AFTER record
- trigger AFTER statement
User-defined triggers will be executed only if, according to Oracle, the statement is correct and can occur. For a wrong DML statement or one that violates a constraint, an error will be returned and the trigger will not be executed. Therefore, for auditing, it is recommended to use especially the LMD triggers at the statement level.
Example of audit trigger:
Let’s assume we want to create a trigger to record in an audit table (called TAB_AUDIT_EMP) information about the DML statements that establish salaries over 20000 (currency is not important here) for the company’s employees. We want to store in TAB_AUDIT_EMP the sequence number of the query, the username, the session, the host and the date.
This can be done with the following:
CREATE TABLE TAB_AUDIT_EMP
(secv_id NUMBER(3) PRIMARY KEY,
username VARCHAR2(20),
session_nr NUMBER(10),
hostname VARCHAR2(100),
query_date DATE
);
CREATE SEQUENCE secv_aud_emp START WITH 1 INCREMENT BY 1;
CREATE OR REPLACE TRIGGER huge_salary
AFTER INSERT OR UPDATE OR DELETE OF SALARY ON EMPLOYEES
FOR EACH ROW WHEN (NEW.salary>20000)
BEGIN
INSERT INTO TAB_AUDIT_EMP
VALUES(secv_aud_emp.NEXTVAL , sys_context('userenv', 'session_user'), sys_context('userenv', 'sessionid'), sys_context('userenv', 'host'), sysdate);
END;
Assume we make a salary modification for the employees in a specific department:
UPDATE EMPLOYEES SET SALARY=25000 WHERE ID_DEPARTMENT = 1;
And then we verify the monitoring:
select secv_id, substr(username,1,20) username, session_nr, substr(hostname,1,30) hostname, query_date from TAB_AUDIT_EMP;
Audit policies
The third auditing method refers to audit policies. The definition of an audit policy contains the following:
- specification of the object (schema, object name, columns) subjected to monitoring
- specification of the actions audited for an object (SELECT, INSERT, UPDATE, DELETE): implicitly it’s SELECT
- specification of the conditions that must be fulfilled in order to record the audited information, it is done in the WHEN clause of the trigger and is optional
- an event handler that additionally handles the event, which it is optional.
An audit policy can fi active (ENABLED status) or inactive (DISABLED status). The list of the audit policies can be obtained by interrogating the ALL_AUDIT_POLICIES view:
SELECT POLICY_TEXT, ENABLED FROM ALL_AUDIT_POLICIES
For audit policy administration we have the DBMS_FGA package. In order to use this package, it is necessary to grant privilege to the users that will write PL/SQL code:
grant execute on dbms_fga to username;
Example of audit policy:
We want to create an audit policy for recording the DML statements that modify the managers (MANAGER_ID) in the DEPARTMENTS table. We can choose to create a handler for the policy, called proc_audit_alert, that informs us of a modification regarding the manager:
CREATE OR REPLACE PROCEDURE proc_audit_alert ( object_schema VARCHAR2, object_name VARCHAR2, policy_name VARCHAR2 ) AS
BEGIN
DBMS_OUTPUT.PUT_LINE('Alert! Manager Changed !');
END;
And the policy can be:
CREATE OR REPLACE PROCEDURE proc_audit_manager AS BEGIN DBMS_FGA.ADD_POLICY ( object_schema=>'ADMINDB', object_name=>'DEPARTMENTS', policy_name=>'proc_audit_manager', audit_column=>'ID_MANAGER', enable=>false, statement_types=>'UPDATE', handler_module=>'proc_audit_alert' ); DBMS_FGA.ENABLE_POLICY ( object_schema=>'ADMINDB', object_name=>'DEPARTMENTS', policy_name=>'proc_audit_manager'); END;
Note that object_schema, object_name, policy_name, audit_column, statement_types and handler_module must be adapted to the policy you want to write.
Then, we execute the procedure:
EXECUTE proc_audit_manager;
We can verify if the policy is enabled:
SELECT ENABLED, POLICY_NAME FROM ALL_AUDIT_POLICIES WHERE OBJECT_NAME='DEPARTMENTS';
Then, verify if the procedure and the policy work correctly, by executing an update:
UPDATE DEPARTMENTS SET ID_MANAGER=2 WHERE ID_DEPARTAMENT=1;
In conclusion, auditing is necessary for each database and the methods provided above help you in finding a suspicious activity that might affect your database security. For more details about Oracle database auditing, see the bibliography below.
Bibliography:
- http://www.datadisk.co.uk/html_docs/oracle/auditing.htm
- http://docs.oracle.com/cd/B10501_01/server.920/a96521/audit.htm
- https://docs.oracle.com/cd/E11882_01/server.112/e10575/tdpsg_auditing.htm#TDPSG50000
