Auditing In Oracle Of Sys and Other Users

- 

- 

-  

SQL> SHOW PARAMETER AUDIT

NAME                 TYPE        VALUE
----------------------------------------------------
audit_file_dest      string      C:\ORACLE\PRODUCT\10.2.0\
                                 ADMIN\DB10G\ADUMP
audit_sys_operations boolean     FALSE
audit_trail          string      NONE
SQL>

Audit is disable by default, but can be enabled by audit_trail 

static parameter which can have following possible values.

Audit_Trail = 'Following_Option'

• None

• OS

• DB

• DB, Extended

• XML

• XML, Extended

Description Of the Above Options:

None Or False 

Auditing is disabled.

OS

Audit is enables with all records directed to the Operating 

Systems.

DB or True

Audit is enables for all records stored in the database audit 

trail ie.SYS.AUD$

DB, Extended

Same as DB , but two more columns will be populated by this 

options which are SQL_BINDS and SQL_TEXT.

XML (New In 10gR2)

Audit is enable for all OS records and written in the file 

format xml OS Files.

XML, Extended (New In 10gR2)

Same as XML , but SQL_BIND and SQL_Text Column Values will 

also be shown in the file format XML OS files.

Now Start Auditing follow the steps below:

SQL> conn sys/password@connection_string AS SYSDBA

SQL> ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;

System altered.

SQL> SHUTDOWN
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP
ORACLE instance started.

Total System Global Area  289406976 bytes
Fixed Size                  1248600 bytes
Variable Size              71303848 bytes
Database Buffers          213909504 bytes
Redo Buffers                2945024 bytes
Database mounted.
Database opened.

SQL>

First we create a simple user name audit_test to verify 

the auditing with different options.

CONNECT sys/password AS SYSDBA

CREATE USER audit_test IDENTIFIED BY password
  DEFAULT TABLESPACE users_test
  TEMPORARY TABLESPACE temp_text
  QUOTA UNLIMITED ON users_test;

GRANT connect TO audit_test;
GRANT create table, create procedure TO audit_test; 

USER CREATED AND GRANTED. 

Our next step is to start auditing for the created user 

AUDIT_TEST.

• AUDIT ALL BY audit_test BY ACCESS;

• AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, 

  DELETE TABLE BY audit_test BY ACCESS;

• AUDIT EXECUTE PROCEDURE BY audit_test BY ACCESS; 

Now we performs some actions which can be use to test 

our auditing is working fine or not.

CONN audit_test/password

CREATE TABLE test_audit (
  test_id  NUMBER
);

INSERT INTO test_audit (test_id) VALUES (1);
UPDATE test_audit SET test_id = test_id;
SELECT * FROM test_audit;
DELETE FROM test_audit;
DROP TABLE test_audit;

Now Time to check out audit tables.

There are some audit views which are populated when 

auditing is set to be true. 

SELECT view_name
FROM   dba_views
WHERE  view_name LIKE 'DBA%AUDIT%'
ORDER BY view_name;

VIEW_NAME
------------------------------
DBA_AUDIT_EXISTS
DBA_AUDIT_OBJECT
DBA_AUDIT_POLICIES
DBA_AUDIT_POLICY_COLUMNS
DBA_AUDIT_SESSION
DBA_AUDIT_STATEMENT
DBA_AUDIT_TRAIL
DBA_COMMON_AUDIT_TRAIL
DBA_FGA_AUDIT_TRAIL
DBA_OBJ_AUDIT_OPTS
DBA_PRIV_AUDIT_OPTS
DBA_REPAUDIT_ATTRIBUTE
DBA_REPAUDIT_COLUMN
DBA_STMT_AUDIT_OPTS

14 rows selected.

COLUMN username FORMAT A10
COLUMN owner    FORMAT A10
COLUMN obj_name FORMAT A10
COLUMN extended_timestamp FORMAT A20

SELECT username,
       extended_timestamp,
       action_name
FROM   dba_audit_trail
WHERE  owner = 'AUDIT_TEST'
ORDER BY timestamp;

USERNAME   EXTENDED_TIMESTAMP                 ACTION_NAME
---------- -------------------------------------------------
AUDIT_TEST 17-OCT-2011 14:16:55.435000 +00:00 CREATE TABLE
AUDIT_TEST 17-OCT-2011 14:16:55.514000 +00:00 INSERT
AUDIT_TEST 17-OCT-2011 14:16:55.545000 +00:00 UPDATE
AUDIT_TEST 17-OCT-2011 14:16:55.592000 +00:00 SELECT
AUDIT_TEST 17-OCT-2011 14:16:55.670000 +00:00 DELETE
AUDIT_TEST 17-OCT-2011 14:17:00.045000 +00:00 DROP TABLE

6 rows selected.

SQL>

Or auditing can be set on in Init.ora file by adding 

the following parameters.

audit_sys_operation <------ It will start auditing 

for sys user. 

audit_file_dest='Location\xml_audit' 

audit_trail='XML','EXTENDED'

and then you can create a batch file of the following 

query to get the logs of user sys on daily basis.

set linesize 500
set pagesize 200
column db_user format a10
column os_user format a24
column os_host format a28
column extended_timestamp format a34
column sql_text format a300

column tm new_value file_time noprint

select to_char(sysdate, 'fmDD-MON-YYYY') tm from dual;

prompt&file_time

spool location\audit_log_sys__&file_time.log(desired Location)

select db_user, os_user, os_host, TO_CHAR(EXTENDED_TIMESTAMP,

'fmDd-MM-YYYY HH:MI:SS AM'), sql_text
from v$xml_audit_trail
where db_user in ('sys', 'SYS', '/')
and to_char(extended_timestamp, 'fmDd-MM-YYYY') 

= to_char(SYSDATE, 'fmDd-MM-YYYY')
order by 4;

spool off;

EXIT;

just copy the above code and paste in the notepad file and 

create a batch file in schedule task. it will populate the 

spool file. 

Modifications can be done by the following command.Or you 

can just start auditing for the specific table by the 

following command.

AUDIT INSERT, UPDATE, DELETE ON sys.aud$ BY ACCESS;

The OS and XML audit trail are managed through OS, These files should be
secure at the OS level and correct permission should be given to the users by
System administrator.
 It is hardly recommended that the above procedure should be done
by only Oracle database administrator.

Hope This will Help.