Auditing Using LogMiner

Scenario: Let's suppose you want to investigate a recent case on a database whereas auditing is NOT enabled, or it's enabled but it didn't cover the activities you are looking for, in this case, you still have the ability to dig and find those activities using LogMiner feature.

LogMiner is a free feature (No license required) available in both Standard and Enterprise Editions, that can analyze the contents of archive log files and display (SYS, non-SYS, COMMITTED, NON-COMMITTED) transactions that happened during a specific period of time. It can also be used to recover a logical corruption of one or multiple transactions as it also contains the reverse (UNDO) statement of all transactions (similar to flashback transaction query).

I'll start with building the test scenario, a new user will be created and by that user, I'll execute some DDLs and DMLs along with wrong statements to figure out what are the statements that LogMiner can fetch for us.

-- Login as SYSDBA and Create a new user "AA" for testing: <let's see if this CREATE USER statement by SYSDBA will get captured>
06-Jul-2018 15:16:15 SYS@orcl> create user AA identified by "aa#12345";
06-Jul-2018 15:16:30 SYS@orcl> grant connect,resource to aa;

-- Connect to the new user:
06-Jul-2018 15:16:37 SYS@orcl> conn aa/aa#12345
Connected.

06-JUL-18 AA@orcl> create table a (a number);
Table created.

-- execute a wrong insert statement: <let's see if wrong statements are getting captured>
06-JUL-18 AA@orcl> insert into a('1');
insert into a('1')
              *
ERROR at line 1:
ORA-00928: missing SELECT keyword

-- execute a committed insert transaction:
06-JUL-18 AA@orcl> insert into a values (2);
1 row created.

06-JUL-18 AA@orcl> commit;
Commit complete.

-- execute a commited delete transaction:
06-JUL-18 AA@orcl> delete from a;
1 row deleted.

06-JUL-18 AA@orcl> commit;
Commit complete.

-- Execute a commited update tansaction that update nothing: 
06-JUL-18 AA@orcl> update a set a=5 where a=1;
0 rows updated.

-- Execute a truncate table statement:
06-JUL-18 AA@orcl> truncate table a;
Table truncated.

-- Create a procedure with compilation errors:
06-JUL-18 AA@orcl> create or replace procedure p as select * from a;
  2  /
Warning: Procedure created with compilation errors.

-- Execute a non-committed insert statement:
06-JUL-18 AA@orcl> insert into a values (3);
1 row created.

06-JUL-18 AA@orcl> rollback;
Rollback complete.

-- Now I'm done let's check the time at the end of this demo:
06-JUL-18 AA@orcl> !date
Fri Jul  6 15:34:51 GST 2018

And now let's use the LogMiner to reveal the transaction details that happened during that demo by user AA.

Step 1: Turn on the database level Supplemental Logging:

-- First check if the Supplemental Logging is already enabled, if supplemental_log_data_min shows "YES" this means it's ENABLED, then better do NOT run the last step of deactivating it:

SQL> SELECT supplemental_log_data_min FROM v$database;
SUPPLEME
--------
NO

-- supplemental_log_data_min shows "NO", so we have to enable the database level Supplemental Logging:
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Step 2: Provide the date & time range of the transactions you want to view, try to make the range as short as possible:

SQL> begin
dbms_logmnr.start_logmnr(
starttime => '06-Jul-2018 15:16:00',
endtime => '06-Jul-2018 15:36:00',
options => dbms_logmnr.dict_from_online_catalog + dbms_logmnr.continuous_mine + dbms_logmnr.print_pretty_sql + dbms_logmnr.committed_data_only
);
end;
/

Note: Specifying "dbms_logmnr.committed_data_only" option means to look for committed transactions only, if you really interested in fetching rolled back transaction, just remove "dbms_logmnr.committed_data_only" from the list of options.


Step 3: Query V$LOGMNR_CONTENTS to get the information you need:

