Oracle Database Audit Trail Security

Oracle Database Audit Trail Security

This article presents Oracle Audit Trail,I will demonstrate how to enable and set audit options.

We all DBA have to set database at security level, because the company doesn’t risk any problems and may not trust users. That’s why one of the best useful Oracle security tool is Audit Trail.

SQL> show parameter audit trail

NAME                           TYPE        VALUE

---------------------------- ----------- ----------------------------

audit_file_dest                string        u01/app/oracle/admin/orcl/adump           

audit_sys_operations      boolean    FALSE

audit_syslog_level          string

audit_trail                      string         NONE

Now lets make Audit Trail by basic Alter Command.

SQL> alter system set audit_sys_operations=TRUE scope=SPFILE;

System altered.

SQL> alter system set audit_trail=db_extended scope=SPFILE;

System altered.

Now close the database to set our changes

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

Total System Global Area  238530560 bytes

Fixed Size                  1335724 bytes

Variable Size             155192916 bytes

Database Buffers           79691776 bytes

Redo Buffers                2310144 bytes

Database mounted.

Database opened.

Now check it again

SQL> show parameter audit trail

NAME                                     TYPE                     VALUE

---------------------------         -----------       ------------------------------

audit_file_dest                      string                     /u01/app/oracle/admin/orcl/adump

audit_sys_operations            boolean                 TRUE

audit_syslog_level                string

audit_trail                             string                    DB_EXTENDED

Okay, its ready to use AUDIT TRAIL. Lets do some examples.

First give the user some privileges then give the user some object privileges

SQL> Audit create any table,drop any table by VEYSI by ACCESS;

Audit succeeded.

Now create Audit table on Veysi User.

SQL> create table audit_vey ( id number(10) );

Table created.

I check my tables for using.

SQL> SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER='VEYSI';

TABLE_NAME

------------------------------

VEY_VEY

TBL_READ_ONLY

STUDENT

FLSHBCK_DB_TEST

AUDIT_VEY

REDEF_TAB2

REDEF_TAB

T2

T1

TB1_VEY1

TABLE1

TABLE_NAME

------------------------------

TAB1_2_LOG

TAB2

TAB1

14 rows selected.

Lets use the student table.

SQL> select * from student;

        ID NAME       SURNAME

---------- ---------- ----------

         1 veysi      gunay

         2 elcin      mustafayev

I have this example table. Now I'm gonna change something.

SQL> insert into student (id,name) values (3,'mustafa');

1 row created.

SQL> update student set surname='pasa' where id=2;

1 row updated.

SQL> commit;            

Commit complete.

SQL> select * from student;

        ID NAME       SURNAME

---------- ---------- ----------

         3 mustafa

         1 veysi      gunay

         2 elcin      pasa

Okay,Now connect as SYS admin and check the audit trail.

SQL> COLUMN user_name       FORMAT A10

SQL> COLUMN action_name   FORMAT A10

SQL> COLUMN object_name   FORMAT A10

SQL> COLUMN sql_text          FORMAT A35

SQL>SELECT username,obj_name,action_name, sql_tex FROM   dba_audit_trail  2  WHERE  username = 'VEYSI'  3  ORDER BY timestamp; USERNAME OBJ_NAME   ACTION_NAME  SQL_TEXT-------- ---------- ------------ -------------------------------------VEYSI     AUDIT_TEST      CREATE TABLE  create table audit_vey (id number)

VEYSI         STUDENT             SELECT         select * from student

VEYSI         STUDENT            INSERT           insert into student (id,name) values (3,'mustafa')

VEYSI         STUDENT             UPDATE       update student set surname='pasa' where id=2

VEYSI         STUDENT             SELECT        select * from student

As you See The Audit Table get the action of Veysi user on the table student. I hope it would useful.