What is SQL injection ? How is Oracle application subject to SQL injection attacks ?

What is SQL injection ? How is Oracle application subject to SQL injection attacks ?
==============================================================================

[This is just for creating awareness about SQL Injection attacks]

SQL injection is a basic attack used to either gain unauthorized access to a database or to retrieve
information directly from the database.SQL injection attacks are simple in nature – an attacker passes
string input to an application in hopes manipulating the SQL statement to his or her advantage.

Oracle may provide stronger and more inherent protections against SQL injection attacks than other
database, however applications without proper defenses against these types of attacks can be vulnerable.
Despite these advantages many web applications are vulnerable to SQL injection attacks.

Oracle has generally faired well against SQL injection attacks as there is no multiple SQL statement
support (SQL Server and PostgreSQL), no EXECUTE statement (SQL Server), and no INTO OUTFILE
function (MySQL).
Also, use of bind variables in Oracle environments for performance reasons provides
strong protection against SQL injection attacks.

Generally Four Types of SQL Injection Attack. these are :-

1. SQL Manipulation
2. Code Injection
3. Function Call Injection
4. Buffer Overflows

All of these types of SQL injection are valid for databases including SQL Server, DB2, MySQL, PostgreSQL and Oracle.

Among these SQL Manipulation is more important. and vulnerable. i just want to describe it Here.

1. SQL Manipulation:
======================
SQL Injection attack is SQL manipulation. The attacker attempts to modify the existing SQL statement
by adding elements to the WHERE clause or extending the SQL statement with set operators
like UNION, INTERSECT, or MINUS.

In this example in first query I add in where clause just "OR 'A'='A' " then what happens.

In second query i add union clause just
" UNION
SELECT USERNAME FROM USER_MAS
WHERE USERNAME LIKE '%' "


Example:-

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 16 15:09:49 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn halim/halim@test107
Connected.
SQL>
SQL>
SQL> create table user_mas (username varchar2(20),password varchar2(20));

Table created.

SQL> insert into user_mas values ('HALIM','TEST');

1 row created.

SQL> insert into user_mas values ('SCOTT','TEST1');

1 row created.

SQL> insert into user_mas values ('ADMIN','TEST2');

1 row created.

SQL> COMMIT;

Commit complete.

SQL>
SQL> SELECT * FROM USER_MAS;

USERNAME PASSWORD
-------------------- --------------------
HALIM TEST
SCOTT TEST1
ADMIN TEST2

SQL>
SQL>
SQL> SELECT USERNAME FROM USER_MAS
2 WHERE USERNAME='HALIM' AND PASSWORD='TEST';

USERNAME
--------------------
HALIM

SQL>
SQL> ED
Wrote file afiedt.buf

1 SELECT USERNAME FROM USER_MAS
2* WHERE USERNAME='HALIM' AND PASSWORD='TEST' OR 'A'='A'
SQL> /

USERNAME
--------------------
HALIM
SCOTT
ADMIN

SQL>
SQL>
SQL> ED
Wrote file afiedt.buf

1 SELECT USERNAME FROM USER_MAS
2* WHERE USERNAME='HALIM' AND PASSWORD='TEST'
SQL> /

USERNAME
--------------------
HALIM

SQL>
SQL> ED
Wrote file afiedt.buf

1 SELECT USERNAME FROM USER_MAS
2 WHERE USERNAME='HALIM' AND PASSWORD='TEST'
3 UNION
4 SELECT USERNAME FROM USER_MAS
5* WHERE USERNAME LIKE '%'
6 /

USERNAME
--------------------
ADMIN
HALIM
SCOTT

SQL>
SQL>
SQL>
SQL>



PLSQL Codes subject to SQL injection attacks
===================================

SQL statements can be executed four different ways in PL/SQL –
a) embedded SQL,
b) cursors,
c) execute immediate statements,
d) the DBMS_SQL package.

Embedded SQL statements and static cursors only allow bind variables, this is not subject to SQL injection attacks.
But following are subject to SQL injection attacks.

