Wednesday, August 22, 2007

Oracle for Beginner: A Simple Audit

$ sqlplus /nolog
SQL> conn / as sysdba
SQL> alter system set audit_trail=db scope=spfile;
SQL> shutdown immediate;
SQL> startup
SQL> conn / as sysdba
SQL> select name, value from v$parameter where name like 'audit%';
SQL> @$ORACLE_HOME/rdbms/admin/cataudit.sql
SQL> audit connect by budsus whenever not successful;
SQL> conn budsus/sdasdasd
SQL> conn budsus/xhsjd
SQL> conn / as sysdba
SQL> desc aud$;
SQL> select * from aud$;

SQL> grant select, insert, update on hr.departments to budsus;
SQL> Audit Update, Delete, Insert On hr.departments By Access Whenever Successful;
SQL> conn budsus/xxx
SQL> insert into hr.departments values (... ... );
SQL> conn / as sysdba
SQL> col comment$text format a20
SQL> select statement, timestamp#, userid, comment$text from aud$ where userid='BUDSUS'
SQL> select * from select * from dba_common_audit_trail;
SQL> noaudit update, delete, insert on hr.departments;

1 comment:

Adhi Yudho said...

Thanks Mr. Budsus... Because of you, now, i know more about Oracle.