How we enable auditing in Oracle Database

How we enable auditing in Oracle Database
How we enable auditing in Oracle Database

In this article we will see the steps and options we have to activate it auditing in one Oracle Database. Through auditing we can record who and how accesses or changes its data.

Auditing can be activated on specific users, on specific objects or on the entire database.

How is it activated?

First we have to see from a command window in the database if the parameter is enabled audit_trail:

SQL>  show parameter audit_trail
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      DB, EXTENDED

If it is not, we activate it with the options db,extended. The extended is needed in order to keep not only who accessed the data but also the sql statement along with its parameters.

To get the change, it needs to be done restart the instance:

alter system set audit_trail = db,extended scope=spfile;

shutdown immediate;

startup;

Audit on a specific object

If we want to audit a specific table, we run the following:

audit select on SCOTT.DEPT by access;

--noaudit select on SCOTT.DEPT;

*The by access defines that every time the same user from the same session goes to access the table to keep it as a separate record

**With the same command starting with the noaudit without the by access We do it disable.

To see that it was activated, we run the following query in the view dba_obj_audit_opts and we will see that it says A/A in the select column:

select * from DBA_OBJ_AUDIT_OPTS where object_name ='DEPT';

*we could add other actions such as insert, update, delete, alter, etc

How we enable auditing in Oracle Database
01

Where we find the results of the auditing

After we run a select on the object we activated the audit before, e.g.:

select * from scott.dept;

We will see the auditing information at dba_audit_trail, which is a table view sys.aud$.

Making the selection in the view we will see information such as from which username it was made, from which terminal, what time, in which object and exactly what sql query was run:

--view
select username,os_username,userhost,terminal,timestamp,owner,obj_name,action_name,SQL_TEXT,SQL_BIND from dba_audit_trail; 

--table select * from sys.aud$;
How we enable auditing in Oracle Database
02

Audit a specific user

We can respectively activate auditing only for a specific user and for specific actions such as select, insert, update, delete:

audit select table,update table,insert table, delete table,execute procedure BY stratos by access;

--noaudit select table,update table,insert table, delete table,execute procedure BY stratos;

Running select on the view dba_stmt_audit_opts we'll see exactly what we set auditing to record:

select * from dba_stmt_audit_opts where user_name='STRATOS';
How we enable auditing in Oracle Database
03

Making an update with this user…:

update scott.dept set LOC='FLORIDA' where LOC='BOSTON';
commit;

We will see that he also recorded this in the dba_audit_trail view.

How we enable auditing in Oracle Database
04

Audit on specific actions

Accordingly, we can set auditing to be activated in specific actions such as select, insert, update, delete:

audit select table,update table,insert table, delete table,execute procedure by access;

--noaudit select table,update table,insert table, delete table,execute procedure;

Running select on the view dba_stmt_audit_opts we will see what we set to record with the username this time empty:

How we enable auditing in Oracle Database
05

Audit on what object is made in the future

If we want to activate auditing on specific actions, in any new object we create, we add the parameter on default, as below:

audit select,insert,update,delete,alter,execute on default by access;

--noaudit select,insert,update,delete,alter,execute on default;

So once we make a table and add a record like here:

create table stratos.stratos (id int);
insert into stratos.stratos values (1);
commit;

It will be added to the view dba_obj_audit_opts:

select * from dba_stmt_audit_opts;
How we enable auditing in Oracle Database
06

And accordingly it will be recorded in the view dba_audit_trail:

select username,os_username,userhost,terminal,timestamp,owner,obj_name,action_name,SQL_TEXT,SQL_BIND from dba_audit_trail; 
How we enable auditing in Oracle Database
07

Audit everywhere

If we want to have audit enabled on everything, we run the following:

audit all by access;

--noaudit all;

If we then run select on the view dba_stmt_audit_opts, we will see that it will contain all the existing actions:

select * from dba_stmt_audit_opts;
How we enable auditing in Oracle Database
08

How we maintain auditing

Because these records will take up a lot of space in system tablespaces, we should periodically delete old records from the table sys.AUD$ which the view sees dba_audit_trail.

To delete the old manual entries we run the following:

alter table sys.AUD$ nologging;
delete from sys.aud$ 
where TIMESTAMP# <= sysdate-30;
commit;

If we want to create a job that will delete records older than 30 days automatically, we run the following:

BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'JOB_PURGE_AUDIT_RECORDS',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, TRUNC(SYSTIMESTAMP)-30); END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=daily; byhour=0; byminute=0; bysecond=0;',
end_date => NULL,
enabled => TRUE,
comments => 'Update last_archive_timestamp');
END;
/

To see when the job ran last and when it will run again, run the following:

select LAST_START_DATE,LAST_RUN_DURATION,NEXT_RUN_DATE from dba_scheduler_jobs where job_name= 'JOB_PURGE_AUDIT_RECORDS';

Sources:

Share it

Leave a reply