Check status , enable and disable the Audit in Oracle
Check the status of audit is Enable or Disable.
SQL> show parameter audit_trail
Brief of following parameter values:
NONE- Auditing is disabled.
DB- Auditing is enabled,(all audit records stored in table(SYS.AUD$).
DB,EXTENDED- As DB,but the SQL_BIND and SQL_TEXT columns are also populated for SYS.AUD$ table
XML- Auditing is enabled, records stored as XML format files.
XML,EXTENDED- As XML,but the SQL_BIND and SQL_TEXT columns are also populated in XML file.
OS- Auditing is enabled, audit records to the operating system's text file.
For Enable or Disable the Audit at Database Level.
ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;
ALTER SYSTEM SET audit_trail=NONE SCOPE=SPFILE;
1. Every Change in parameter effected after restart the database.
2. AUDIT_SYS_OPERATIONS parameter is used for auditing the operations performed by SYSDBA or SYSOPER privileges users. All audit records are written to the audit trail. You need to enable this parameter (alter system set audit_sys_operations=true scope=spfile)
Three levels of audit:
1. Statement level
Auditing will be done at statement level. Statements level can be checked with STMT_AUDIT_OPTION_MAP.
SQL>audit table by scott;
--Audit records can be found in DBA_STMT_AUDIT_OPTS.
SQL> select * from DBA_STMT_AUDIT_OPTS;
2. Object level
Database objects can be audited: tables, views, sequences, packages, stored procedures and stored functions.
SQL> audit insert, update, delete on scott.emp by hr;
Audit records can be found in DBA_OBJ_AUDIT_OPTS.
SQL>select * from DBA_OBJ_AUDIT_OPTS;
3. Privilege level
All system privileges that are found in SYSTEM_PRIVILEGE_MAP can be audited.
SQL> audit create tablespace, alter tablespace by all;
Note: Specify ALL PRIVILEGES to audit all system privileges.
--Audit records can be found in DBA_PRIV_AUDIT_OPTS.
SQL> select * from DBA_PRIV_AUDIT_OPTS;
AUDIT have two options:
1. BY SESSION
If you want Oracle to write a single record for all SQL statements of the same type issued and operations of the same type executed on the same schema objects in the same session.
SQL> audit create, alter, drop on currency by xe by session;
SQL> audit alter materialized view by session;
2. BY ACCESS
Specify BY ACCESS if you want Oracle database to write one record for each audited statement and operation.
SQL> audit update on health by access;
SQL> audit alter sequence by tester by access;
Enable the Audit at different levels:
--Auditing for particular user for DML statement:
SQL> audit select table, insert table, update table, delete table by SCOTT by access;
--Auditing for all user activity:
SQL> audit all by SCOTT by access;
--Audit all Oracle user viewing activity:
SQL> audit select table by SCOTT by access;
--Audit all Oracle user data change activity:
SQL> audit update table, delete table,insert table by SCOTT by access;
--Audit all Oracle user viewing activity:
SQL> audit execute procedure by SCOTT by access;
Disable Audit at different levels:
NOAUDIT statement turns off the various audit options of Oracle.Use it to reset statement, privilege and object audit options.
SQL> NOAUDIT session;
SQL> NOAUDIT session BY scott, hr;
SQL> NOAUDIT DELETE ON emp;
SQL> NOAUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE;
SQL> NOAUDIT ALL;
SQL> NOAUDIT ALL PRIVILEGES;
SQL> NOAUDIT ALL ON DEFAULT;
SQL> noaudit session by appowner;
SQL> noaudit create session by appowner;
SQL> noaudit SELECT TABLE by appowner;
SQL> noaudit INSERT TABLE by appowner;
SQL> noaudit EXECUTE PROCEDURE by appowner;
SQL> noaudit DELETE TABLE by appowner;
SQL> noaudit DELETE ANY TABLE by appowner;
Script for check all the enabled auditing on Database
--Check the parameter is enabled or disable for Audit
select name || '=' || value PARAMETER from sys.v_$parameter where name like '%audit%';
--Statement Audits Enabled on this Database
column user_name format a10
column audit_option format a40
select * from sys.dba_stmt_audit_opts;
--Privilege Audits Enabled on this Database
select * from dba_priv_audit_opts;
-- Object Audits Enabled on this Database
select (owner ||'.'|| object_name) object_name,
alt, aud, com, del, gra, ind, ins, loc, ren, sel, upd, ref, exe
where alt != '-/-' or aud != '-/-'
or com != '-/-' or del != '-/-'
or gra != '-/-' or ind != '-/-'
or ins != '-/-' or loc != '-/-'
or ren != '-/-' or sel != '-/-'
or upd != '-/-' or ref != '-/-'
or exe != '-/-';
--Default Audits Enabled on this Database
select * from all_def_audit_opts;
hi, how can audit records be purged? thanks
Hi You can DELETE data from AUD$ table or for complete purge use TRUNCATE command.
If you have configured unified auditing then follows blog:
this oracle supplied audit policy “ORA_LOGON_FAILURES” does not looks like it is enable. is there a view to query if is it enabled? and what columns to look for. thanks.
select * from audit_unified_enabled_policies;
thanks so much that helps.
For detail check the following view :
from audit_unified_policies where policy_name = ‘ORA_LOGON_FAILURES’;
For Enable : AUDIT POLICY ora_logon_failures
For Disable: NOAUDIT POLICY ora_logon_failures
is there a way to get the metadata of that “ORA_LOGON_FAILURES” audit policy? or a way to know from any views/table that policy applies only to failed logon.
For Get metadata: https://smarttechways.com/2020/06/09/script-for-generate-policy-ddl-in-unified-audit-polices/
How as a private citizen can I stop oracle from tracking me
I don’t have audit select enabled on all levels of audit and still got audit select on tables
can any one advice please
Does Enabling audit requires an additional license for Oracle 10g Enterprise Edition.
Its not mentioned anywhere. I think not required fine grained auditing in included so it may also included.