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.