B) dynamic cursors.(ref cursor) [To prevent SQL INJECTION attack bind variables should always be used]

CREATE OR REPLACE PROCEDURE pro_sample(P_empno IN VARCHAR2)
AS
sql_s VARCHAR2;
BEGIN
sql_s := 'SELECT * FROM emp WHERE empno = ''' || p_empno || '''';
OPEN cursor_states FOR sql_s;
LOOP FETCH cursor_states
INTO rec_state
EXIT WHEN cursor_states%NOTFOUND;
END LOOP;
CLOSE cursor_status;
END;

This can subject to attack by Sql injection

C) EXECUTE IMMEDIATE [To prevent SQL INJECTION attack bind variables should always be used]

D) DBMS_SQL package (for dynamic SQL statements)
[To prevent SQL INJECTION attack bind variables should always be used]



To prevent SQL injection and to improve application performance, bind variables should always be used.

Using LogMiner to Analyze Redo Log Files or archivelog files in oracle 10g

Using LogMiner to Analyze Redo Log Files or archivelog files in oracle 10g
=====================================================
=====================================================


Enables you to query online redolog files and archived log files through a SQL interface.
Redo log and archived log files contain information about the history of activity on a database.
such as

1. you can find what errors made at the application level. These might include
errors such as those where the wrong rows were deleted because of incorrect
values in a WHERE clause, rows were updated with incorrect values, the wrong
index was dropped, and so forth.

2. Using LogMiner to Track Changes Made by a Specific User.

3. you can use it as a powerful audit tool.

4. You can determine which tables get the most updates and inserts.
5. Performance tuning and capacity planning through trend analysis.
6. Determining what actions you would have to take to perform fine-grained recovery at the transaction level
7. LogMiner can be used to track any data manipulation language (DML)
and data definition language (DDL) statements executed on the Database



Extract a LogMiner Dictionary

To use LogMiner, you must supply it with a dictionary by doing one of the following:

1. Specify use of the online catalog by using the DICT_FROM_ONLINE_CATALOG option when you start Log Miner.
(This is very convenient. i use it in the following )

2. Extract database dictionary information to the redo log files.
(When table structure change)

3. Extract database dictionary information to a flat file.



Steps are :=



1. Enable SUPPLEMENTAL Log to Database.
=======================================

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;


2. Install logminer package (if not installed by default installed) from following path
============================================
ORACLE_HOME/rdbms/admin/dbmslm.sql



3. Determine which redo log file was most recently archived.
===============================================

SQL> SELECT NAME FROM V$ARCHIVED_LOG
WHERE FIRST_TIME = (SELECT MAX(FIRST_TIME) FROM V$ARCHIVED_LOG);

------H:\FLASH_BACK\STLBAS\ARCHIVELOG\2010_08_16\O1_MF_1_2556_66KP9J6T_.ARC

4. Specify the list of redo log files to be analyzed.
=========================================

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
LOGFILENAME => 'H:\FLASH_BACK\STLBAS\ARCHIVELOG\2010_08_16\O1_MF_1_2556_66KP9J6T_.ARC', -
OPTIONS => DBMS_LOGMNR.NEW);

5. Start LogMiner.
========================

Start LogMiner and specify the dictionary to use.

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR( -
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

OR
Start LogMiner by specifying the dictionary to use and the COMMITTED_DATA_ONLY option.

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR( -
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
DBMS_LOGMNR.COMMITTED_DATA_ONLY);

OR

Start LogMiner by specifying the dictionary to use and the COMMITTED_DATA_ONLY and PRINT_PRETTY_SQL options.

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(-
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
DBMS_LOGMNR.COMMITTED_DATA_ONLY + -
DBMS_LOGMNR.PRINT_PRETTY_SQL);


6. Query the V$LOGMNR_CONTENTS view.
==================================


SQL> SELECT username AS USR, (XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS XID,
SQL_REDO, SQL_UNDO FROM V$LOGMNR_CONTENTS WHERE username IN ('HALIM');

7. End the LogMiner session.
==============================

SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR();






Example:=

The easiest way to examine the modification history of a database is to mine at the source database and use the
online catalog to translate the redo log files. This example shows how to do the simplest analysis using LogMiner.

This example finds all modifications that are contained in the last archived redo log generated by the database
(assuming that the database is not an Oracle Real Application Clusters database)

=====================================
======================================


SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 16 12:11:54 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn sys@test107 as sysdba
Enter password:
Connected.
SQL>
SQL>
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Database altered.

SQL>
SQL>
SQL>
SQL>
SQL> grant dba to halim identified by halim ;

Grant succeeded.

SQL>
SQL> conn halim/halim@test107
Connected.
SQL>
SQL>
SQL> create table halim_table (id number,name varchar2(20));

Table created.

SQL>
SQL> insert into halim_table select rownum,rownum||'halim' from all_objects where rownum<=10;

10 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> delete from halim_table;

10 rows deleted.

SQL> rollback;

Rollback complete.

SQL> update halim_table
2 set name='change_halim' ;

10 rows updated.

SQL> commit;

Commit complete.

SQL>
SQL>
SQL> alter system switch logfile;

System altered.

SQL>
SQL>
SQL>
SQL> SELECT NAME FROM V$ARCHIVED_LOG
2 WHERE FIRST_TIME = (SELECT MAX(FIRST_TIME) FROM V$ARCHIVED_LOG);

NAME
--------------------------------------------------------------------------------
H:\FLASH_BACK\STLBAS\ARCHIVELOG\2010_08_16\O1_MF_1_2556_66KP9J6T_.ARC

SQL>
SQL>
SQL>
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
> LOGFILENAME => 'H:\FLASH_BACK\STLBAS\ARCHIVELOG\2010_08_16\O1_MF_1_2556_66KP9J6T_.ARC', -
> OPTIONS => DBMS_LOGMNR.NEW);

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR( -
> OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL>
SQL> set linesize 1000
SQL> set pagesize 1000
SQL>
SQL>
SQL>
SQL>
===================================================================
===================================================================
Now you find all activity of above.
You can find here Original sql as well as Undo sql, you can undo the change by undo sql
===================================================================
===================================================================

SQL>
SQL> SELECT username AS USR, (XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS XID,
2 SQL_REDO, SQL_UNDO FROM V$LOGMNR_CONTENTS WHERE username IN ('HALIM');

USR XID
------------------------------ --------------------------------------------------------------------------------------------------------------------------
SQL_REDO
-----------------------------------------------------------------------
SQL_UNDO
-----------------------------------------------------------------------------------------------------------


HALIM 4.26.6403
create table halim_table (id number,name varchar2(20));


HALIM 7.21.8139
set transaction read write;


HALIM 7.21.8139
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('1','1halim');
delete from "HALIM"."HALIM_TABLE" where "ID" = '1' and "NAME" = '1halim' and ROWID = 'AAATjnAAEAACJW0AAA';

HALIM 7.21.8139
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('2','2halim');
delete from "HALIM"."HALIM_TABLE" where "ID" = '2' and "NAME" = '2halim' and ROWID = 'AAATjnAAEAACJW0AAB';

HALIM 7.21.8139
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('3','3halim');
delete from "HALIM"."HALIM_TABLE" where "ID" = '3' and "NAME" = '3halim' and ROWID = 'AAATjnAAEAACJW0AAC';

HALIM 7.21.8139
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('4','4halim');
delete from "HALIM"."HALIM_TABLE" where "ID" = '4' and "NAME" = '4halim' and ROWID = 'AAATjnAAEAACJW0AAD';

HALIM 7.21.8139
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('5','5halim');
delete from "HALIM"."HALIM_TABLE" where "ID" = '5' and "NAME" = '5halim' and ROWID = 'AAATjnAAEAACJW0AAE';

HALIM 7.21.8139
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('6','6halim');
delete from "HALIM"."HALIM_TABLE" where "ID" = '6' and "NAME" = '6halim' and ROWID = 'AAATjnAAEAACJW0AAF';

HALIM 7.21.8139
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('7','7halim');
delete from "HALIM"."HALIM_TABLE" where "ID" = '7' and "NAME" = '7halim' and ROWID = 'AAATjnAAEAACJW0AAG';

HALIM 7.21.8139
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('8','8halim');
delete from "HALIM"."HALIM_TABLE" where "ID" = '8' and "NAME" = '8halim' and ROWID = 'AAATjnAAEAACJW0AAH';

HALIM 7.21.8139
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('9','9halim');
delete from "HALIM"."HALIM_TABLE" where "ID" = '9' and "NAME" = '9halim' and ROWID = 'AAATjnAAEAACJW0AAI';

HALIM 7.21.8139
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('10','10halim');
delete from "HALIM"."HALIM_TABLE" where "ID" = '10' and "NAME" = '10halim' and ROWID = 'AAATjnAAEAACJW0AAJ';

HALIM 7.21.8139
commit;


HALIM 2.34.7758
set transaction read write;


HALIM 2.34.7758
delete from "HALIM"."HALIM_TABLE" where "ID" = '1' and "NAME" = '1halim' and ROWID = 'AAATjnAAEAACJW0AAA';
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('1','1halim');

HALIM 2.34.7758
delete from "HALIM"."HALIM_TABLE" where "ID" = '2' and "NAME" = '2halim' and ROWID = 'AAATjnAAEAACJW0AAB';
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('2','2halim');

HALIM 2.34.7758
delete from "HALIM"."HALIM_TABLE" where "ID" = '3' and "NAME" = '3halim' and ROWID = 'AAATjnAAEAACJW0AAC';
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('3','3halim');

HALIM 2.34.7758
delete from "HALIM"."HALIM_TABLE" where "ID" = '4' and "NAME" = '4halim' and ROWID = 'AAATjnAAEAACJW0AAD';
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('4','4halim');

HALIM 2.34.7758
delete from "HALIM"."HALIM_TABLE" where "ID" = '5' and "NAME" = '5halim' and ROWID = 'AAATjnAAEAACJW0AAE';
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('5','5halim');

HALIM 2.34.7758
delete from "HALIM"."HALIM_TABLE" where "ID" = '6' and "NAME" = '6halim' and ROWID = 'AAATjnAAEAACJW0AAF';
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('6','6halim');

HALIM 2.34.7758
delete from "HALIM"."HALIM_TABLE" where "ID" = '7' and "NAME" = '7halim' and ROWID = 'AAATjnAAEAACJW0AAG';
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('7','7halim');

HALIM 2.34.7758
delete from "HALIM"."HALIM_TABLE" where "ID" = '8' and "NAME" = '8halim' and ROWID = 'AAATjnAAEAACJW0AAH';
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('8','8halim');

HALIM 2.34.7758
delete from "HALIM"."HALIM_TABLE" where "ID" = '9' and "NAME" = '9halim' and ROWID = 'AAATjnAAEAACJW0AAI';
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('9','9halim');

HALIM 2.34.7758
delete from "HALIM"."HALIM_TABLE" where "ID" = '10' and "NAME" = '10halim' and ROWID = 'AAATjnAAEAACJW0AAJ';
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('10','10halim');

HALIM 2.34.7758
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('10','10halim');


HALIM 2.34.7758
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('9','9halim');


HALIM 2.34.7758
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('8','8halim');


HALIM 2.34.7758
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('7','7halim');


HALIM 2.34.7758
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('6','6halim');


HALIM 2.34.7758
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('5','5halim');


HALIM 2.34.7758
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('4','4halim');


HALIM 2.34.7758
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('3','3halim');


HALIM 2.34.7758
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('2','2halim');


HALIM 2.34.7758
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('1','1halim');


HALIM 2.34.7758
rollback;


HALIM 10.20.11872
set transaction read write;


HALIM 10.20.11872
update "HALIM"."HALIM_TABLE" set "NAME" = 'change_halim' where "NAME" = '1halim' and ROWID = 'AAATjnAAEAACJW0AAA';
update "HALIM"."HALIM_TABLE" set "NAME" = '1halim' where "NAME" = 'change_halim' and ROWID = 'AAATjnAAEAACJW0AAA';

HALIM 10.20.11872
update "HALIM"."HALIM_TABLE" set "NAME" = 'change_halim' where "NAME" = '2halim' and ROWID = 'AAATjnAAEAACJW0AAB';
update "HALIM"."HALIM_TABLE" set "NAME" = '2halim' where "NAME" = 'change_halim' and ROWID = 'AAATjnAAEAACJW0AAB';

HALIM 10.20.11872
update "HALIM"."HALIM_TABLE" set "NAME" = 'change_halim' where "NAME" = '3halim' and ROWID = 'AAATjnAAEAACJW0AAC';
update "HALIM"."HALIM_TABLE" set "NAME" = '3halim' where "NAME" = 'change_halim' and ROWID = 'AAATjnAAEAACJW0AAC';

HALIM 10.20.11872
update "HALIM"."HALIM_TABLE" set "NAME" = 'change_halim' where "NAME" = '4halim' and ROWID = 'AAATjnAAEAACJW0AAD';
update "HALIM"."HALIM_TABLE" set "NAME" = '4halim' where "NAME" = 'change_halim' and ROWID = 'AAATjnAAEAACJW0AAD';

HALIM 10.20.11872
update "HALIM"."HALIM_TABLE" set "NAME" = 'change_halim' where "NAME" = '5halim' and ROWID = 'AAATjnAAEAACJW0AAE';
update "HALIM"."HALIM_TABLE" set "NAME" = '5halim' where "NAME" = 'change_halim' and ROWID = 'AAATjnAAEAACJW0AAE';

HALIM 10.20.11872
update "HALIM"."HALIM_TABLE" set "NAME" = 'change_halim' where "NAME" = '6halim' and ROWID = 'AAATjnAAEAACJW0AAF';
update "HALIM"."HALIM_TABLE" set "NAME" = '6halim' where "NAME" = 'change_halim' and ROWID = 'AAATjnAAEAACJW0AAF';

HALIM 10.20.11872
update "HALIM"."HALIM_TABLE" set "NAME" = 'change_halim' where "NAME" = '7halim' and ROWID = 'AAATjnAAEAACJW0AAG';
update "HALIM"."HALIM_TABLE" set "NAME" = '7halim' where "NAME" = 'change_halim' and ROWID = 'AAATjnAAEAACJW0AAG';

HALIM 10.20.11872
update "HALIM"."HALIM_TABLE" set "NAME" = 'change_halim' where "NAME" = '8halim' and ROWID = 'AAATjnAAEAACJW0AAH';
update "HALIM"."HALIM_TABLE" set "NAME" = '8halim' where "NAME" = 'change_halim' and ROWID = 'AAATjnAAEAACJW0AAH';

HALIM 10.20.11872
update "HALIM"."HALIM_TABLE" set "NAME" = 'change_halim' where "NAME" = '9halim' and ROWID = 'AAATjnAAEAACJW0AAI';
update "HALIM"."HALIM_TABLE" set "NAME" = '9halim' where "NAME" = 'change_halim' and ROWID = 'AAATjnAAEAACJW0AAI';

HALIM 10.20.11872
update "HALIM"."HALIM_TABLE" set "NAME" = 'change_halim' where "NAME" = '10halim' and ROWID = 'AAATjnAAEAACJW0AAJ';
update "HALIM"."HALIM_TABLE" set "NAME" = '10halim' where "NAME" = 'change_halim' and ROWID = 'AAATjnAAEAACJW0AAJ';

HALIM 10.20.11872
commit;



70 rows selected.

SQL>
SQL>
SQL>
SQL>
SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR();

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL>

Cheers.............

Audit in oracle Database 10g

Audit in oracle Database 10g
===================================
===========================================
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