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$;
Loading...
