How to enable auditing in Oracle Database

Author
Recent Posts

The journey began in 2008 where I officially started working in the field of IT (IT). Starting the first semester of school I realized a special attraction towards databases and automation. I have been involved with databases such as Microsoft SQL Server / Oracle Database, data analysis and automations using the command line (CLI), Visual Basic for Applications and Python. Through years of experience I have developed these capabilities so that I can make my life easier. For me, the purpose of every IT guy and every office worker is to have the knowledge so that through tools he can work a little but produce a lot. Through this his website DataPlatform.gr I try to offer knowledge and propose solutions to everyday problems.

Certifications:

certs

Latest posts by Stratos Matzouranis (see all)

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$;

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';

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.

In case we have enabled auditing to record it CREATE SESSION then we can see what time each user connected with the following query:

--audit CREATE SESSION by STRATOS by session;

select * from dba_audit_session order by timestamp desc;

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:

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;

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;

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 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.

First of all, we must have done an init cleanup in which we define that the audits records are transferred from the schema SYSTEM in the SYSAUX:

BEGIN
DBMS_AUDIT_MGMT.INIT_CLEANUP(
              audit_trail_type   => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
      default_cleanup_interval   => 12 /* hours */);
END;

Then we check that it has been activated:

SET SERVEROUTPUT ON
BEGIN
IF DBMS_AUDIT_MGMT.is_cleanup_initialized(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN
DBMS_OUTPUT.put_line('YES');
ELSE
DBMS_OUTPUT.put_line('NO');
END IF;
END;
/

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); 
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
audit_trail_type         =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
use_last_arch_timestamp  =>  TRUE); 
END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=daily; byhour=0; byminute=0; bysecond=0;',
end_date => NULL,
enabled => TRUE,
comments => 'JOB_PURGE_AUDIT_RECORDS');
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';

How do I store the audit trail files in a separate tablespace

So that we don't fill up SYS / SYSTEM / SYSAUX tablespace we have the possibility to create a new tablespace and define in it that the auditing is stored.

To do this we first create this new tablespace:

create tablespace AUDIT_TS datafile '/oracle/oradata/orcl/audit_ts_001.dbf' size 100m autoextend on next 64m maxsize 32767m;

And then we run the following command putting the name of the tablespace we just created:

BEGIN DBMS_AUDIT_MGMT.set_audit_trail_location(audit_trail_type=>DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, audit_trail_location_value => 'AUDIT_TS');
END;
/

So by running the following query we can confirm that the table AUD$ belongs to the new tablespace:

select * from dba_tables where table_name ='AUD$';

We reset the init cleanup to make sure that the records are transferred to the new tablespace:

BEGIN
DBMS_AUDIT_MGMT.DEINIT_CLEANUP(
  AUDIT_TRAIL_TYPE  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL);
END;

BEGIN
DBMS_AUDIT_MGMT.INIT_CLEANUP(
              audit_trail_type   => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
      default_cleanup_interval   => 12 /* hours */);
END;

SET SERVEROUTPUT ON
BEGIN
IF DBMS_AUDIT_MGMT.is_cleanup_initialized(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN
DBMS_OUTPUT.put_line('YES');
ELSE
DBMS_OUTPUT.put_line('NO');
END IF;
END;
/

In case of ORA-01403 error

In case we have upgraded from an older version, the tablespace change command may show us the following error:

ORA-01403: no data found

In this case we check in which schema the table is AUD$:

select * from dba_tables where table_name ='AUD$';

If it is indeed in a different e.g. in the SYSTEM instead of SYS then we should keep a backup of the table DDL:

select dbms_metadata.get_ddl('TABLE', 'AUD$','SYSTEM') from dual;

Then we delete the synonym and the table and rebuild them with the DDL we kept in the schema SYS:

drop table system.aud$
drop synonym sys.aud$;
 
  CREATE TABLE "SYS"."AUD$" 
   (  "SESSIONID" NUMBER NOT NULL ENABLE, 
  "ENTRYID" NUMBER NOT NULL ENABLE, 
  "STATEMENT" NUMBER NOT NULL ENABLE, 
  "TIMESTAMP#" DATE, 
  "USERID" VARCHAR2(128), 
  "USERHOST" VARCHAR2(128), 
  "TERMINAL" VARCHAR2(255), 
  "ACTION#" NUMBER NOT NULL ENABLE, 
  "RETURNCODE" NUMBER NOT NULL ENABLE, 
  "OBJ$CREATOR" VARCHAR2(128), 
  "OBJ$NAME" VARCHAR2(128), 
  "AUTH$PRIVILEGES" VARCHAR2(16), 
  "AUTH$GRANTEE" VARCHAR2(128), 
  "NEW$OWNER" VARCHAR2(128), 
  "NEW$NAME" VARCHAR2(128), 
  "SES$ACTIONS" VARCHAR2(19), 
  "SES$TID" NUMBER, 
  "LOGOFF$LREAD" NUMBER, 
  "LOGOFF$PREAD" NUMBER, 
  "LOGOFF$LWRITE" NUMBER, 
  "LOGOFF$DEAD" NUMBER, 
  "LOGOFF$TIME" DATE, 
  "COMMENT$TEXT" VARCHAR2(4000), 
  "CLIENTID" VARCHAR2(128), 
  "SPARE1" VARCHAR2(255), 
  "SPARE2" NUMBER, 
  "OBJ$LABEL" RAW(255), 
  "SES$LABEL" RAW(255), 
  "PRIV$USED" NUMBER, 
  "SESSIONCPU" NUMBER, 
  "NTIMESTAMP#" TIMESTAMP (6), 
  "PROXY$SID" NUMBER, 
  "USER$GUID" VARCHAR2(32), 
  "INSTANCE#" NUMBER, 
  "PROCESS#" VARCHAR2(16), 
  "XID" RAW(8), 
  "AUDITID" VARCHAR2(64), 
  "SCN" NUMBER, 
  "DBID" NUMBER, 
  "SQLBIND" CLOB, 
  "SQLTEXT" CLOB, 
  "OBJ$EDITION" VARCHAR2(128), 
  "RLS$INFO" CLOB, 
  "CURRENT_USER" VARCHAR2(128)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM" 
LOB ("SQLBIND") STORE AS BASICFILE (
  TABLESPACE "SYSTEM" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION 
  NOCACHE LOGGING 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) 
LOB ("SQLTEXT") STORE AS BASICFILE (
  TABLESPACE "SYSTEM" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION 
  NOCACHE LOGGING 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) 
LOB ("RLS$INFO") STORE AS BASICFILE (
  TABLESPACE "SYSTEM" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION 
  NOCACHE LOGGING 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))

We give the right of delete to delete_catalog_role and retry the change tablespace command:

GRANT DELETE ON AUD$ TO DELETE_CATALOG_ROLE;

BEGIN DBMS_AUDIT_MGMT.set_audit_trail_location(audit_trail_type=>DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, audit_trail_location_value => 'AUDIT_TS');
END;
/