SQL*Plus: Release 11.2.0.1.0 Production on Fri Sep 2 15:24:45 2016
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter user-name: sys as sysdba
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SHOW PARAMETER AUDIT
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string C:\APP\MIS\ADMIN\ORCL\ADUMP
audit_sys_operations boolean FALSE
audit_trail string DB
SQL> ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;
System altered.
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 778387456 bytes
Fixed Size 1374808 bytes
Variable Size 444597672 bytes
Database Buffers 327155712 bytes
Redo Buffers 5259264 bytes
Database mounted.
Database opened.
SQL> ALTER SYSTEM SET audit_trail=db,extended scope=spfile;
System altered.
SQL> ALTER SYSTEM SET audit_sys_operations=true scope=spfile;
System altered.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 778387456 bytes
Fixed Size 1374808 bytes
Variable Size 444597672 bytes
Database Buffers 327155712 bytes
Redo Buffers 5259264 bytes
Database mounted.
Database opened.
SQL> create user myaudit identified by myaudit;
User created.
SQL> grant resource,connect to myaudit;
Grant succeeded.
SQL> audit all by myaudit by access;
Audit succeeded.
SQL> audit select table,update table,insert table,delete table by myaudit by acc
ess;
Audit succeeded.
SQL> conn myaudit/myaudit@orcl;
Connected.
SQL> create table emp(sno number, name varchar2(50));
Table created.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
EMP TABLE
SQL> insert into emp values(23,'ZOUNR');
1 row created.
SQL> insert into emp values(55,'KLM');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> select * from emp;
SNO NAME
---------- --------------------------------------------------
23 ZOUNR
55 KLM
SQL> update emp set name='ASHRAF' where sno=23;
1 row updated.
SQL> commit;
Commit complete.
Now Check the Audit Trial Log using following Quires
SELECT username, extended_timestamp,owner,obj_name,terminal,action_name,SQL_TEXT FROM dba_audit_trail where username='MYAUDIT' oRDER BY timestamp;
SELECT OS_USERNAME,terminal,userhost,logoff_time,timestamp,username,owner,obj_name,action_name,ADMIN_OPTION,sql_bind, SQL_TEXT,EXTENDED_TIMESTAMP
FROM dba_audit_trail where username='MYAUDIT' oRDER BY timestamp;
SQL> CONN sys as sysdba;
Enter password:
Connected.
SQL> DELETE FROM sys.aud$;
8190 rows deleted.
SQL> commit;
Commit complete.
Now Apply NO Audit
SQL> NOAUDIT session BY myaudit;
Noaudit succeeded.
SQL> NOAUDIT ALL;
Noaudit succeeded.
Check Audit Parameter
SQL> select user_name, privilege from dba_priv_audit_opts;
USER_NAME PRIVILEGE
------------------------------ ----------------------------------------
CREATE EXTERNAL JOB
CREATE ANY JOB
GRANT ANY OBJECT PRIVILEGE
EXEMPT ACCESS POLICY
CREATE ANY LIBRARY
GRANT ANY PRIVILEGE
DROP PROFILE
ALTER PROFILE
DROP ANY PROCEDURE
ALTER ANY PROCEDURE
CREATE ANY PROCEDURE
USER_NAME PRIVILEGE
------------------------------ ----------------------------------------
ALTER DATABASE
GRANT ANY ROLE
CREATE PUBLIC DATABASE LINK
DROP ANY TABLE
ALTER ANY TABLE
CREATE ANY TABLE
DROP USER
ALTER USER
CREATE USER
MYAUDIT AUDIT SYSTEM
MYAUDIT ALTER SYSTEM
22 rows selected.
SQL>
CHECK AUDIT PREVILEDGES
-----------------------------
select user_name, privilege from dba_priv_audit_opts;
To enable network auditing run the following command:
SQL> AUDIT NETWORK;
To disable network auditing, enter the following:
SQL> NOAUDIT NETWORK;
