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.
Halim, a Georgia Tech graduate Senior Database Engineer/Data Architect based in Atlanta, USA, is an Oracle OCP DBA and Developer, Certified Cloud Architect Professional, and OCI Autonomous Database Specialist. With extensive expertise in database design, configuration, tuning, capacity planning, RAC, DG, scripting, Python, APEX, and PL/SQL, he combines technical mastery with a passion for innovation. Notably, Halim secured 16th place worldwide in PL/SQL Challenge Cup Playoff on the year 2010.
Monday, August 16, 2010
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.............
=====================================================
=====================================================
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.............
Labels:
log Miner
Sunday, August 15, 2010
Audit in oracle Database 10g
Audit in oracle Database 10g
===================================
===========================================
Audit_trail =DB
=======================================
===================================
===========================================
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
Subscribe to:
Posts (Atom)
My Blog List
-
-
-
ASSM states3 weeks ago
-
UKOUG Discover 20241 month ago
-
-
-
-
-
-
-
-
Moving Sideways8 years ago
-
-
Upcoming Events...11 years ago
-