The idea of missing ID's can be useful to look for missing data or when data might have been removed. We will use the idea of a user for this analysis but it could be other data as well not just users. There are a few scenarios for our example of a user being manipulated and how we may detect this when there is no auditing around this:
- Hacker adds a user and its still there : We can check dates and see if the user is created after start of the breach so could be the work of the attacker
- Hacker adds a user and removed it : There will be no create date for the user as it has been removed. There is no GAP in IDs as it was the last user created. We can check what the next user ID is and if there is still a gap based on this number we can assume a user was added and removed but we don't know when
- The hacker uses an existing user : Again the create date is not useful as the hacker didn't create the user, it existed already. BUT, it could be locked or expired or password changed during the attack phase.
- The hacker uses an existing user and removed it : The dates above are not there as the user was removed
We could use block analysis or redo logs or archive logs to confirm our assumptions. Because manipulation of users is recorded in the redo/archive logs then we can see this and get exact dates/times. If all else fails we could open the system data file in a hex editor and locate the records for user changes and removed users and see the dates at least for create, lock, expire or password changes but no transaction dates.
Lets test adding a user to the database and see the dates afterwards. First look at the existing user records:
SQL> col user_id for 9999999999999
SQL> col username for a30
SQL> col lock_date for a20
SQL> col expiry_date for a20
SQL> col password_change_date for a20
SQL> set lines 220
SQL> select user_id,username,to_char(created,'DD-MON-YYYY:HH24:MI:SS'),to_char(lock_date,'DD-MON-YYYY:HH24:MI:SS'),to_char(expiry_date,'DD-MON-YYYY:HH24:MI:SS'),to_char(password_change_date,'DD-MON-YYYY:HH24:MI:SS') from dba_users order by created;
USER_ID USERNAME TO_CHAR(CREATED,'DD-MON-YYYY: TO_CHAR(LOCK_DATE,'DD-MON-YYY TO_CHAR(EXPIRY_DATE,'DD-MON-Y TO_CHAR(PASSWORD_CHANGE_DATE,
-------------- ------------------------------ ----------------------------- ----------------------------- ----------------------------- -----------------------------
0 SYS 26-MAR-2023:02:26:54
8 AUDSYS 26-MAR-2023:02:26:55 03-APR-2023:16:50:15
9 SYSTEM 26-MAR-2023:02:26:56
2147483620 SYSRAC 26-MAR-2023:02:26:56
2147483617 SYSBACKUP 26-MAR-2023:02:26:56 03-APR-2023:16:50:15
2147483619 SYSKM 26-MAR-2023:02:26:56 03-APR-2023:16:50:15
2147483618 SYSDG 26-MAR-2023:02:26:56 03-APR-2023:16:50:15
13 OUTLN 26-MAR-2023:02:27:05 03-APR-2023:16:50:15
25 GSMUSER 26-MAR-2023:03:11:57 03-APR-2023:16:50:15
24 GSMADMIN_INTERNAL 26-MAR-2023:03:11:57 03-APR-2023:16:50:15
26 DIP 26-MAR-2023:03:12:06 26-MAR-2023:03:12:06
USER_ID USERNAME TO_CHAR(CREATED,'DD-MON-YYYY: TO_CHAR(LOCK_DATE,'DD-MON-YYY TO_CHAR(EXPIRY_DATE,'DD-MON-Y TO_CHAR(PASSWORD_CHANGE_DATE,
-------------- ------------------------------ ----------------------------- ----------------------------- ----------------------------- -----------------------------
2147483638 XS$NULL 26-MAR-2023:03:12:31 26-MAR-2023:03:12:31
40 REMOTE_SCHEDULER_AGENT 26-MAR-2023:03:12:38 03-APR-2023:16:50:15
41 DBSFWUSER 26-MAR-2023:03:12:41 03-APR-2023:16:50:15
53 GGSHAREDCAP 26-MAR-2023:03:15:43 03-APR-2023:16:50:15
55 SYS$UMF 26-MAR-2023:03:27:03 03-APR-2023:16:50:15
62 DGPDB_INT 26-MAR-2023:03:29:06 03-APR-2023:16:50:15
75 DBSNMP 26-MAR-2023:03:33:52 03-APR-2023:16:50:15
76 APPQOSSYS 26-MAR-2023:03:33:52 03-APR-2023:16:50:15
82 GSMCATUSER 26-MAR-2023:03:33:56 03-APR-2023:16:50:15
83 GGSYS 26-MAR-2023:03:34:04 03-APR-2023:16:50:15
86 ANONYMOUS 26-MAR-2023:03:35:41 03-APR-2023:16:50:15
USER_ID USERNAME TO_CHAR(CREATED,'DD-MON-YYYY: TO_CHAR(LOCK_DATE,'DD-MON-YYY TO_CHAR(EXPIRY_DATE,'DD-MON-Y TO_CHAR(PASSWORD_CHANGE_DATE,
-------------- ------------------------------ ----------------------------- ----------------------------- ----------------------------- -----------------------------
85 XDB 26-MAR-2023:03:35:41 03-APR-2023:16:50:15
95 WMSYS 26-MAR-2023:03:48:27 03-APR-2023:16:50:15
104 OJVMSYS 26-MAR-2023:03:50:24 07-APR-2023:14:53:16
106 CTXSYS 26-MAR-2023:03:53:39 03-APR-2023:16:50:15
109 OLAPSYS 26-MAR-2023:03:55:37 03-APR-2023:16:50:15
112 MDSYS 26-MAR-2023:04:00:40 03-APR-2023:16:50:15
113 MDDATA 26-MAR-2023:04:00:40 03-APR-2023:16:50:15
127 LBACSYS 26-MAR-2023:04:05:33 03-APR-2023:16:50:15
1279990 DVSYS 26-MAR-2023:04:06:30 03-APR-2023:16:50:15
129 DVF 26-MAR-2023:04:06:30 03-APR-2023:16:50:15
130 PDBADMIN 03-APR-2023:16:50:15 03-APR-2023:16:50:15
USER_ID USERNAME TO_CHAR(CREATED,'DD-MON-YYYY: TO_CHAR(LOCK_DATE,'DD-MON-YYY TO_CHAR(EXPIRY_DATE,'DD-MON-Y TO_CHAR(PASSWORD_CHANGE_DATE,
-------------- ------------------------------ ----------------------------- ----------------------------- ----------------------------- -----------------------------
133 APEX_PUBLIC_USER 03-APR-2023:16:53:17 03-APR-2023:17:00:25
131 APEX_220200 03-APR-2023:16:53:17 03-APR-2023:16:53:17
132 FLOWS_FILES 03-APR-2023:16:53:17 03-APR-2023:16:53:17
137 APEX_LISTENER 03-APR-2023:17:00:24 03-APR-2023:17:00:24
138 APEX_REST_PUBLIC_USER 03-APR-2023:17:00:24 03-APR-2023:17:00:24
139 ORDS_PUBLIC_USER 03-APR-2023:17:00:39 03-APR-2023:17:00:39
140 ORDS_METADATA 03-APR-2023:17:00:40
143 HRREST 03-APR-2023:17:01:12 03-APR-2023:17:01:12
144 HR 03-APR-2023:17:01:14 03-APR-2023:17:01:14
145 OE 03-APR-2023:17:01:19 03-APR-2023:17:01:19
146 PM 03-APR-2023:17:01:56 03-APR-2023:17:01:56
USER_ID USERNAME TO_CHAR(CREATED,'DD-MON-YYYY: TO_CHAR(LOCK_DATE,'DD-MON-YYY TO_CHAR(EXPIRY_DATE,'DD-MON-Y TO_CHAR(PASSWORD_CHANGE_DATE,
-------------- ------------------------------ ----------------------------- ----------------------------- ----------------------------- -----------------------------
147 IX 03-APR-2023:17:02:02 03-APR-2023:17:02:02
148 SH 03-APR-2023:17:02:09 03-APR-2023:17:02:09
149 BI 03-APR-2023:17:02:48 03-APR-2023:17:02:48
150 AV 03-APR-2023:17:02:53 03-APR-2023:17:02:53
151 PFCLSCAN 05-APR-2023:13:05:50 21-APR-2023:12:52:44
152 VA 06-APR-2023:15:20:00 26-MAY-2023:11:46:46
153 VB 06-APR-2023:15:21:44 06-APR-2023:15:21:44
154 VC 06-APR-2023:15:26:36 06-APR-2023:15:26:36
157 VF 10-APR-2023:11:11:31 10-APR-2023:11:11:31
158 VG 10-APR-2023:12:06:57 10-APR-2023:12:06:57
159 VE 14-APR-2023:13:21:53 14-APR-2023:13:21:53
USER_ID USERNAME TO_CHAR(CREATED,'DD-MON-YYYY: TO_CHAR(LOCK_DATE,'DD-MON-YYY TO_CHAR(EXPIRY_DATE,'DD-MON-Y TO_CHAR(PASSWORD_CHANGE_DATE,
-------------- ------------------------------ ----------------------------- ----------------------------- ----------------------------- -----------------------------
160 VJ 02-MAY-2023:12:51:56 02-MAY-2023:12:51:56
165 VV 26-JUN-2023:08:52:17 26-JUN-2023:08:52:17
166 VX 26-JUN-2023:09:55:57 26-JUN-2023:09:55:57
167 SCHEMA1 26-JUN-2023:14:18:49 26-JUN-2023:14:18:49
168 SCHEMA2 26-JUN-2023:14:19:32 26-JUN-2023:14:19:32
169 CON1 26-JUN-2023:14:27:16 26-JUN-2023:14:27:16
170 CON2 26-JUN-2023:14:28:23 26-JUN-2023:14:28:23
171 TESTTEST 14-JUL-2023:08:56:43 14-JUL-2023:08:56:43
172 TT1 09-OCT-2023:09:15:27 09-OCT-2023:09:15:27
173 UU1 09-OCT-2023:09:16:49 09-OCT-2023:09:16:49
174 TEST33 03-NOV-2023:14:30:26 03-NOV-2023:14:30:26
USER_ID USERNAME TO_CHAR(CREATED,'DD-MON-YYYY: TO_CHAR(LOCK_DATE,'DD-MON-YYY TO_CHAR(EXPIRY_DATE,'DD-MON-Y TO_CHAR(PASSWORD_CHANGE_DATE,
-------------- ------------------------------ ----------------------------- ----------------------------- ----------------------------- -----------------------------
187 ORABLOG 16-NOV-2023:15:09:30 16-NOV-2023:15:09:30
188 VM 16-NOV-2023:15:09:53 16-NOV-2023:15:09:53
189 SQL_F 16-NOV-2023:15:10:39 16-NOV-2023:15:10:39
190 VQ 16-NOV-2023:15:29:46 16-NOV-2023:15:29:46
191 SCANNER 29-NOV-2023:12:43:36 29-NOV-2023:12:43:36
192 PROXY 29-NOV-2023:14:21:46 29-NOV-2023:14:21:46
72 rows selected.
SQL>
As we can see already there are issues that make analysis hard. The first is that the list is ordered by created date and not ID we whilst user IDs for user created users and most others are 3 digits there are some users that have large USER_IDS such as 2147483620 for SYSRAC and others in a different sequence such as 1279990 for DVSYS.
Also by using CEATED as the order by some IDs are out of order such as 85 and 86 which have the same date. If we change the ORDER BY to include the USER_ID:
SQL> select user_id,username,to_char(created,'DD-MON-YYYY:HH24:MI:SS'),to_char(lock_date,'DD-MON-YYYY:HH24:MI:SS'),to_char(expiry_date,'DD-MON-YYYY:HH24:MI:SS'),to_char(password_change_date,'DD-MON-YYYY:HH24:MI:SS') from dba_users order by created,user_id;
USER_ID USERNAME TO_CHAR(CREATED,'DD-MON-YYYY: TO_CHAR(LOCK_DATE,'DD-MON-YYY TO_CHAR(EXPIRY_DATE,'DD-MON-Y TO_CHAR(PASSWORD_CHANGE_DATE,
-------------- ------------------------------ ----------------------------- ----------------------------- ----------------------------- -----------------------------
0 SYS 26-MAR-2023:02:26:54
8 AUDSYS 26-MAR-2023:02:26:55 03-APR-2023:16:50:15
9 SYSTEM 26-MAR-2023:02:26:56
2147483617 SYSBACKUP 26-MAR-2023:02:26:56 03-APR-2023:16:50:15
2147483618 SYSDG 26-MAR-2023:02:26:56 03-APR-2023:16:50:15
2147483619 SYSKM 26-MAR-2023:02:26:56 03-APR-2023:16:50:15
2147483620 SYSRAC 26-MAR-2023:02:26:56
13 OUTLN 26-MAR-2023:02:27:05 03-APR-2023:16:50:15
24 GSMADMIN_INTERNAL 26-MAR-2023:03:11:57 03-APR-2023:16:50:15
25 GSMUSER 26-MAR-2023:03:11:57 03-APR-2023:16:50:15
26 DIP 26-MAR-2023:03:12:06 26-MAR-2023:03:12:06
USER_ID USERNAME TO_CHAR(CREATED,'DD-MON-YYYY: TO_CHAR(LOCK_DATE,'DD-MON-YYY TO_CHAR(EXPIRY_DATE,'DD-MON-Y TO_CHAR(PASSWORD_CHANGE_DATE,
-------------- ------------------------------ ----------------------------- ----------------------------- ----------------------------- -----------------------------
2147483638 XS$NULL 26-MAR-2023:03:12:31 26-MAR-2023:03:12:31
40 REMOTE_SCHEDULER_AGENT 26-MAR-2023:03:12:38 03-APR-2023:16:50:15
41 DBSFWUSER 26-MAR-2023:03:12:41 03-APR-2023:16:50:15
53 GGSHAREDCAP 26-MAR-2023:03:15:43 03-APR-2023:16:50:15
55 SYS$UMF 26-MAR-2023:03:27:03 03-APR-2023:16:50:15
62 DGPDB_INT 26-MAR-2023:03:29:06 03-APR-2023:16:50:15
75 DBSNMP 26-MAR-2023:03:33:52 03-APR-2023:16:50:15
76 APPQOSSYS 26-MAR-2023:03:33:52 03-APR-2023:16:50:15
82 GSMCATUSER 26-MAR-2023:03:33:56 03-APR-2023:16:50:15
83 GGSYS 26-MAR-2023:03:34:04 03-APR-2023:16:50:15
85 XDB 26-MAR-2023:03:35:41 03-APR-2023:16:50:15
USER_ID USERNAME TO_CHAR(CREATED,'DD-MON-YYYY: TO_CHAR(LOCK_DATE,'DD-MON-YYY TO_CHAR(EXPIRY_DATE,'DD-MON-Y TO_CHAR(PASSWORD_CHANGE_DATE,
-------------- ------------------------------ ----------------------------- ----------------------------- ----------------------------- -----------------------------
86 ANONYMOUS 26-MAR-2023:03:35:41 03-APR-2023:16:50:15
95 WMSYS 26-MAR-2023:03:48:27 03-APR-2023:16:50:15
104 OJVMSYS 26-MAR-2023:03:50:24 07-APR-2023:14:53:16
106 CTXSYS 26-MAR-2023:03:53:39 03-APR-2023:16:50:15
109 OLAPSYS 26-MAR-2023:03:55:37 03-APR-2023:16:50:15
112 MDSYS 26-MAR-2023:04:00:40 03-APR-2023:16:50:15
113 MDDATA 26-MAR-2023:04:00:40 03-APR-2023:16:50:15
127 LBACSYS 26-MAR-2023:04:05:33 03-APR-2023:16:50:15
129 DVF 26-MAR-2023:04:06:30 03-APR-2023:16:50:15
1279990 DVSYS 26-MAR-2023:04:06:30 03-APR-2023:16:50:15
130 PDBADMIN 03-APR-2023:16:50:15 03-APR-2023:16:50:15
USER_ID USERNAME TO_CHAR(CREATED,'DD-MON-YYYY: TO_CHAR(LOCK_DATE,'DD-MON-YYY TO_CHAR(EXPIRY_DATE,'DD-MON-Y TO_CHAR(PASSWORD_CHANGE_DATE,
-------------- ------------------------------ ----------------------------- ----------------------------- ----------------------------- -----------------------------
131 APEX_220200 03-APR-2023:16:53:17 03-APR-2023:16:53:17
132 FLOWS_FILES 03-APR-2023:16:53:17 03-APR-2023:16:53:17
133 APEX_PUBLIC_USER 03-APR-2023:16:53:17 03-APR-2023:17:00:25
137 APEX_LISTENER 03-APR-2023:17:00:24 03-APR-2023:17:00:24
138 APEX_REST_PUBLIC_USER 03-APR-2023:17:00:24 03-APR-2023:17:00:24
139 ORDS_PUBLIC_USER 03-APR-2023:17:00:39 03-APR-2023:17:00:39
140 ORDS_METADATA 03-APR-2023:17:00:40
143 HRREST 03-APR-2023:17:01:12 03-APR-2023:17:01:12
144 HR 03-APR-2023:17:01:14 03-APR-2023:17:01:14
145 OE 03-APR-2023:17:01:19 03-APR-2023:17:01:19
146 PM 03-APR-2023:17:01:56 03-APR-2023:17:01:56
USER_ID USERNAME TO_CHAR(CREATED,'DD-MON-YYYY: TO_CHAR(LOCK_DATE,'DD-MON-YYY TO_CHAR(EXPIRY_DATE,'DD-MON-Y TO_CHAR(PASSWORD_CHANGE_DATE,
-------------- ------------------------------ ----------------------------- ----------------------------- ----------------------------- -----------------------------
147 IX 03-APR-2023:17:02:02 03-APR-2023:17:02:02
148 SH 03-APR-2023:17:02:09 03-APR-2023:17:02:09
149 BI 03-APR-2023:17:02:48 03-APR-2023:17:02:48
150 AV 03-APR-2023:17:02:53 03-APR-2023:17:02:53
151 PFCLSCAN 05-APR-2023:13:05:50 21-APR-2023:12:52:44
152 VA 06-APR-2023:15:20:00 26-MAY-2023:11:46:46
153 VB 06-APR-2023:15:21:44 06-APR-2023:15:21:44
154 VC 06-APR-2023:15:26:36 06-APR-2023:15:26:36
157 VF 10-APR-2023:11:11:31 10-APR-2023:11:11:31
158 VG 10-APR-2023:12:06:57 10-APR-2023:12:06:57
159 VE 14-APR-2023:13:21:53 14-APR-2023:13:21:53
USER_ID USERNAME TO_CHAR(CREATED,'DD-MON-YYYY: TO_CHAR(LOCK_DATE,'DD-MON-YYY TO_CHAR(EXPIRY_DATE,'DD-MON-Y TO_CHAR(PASSWORD_CHANGE_DATE,
-------------- ------------------------------ ----------------------------- ----------------------------- ----------------------------- -----------------------------
160 VJ 02-MAY-2023:12:51:56 02-MAY-2023:12:51:56
165 VV 26-JUN-2023:08:52:17 26-JUN-2023:08:52:17
166 VX 26-JUN-2023:09:55:57 26-JUN-2023:09:55:57
167 SCHEMA1 26-JUN-2023:14:18:49 26-JUN-2023:14:18:49
168 SCHEMA2 26-JUN-2023:14:19:32 26-JUN-2023:14:19:32
169 CON1 26-JUN-2023:14:27:16 26-JUN-2023:14:27:16
170 CON2 26-JUN-2023:14:28:23 26-JUN-2023:14:28:23
171 TESTTEST 14-JUL-2023:08:56:43 14-JUL-2023:08:56:43
172 TT1 09-OCT-2023:09:15:27 09-OCT-2023:09:15:27
173 UU1 09-OCT-2023:09:16:49 09-OCT-2023:09:16:49
174 TEST33 03-NOV-2023:14:30:26 03-NOV-2023:14:30:26
USER_ID USERNAME TO_CHAR(CREATED,'DD-MON-YYYY: TO_CHAR(LOCK_DATE,'DD-MON-YYY TO_CHAR(EXPIRY_DATE,'DD-MON-Y TO_CHAR(PASSWORD_CHANGE_DATE,
-------------- ------------------------------ ----------------------------- ----------------------------- ----------------------------- -----------------------------
187 ORABLOG 16-NOV-2023:15:09:30 16-NOV-2023:15:09:30
188 VM 16-NOV-2023:15:09:53 16-NOV-2023:15:09:53
189 SQL_F 16-NOV-2023:15:10:39 16-NOV-2023:15:10:39
190 VQ 16-NOV-2023:15:29:46 16-NOV-2023:15:29:46
191 SCANNER 29-NOV-2023:12:43:36 29-NOV-2023:12:43:36
192 PROXY 29-NOV-2023:14:21:46 29-NOV-2023:14:21:46
72 rows selected.
SQL>
This fixes the issue of USER_ID 85 and 86. The other thing that stands out is the big gaps in USER_IDs such as SYS = 0 and SYSTEM = 9. This is because ROLES also get a USER_ID as both are stored in SYS.USER$. Lets get the details instead from USER$. First here is a simple script:
SQL> get for_user
1 -- user analysis
2 --col user_id for 9999999999999
3 --col username for a30
4 --col lock_date for a20
5 --col expiry_date for a20
6 --col password_change_date for a20
7 --set lines 220
8 --
9 --select user_id,
10 -- username,
11 -- to_char(created,'DD-MON-YYYY:HH24:MI:SS'),
12 -- to_char(lock_date,'DD-MON-YYYY:HH24:MI:SS'),
13 -- to_char(expiry_date,'DD-MON-YYYY:HH24:MI:SS'),
14 -- to_char(password_change_date,'DD-MON-YYYY:HH24:MI:SS')
15 --from dba_users order by created,user_id
16 --/
17 col user# for 9999999999999
18 col name for a32
19 col ctime for a20
20 col ltime for a20
21 col exptime for a20
22 col ptime for a20
23 col type# for a4
24 select user#,
25 decode(type#,0,'ROLE',1,'USER','UNKN'),
26 name,
27 to_char(ctime,'DD-MON-YYYY:HH24:MI:SS'),
28 to_char(ltime,'DD-MON-YYYY:HH24:MI:SS'),
29 to_char(exptime,'DD-MON-YYYY:HH24:MI:SS'),
30 to_char(ptime,'DD-MON-YYYY:HH24:MI:SS')
31 from sys.user$
32* order by ctime,user#
33 .
SQL>
Now run it and see the results:
SQL> @for_user
USER# DECO NAME TO_CHAR(CTIME,'DD-MON-YYYY:HH TO_CHAR(LTIME,'DD-MON-YYYY:HH TO_CHAR(EXPTIME,'DD-MON-YYYY: TO_CHAR(PTIME,'DD-MON-YYYY:HH
-------------- ---- -------------------------------- ----------------------------- ----------------------------- ----------------------------- -----------------------------
0 USER SYS 26-MAR-2023:02:26:54
1 ROLE PUBLIC 26-MAR-2023:02:26:55
2 ROLE CONNECT 26-MAR-2023:02:26:55
3 ROLE RESOURCE 26-MAR-2023:02:26:55
4 ROLE DBA 26-MAR-2023:02:26:55
5 ROLE PDB_DBA 26-MAR-2023:02:26:55
6 ROLE AUDIT_ADMIN 26-MAR-2023:02:26:55
7 ROLE AUDIT_VIEWER 26-MAR-2023:02:26:55
8 USER AUDSYS 26-MAR-2023:02:26:55 03-APR-2023:16:50:15
193 ROLE _NEXT_USER 26-MAR-2023:02:26:55
9 USER SYSTEM 26-MAR-2023:02:26:56
USER# DECO NAME TO_CHAR(CTIME,'DD-MON-YYYY:HH TO_CHAR(LTIME,'DD-MON-YYYY:HH TO_CHAR(EXPTIME,'DD-MON-YYYY: TO_CHAR(PTIME,'DD-MON-YYYY:HH
-------------- ---- -------------------------------- ----------------------------- ----------------------------- ----------------------------- -----------------------------
10 ROLE SELECT_CATALOG_ROLE 26-MAR-2023:02:26:56
11 ROLE EXECUTE_CATALOG_ROLE 26-MAR-2023:02:26:56
12 ROLE CAPTURE_ADMIN 26-MAR-2023:02:26:56
2147483617 USER SYSBACKUP 26-MAR-2023:02:26:56 03-APR-2023:16:50:15
2147483618 USER SYSDG 26-MAR-2023:02:26:56 03-APR-2023:16:50:15
2147483619 USER SYSKM 26-MAR-2023:02:26:56 03-APR-2023:16:50:15
2147483620 USER SYSRAC 26-MAR-2023:02:26:56
13 USER OUTLN 26-MAR-2023:02:27:05 03-APR-2023:16:50:15
14 ROLE EXP_FULL_DATABASE 26-MAR-2023:02:27:30
15 ROLE IMP_FULL_DATABASE 26-MAR-2023:02:27:30
16 ROLE AVTUNE_PKG_ROLE 26-MAR-2023:02:27:46
USER# DECO NAME TO_CHAR(CTIME,'DD-MON-YYYY:HH TO_CHAR(LTIME,'DD-MON-YYYY:HH TO_CHAR(EXPTIME,'DD-MON-YYYY: TO_CHAR(PTIME,'DD-MON-YYYY:HH
-------------- ---- -------------------------------- ----------------------------- ----------------------------- ----------------------------- -----------------------------
17 ROLE CDB_DBA 26-MAR-2023:03:10:41
18 ROLE APPLICATION_TRACE_VIEWER 26-MAR-2023:03:11:00
19 ROLE ACCHK_READ 26-MAR-2023:03:11:00
20 ROLE LOGSTDBY_ADMINISTRATOR 26-MAR-2023:03:11:48
21 ROLE DBFS_ROLE 26-MAR-2023:03:11:55
22 ROLE GSMUSER_ROLE 26-MAR-2023:03:11:57
23 ROLE GSMROOTUSER_ROLE 26-MAR-2023:03:11:57
24 USER GSMADMIN_INTERNAL 26-MAR-2023:03:11:57 03-APR-2023:16:50:15
25 USER GSMUSER 26-MAR-2023:03:11:57 03-APR-2023:16:50:15
26 USER DIP 26-MAR-2023:03:12:06 26-MAR-2023:03:12:06
27 ROLE SAGA_ADM_ROLE 26-MAR-2023:03:12:11
USER# DECO NAME TO_CHAR(CTIME,'DD-MON-YYYY:HH TO_CHAR(LTIME,'DD-MON-YYYY:HH TO_CHAR(EXPTIME,'DD-MON-YYYY: TO_CHAR(PTIME,'DD-MON-YYYY:HH
-------------- ---- -------------------------------- ----------------------------- ----------------------------- ----------------------------- -----------------------------
28 ROLE SAGA_PARTICIPANT_ROLE 26-MAR-2023:03:12:11
29 ROLE SAGA_CONNECT_ROLE 26-MAR-2023:03:12:11
30 ROLE AQ_ADMINISTRATOR_ROLE 26-MAR-2023:03:12:13
31 ROLE AQ_USER_ROLE 26-MAR-2023:03:12:13
32 ROLE DATAPUMP_EXP_FULL_DATABASE 26-MAR-2023:03:12:16
33 ROLE DATAPUMP_IMP_FULL_DATABASE 26-MAR-2023:03:12:16
34 ROLE ADM_PARALLEL_EXECUTE_TASK 26-MAR-2023:03:12:28
35 ROLE PROVISIONER 26-MAR-2023:03:12:30
36 ROLE XS_SESSION_ADMIN 26-MAR-2023:03:12:30
37 ROLE XS_NAMESPACE_ADMIN 26-MAR-2023:03:12:30
38 ROLE XS_CACHE_ADMIN 26-MAR-2023:03:12:30
USER# DECO NAME TO_CHAR(CTIME,'DD-MON-YYYY:HH TO_CHAR(LTIME,'DD-MON-YYYY:HH TO_CHAR(EXPTIME,'DD-MON-YYYY: TO_CHAR(PTIME,'DD-MON-YYYY:HH
-------------- ---- -------------------------------- ----------------------------- ----------------------------- ----------------------------- -----------------------------
39 ROLE XS_CONNECT 26-MAR-2023:03:12:30
2147483638 USER XS$NULL 26-MAR-2023:03:12:31 26-MAR-2023:03:12:31
40 USER REMOTE_SCHEDULER_AGENT 26-MAR-2023:03:12:38 03-APR-2023:16:50:15
41 USER DBSFWUSER 26-MAR-2023:03:12:41 03-APR-2023:16:50:15
42 ROLE SQL_FIREWALL_ADMIN 26-MAR-2023:03:12:48
43 ROLE SQL_FIREWALL_VIEWER 26-MAR-2023:03:12:48
44 ROLE OSAK_ADMIN_ROLE 26-MAR-2023:03:12:49
45 ROLE GATHER_SYSTEM_STATISTICS 26-MAR-2023:03:13:49
46 ROLE OPTIMIZER_PROCESSING_RATE 26-MAR-2023:03:13:49
47 ROLE DBMS_MDX_INTERNAL 26-MAR-2023:03:14:00
48 ROLE BDSQL_ADMIN 26-MAR-2023:03:14:35
USER# DECO NAME TO_CHAR(CTIME,'DD-MON-YYYY:HH TO_CHAR(LTIME,'DD-MON-YYYY:HH TO_CHAR(EXPTIME,'DD-MON-YYYY: TO_CHAR(PTIME,'DD-MON-YYYY:HH
-------------- ---- -------------------------------- ----------------------------- ----------------------------- ----------------------------- -----------------------------
49 ROLE BDSQL_USER 26-MAR-2023:03:14:35
50 ROLE RECOVERY_CATALOG_OWNER 26-MAR-2023:03:14:56
51 ROLE RECOVERY_CATALOG_OWNER_VPD 26-MAR-2023:03:14:56
52 ROLE RECOVERY_CATALOG_USER 26-MAR-2023:03:14:56
53 USER GGSHAREDCAP 26-MAR-2023:03:15:43 03-APR-2023:16:50:15
54 ROLE SYSUMF_ROLE 26-MAR-2023:03:27:03
55 USER SYS$UMF 26-MAR-2023:03:27:03 03-APR-2023:16:50:15
56 ROLE MAINTPLAN_APP 26-MAR-2023:03:27:04
57 ROLE NOTIFICATIONS_USER 26-MAR-2023:03:27:06
58 ROLE NOTIFICATIONS_ADMIN 26-MAR-2023:03:27:06
59 ROLE SCHEDULER_ADMIN 26-MAR-2023:03:28:51
USER# DECO NAME TO_CHAR(CTIME,'DD-MON-YYYY:HH TO_CHAR(LTIME,'DD-MON-YYYY:HH TO_CHAR(EXPTIME,'DD-MON-YYYY: TO_CHAR(PTIME,'DD-MON-YYYY:HH
-------------- ---- -------------------------------- ----------------------------- ----------------------------- ----------------------------- -----------------------------
60 ROLE PPLB_ROLE 26-MAR-2023:03:29:06
61 ROLE DGPDB_ROLE 26-MAR-2023:03:29:06
62 USER DGPDB_INT 26-MAR-2023:03:29:06 03-APR-2023:16:50:15
63 ROLE HS_ADMIN_SELECT_ROLE 26-MAR-2023:03:29:25
64 ROLE HS_ADMIN_EXECUTE_ROLE 26-MAR-2023:03:29:25
65 ROLE HS_ADMIN_ROLE 26-MAR-2023:03:29:25
66 ROLE GLOBAL_AQ_USER_ROLE 26-MAR-2023:03:29:28
67 ROLE OGG_CAPTURE 26-MAR-2023:03:33:51
68 ROLE OGG_APPLY 26-MAR-2023:03:33:51
69 ROLE OGG_APPLY_PROCREP 26-MAR-2023:03:33:51
70 ROLE OGG_SHARED_CAPTURE 26-MAR-2023:03:33:51
USER# DECO NAME TO_CHAR(CTIME,'DD-MON-YYYY:HH TO_CHAR(LTIME,'DD-MON-YYYY:HH TO_CHAR(EXPTIME,'DD-MON-YYYY: TO_CHAR(PTIME,'DD-MON-YYYY:HH
-------------- ---- -------------------------------- ----------------------------- ----------------------------- ----------------------------- -----------------------------
71 ROLE XSTREAM_CAPTURE 26-MAR-2023:03:33:51
72 ROLE XSTREAM_APPLY 26-MAR-2023:03:33:51
73 ROLE OEM_ADVISOR 26-MAR-2023:03:33:52
74 ROLE OEM_MONITOR 26-MAR-2023:03:33:52
75 USER DBSNMP 26-MAR-2023:03:33:52 03-APR-2023:16:50:15
76 USER APPQOSSYS 26-MAR-2023:03:33:52 03-APR-2023:16:50:15
77 ROLE GSMCATUSER_ROLE 26-MAR-2023:03:33:54
78 ROLE GSMADMIN_ROLE 26-MAR-2023:03:33:54
79 ROLE GSM_POOLADMIN_ROLE 26-MAR-2023:03:33:54
80 ROLE GDS_CATALOG_SELECT 26-MAR-2023:03:33:54
81 ROLE SHARDED_SCHEMA_OWNER 26-MAR-2023:03:33:54
USER# DECO NAME TO_CHAR(CTIME,'DD-MON-YYYY:HH TO_CHAR(LTIME,'DD-MON-YYYY:HH TO_CHAR(EXPTIME,'DD-MON-YYYY: TO_CHAR(PTIME,'DD-MON-YYYY:HH
-------------- ---- -------------------------------- ----------------------------- ----------------------------- ----------------------------- -----------------------------
82 USER GSMCATUSER 26-MAR-2023:03:33:56 03-APR-2023:16:50:15
83 USER GGSYS 26-MAR-2023:03:34:04 03-APR-2023:16:50:15
84 ROLE GGSYS_ROLE 26-MAR-2023:03:34:04
85 USER XDB 26-MAR-2023:03:35:41 03-APR-2023:16:50:15
86 USER ANONYMOUS 26-MAR-2023:03:35:41 03-APR-2023:16:50:15
87 ROLE XDBADMIN 26-MAR-2023:03:35:41
88 ROLE XDB_SET_INVOKER 26-MAR-2023:03:35:59
89 ROLE AUTHENTICATEDUSER 26-MAR-2023:03:36:01
90 ROLE XDB_WEBSERVICES 26-MAR-2023:03:36:01
91 ROLE XDB_WEBSERVICES_WITH_PUBLIC 26-MAR-2023:03:36:01
92 ROLE XDB_WEBSERVICES_OVER_HTTP 26-MAR-2023:03:36:01
USER# DECO NAME TO_CHAR(CTIME,'DD-MON-YYYY:HH TO_CHAR(LTIME,'DD-MON-YYYY:HH TO_CHAR(EXPTIME,'DD-MON-YYYY: TO_CHAR(PTIME,'DD-MON-YYYY:HH
-------------- ---- -------------------------------- ----------------------------- ----------------------------- ----------------------------- -----------------------------
93 ROLE SODA_APP 26-MAR-2023:03:46:36
94 ROLE DB_DEVELOPER_ROLE 26-MAR-2023:03:46:50
95 USER WMSYS 26-MAR-2023:03:48:27 03-APR-2023:16:50:15
96 ROLE WM_ADMIN_ROLE 26-MAR-2023:03:48:33
97 ROLE JAVAUSERPRIV 26-MAR-2023:03:50:20
98 ROLE JAVAIDPRIV 26-MAR-2023:03:50:20
99 ROLE JAVASYSPRIV 26-MAR-2023:03:50:20
100 ROLE JAVADEBUGPRIV 26-MAR-2023:03:50:20
101 ROLE EJBCLIENT 26-MAR-2023:03:50:20
102 ROLE JMXSERVER 26-MAR-2023:03:50:20
103 ROLE DBJAVASCRIPT 26-MAR-2023:03:50:20
USER# DECO NAME TO_CHAR(CTIME,'DD-MON-YYYY:HH TO_CHAR(LTIME,'DD-MON-YYYY:HH TO_CHAR(EXPTIME,'DD-MON-YYYY: TO_CHAR(PTIME,'DD-MON-YYYY:HH
-------------- ---- -------------------------------- ----------------------------- ----------------------------- ----------------------------- -----------------------------
104 USER OJVMSYS 26-MAR-2023:03:50:24 07-APR-2023:14:53:16
105 ROLE JAVA_ADMIN 26-MAR-2023:03:50:44
106 USER CTXSYS 26-MAR-2023:03:53:39 03-APR-2023:16:50:15
107 ROLE CTXAPP 26-MAR-2023:03:53:40
108 ROLE OLAP_XS_ADMIN 26-MAR-2023:03:55:31
109 USER OLAPSYS 26-MAR-2023:03:55:37 03-APR-2023:16:50:15
110 ROLE OLAP_DBA 26-MAR-2023:03:55:38
111 ROLE OLAP_USER 26-MAR-2023:03:55:38
112 USER MDSYS 26-MAR-2023:04:00:40 03-APR-2023:16:50:15
113 USER MDDATA 26-MAR-2023:04:00:40 03-APR-2023:16:50:15
114 ROLE RDFCTX_ADMIN 26-MAR-2023:04:04:29
USER# DECO NAME TO_CHAR(CTIME,'DD-MON-YYYY:HH TO_CHAR(LTIME,'DD-MON-YYYY:HH TO_CHAR(EXPTIME,'DD-MON-YYYY: TO_CHAR(PTIME,'DD-MON-YYYY:HH
-------------- ---- -------------------------------- ----------------------------- ----------------------------- ----------------------------- -----------------------------
115 ROLE GRAPH_DEVELOPER 26-MAR-2023:04:04:38
116 ROLE GRAPH_ADMINISTRATOR 26-MAR-2023:04:04:38
117 ROLE GRAPH_USER 26-MAR-2023:04:04:38
118 ROLE PGX_SESSION_CREATE 26-MAR-2023:04:04:38
119 ROLE PGX_SERVER_GET_INFO 26-MAR-2023:04:04:38
120 ROLE PGX_SERVER_MANAGE 26-MAR-2023:04:04:38
121 ROLE PGX_SESSION_READ_MODEL 26-MAR-2023:04:04:38
122 ROLE PGX_SESSION_MODIFY_MODEL 26-MAR-2023:04:04:38
123 ROLE PGX_SESSION_NEW_GRAPH 26-MAR-2023:04:04:38
124 ROLE PGX_SESSION_GET_PUBLISHED_GRAPH 26-MAR-2023:04:04:38
125 ROLE PGX_SESSION_COMPILE_ALGORITHM 26-MAR-2023:04:04:38
USER# DECO NAME TO_CHAR(CTIME,'DD-MON-YYYY:HH TO_CHAR(LTIME,'DD-MON-YYYY:HH TO_CHAR(EXPTIME,'DD-MON-YYYY: TO_CHAR(PTIME,'DD-MON-YYYY:HH
-------------- ---- -------------------------------- ----------------------------- ----------------------------- ----------------------------- -----------------------------
126 ROLE PGX_SESSION_ADD_PUBLISHED_GRAPH 26-MAR-2023:04:04:38
127 USER LBACSYS 26-MAR-2023:04:05:33 03-APR-2023:16:50:15
128 ROLE LBAC_DBA 26-MAR-2023:04:05:33
129 USER DVF 26-MAR-2023:04:06:30 03-APR-2023:16:50:15
1279990 USER DVSYS 26-MAR-2023:04:06:30 03-APR-2023:16:50:15
1279991 ROLE DV_ACCTMGR 26-MAR-2023:04:06:40
1279992 ROLE DV_OWNER 26-MAR-2023:04:06:40
1279993 ROLE DV_ADMIN 26-MAR-2023:04:06:40
1279994 ROLE DV_SECANALYST 26-MAR-2023:04:06:40
2147483627 ROLE DV_PATCH_ADMIN 26-MAR-2023:04:06:40
2147483628 ROLE DV_MONITOR 26-MAR-2023:04:06:40
USER# DECO NAME TO_CHAR(CTIME,'DD-MON-YYYY:HH TO_CHAR(LTIME,'DD-MON-YYYY:HH TO_CHAR(EXPTIME,'DD-MON-YYYY: TO_CHAR(PTIME,'DD-MON-YYYY:HH
-------------- ---- -------------------------------- ----------------------------- ----------------------------- ----------------------------- -----------------------------
2147483629 ROLE DV_STREAMS_ADMIN 26-MAR-2023:04:06:40
2147483630 ROLE DV_GOLDENGATE_ADMIN 26-MAR-2023:04:06:40
2147483631 ROLE DV_XSTREAM_ADMIN 26-MAR-2023:04:06:40
2147483632 ROLE DV_GOLDENGATE_REDO_ACCESS 26-MAR-2023:04:06:40
2147483633 ROLE DV_AUDIT_CLEANUP 26-MAR-2023:04:06:40
2147483634 ROLE DV_DATAPUMP_NETWORK_LINK 26-MAR-2023:04:06:40
2147483635 ROLE DV_POLICY_OWNER 26-MAR-2023:04:06:41
130 USER PDBADMIN 03-APR-2023:16:50:15 30-SEP-2023:16:50:15 03-APR-2023:16:50:15
131 USER APEX_220200 03-APR-2023:16:53:17 03-APR-2023:16:53:17
132 USER FLOWS_FILES 03-APR-2023:16:53:17 03-APR-2023:16:53:17
133 USER APEX_PUBLIC_USER 03-APR-2023:16:53:17 03-APR-2023:17:00:25
USER# DECO NAME TO_CHAR(CTIME,'DD-MON-YYYY:HH TO_CHAR(LTIME,'DD-MON-YYYY:HH TO_CHAR(EXPTIME,'DD-MON-YYYY: TO_CHAR(PTIME,'DD-MON-YYYY:HH
-------------- ---- -------------------------------- ----------------------------- ----------------------------- ----------------------------- -----------------------------
134 ROLE APEX_ADMINISTRATOR_READ_ROLE 03-APR-2023:16:53:21
135 ROLE APEX_ADMINISTRATOR_ROLE 03-APR-2023:16:53:21
136 ROLE APEX_GRANTS_FOR_NEW_USERS_ROLE 03-APR-2023:16:53:21
137 USER APEX_LISTENER 03-APR-2023:17:00:24 03-APR-2023:17:00:24
138 USER APEX_REST_PUBLIC_USER 03-APR-2023:17:00:24 03-APR-2023:17:00:24
139 USER ORDS_PUBLIC_USER 03-APR-2023:17:00:39 03-APR-2023:17:00:39
140 USER ORDS_METADATA 03-APR-2023:17:00:40
141 ROLE ORDS_ADMINISTRATOR_ROLE 03-APR-2023:17:00:52
142 ROLE ORDS_RUNTIME_ROLE 03-APR-2023:17:00:52
143 USER HRREST 03-APR-2023:17:01:12 03-APR-2023:17:01:12
144 USER HR 03-APR-2023:17:01:14 03-APR-2023:17:01:14
USER# DECO NAME TO_CHAR(CTIME,'DD-MON-YYYY:HH TO_CHAR(LTIME,'DD-MON-YYYY:HH TO_CHAR(EXPTIME,'DD-MON-YYYY: TO_CHAR(PTIME,'DD-MON-YYYY:HH
-------------- ---- -------------------------------- ----------------------------- ----------------------------- ----------------------------- -----------------------------
145 USER OE 03-APR-2023:17:01:19 03-APR-2023:17:01:19
146 USER PM 03-APR-2023:17:01:56 03-APR-2023:17:01:56
147 USER IX 03-APR-2023:17:02:02 03-APR-2023:17:02:02
148 USER SH 03-APR-2023:17:02:09 03-APR-2023:17:02:09
149 USER BI 03-APR-2023:17:02:48 03-APR-2023:17:02:48
150 USER AV 03-APR-2023:17:02:53 03-APR-2023:17:02:53
151 USER PFCLSCAN 05-APR-2023:13:05:50 21-APR-2023:12:52:44
152 USER VA 06-APR-2023:15:20:00 26-MAY-2023:11:46:46
153 USER VB 06-APR-2023:15:21:44 06-APR-2023:15:21:44
154 USER VC 06-APR-2023:15:26:36 06-APR-2023:15:26:36
157 USER VF 10-APR-2023:11:11:31 10-APR-2023:11:11:31
USER# DECO NAME TO_CHAR(CTIME,'DD-MON-YYYY:HH TO_CHAR(LTIME,'DD-MON-YYYY:HH TO_CHAR(EXPTIME,'DD-MON-YYYY: TO_CHAR(PTIME,'DD-MON-YYYY:HH
-------------- ---- -------------------------------- ----------------------------- ----------------------------- ----------------------------- -----------------------------
158 USER VG 10-APR-2023:12:06:57 10-APR-2023:12:06:57
159 USER VE 14-APR-2023:13:21:53 14-APR-2023:13:21:53
160 USER VJ 02-MAY-2023:12:51:56 02-MAY-2023:12:51:56
165 USER VV 26-JUN-2023:08:52:17 26-JUN-2023:08:52:17
166 USER VX 26-JUN-2023:09:55:57 26-JUN-2023:09:55:57
167 USER SCHEMA1 26-JUN-2023:14:18:49 26-JUN-2023:14:18:49
168 USER SCHEMA2 26-JUN-2023:14:19:32 26-JUN-2023:14:19:32
169 USER CON1 26-JUN-2023:14:27:16 26-JUN-2023:14:27:16
170 USER CON2 26-JUN-2023:14:28:23 26-JUN-2023:14:28:23
171 USER TESTTEST 14-JUL-2023:08:56:43 14-JUL-2023:08:56:43
172 USER TT1 09-OCT-2023:09:15:27 09-OCT-2023:09:15:27
USER# DECO NAME TO_CHAR(CTIME,'DD-MON-YYYY:HH TO_CHAR(LTIME,'DD-MON-YYYY:HH TO_CHAR(EXPTIME,'DD-MON-YYYY: TO_CHAR(PTIME,'DD-MON-YYYY:HH
-------------- ---- -------------------------------- ----------------------------- ----------------------------- ----------------------------- -----------------------------
173 USER UU1 09-OCT-2023:09:16:49 09-OCT-2023:09:16:49
174 USER TEST33 03-NOV-2023:14:30:26 03-NOV-2023:14:30:26
187 USER ORABLOG 16-NOV-2023:15:09:30 16-NOV-2023:15:09:30
188 USER VM 16-NOV-2023:15:09:53 16-NOV-2023:15:09:53
189 USER SQL_F 16-NOV-2023:15:10:39 16-NOV-2023:15:10:39
190 USER VQ 16-NOV-2023:15:29:46 16-NOV-2023:15:29:46
191 USER SCANNER 29-NOV-2023:12:43:36 29-NOV-2023:12:43:36
192 USER PROXY 29-NOV-2023:14:21:46 29-NOV-2023:14:21:46
195 rows selected.
SQL>
There are a few things of interest:
- Roles also use the bigger numbers in some cases for the series 2147483635 or 1279994
- PUBLIC is shown as TYPE=0 and therefore in this script its shows as a ROLE but its a USER GROUP not a role.
- The sequence _NEXT_USER has a ID value of 193 and is shown in this script as a ROLE but its a sequence and it shows the next normal USER ID to use
- There are gaps visible. Lets use the example of the gap between IDs 154 to 157. User id 154 is user VC and the next user id is 157 for user VF. So it would appear two users are missing, ID 155 and ID 156, we can reasonably know the IDs but not the names. We know user VC was created on 06-APR-2023:15:26:36 and VF on 10-APR-2023:11:11:31 SO we can say for certain that users 155 and 156 were created between the 6th April and the 10th April BUT we do not know when they were removed. We can say for certain that they were removed after the CREATE date and before the current SYSDATE - a much bigger range of dates. We can see user IDs 152 and 153 have names VA and VB and the last user before the gap is VC and the next user is VF and the one after is VG (ID 158). So we can assume as a working hypothesis that the missing users are VD and VE. We can see VE exists as ID 159 so the assumption is VD and VE were deleted and VE was also re-created. This is good news to reduce the date range of when the users were deleted. Because user VE as User ID 159 was created on 10-APR-2023:12:06:57 then this narrows the window of when user IDs 155 and 156 were removed. If one of these users was VE then it could not be added if it existed so its likely now that it was deleted between the create date of User ID 154 and 159
- Because the next user sequence is 193 and the last current user ID is 192 we can assume no users were removed after the creation date of user ID 192
The next step is to ascertain when the attack took place - or currently is assumed to have taken place. As more evidence is looked at we can narrow the dates of the attack to be more certain of what events took place during the attack dates and slowly narrow the date range.
We would need to look at more data, objects created, tables, views, PL/SQL and also grants made. Even if we have no audit trails we can still use gaps in data to help us investigate. Once we have a more detailed view of events that occurred during the attack we can start to use other data to try and correlate this; the SQL, redo, library cache, even external logs.
The biggest problem is that if an attack was purely reading data then no objects are created or dropped and if there is no audit trails it is much harder to find other data to corroborate an attack.
All is not lost if there are no audit trails BUT its harder to find evidence or prove something happened in the database but techniques such as these require work and detective work but can be fruitful.
#oracleace #sym_42 #oracle #database #security #forensics #liveresponse #databreach
