If you want to filter and analyze your audit data from AUD$ table you need to scan data using ACTION# column. It keeps information about audit actions but is NUMERIC columns. To find out meaning of the column check AUDIT_ACTIONS table
So using simple join between AUD# and AUDIT_ACTIONS tables filter data as you want
SELECT * FROM sys.aud$ WHERE ntimestamp# > SYSDATE - 7 AND action# IN ( SELECT action FROM audit_actions WHERE name IN ('ALTER USER', 'CREATE USER', 'DROP USER'));
List of all audit actions
SELECT action, name FROM audit_actions ORDER BY 1;
ACTION | NAME |
0 | UNKNOWN |
1 | CREATE TABLE |
2 | INSERT |
3 | SELECT |
4 | CREATE CLUSTER |
5 | ALTER CLUSTER |
6 | UPDATE |
7 | DELETE |
8 | DROP CLUSTER |
9 | CREATE INDEX |
10 | DROP INDEX |
11 | ALTER INDEX |
12 | DROP TABLE |
13 | CREATE SEQUENCE |
14 | ALTER SEQUENCE |
15 | ALTER TABLE |
16 | DROP SEQUENCE |
17 | GRANT OBJECT |
18 | REVOKE OBJECT |
19 | CREATE SYNONYM |
20 | DROP SYNONYM |
21 | CREATE VIEW |
22 | DROP VIEW |
23 | VALIDATE INDEX |
24 | CREATE PROCEDURE |
25 | ALTER PROCEDURE |
26 | LOCK |
27 | NO-OP |
28 | RENAME |
29 | COMMENT |
30 | AUDIT OBJECT |
31 | NOAUDIT OBJECT |
32 | CREATE DATABASE LINK |
33 | DROP DATABASE LINK |
34 | CREATE DATABASE |
35 | ALTER DATABASE |
36 | CREATE ROLLBACK SEG |
37 | ALTER ROLLBACK SEG |
38 | DROP ROLLBACK SEG |
39 | CREATE TABLESPACE |
40 | ALTER TABLESPACE |
41 | DROP TABLESPACE |
42 | ALTER SESSION |
43 | ALTER USER |
44 | COMMIT |
45 | ROLLBACK |
46 | SAVEPOINT |
47 | PL/SQL EXECUTE |
48 | SET TRANSACTION |
49 | ALTER SYSTEM |
50 | EXPLAIN |
51 | CREATE USER |
52 | CREATE ROLE |
53 | DROP USER |
54 | DROP ROLE |
55 | SET ROLE |
56 | CREATE SCHEMA |
57 | CREATE CONTROL FILE |
59 | CREATE TRIGGER |
60 | ALTER TRIGGER |
61 | DROP TRIGGER |
62 | ANALYZE TABLE |
63 | ANALYZE INDEX |
64 | ANALYZE CLUSTER |
65 | CREATE PROFILE |
66 | DROP PROFILE |
67 | ALTER PROFILE |
68 | DROP PROCEDURE |
70 | ALTER RESOURCE COST |
71 | CREATE MATERIALIZED VIEW LOG |
72 | ALTER MATERIALIZED VIEW LOG |
73 | DROP MATERIALIZED VIEW LOG |
74 | CREATE MATERIALIZED VIEW |
75 | ALTER MATERIALIZED VIEW |
76 | DROP MATERIALIZED VIEW |
77 | CREATE TYPE |
78 | DROP TYPE |
79 | ALTER ROLE |
80 | ALTER TYPE |
81 | CREATE TYPE BODY |
82 | ALTER TYPE BODY |
83 | DROP TYPE BODY |
84 | DROP LIBRARY |
85 | TRUNCATE TABLE |
86 | TRUNCATE CLUSTER |
88 | ALTER VIEW |
91 | CREATE FUNCTION |
92 | ALTER FUNCTION |
93 | DROP FUNCTION |
94 | CREATE PACKAGE |
95 | ALTER PACKAGE |
96 | DROP PACKAGE |
97 | CREATE PACKAGE BODY |
98 | ALTER PACKAGE BODY |
99 | DROP PACKAGE BODY |
100 | LOGON |
101 | LOGOFF |
102 | LOGOFF BY CLEANUP |
103 | SESSION REC |
104 | SYSTEM AUDIT |
105 | SYSTEM NOAUDIT |
106 | AUDIT DEFAULT |
107 | NOAUDIT DEFAULT |
108 | SYSTEM GRANT |
109 | SYSTEM REVOKE |
110 | CREATE PUBLIC SYNONYM |
111 | DROP PUBLIC SYNONYM |
112 | CREATE PUBLIC DATABASE LINK |
113 | DROP PUBLIC DATABASE LINK |
114 | GRANT ROLE |
115 | REVOKE ROLE |
116 | EXECUTE PROCEDURE |
117 | USER COMMENT |
118 | ENABLE TRIGGER |
119 | DISABLE TRIGGER |
120 | ENABLE ALL TRIGGERS |
121 | DISABLE ALL TRIGGERS |
122 | NETWORK ERROR |
123 | EXECUTE TYPE |
128 | FLASHBACK |
129 | CREATE SESSION |
130 | ALTER MINING MODEL |
131 | SELECT MINING MODEL |
133 | CREATE MINING MODEL |
134 | ALTER PUBLIC SYNONYM |
135 | DIRECTORY EXECUTE |
136 | SQL*LOADER DIRECT PATH LOAD |
137 | DATAPUMP DIRECT PATH UNLOAD |
157 | CREATE DIRECTORY |
158 | DROP DIRECTORY |
159 | CREATE LIBRARY |
160 | CREATE JAVA |
161 | ALTER JAVA |
162 | DROP JAVA |
163 | CREATE OPERATOR |
164 | CREATE INDEXTYPE |
165 | DROP INDEXTYPE |
166 | ALTER INDEXTYPE |
167 | DROP OPERATOR |
168 | ASSOCIATE STATISTICS |
169 | DISASSOCIATE STATISTICS |
170 | CALL METHOD |
171 | CREATE SUMMARY |
172 | ALTER SUMMARY |
173 | DROP SUMMARY |
174 | CREATE DIMENSION |
175 | ALTER DIMENSION |
176 | DROP DIMENSION |
177 | CREATE CONTEXT |
178 | DROP CONTEXT |
179 | ALTER OUTLINE |
180 | CREATE OUTLINE |
181 | DROP OUTLINE |
182 | UPDATE INDEXES |
183 | ALTER OPERATOR |
192 | ALTER SYNONYM |
197 | PURGE USER_RECYCLEBIN |
198 | PURGE DBA_RECYCLEBIN |
199 | PURGE TABLESPACE |
200 | PURGE TABLE |
201 | PURGE INDEX |
202 | UNDROP OBJECT |
204 | FLASHBACK DATABASE |
205 | FLASHBACK TABLE |
206 | CREATE RESTORE POINT |
207 | DROP RESTORE POINT |
208 | PROXY AUTHENTICATION ONLY |
209 | DECLARE REWRITE EQUIVALENCE |
210 | ALTER REWRITE EQUIVALENCE |
211 | DROP REWRITE EQUIVALENCE |
212 | CREATE EDITION |
213 | ALTER EDITION |
214 | DROP EDITION |
215 | DROP ASSEMBLY |
216 | CREATE ASSEMBLY |
217 | ALTER ASSEMBLY |
218 | CREATE FLASHBACK ARCHIVE |
219 | ALTER FLASHBACK ARCHIVE |
220 | DROP FLASHBACK ARCHIVE |
225 | ALTER DATABASE LINK |
305 | ALTER PUBLIC DATABASE LINK |
Have a fun 🙂
Tomasz