Auditing procedure in oracle10g

[Gerri]

Hi all,

I need to know about the auditing procedure for oracle database and tell me.How this information would be helpful to manage my database and how many portions where I can apply this procedure to find out the result.I heard more about but didn't use this utility ever.Is it co-related to the dynamic views of database.

Thanks and regards
G.Williams

[opaper]

Server Setup for Auditing :

The auditing is an default behavior of the database.You can view all current settings for the database to execute the following parameter.

SQL> SHOW PARAMETER AUDIT;

The audit_trail parameter shows the current status as NONEwhen auditing is disabled which is default behavior of database.

AUDIT_TRAIL = { none | os | db | db,extended | xml | xml,extended }

There are six values are shown above that you can set to change the behavior of Auditing.

[MindSpace]

Auditing options :

To perform auditing,you have to create a new user for only auditing purpose.

SQL> CONNECT sys/password AS SYSDBA

SQL> CREATE USER Audit_man IDENTIFIED BY password
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON users;
SQL> GRANT connect TO audit_test;
SQL> GRANT create table, create procedure TO audit_test;


Now,we can perform the audit operations using Audit_name user :

SQL> CONNECT sys/password AS SYSDBA

SQL> AUDIT ALL BY audit_test BY ACCESS;
SQL> AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, 
                                 DELETE TABLE BY audit_test BY ACCESS;
SQL> AUDIT EXECUTE PROCEDURE BY audit_test BY ACCESS;

[Modifier]

Displaying Audit Trail :

You can view the generated audit trails which is stored in SYS.AUD$ table.You can view through this or you can use these views to get information in more simplified format :

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

This view will represent the different views which can be used to step forward for more auditing operations.

[Praetor]

Fine Grained Auditing (FGA) :

The Fine grained auditing is not controlled by the database,it is user controlled auditing.It performs the auditing actions based on user-defined predicates. It doesn't follow the parameter AUDIT_TRAIL and store the audit record into FGA_LOG$ table.

Using fine grained auditing :

create a table and get connect with audit_man user :

SQL> CONN audit_man/password


SQL> INSERT INTO emp (empno, ename, sal) VALUES (9999, 'Tim', 1);
SQL> INSERT INTO emp (empno, ename, sal) VALUES (9999, 'Larry', 50001);
SQL> COMMIT;

queries on the salary column :

SQL> CONN sys/password AS sysdba

BEGIN
DBMS_FGA.add_policy(
object_schema => 'AUDIT_TEST',
object_name => 'EMP',
policy_name => 'SALARY_CHK_AUDIT',
audit_condition => 'SAL > 50000',
audit_column => 'SAL');
END;

[Katty]

Auditing Administrative Users :

The session which is connected as a SYS can also be audited.You can make it enable and disable it using the following parameter :


AUDIT_SYS_OPERATIONS = TRUE

If your audit trail is specified as XML then your audit trail would be recorded in specific location.If AUDIT_FILE_DEST parameter is not specified with any value then the default location would be $ORACLE_BASE/admin/$DB_UNIQUE_NAME/adumpYou can specify different location to to change the values.