Get the App
SLTechnology News&Howtos  ›  Database  › 

Oracle database SQL audit

Shulou Source: shulou.com Published: 2022-06-01 16:26:41 11月16日 Update

In our daily work, some databases with high security requirements need to increase audit operations, which user did what at what time.

1. Open database audit

Alter system set audit_sys_operations=TRUE scope=spfile; / / Audit management user alter system set audit_trail=db,extended scope=spfile; / / write the sql statement to the audit table

two。 Restart the database and view

Restart the database shutdown immediate;startup;show parameter audit

3. Increase audit strategy

We need to audit all the operations of the caiwu users of the database

/ / Audit user caiwu all successful operations audit all by caiwu by access whenever successful

Or

/ / audit for users (audit if not performed successfully) audit select table by caiwu by access; / / look up table audit audit update table by caiwu by access; / / update audit audit delete table by caiwu by access; / / delete audit audit insert table by caiwu by access; / / insert audit / / update audit for a table, delete audit (error audit) AUDIT UPDATE,DELETE,INSERT ON T_TEST by access / / Protection Audit audit all on sys.aud$ by access

4. Cancel the audit

NOAUDIT UPDATE,DELETE,INSERT ON T_TEST by access

5. Query audit results

Select OS_USERNAME,username,USERHOST,TERMINAL,TIMESTAMP,OWNER,obj_name,ACTION_NAME,sessionid,os_process,sql_text from dba_audit_trail

6. Open an audit table query to a user

Grant select on dba_audit_trail to caiwu

7. Empty audit records

DELETE FROM SYS.AUD$

Tags: Audit user data database success update query security security time policy result statement error protection work openness management Apple Docker Huawei Linux macOS MariaDB Microsoft MySQL NVidia OPPO Reno Huawei NVidia MySQL Shulou Information Microsoft