SoFunction
Updated on 2025-03-02

How to enable audit logs in oracle database

1 The audit function is controlled by the parameter audit_trail

The value range and meaning are as follows:

Parameter value Description

  • DB (default) enables audit function.
  • OS writes audit records to a file in the operating system
  • TRUE Enables the audit function.
  • FALSE Turn off the audit function.
  • NONE Turn off the audit function.

2 Log in to SQL plus as DBA

All the following commands are executed in this mode:

sqlplus / as sysdba

2.1 View the value of audit_trail

SHOW PARAMETER AUDIT

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /data/app/oracle/admin/orcl/ad
                                                 ump
audit_sys_operations                 boolean     TRUE
audit_syslog_level                   string
audit_trail                          string      DB

According to the query results, we can see

Database audit function has been enabled

2.2 Execute the following statement to turn off the database audit function

ALTER SYSTEM SET AUDIT_TRAIL='NONE' SCOPE=SPFILE;

2.3 Restart the database

shutdown immediate;
startup;

2.4 Verify that the audit has been closed

show parameter audit_trail

NAME TYPE VALUE

audit_trail string FALSE

illustrate:

  • VALUE value is FALSE
  • The surface audit function is turned off

2.5 Turn on the audit function

alter system set audit_sys_operations=TRUE scope=spfile;--Audit management users(bysysdba/sysoperCharacter login)
alter system set audit_trail=db,extended scope=spfile;

Finally, restart the service and start it.

2.6 Delete the audit log

When a very specific log has been formed, the records inside can be deleted. Currently, they are directly deleted and have no impact on the database.

  • Query the current log information: select * FROM $;
  • Delete existing audit information: DELETE FROM $;
  • Or quickly delete table information: truncate table $;

It is generally recommended that if the audit function is not used after deploying oracle, it is turned off to save space.

3 Turn on auditing for specific users and specific tables

select * from dba_audit_object --Check whether the log is generated
AUDIT ALL ON "c##zhangsan".TEST2 by ACCESS; Set up a specific user-specific table auditAUDIT ALL BY "c##zhangsan" BY ACCESS;
AUDIT ALL ON "c##zhangsan".TEST2;
select * from DBA_OBJ_AUDIT_OPTS  --Query the user account opening audit function
AUDIT UPDATE ON ;
AUDIT DELETE ON  by ACCESS;
AUDIT UPDATE ON ROOT.TEST2;

Summarize

The above is personal experience. I hope you can give you a reference and I hope you can support me more.