SQL>    set linesize 170 pages 100
col TIMESTAMP for a20
col sql_redo for a40
col sql_undo for a40
col username for a20
col os_username for a20
col MACHINE_NAME for a20
select xid,to_char(timestamp,'DD-MON-YY HH24:MI:SS') TIMESTAMP,
username,OS_USERNAME,MACHINE_NAME,sql_redo,sql_undo,ROLLBACK from v$logmnr_contents
where SEG_OWNER='AA';

=> sql_redo: the statement it self.
=> sql_undo: the reverse statement that can be used to rollback the original statement.
-- You can also search by Segment Name "SEG_NAME='A'" or by operation "operation in ('INSERT','UPDATE','DELETE')"

XID TIMESTAMP       USERNAME  OS_USERNAME  MACHINE_NAME SQL_REDO
---------------- -------------------- --------- ------------ ------------ ----------------------------------------
SQL_UNDO
----------------------------------------
09001900E39D0000 06-JUL-18 15:16:30   UNKNOWN  UNKNOWN UNKNOWN create user aa identified by  VALUES 'S:
D4D17D1076EC7C6F5B1D68FBCB244E8789691A14
6F5209DF2A51AC579007;E3EE1448DE26764F' ;


08001E002E830000 06-JUL-18 15:16:57   UNKNOWN  UNKNOWN UNKNOWN create table a (a number);


0A002100A8510600 06-JUL-18 15:17:34   UNKNOWN  UNKNOWN UNKNOWN insert into "AA"."A"
  values
"A" = 2;
delete from "AA"."A"
 where
    "A" = 2 and
    ROWID = 'AAAjrvAAEAAAACvAAA';

09000600E09D0000 06-JUL-18 15:17:43   UNKNOWN  UNKNOWN UNKNOWN delete from "AA"."A"
  where
"A" = 2 and
ROWID = 'AAAjrvAAEAAAACvAAA';
insert into "AA"."A"
 values
    "A" = 2;

05000D00AC800000 06-JUL-18 15:18:21   UNKNOWN  UNKNOWN UNKNOWN truncate table a;


0A0007004D510600 06-JUL-18 15:18:45   UNKNOWN  UNKNOWN UNKNOWN create or replace procedure p as select
* from a;;
6 rows selected.

As you can see, LogMiner fetches all kind of statements that made changes to the database including DML & DDLs along with the statements executed with SYSDBA privilege.
Any other statements that don't make any change to the database like SELECT and failed SQL statements along with the DML statements that didn't make any actual change to the data will not be shown by the LogMiner.

Once you are done:

- End the Log Miner Session and flush the contents of v$logmnr_contents:
SQL> execute dbms_logmnr.end_logmnr();
SQL> select * from v$logmnr_contents;

No Rows should be returned.

-Deactivate the database level Supplemental Logging:
In case the SUPPLEMENTAL LOG DATA was already enabled in step 1, then don't execute this step, replication software like Oracle Streams & golden gate depends on the SUPPLEMENTAL LOG DATA in order to perform the replication.

SQL> ALTER DATABASE drop SUPPLEMENTAL LOG DATA;

SQL> SELECT supplemental_log_data_min,supplemental_log_data_pk,supplemental_log_data_ui,supplemental_log_data_fk,supplemental_log_data_all FROM v$database;

LogMiner cannot work as an alternative to Auditing, LogMiner can only fetch the changes that happened to the database, unlike Auditing that can capture details like SELECT statements and login details, such information is not available in the Archivelogs to be mined.

Moreover, LogMiner can analyze only the available archive logs, it cannot go far in the past where the archive logs are not available, unless you restore the archivelogs from a backup media and feed them into the LogMiner.

To sum up. LogMiner is mainly used by the DBAs to recover logical data corruption, but we can use it as a forensic tool.

References:
https://oracle-base.com/articles/8i/logminer
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:433619656232