ORACLE TYPES OF AUDITING

Share via:

                                                            AUDITING

Auditing is for verification purpose    

Types of auditing:

  • Privilege level
  • Statement level
  • Object level

To enable the auditing  audit_trail=true or db or os

To disable the auditing audit_trail=false or none

If the value is true it will enable the auditing and audit files will be stored in database level as well as o/s level

If the value is db it will enable the auditing and audit files will be stored in database level only

If the value is o/s it will enable the auditing and audit files will be stored at o/s level

Default destination os audit files will be

$cd $ORACLE_HOME/rdbms/audit

If you want to store audit files in user defined destination then we have to use one parameter called as audit_file_dest=

 

Object level auditing: when user1 granted select privilege on emp table to user2. Now user1 wants to audit the privilege on emp table. User1 wants to check How many times user2 has  used his  privilege.

User1>grant select on emp to user2;

User1>audit select on emp;

User2>select * from user1.emp;

User1>select username,obj_name,action from user_audit_trail;

Privilege level auditing:  in this privilege level we can audit on system level privileges. Privilege level is also known as system level. As a sysdba I granted create session privilege to user.

Now as a sysdba I want to check at what time user has login to database and what time he has logout from database. Whether he is successfully login or not.

Sys>audit create session;

Sys>conn u1/u1

Sys>select username,to_char(timestamp,’dd-mon-yy:hh24:mi:ss’) login,to_char(logofftime, ‘dd-mon-yy:hh24:mi:ss’) logoff,decode(returncode,1017,’FAIL’,’success’)

From dba_audit_trail;

Statement level auditing: in this we can audit on oracle preserved key words like table, view, index etc.

Sys>audit table;

Sys>noaudit table;

User1>create table t1(no number);

User1>alter table t1 add name varchar2(10);

Sys>select count(*) from aud$;

 

Share via:
Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (11 votes, average: 5.00 out of 5)
Loading...

Add Comment