2
Challenge! A member of staff’s record has been deleted erroneously possibly maliciously and we wish to find the user who did it, what time they did it and also recover the data to before they did it so that we can compare before and after versions of the same data.
This is what the deleter did. They connected as another user.
CONN SCOTT/TIGER;
SELECT * FROM emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
DELETE FROM EMP WHERE EMPNO = 7499;
The DBA Security person notices something wrong when they issue this query.
SELECT * FROM emp; --an employee has disappeared from the emp table.
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
Given that the database has not been rebooted for a long time there is a good chance that the DBA is going to be able to collect evidence that will allow them to find what has happened and if there has been malicious activity.
The DBA Security person needs to know what audit is recorded.
SELECT NAME, value FROM v$parameter WHERE NAME LIKE 'audit%';
SQL> SELECT NAME, value FROM v$parameter WHERE NAME LIKE 'audit%';
NAME
----------------------------------------------------------------------
VALUE
----------------------------------------------------------------------
audit_sys_operations
FALSE
audit_file_dest
E:\ORACLE\PRODUCT\10.2.0\ADMIN\XP10R2JAN\ADUMP
audit_trail
DB
The audit is being done to the DB
SQL> desc dba_audit_trail;
Name Null? Type
----------------------------------------- -------- ---------------------------
OS_USERNAME VARCHAR2(255)
USERNAME VARCHAR2(30)
USERHOST VARCHAR2(128)
TERMINAL VARCHAR2(255)
TIMESTAMP NOT NULL DATE
OWNER VARCHAR2(30)
OBJ_NAME VARCHAR2(128)
ACTION NOT NULL NUMBER
ACTION_NAME VARCHAR2(27)
NEW_OWNER VARCHAR2(30)
NEW_NAME VARCHAR2(128)
OBJ_PRIVILEGE VARCHAR2(16)
SYS_PRIVILEGE VARCHAR2(40)
ADMIN_OPTION VARCHAR2(1)
GRANTEE VARCHAR2(30)
AUDIT_OPTION VARCHAR2(40)
SES_ACTIONS VARCHAR2(19)
LOGOFF_TIME DATE
LOGOFF_LREAD NUMBER
LOGOFF_PREAD NUMBER
LOGOFF_LWRITE NUMBER
LOGOFF_DLOCK VARCHAR2(40)
COMMENT_TEXT VARCHAR2(4000)
SESSIONID NOT NULL NUMBER
ENTRYID NOT NULL NUMBER
STATEMENTID NOT NULL NUMBER
RETURNCODE NOT NULL NUMBER
PRIV_USED VARCHAR2(40)
CLIENT_ID VARCHAR2(64)
SESSION_CPU NUMBER
The analyst has been reading this book so they know that a VIEW could be root kitted therefore more forensically sound to get the data from the underlying base table SYS.AUD$
SELECT userid, action#, STATEMENT, OBJ$NAME, To_Char (timestamp#, 'mm/dd/yyyy hh24:mi:ss') FROM sys.aud$ ORDER BY timestamp# asc;
Timeline from the database audit:
USERID ACTION# STATEMENT OBJ$NAME TIMESTAMP
SCOTT 101 1 04/30/2006 09:11:36
SCOTT 3 2 X$NLS_PARAMETERS 04/30/2006 09:29:07
SCOTT 3 2 GV$NLS_PARAMETERS 04/30/2006 09:29:07
SCOTT 3 2 V$NLS_PARAMETERS 04/30/2006 09:29:07
SCOTT 3 2 NLS_SESSION_PARAMETERS 04/30/2006 09:29:07
SCOTT 3 5 DUAL 04/30/2006 09:29:07
SCOTT 100 1 04/30/2006 09:29:41
SCOTT 3 22 OBJ$ 04/30/2006 09:31:07
SCOTT 3 22 USER_OBJECTS 04/30/2006 09:31:07
SCOTT 3 28 EMP 04/30/2006 09:32:01
SCOTT 3 31 EMP 04/30/2006 09:32:20
SCOTT 7 37 EMP 04/30/2006 09:33:28
SCOTT 3 46 EMP 04/30/2006 09:35:24
SCOTT 7 52 EMP 04/30/2006 09:37:04
SCOTT 7 55 EMP 04/30/2006 09:37:13
SCOTT 3 61 EMP 04/30/2006 09:37:28
Need to read the actions and statements manually.
SELECT * FROM AUDIT_ACTIONS;
Action 7 is a delete so we can see that SCOTT has deleted from emp at 9.37. So we want to flashback to before then so have to get the recorded timestamp. Oracle does not actually record a full timeline. Only takes the time every 5 minutes with the relevant SCN. Every 5 minutes new SCN added and old one taken away to give a maximum 5 day rolling figure to an accuracy of 5 minutes using timestamp.
SELECT To_Char(TIME_DP, 'dd/mm/yyyy hh24:mi:ss'), SCN_BAS FROM SYS.SMON_SCN_TIME;
30/04/2006 10:07:00 9637921
30/04/2006 10:01:53 9637140
30/04/2006 09:56:46 9636359
30/04/2006 09:51:39 9635645
30/04/2006 09:46:31 9634864
30/04/2006 09:41:24 9634083
30/04/2006 09:36:17 9633367
30/04/2006 09:31:10 9632579
30/04/2006 09:26:03 9631772
30/04/2006 09:20:55 9631059
30/04/2006 09:15:48 9630277
30/04/2006 09:10:41 9629478
30/04/2006 09:05:34 9628692
CREATE TABLE EMPRECOVER AS SELECT * FROM SCOTT.EMP AS OF TIMESTAMP (TO_TIMESTAMP('30/04/2006 09:31:10','DD-MM-YYYY:HH24:MI:SS'));
SELECT * FROM EMPRECOVER;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
So the DBA security person has found the deletion, time and user, and recovered the data. Good job! ? But not finished yet as simply assuming that SCOTT is the culprit is simplistic since SCOTT would have to be incredibly stupid to simply delete their adversaries row in the emp table. Perhaps a different user committed this malicious act pretending to be SCOTT in order to get them into trouble? Therefore the OS username and machine terminal columns of the audit trail are also queried below from SYS.AUD$.
SELECT userid, USERHOST, TERMINAL, SPARE1, action#, STATEMENT, OBJ$NAME, To_Char (timestamp#, 'mm/dd/yyyy hh24:mi:ss') FROM sys.aud$ ORDER BY timestamp# asc;
This additional data shows that SCOTT was coming from a different workstation from normal additionally the SPARE1 column shows that the Windows username was in fact GEORGE and not SCOTT.
The investigation passes to the Windows and network administrators in order to verify if that account was also being used fraudulently. This highlights the requirements for cross platform knowledge for security officers.
