Oracle audit table example
In this post, we are going to perform an activity on audit table. We are going to explore various auditing types in Oracle 11g.
According to Oracle, Auditing is the monitoring and recording of selected user database actions. In standard auditing, you use initialization parameters and the AUDITNOAUDIT SQL statements to audit SQL statements, privileges, and schema objects, and network and multitier activities.
Example.
- How to enable audit trail in Oracle Database 11g.
We can enable audit_trail using the below command
alter system set audit_trail=db scope=spfile;
|
1 2 3 4 5 6 7 8 9 |
SYS> show parameter audit_trail NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_trail string DB SYS> |
DB-> Auditing is enabled, with all audit records stored in the database audit trial (SYS.AUD$).
Else,
alter system set audit_trail=db scope=spfile;
Restart the datatabase.
Example,
In the following example, we will create a user audit_test enable audit for the user and monitor the auditing activity.
Step 1: Create a user
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SYS> SYS> CREATE USER audit_test IDENTIFIED BY password DEFAULT TABLESPACE users TEMPORARY TABLESPACE TEMP2 QUOTA UNLIMITED ON users; 2 3 4 User created. |
Step 2: Grant default permissions to user
|
1 2 3 4 5 6 7 8 9 |
SYS> GRANT connect TO audit_test; Grant succeeded. SYS> GRANT create table, create procedure TO audit_test; Grant succeeded. |
Step 3: Audit the user audit_test with the below command
|
1 2 3 |
SYS> AUDIT ALL BY audit_test BY ACCESS; Audit succeeded. |
Other auditing types include.
|
1 2 3 4 5 6 7 8 9 |
SYS> AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY audit_test BY ACCESS; Audit succeeded. SYS> AUDIT EXECUTE PROCEDURE BY audit_test BY ACCESS; Audit succeeded. |
Step 4: Connect the user and perform some activity.
|
1 2 3 4 5 |
conn audit_test Enter password: Connected. |
Step 5: Now, as an AUDIT_TEST user do some DML and DDL.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
AUDIT_TEST> AUDIT_TEST> CREATE TABLE test_tab ( id NUMBER ); 2 3 Table created. AUDIT_TEST> INSERT INTO test_tab (id) VALUES (1); 1 row created. AUDIT_TEST> UPDATE test_tab SET id = id; 1 row updated. AUDIT_TEST> SELECT * FROM test_tab; ID ---------- 1 AUDIT_TEST> DELETE FROM test_tab; 1 row deleted. AUDIT_TEST> DROP TABLE test_tab; Table dropped. AUDIT_TEST> |
Step 6: Query the auditing information using below query
SELECT username,
extended_timestamp,
owner,
obj_name,
action_name
FROM dba_audit_trail
WHERE owner = ‘AUDIT_TEST’
ORDER BY timestamp;
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
SYS> COLUMN username FORMAT A10 SYS> COLUMN owner FORMAT A10 SYS> COLUMN obj_name FORMAT A10 SYS> COLUMN extended_timestamp FORMAT A35 SYS> SYS> 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 27-MAR-18 07.05.19.780865 PM +05:30 AUDIT_TEST TEST_TAB CREATE TABLE AUDIT_TEST 27-MAR-18 07.05.23.368659 PM +05:30 AUDIT_TEST TEST_TAB INSERT AUDIT_TEST 27-MAR-18 07.05.26.455413 PM +05:30 AUDIT_TEST TEST_TAB UPDATE AUDIT_TEST 27-MAR-18 07.05.29.557906 PM +05:30 AUDIT_TEST TEST_TAB SELECT AUDIT_TEST 27-MAR-18 07.05.32.405886 PM +05:30 AUDIT_TEST TEST_TAB DELETE AUDIT_TEST 27-MAR-18 07.05.35.447036 PM +05:30 AUDIT_TEST TEST_TAB DROP TABLE 6 rows selected. SYS> |
Some of the important dictionary views include
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
AUDIT_ACTIONS USER_AUDIT_TRAIL DBA_AUDIT_OBJECT USER_AUDIT_SESSION DBA_FGA_AUDIT_TRAIL USER_AUDIT_OBJECT DBA_PRIV_AUDIT_OPTS |
Words from dbapath
Thank you for giving your valuable time to read the above information. I hope the content served your purpose in reaching out the blog.
Suggestions for improvement of the blog are highly appreciable. Please contact us for any information/suggestion/feedback.
If you want to be updated with all our articles
please follow us on Facebook | Twitter
Please subscribe to our newsletter.