===================================
===========================================
Audit_trail =DB
=======================================
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Aug 15 10:32:16 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> spool c:/oracle_audit_10g.txt
SQL>
SQL> set serveroutput on
SQL> set sqlprompt 'Halim@dba>';
Halim@dba>
Halim@dba>
Halim@dba>
Halim@dba>show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string H:\ORACLE\PRODUCT\10.2.0\ADMIN
\HALIM\ADUMP
audit_sys_operations boolean FALSE
audit_trail string NONE
Halim@dba>
Halim@dba>
Halim@dba>
Halim@dba>ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;
System altered.
Halim@dba>
Halim@dba>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
Halim@dba>
Halim@dba>
Halim@dba>startup
ORACLE instance started.
Total System Global Area 591396864 bytes
Fixed Size 1250308 bytes
Variable Size 205523964 bytes
Database Buffers 377487360 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
Halim@dba>
Halim@dba>
Halim@dba>conn sys@halim as sysdba
Connected.
Halim@dba>
Halim@dba>
Halim@dba>CREATE USER audit_test IDENTIFIED BY password
2 DEFAULT TABLESPACE users
3 TEMPORARY TABLESPACE temp
4 QUOTA UNLIMITED ON users;
User created.
Halim@dba>
Halim@dba>GRANT connect TO audit_test;
Grant succeeded.
Halim@dba>GRANT create table, create procedure TO audit_test;
Grant succeeded.
Halim@dba>
Halim@dba>
Halim@dba>
Halim@dba>conn / as sysdba
Connected.
Halim@dba>
Halim@dba>
Halim@dba>AUDIT ALL BY audit_test BY ACCESS;
Audit succeeded.
Halim@dba>AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY audit_test BY ACCESS;
Audit succeeded.
Halim@dba>AUDIT EXECUTE PROCEDURE BY audit_test BY ACCESS;
Audit succeeded.
Halim@dba>
Halim@dba>
Halim@dba>conn audit_test/passowrd
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
Halim@dba>
Halim@dba>
Halim@dba>
Halim@dba>conn audit_test/password@halim
Connected.
Halim@dba>
Halim@dba>CREATE TABLE test_tab (
2 id NUMBER
3 );
Table created.
Halim@dba>
Halim@dba>INSERT INTO test_tab (id) VALUES (1);
1 row created.
Halim@dba>UPDATE test_tab SET id = id;
1 row updated.
Halim@dba>SELECT * FROM test_tab;
ID
----------
1
Halim@dba>DELETE FROM test_tab;
1 row deleted.
Halim@dba>
Halim@dba>DROP TABLE test_tab;
Table dropped.
Halim@dba>
Halim@dba>set pagesize 1000
Halim@dba>set linesize 1000
Halim@dba>
Halim@dba>
Halim@dba>COLUMN username FORMAT A10
Halim@dba>COLUMN owner FORMAT A10
Halim@dba>COLUMN obj_name FORMAT A10
Halim@dba>COLUMN extended_timestamp FORMAT A35
Halim@dba>
Halim@dba>
Halim@dba>
Halim@dba>SELECT username,
2 extended_timestamp,
3 owner,
4 obj_name,
5 action_name
6 FROM dba_audit_trail
7 WHERE owner = 'AUDIT_TEST'
8 ORDER BY timestamp;
USERNAME EXTENDED_TIMESTAMP OWNER OBJ_NAME ACTION_NAME
---------- ----------------------------------- ---------- ---------- ----------------------------
AUDIT_TEST 15-AUG-10 10.47.39.218000 AM +06:00 AUDIT_TEST TEST_TAB CREATE TABLE
AUDIT_TEST 15-AUG-10 10.47.39.234000 AM +06:00 AUDIT_TEST TEST_TAB INSERT
AUDIT_TEST 15-AUG-10 10.47.39.234000 AM +06:00 AUDIT_TEST TEST_TAB UPDATE
AUDIT_TEST 15-AUG-10 10.47.39.234000 AM +06:00 AUDIT_TEST TEST_TAB SELECT
AUDIT_TEST 15-AUG-10 10.47.39.265000 AM +06:00 AUDIT_TEST TEST_TAB DELETE
AUDIT_TEST 15-AUG-10 10.47.43.843000 AM +06:00 AUDIT_TEST TEST_TAB DROP TABLE
6 rows selected.
Halim@dba>
Halim@dba>create view audit_test.view_audit as select * from dba_audit_trail;
create view audit_test.view_audit as select * from dba_audit_trail
*
ERROR at line 1:
ORA-01031: insufficient privileges
Halim@dba>grant create view to audit_test;
Grant succeeded.
Halim@dba>create view audit_test.view_audit as select * from dba_audit_trail;
create view audit_test.view_audit as select * from dba_audit_trail
*
ERROR at line 1:
ORA-01031: insufficient privileges
Halim@dba>grant select on dba_audit_trail to audit_test;
Grant succeeded.
Halim@dba>create view audit_test.view_audit as select * from dba_audit_trail;
View created.
Halim@dba>
Halim@dba>
Halim@dba>
Halim@dba>
Halim@dba>
Halim@dba>desc view_audit
Halim@dba>
Halim@dba>conn / as sysdba
Connected.
Halim@dba>
Halim@dba>
Halim@dba>AUDIT INSERT, UPDATE, DELETE ON sys.aud$ BY ACCESS;
Audit succeeded.
Halim@dba>
Halim@dba>
Halim@dba>
Halim@dba>conn / as sysdba
Connected.
Halim@dba>
Halim@dba>
Halim@dba>noaudit all by audit_test;
Noaudit succeeded.
Halim@dba>
Halim@dba>
Halim@dba>conn / as sysdba
Connected.
Halim@dba>
Halim@dba>
Halim@dba>noaudit all
2 /
Noaudit succeeded.
Halim@dba>
Halim@dba>
Halim@dba>NOAUDIT session;
Noaudit succeeded.
Halim@dba>NOAUDIT session BY scott;
Noaudit succeeded.
Halim@dba>NOAUDIT DELETE ANY TABLE;
Noaudit succeeded.
Halim@dba>NOAUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE,
2 EXECUTE PROCEDURE;
Noaudit succeeded.
Halim@dba>DELETE FROM SYS.AUD$;
261 rows deleted.
Halim@dba>
Halim@dba>
Halim@dba>commit;
Commit complete.
Halim@dba>
Halim@dba>
Halim@dba>
Halim@dba>
Halim@dba>
Halim@dba>
Halim@dba>
============================================================
============================================================
Fine Grained Auditing (FGA)
============================================================
============================================================
Halim@dba>
Halim@dba>
Halim@dba>conn audit_test/password
Connected.
Halim@dba>
Halim@dba>CREATE TABLE emp (
2 empno NUMBER(4) NOT NULL,
3 ename VARCHAR2(10),
4 job VARCHAR2(9),
5 mgr NUMBER(4),
6 hiredate DATE,
7 sal NUMBER(7,2),
8 comm NUMBER(7,2),
9 deptno NUMBER(2)
10 );
Table created.
Halim@dba>
Halim@dba>INSERT INTO emp (empno, ename, sal) VALUES (9999, 'Tim', 1);
1 row created.
Halim@dba>INSERT INTO emp (empno, ename, sal) VALUES (9999, 'Larry', 50001);
1 row created.
Halim@dba>COMMIT;
Commit complete.
Halim@dba>
Halim@dba>
Halim@dba>conn / as sysdba
Connected.
Halim@dba>
Halim@dba>
Halim@dba>BEGIN
2 DBMS_FGA.add_policy(
3 object_schema => 'AUDIT_TEST',
4 object_name => 'EMP',
5 policy_name => 'SALARY_CHK_AUDIT',
6 audit_condition => 'SAL > 50000',
7 audit_column => 'SAL');
8 END;
9 /
PL/SQL procedure successfully completed.
Halim@dba>
Halim@dba>
Halim@dba>CONN audit_test/password
Connected.
Halim@dba>SELECT sal FROM emp WHERE ename = 'Tim';
SAL
----------
1
Halim@dba>SELECT sal FROM emp WHERE ename = 'Larry';
SAL
----------
50001
Halim@dba>
Halim@dba>
Halim@dba>conn / as sysdba
Connected.
Halim@dba>
Halim@dba>SELECT sql_text
2 FROM dba_fga_audit_trail;
SQL_TEXT
--------------------------------------------
SELECT sal FROM emp WHERE ename = 'Larry'
Halim@dba>
Halim@dba>
Halim@dba>conn / as sysdba
Connected.
Halim@dba>
Halim@dba>
Halim@dba>truncate table fga_log$;
Table truncated.
Halim@dba>SELECT sql_text FROM dba_fga_audit_trail;
no rows selected
Halim@dba>
Halim@dba>
Halim@dba>
Halim@dba>BEGIN
2 DBMS_FGA.add_policy(
3 object_schema => 'AUDIT_TEST',
4 object_name => 'EMP',
5 policy_name => 'SAL_AUDIT',
6 audit_condition => NULL, -- Equivalent to TRUE
7 audit_column => 'SAL',
8 statement_types => 'SELECT,INSERT,UPDATE,DELETE');
9 END;
10 /
PL/SQL procedure successfully completed.
Halim@dba>
Halim@dba>
Halim@dba>conn audit_test/password
Connected.
Halim@dba>
Halim@dba>SELECT * FROM emp WHERE empno = 9998;
no rows selected
Halim@dba>INSERT INTO emp (empno, ename, sal) VALUES (9998, 'Bill', 1);
1 row created.
Halim@dba>UPDATE emp SET sal = 10 WHERE empno = 9998;
1 row updated.
Halim@dba>DELETE emp WHERE empno = 9998;
1 row deleted.
Halim@dba>ROLLBACK;
Rollback complete.
Halim@dba>
Halim@dba>
Halim@dba>
Halim@dba>conn / as sysdba
Connected.
Halim@dba>
Halim@dba>SELECT sql_text FROM dba_fga_audit_trail;
SQL_TEXT
--------------------------------------------------------------------
SELECT * FROM emp WHERE empno = 9998
INSERT INTO emp (empno, ename, sal) VALUES (9998, 'Bill', 1)
UPDATE emp SET sal = 10 WHERE empno = 9998
DELETE emp WHERE empno = 9998
Halim@dba>
Halim@dba>
Halim@dba>
Halim@dba>conn / as sysdba
Connected.
Halim@dba>
Halim@dba>SELECT sql_text FROM dba_fga_audit_trail;
SQL_TEXT
---------------------------------------------------------------------
SELECT * FROM emp WHERE empno = 9998
INSERT INTO emp (empno, ename, sal) VALUES (9998, 'Bill', 1)
UPDATE emp SET sal = 10 WHERE empno = 9998
DELETE emp WHERE empno = 9998
Halim@dba>BEGIN
2 DBMS_FGA.drop_policy(
3 object_schema => 'AUDIT_TEST',
4 object_name => 'EMP',
5 policy_name => 'SAL_AUDIT');
6 END;
7 /
PL/SQL procedure successfully completed.
=============================================================================
Audit_trail in OS and XML
============================================================================
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Aug 15 11:43:47 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> spool c:/audit_os.txt
SQL>
SQL>
SQL>
SQL> set sqlprompt 'Halim@dba#> ';
Halim@dba#>
Halim@dba#>
Halim@dba#> conn / as sysdba
Connected.
Halim@dba#>
Halim@dba#>
Halim@dba#> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string H:\ORACLE\PRODUCT\10.2.0\ADMIN
\HALIM\ADUMP
audit_sys_operations boolean FALSE
audit_trail string DB
Halim@dba#>
Halim@dba#>
Halim@dba#> alter system set audit_trail=os scope=spfile;
System altered.
Halim@dba#>
Halim@dba#> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
Halim@dba#>
Halim@dba#>
Halim@dba#> startup
ORACLE instance started.
Total System Global Area 591396864 bytes
Fixed Size 1250308 bytes
Variable Size 209718268 bytes
Database Buffers 373293056 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
Halim@dba#>
Halim@dba#>
Halim@dba#>
Halim@dba#> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string H:\ORACLE\PRODUCT\10.2.0\ADMIN
\HALIM\ADUMP
audit_sys_operations boolean FALSE
audit_trail string OS
Halim@dba#>
Halim@dba#>
Halim@dba#>
Halim@dba#> audit all by audit_test;
Audit succeeded.
Halim@dba#> create table halim (id number,name varchar2(20));
Table created.
Halim@dba#> insert into halim
2 select rownum, rownum||' '||'halim' from all_objects where rownum<=100;
100 rows created.
Halim@dba#> commit ;
Commit complete.
Halim@dba#>
Halim@dba#> conn audit_test/password@halim
Connected.
Halim@dba#>
Halim@dba#>
Halim@dba#> create table halim (id number,name varchar2(20));
Table created.
Halim@dba#> insert into halim select rownum, rownum||' '||'halim' from all_objects where rownum<=100;
100 rows created.
Halim@dba#> commit;
Commit complete.
Halim@dba#> conn / as sysdba
Connected.
Halim@dba#>
Halim@dba#>
Halim@dba#>
Halim@dba#> conn system/system
Connected.
Halim@dba#>
Halim@dba#>
Halim@dba#> audit connect;
Audit succeeded.
Halim@dba#>
Halim@dba#>
Halim@dba#>
Halim@dba#>
Halim@dba#>
Halim@dba#> grant dba to test identified by test;
Grant succeeded.
Halim@dba#>
Halim@dba#> conn test/test@halim
Connected.
Halim@dba#> conn test/test
Connected.
Halim@dba#>
Halim@dba#> create table t (id number) ;
Table created.
Halim@dba#> conn / as sysdba
Connected.
Halim@dba#> audit select table,insert table ,update table by test;
Audit succeeded.
Halim@dba#>
Halim@dba#> conn test/test
Connected.
Halim@dba#>
Halim@dba#>
Halim@dba#> select * from t;
no rows selected
Halim@dba#>
Halim@dba#>
Halim@dba#>
Halim@dba#> prompt 'Its write on Event Viewer/applicate log of OS'
'Its write on Event Viewer/applicate log of OS' only for some windows platform
======================================================
Halim@dba#>
Halim@dba#>
Halim@dba#>
Halim@dba#>
Halim@dba#>
Halim@dba#> alter system set audit_trail=XML scope=spfile;
System altered.
Halim@dba#> shutdown immediate
ORA-01031: insufficient privileges
Halim@dba#>
Halim@dba#> conn / as sysdba
Connected.
Halim@dba#>
Halim@dba#> shutdown immdiate
SP2-0717: illegal SHUTDOWN option
Halim@dba#> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
Halim@dba#>
Halim@dba#>
Halim@dba#> startup
ORACLE instance started.
Total System Global Area 591396864 bytes
Fixed Size 1250308 bytes
Variable Size 213912572 bytes
Database Buffers 369098752 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
Halim@dba#>
Halim@dba#>
Halim@dba#>
Halim@dba#> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string H:\ORACLE\PRODUCT\10.2.0\ADMIN
\HALIM\ADUMP
audit_sys_operations boolean FALSE
audit_trail string XML
Halim@dba#>
Halim@dba#>
Halim@dba#> audit select table,insert table ,update table by test;
Audit succeeded.
Halim@dba#>
Halim@dba#> insert into t select rownum from all_objects where rownum<=100;
100 rows created.
Halim@dba#> commit;
Commit complete.
Halim@dba#> select * from t;
Halim@dba#>
Halim@dba#> conn / as sysdba
Connected.
Halim@dba#>
Halim@dba#>
Halim@dba#> conn test/test
Connected.
Halim@dba#> conn / as sysdba
Connected.
Halim@dba#>
Halim@dba#> audit select on test.t;
Audit succeeded.
Halim@dba#>
Halim@dba#> disconnect
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Halim@dba#>
Halim@dba#> spool off
==============================================================
audit_sys_operations = true
==============================================================
Halim@dba#>
Halim@dba#>
Halim@dba#>
Halim@dba#> conn / as sysdba
Connected.
Halim@dba#>
Halim@dba#> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string H:\ORACLE\PRODUCT\10.2.0\ADMIN
\HALIM\ADUMP
audit_sys_operations boolean FALSE
audit_trail string XML
Halim@dba#>
Halim@dba#>
Halim@dba#> alter system set audit_sys_operations=true scope=both;
alter system set audit_sys_operations=true scope=both
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
Halim@dba#> alter system set audit_sys_operations=true scope=spfile;
System altered.
Halim@dba#>
Halim@dba#> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
Halim@dba#>
Halim@dba#> startup
ORACLE instance started.
Total System Global Area 591396864 bytes
Fixed Size 1250308 bytes
Variable Size 218106876 bytes
Database Buffers 364904448 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
Halim@dba#> alter system set audit_trail=false scope=spfile;
System altered.
Halim@dba#> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
Halim@dba#>
Halim@dba#>
Halim@dba#> startup
ORACLE instance started.
Total System Global Area 591396864 bytes
Fixed Size 1250308 bytes
Variable Size 218106876 bytes
Database Buffers 364904448 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
Halim@dba#>
Halim@dba#>
Halim@dba#> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string H:\ORACLE\PRODUCT\10.2.0\ADMIN
\HALIM\ADUMP
audit_sys_operations boolean TRUE
audit_trail string FALSE
Halim@dba#>
Halim@dba#> conn / as sysdba
Connected.
Halim@dba#>
Halim@dba#> grant dba to test;
Grant succeeded.
Halim@dba#>
Halim@dba#>
Halim@dba#> prompt 'See in windows Event Viewer/applecation'
'See in windows Event Viewer/applecation'
Halim@dba#>
Halim@dba#>
Halim@dba#>
Halim@dba#> spool off
No comments:
Post a Comment