Auditing is divided into three types:
>
SYS Activities Auditing.
>
System and Object Privileges Auditing.
>
Fine Grained Auditing (FGA).
Note: Auditing by triggers is not within the scope of this article.
Before I start you have to diffrentiate between SYSTEM and OBJECT privileges:
SYSTEM PRIVILEGES are:
Alter(session, any XXX, user, profile, tablespace), SYSgrant, Create, drop, truncate, select any, update any, delete any
OBJECT PRIVILEGES are:
Alter(OBJECT like: tab,view,..), audit, grant(on OBJ), locate, rename,select, insert, update, delete, execute.
Now let's explain each type in details.
########################################################################
SYS Activities Auditing:
########################################################################
> Will audit all activities for the users who log in to the DB with SYSDBA privilege, audit records will be stored on the filesystem.
> Auditing SYS/OBJ privileges will never audit SYSDBA privilege.
> Activate it by setting the parameter "AUDIT_SYS_OPERATIONS" to TRUE.
> Audit file location controlled by AUDIT_FILE_DEST initialization parameter, DEFAULT is $ORACLE_HOME/rdbms/audit or adump.
In order to audit statements ran by SYS user, the following intialization parameters should be set:
SQL> ALTER SYSTEM SET AUDIT_SYS_OPERATIONS= TRUE SCOPE=SPFILE;
SQL> ALTER SYSTEM SET AUDIT_FILE_DEST=<destination of audit files> SCOPE=SPFILE;
SQL> ALTER SYSTEM SET AUDIT_SYSLOG_LEVEL =<OS service log level> SCOPE=SPFILE;
Special Task:
Forward Audit SYS Operations records to FILE accessed By ROOT only:
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
In case you don't want to allow people with no root privilege on the server to read/write the audit logs:
Set the following intialization parameter:
SQL> ALTER SYSTEM SET audit_syslog_level='local5.info' SCOPE=SPFILE;
On OS: [By Root]
--------
# vi /etc/syslog.conf
Add this parameter:
local5.info /var/log/oracle_audit_sys.log
Restart syslog service:
# /sbin/service syslog restart
Now you can view audit records in /var/log/oracle_audit_sys.log which owned by ROOT user.
Warning: After forwarding Audit records to OS logging service:
> Records will be in a difficult shape to read.
> Fine-grained FGA, Oracle Label Security, Oracle Database Vault audit records will not be captured.
########################################################################
System Privileges Auditing:
########################################################################
> Audit SYSTEM PRIVILEGES like: [Create, drop, truncate, select any, update any, delete any, Alter(session,any.,user,profile,tablespace),SYSgrant]
when used by ALL|SPECIFIC users (Except the ones connected with SYSDBA).
> Audit data can be found in "DBA_PRIV_AUDIT_OPTS" view.
> Audit records stored in SYS.AUD$ table can be viewed in DBA_AUDIT_TRAIL view.
> Auditing should be enabled on the database by setting the parameter "AUDIT_TRAIL" to value other than "NONE".
> Will not work for users connected with SYSDBA privilege.
Set AUDIT_TRAIL Initialization Parameter:
-----------------------------------------------------
SQL> ALTER SYSTEM SET AUDIT_TRAIL=DB SCOPE=SPFILE;
AUDIT_TRAIL=NONE =>Disable Auditing.
AUDIT_TRAIL=DB =>Enable Auditing (without auditing SQL STMTS/BINDS), records stored in the DB. [half DB_EXTENDED Performance Overhead 8%]
AUDIT_TRAIL=DB_EXTENDED =>Enable Auditing (with auditing SQL STMTS/BINDS), records stored in the DB. [Highest Performace Overhead 16%] 2xDB
AUDIT_TRAIL=OS =>Enable Auditing ,records stored in the OS Audit Trail. [Lowest Performace Overhead 2%]
AUDIT_TRAIL=XML =>Enable Auditing (without auditing SQL STMTS/BINDS), records stored in the OS (XML Format). [Overhead Lower than DB 3.5%]
AUDIT_TRAIL=XML_EXTENDED=>Enable Auditing (with auditing SQL STMTS/BINDS), records stored in the OS (XML Format). [Overhead Lower than DB 5%]
Performance stats reference quoted from:
http://www.oracle.com/technetwork/products/audit-vault/learnmore/twp-security-auditperformance-166655.pdf
Examples:
------------
SQL> AUDIT ALL;
=>Audit all system privileges Except the following privileges:
--ALTER SEQUENCE
--ALTER TABLE
--DELETE TABLE
--SELECT ANY TABLE
--COMMENT TABLE
--GRANT TABLE
--GRANT TYPE
--GRANT DIRECTORY
--GRANT PROCEDURE
--GRANT SEQUENCE
SQL> AUDIT RESOURCE BY SCOTT;
=>Audit ALTER SYSTEM, CLUSTER, DATABASE LINK, PROCEDURE, ROLLBACK SEGMENT, SEQUENCE, SYNONYM, TABLE, TABLESPACE, VIEW when used by SCOTT only.
SQL> AUDIT ALL BY SCOTT;
=>AUDIT ALL Statements when issued by SCOTT user.
SQL> NOAUDIT ALL by SCOTT;
SQL> NOAUDIT ALL;
=>Will not audit System privileges (Will not affect on OBJECT privilege auditing).
SQL> audit ALL PRIVILEGES;
=>Audit ALL SYSTEM PRIVILEGES <Not RECOMMENDED>.
SQL> NOAUDIT ALL PRIVILEGES;
=>Don't audit ANY SYSTEM PRIVILEGES <Not RECOMMENDED>.
The following are some auditing techniques:
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Turning ON SYSTEM PRIVILEGES auditing as per 11g settings: <secconf.sql script>
------------------------------------------------------------------------------
Audit ALTER ANY TABLE by access;
Audit CREATE ANY TABLE by access;
Audit DROP ANY TABLE by access;
Audit CREATE ANY PROCEDURE by access;
Audit DROP ANY PROCEDURE by access;
Audit ALTER ANY PROCEDURE by access;
Audit GRANT ANY PRIVILEGE by access;
Audit GRANT ANY OBJECT PRIVILEGE by access;
Audit GRANT ANY ROLE by access;
Audit AUDIT SYSTEM by access;
Audit CREATE EXTERNAL JOB by access;
Audit CREATE ANY JOB by access;
Audit CREATE ANY LIBRARY by access;
Audit CREATE PUBLIC DATABASE LINK by access;
Audit EXEMPT ACCESS POLICY by access;
Audit ALTER USER by access;
Audit CREATE USER by access;
Audit ROLE by access;
Audit CREATE SESSION by access;
Audit DROP USER by access;
Audit ALTER DATABASE by access;
Audit ALTER SYSTEM by access;
Audit ALTER PROFILE by access;
Audit DROP PROFILE by access;
Audit DATABASE LINK by access;
Audit SYSTEM AUDIT by access;
Audit PROFILE by access;
Audit PUBLIC SYNONYM by access;
Audit SYSTEM GRANT by access;
Extreme Audit (All DDL): (audit by Statemtnt)
-----------------------
Audit RESOURCE; --ALTER SYSTEM, CLUSTER, DATABASE LINK, PROCEDURE, ROLLBACK SEGMENT, SEQUENCE, SYNONYM, TABLE, TABLESPACE, VIEW.
Audit CLUSTER; --CREATE, DROP, ALTER, TRUNCATE CLUSTER.
Audit CONTEXT; --CREATE, DROP CONTEXT.
Audit DATABASE LINK; --CREATE, DROP DATABASE LINK.
Audit DIMENSION; --CREATE, DROP, ALTER DIMENSION.
Audit DIRECTORY; --CREATE, DROP DIRECTORY.
Audit INDEX; --CREATE, DROP, ALTER, ANALYZE INDEX.
Audit MATERIALIZED VIEW;--CREATE, DROP, ALTER MATERIALIZED VIEW.
Audit OUTLINE; --CREATE, DROP, ALTER OUTLINE.
Audit PROCEDURE; --CREATE FUNCTION/PACKAGE/PACKAGE BODY/PROCEDURE/LIBRARY, DROP FUNCTION/PACKAGE/PACKAGE BODY/PROCEDURE/LIBRARY.
Audit PROFILE; --CREATE, DROP, ALTER PROFILE.
Audit PUBLIC DATABASE LINK; --CREATE, DROP PUBLIC DATABASE LINK.
Audit PUBLIC SYNONYM; --CREATE, DROP PUBLIC SYNONYM.
Audit ROLE; --CREATE, DROP, ALTER, SET ROLE.
Audit ROLLBACK SEGMENT; --CREATE, DROP, ALTER ROLLBACK SEGMENT.
Audit SEQUENCE; --CREATE, DROP SEQUENCE.
Audit SESSION; --LOGIN / LOGOFF. Audit specific users=> "AUDIT SESSION BY jeff, scott;"
Audit SYNONYM; --CREATE, DROP SYNONYM.
Audit SYSTEM AUDIT; --AUDIT, NOAUDIT.
Audit SYSTEM GRANT; --GRANT SYSTEM PRIVILEGES/ROLES TO USER/ROLE, REVOKE SYSTEM PRIVILEGES/ROLES FROM USER/ROLE
Audit TABLE; --CREATE, DROP, TRUNCATE TABLE. <<<No ALTER>>>
Audit TABLESPACE; --CREATE, DROP, ALTER TABLESPACE.
Audit TRIGGER; --CREATE, DROP, ALTER TRIGGER, ALTER TABLE ENABLE/DISABLE TRIGGER.
Audit TYPE;
Audit USER; --CREATE, DROP, ALTER USER.
Audit VIEW; --CREATE, DROP VIEW.
Audit ALTER SEQUENCE;
Audit ALTER TABLE;
Audit DELETE TABLE; --DELETE FROM TABLE/VIEW.
Audit COMMENT TABLE;
Audit GRANT DIRECTORY; --GRANT, REVOKE on DIRECTORY.
Audit GRANT PROCEDURE; --GRANT, REVOKE on PROCEDURE.
Audit GRANT SEQUENCE; --GRANT, REVOKE on SEQUENCE.
Audit GRANT TABLE; --GRANT, REVOKE on TABLE/VIEW/MATERIALIZED VIEW.
Audit GRANT TYPE; --GRANT, REVOKE on TYPE.
Audit NOT EXISTS; --audit SQL stmts that fail because the target object does not exist.
Audit Everything: <Paranoied> "NOT RECOMMENDED AT ALL"
---------------------
SQL> Audit ALL PRIVILEGES;
SQL> Audit ALL STATEMENTS;
>Rollback:
SQL> NOAUDIT ALL PRIVILEGES;
SQL> NOAUDIT ALL;
Note: Above statements will audit SYSTEM PRIVILEGES when been used successfully or when they generated an error,
You limit this behavior by specifying audit mode SUCCESSFUL/ NOT SUCCESSFUL;:
e.g.
SQL> Audit table WHENEVER NOT SUCCESSFUL;
-->This will audit all failed SYSTEM PRIVILEGES commands against any table in the DB for all users.
Checking SYSTEM PRIVILEGES audit data: (DBA_PRIV_AUDIT_OPTS)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
SQL> select user_name,PRIVILEGE,success,failure from DBA_PRIV_AUDIT_OPTS;
USER_NAME PRIVILEGE SUCCESS FAILURE
------------------------------ ---------------------------------------- ---------- ----------
CREATE ANY TRIGGER BY ACCESS BY ACCESS
DROP ANY TABLE BY ACCESS BY ACCESS
SYSADM SELECT ANY TABLE BY SESSION BY SESSION
AA SELECT ANY TABLE BY SESSION BY SESSION
UPDATE ANY TABLE BY ACCESS BY ACCESS
.....
Note: Blank field under USER_NAME means "audit for all users", that means "ALTER ANY TABLE" privilege will be audited for all users who will use it whenever successful or not successful.
Note: By default, some of SYSTEM PRIVILEGES are being audited, even if you didn't audit them manually.
########################################################################
Object Privileges Auditing:
########################################################################
> Audit object privileges like [select, insert, update, delete, execute, alter(OBJECT>table,view,..), audit, grant(on OBJ), locate, rename]
on an object when used by ALL USERS (Except the ones connected with SYSDBA).
> Audit data can be found in "DBA_OBJ_AUDIT_OPTS" view.
> Audit records stored in SYS.AUD$ table can be viewed in DBA_AUDIT_TRAIL view.
> Auditing should be enabled on the database by setting the parameter "AUDIT_TRAIL" to value other than "NONE".
AUDIT specific privileges on specific table:
SQL> AUDIT SELECT,update ON scott.emp;
=>Audit any user try USE PRIVILEGE SELECT or UPDATE on scott.emp (Except SYSDBA user).
SQL> AUDIT ALL ON scott.emp;
=>Audit any user try USE any OBJECT PRIVILEGE on scott.emp (Except SYSDBA user).
SQL> NOAUDIT SELECT,update ON scott.emp;
=>Remove specific OBJECT PRIVIEGES auditing on scott.emp.
SQL> NOAUDIT ALL ON scott.emp;
=>Remove ALL OBJECT PRIVIEGES auditing on scott.emp.
Audit specific privileges on all tables inside a schema:
i.e. audit (DROP, TRUNCATE, ALTER) privilege on all tables under specific schema:
select 'AUDIT AUDIT,ALTER ON '||owner||'.'||table_name||' BY ACCESS;' from dba_tables where owner=upper('&USERNAME');
Extreme Object Privilege AUDITING: <Not RECOMMENDED>
===========================
SQL> AUDIT SELECT TABLE; --SELECT FROM table/view/materialized view.
SQL> AUDIT INSERT TABLE; --INSERT INTO table/view.
SQL> AUDIT UPDATE TABLE; --UPDATE TABLE/VIEW.
SQL> AUDIT LOCK TABLE; --LOCK TABLE table/view.
SQL> AUDIT SELECT SEQUENCE; --Any statement containing sequence.CURRVAL or sequence.NEXTVAL.
Checking OBJECT PRIVILEGES audit data: (DBA_OBJ_AUDIT_OPTS)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
SQL> select * from DBA_OBJ_AUDIT_OPTS;
OWNER OBJECT_NAME OBJECT_TYPE ALT AUD COM DEL GRA IND INS LOC
------------------------------ ------------------------------ ----------------------- --- --- --- --- --- --- --- ---
REN SEL UPD REF EXE CRE REA WRI FBK
--- --- --- --- --- --- --- --- ---
AA AA TABLE -/- -/- -/- -/- -/- -/- -/- -/-
-/- A/A -/- -/- -/- -/- -/- -/- -/-
.....
Note: There is no OBJECT PRIVILEGES are being audited by default. they need to be setup manually.
Note: In case you will query DBA_AUDIT_TRAIL, ACTION_NAME FOR OBJECT Privileges will be named as "SESSION REC".
View Audit data: <SYSTEM & OBJECT Privileges Auditing>
============
All Audit data: DBA_AUDIT_TRAIL
-----------------
SQL>
set linesize 160
col OS_USERNAME for a15
col USERNAME for a15
col TERMINAL for a15
col ACTION_NAME for a9
col EXTENDED_TIMESTAMP for a36
col USERHOST for a21
col owner for a15
col OBJ_NAME for a30
col ACTION_NAME for a15
col COMMENT_TEXT for a30
select
OS_USERNAME,
USERNAME,
ACTION_NAME,OWNER,OBJ_NAME,
EXTENDED_TIMESTAMP from dba_audit_trail where username='SCOTT';
Session LOGIN/LOGOFF data:
-------------------------------------
>Failed logins in the last 24Hours:
>>>>>>>>>>>>>>>>>>>>>>>>
SQL>
set linesize 170
col OS_USERNAME for a15
col USERNAME for a15
col TERMINAL for a15
col ACTION_NAME for a9
col EXTENDED_TIMESTAMP for a40
col USERHOST for a21
col COMMENT_TEXT for a40
select /*+ parallel 2 */ EXTENDED_TIMESTAMP,OS_USERNAME,USERNAME,TERMINAL,USERHOST,ACTION_NAME,COMMENT_TEXT
from DBA_AUDIT_TRAIL
where returncode = 1017
and timestamp > (sysdate -1)
order by 1;
>Successful logins during a period of time: -Last 6Hours-
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
SQL>
set linesize 170
col OS_USERNAME for a15
col USERNAME for a15
col TERMINAL for a15
col ACTION_NAME for a9
col EXTENDED_TIMESTAMP for a40
col USERHOST for a21
col COMMENT_TEXT for a40
select /*+ parallel 2 */ EXTENDED_TIMESTAMP,
--OS_USERNAME,
USERNAME,TERMINAL
,COMMENT_TEXT,
ACTION_NAME from DBA_AUDIT_TRAIL
where
EXTENDED_TIMESTAMP > (SYSDATE-1/4);
Failed statements due to "XXX does not exist" Error: DBA_AUDIT_EXISTS
--------------------------------------------------------------
SQL>
set linesize 170
col OS_USERNAME for a15
col USERNAME for a15
col TERMINAL for a15
col ACTION_NAME for a9
col EXTENDED_TIMESTAMP for a36
col USERHOST for a21
col owner for a15
col OBJ_NAME for a20
col ACTION_NAME for a15
col COMMENT_TEXT for a30
select
OS_USERNAME,
USERNAME,
sql_text,
USERHOST,
ACTION_NAME,OWNER,OBJ_NAME,
COMMENT_TEXT,
EXTENDED_TIMESTAMP from DBA_AUDIT_EXISTS where username='AA' order by EXTENDED_TIMESTAMP;
Auditing in a Multitier Environment:
--------------------------------------------
In a multitier environment, Oracle preserves the identity of the client through all tiers, which enables auditing of actions taken on behalf of the client. To do such auditing:
Ex:
SQL> AUDIT SELECT TABLE BY appserve ON BEHALF OF jackson/ANY;
-->audits SELECT TABLE statements issued on behalf of client Jackson by the proxy application server appserver.
Auditing SYS.AUD$:
-------------------------
If you want to audit SYS.AUD$ table against modification by any NON-SYSDBA user:
SQL> AUDIT select, update, delete on sys.aud$;
If a NON-SYSDBA user tries to delete records from SYS.AUD$, the delete will succeed, however, the last delete action will be recorded in SYS.AUD$.
########################################################################
Fine Grained Auditing [FGA]:
########################################################################
> You can check FGA Audit Policies by querying "DBA_AUDIT_POLICIES" view.
> Audit records stored in SYS.FGA_LOG$ table can be viewed in DBA_FGA_AUDIT_TRAIL view.
> FGA Auditing is a sort of OBJECT PRIVILEGES AUDITING but creating a policy to audit ONLY (SELECT, INSERT, UPDATE, DELETE) on (Tables, Views)
and it can narrow the scope of auditing to audit columns instead of full table and audit specific conditions.
It can also execute a specific procedure [like sending an email] when audit conditions are met.
> FGA is very powerful in auditing columns holding sensitive data [e.g. SAL, COMM columns in EMP table].
To start using FGA first create the audit policy:
Create Policy Examples:
=================
--<Audit "select statement" on all table DEPT columns>
SQL>
begin
dbms_fga.add_policy
(object_schema=>'SCOTT',
OBJECT_NAME=>'DEPT',
POLICY_NAME=>'POLICY1',
enable => TRUE,
STATEMENT_TYPES=>'select');
end;
/
--<Audit "select" on specific columns on table>
SQL>
begin
dbms_fga.add_policy
(object_schema=>'SCOTT',
OBJECT_NAME=>'EMP',
POLICY_NAME=>'EMP_POLICY1',
STATEMENT_TYPES=>'select',
enable => TRUE,
audit_column=>'sal,comm');
end;
/
--<Audit "select" on table if depno 10 been selected>
SQL>
begin
dbms_fga.add_policy
(object_schema=>'SCOTT',
OBJECT_NAME=>'EMP',
POLICY_NAME=>'POLICY3',
STATEMENT_TYPES=>'select',
enable => TRUE,
audit_condition=>'deptno=10');
end;
/
=> It's not a must that SQL statement include "WHERE DEPTNO=10", but if DEPTNO 10 been queried in any statement audit will fire.
Following queries will fire the audit:
SQL> select * from emp;
SQL> select count(*) from emp;
The following query will NOT fire the audit:
SQL> select ename from emp;
SQL> select ename from emp where deptno>10;
--<Audit when salary column being queried in deptno=10 plus running a procedure>:
SQL>
begin
dbms_fga.add_policy (
object_schema => 'scott',
object_name => 'emp',
policy_name => 'audit_emp_salary',
audit_condition=> 'deptno=10',
audit_column => 'salary',
handler_schema => 'sec',
handler_module => 'log_emps_salary',
enable => TRUE,
statement_types=> 'select' );
end;
/
handler_module=> Any DB procedure executes when audit happens to do additional actions, like sending an e-mail to the DBA.
Notes:
In case you created an audit policy successfully but it cannot capture any audit data, this may be due to:
– The audit condition is invalid
– The event handler does not exist or is invalid
Disable Policy:
===========
SQL>
begin
dbms_fga.disable_policy
(object_schema=>'SCOTT',
OBJECT_NAME=>'EMP',
POLICY_NAME=>'emp_POLICY1');
end;
/
Enable Policy:
==========
SQL>
begin
dbms_fga.enable_policy
(object_schema=>'SCOTT',
OBJECT_NAME=>'EMP',
POLICY_NAME=>'emp_POLICY1');
end;
/
Drop Policy:
=========
SQL>
begin
dbms_fga.drop_policy
(object_schema=>'SCOTT',
OBJECT_NAME=>'EMP',
POLICY_NAME=>'EMP_POLICY1');
end;
/
View All FGA policies: DBA_AUDIT_POLICIES
=================
SQL> Select * from DBA_AUDIT_POLICIES;
SQL> Select POLICY_NAME,ENABLED,OBJECT_SCHEMA,OBJECT_NAME,POLICY_COLUMN from DBA_AUDIT_POLICIES;
Note: POLICY_COLUMN sometimes shows only one column (usually the first one in the policy).
View FGA Audit data: DBA_FGA_AUDIT_TRAIL
================
SQL>
set linesize 170
col USERHOST for a30
col sql_text for a70
select Policy_name,db_user,to_char(timestamp,'DD-MM-YYYY HH24:MI:SS') as time,userhost,sql_bind,sql_text
from dba_fga_audit_trail;
--where policy_name='POLICY2';
###########################################################
AUDIT MAINTENANCE:
###########################################################
Moving the Database Audit Tables outside SYSTEM Tablespace:
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
(SYS.AUD$ & SYS.FGA_LOG$) tables can be moved from SYSTEM tablespace to any tablespace using following procedure:
Why Moving Audit Tables?:
---------------------------------
It's important to move audit tables from SYSTEM tablespace to an auto extensible tablespace on separate disks to:
>Eliminate DB hang if audit tables are full.
>Improve DB performance by decrease the I/O's on SYSTEM TBS.
CAUTION:
*********
This operation will cause the auditing to hang the thing will lead the whole database to hang, perform this job during downtime window.
Move Procedure:
============
SQL>
BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, --Move AUD$ only
--AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD, --Move FGA_LOGS$ only
--AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD, --Move Both AUD$ & FGA_LOGS$
AUDIT_TRAIL_LOCATION_VALUE => 'TS_USER_DATA_01'); --New Tablespace
END;
/
Checking audit trail options: (TBS,max_size,age)
---------------------------------
SQL>
col PARAMETER_NAME for a30
col PARAMETER_VALUE for a30
col AUDIT_TRAIL for a30
select * from DBA_AUDIT_MGMT_CONFIG_PARAMS;
Backup & Purge Audit tables:
########################
Create a job (DB or OS)
Run this sql with changing the number of days you want to keep: here one year data will retain (Aramco standards)
Backing up the historical audit data to a DB table:
-----------------------------------------------------------
SQL> Create table aud$_backup compress tablespace audit2 as select * from AUD$ where ntimestamp# < sysdate-365;
SQL> Create table fga_log$_backup compress tablespace audit2 as select * from FGA_LOG$ where ntimestamp# < sysdate-365;
SQL> Delete from aud$ where ntimestamp#< sysdate-365;
SQL> COMMIT;
SQL> Delete from FGA_LOG$ where ntimestamp# < sysdate-365;
SQL> COMMIT;
Or Export the historical audit data to a dumpfile:
---------------------------------------------------------
# exp \'\/ as sysdba \' TABLES=SYS.AUD$ QUERY=\"where NTIMESTAMP#\<\(\'01\-Jan\-13\'\)\" STATISTICS=none FILE=/backup/AUD_OLDER_JAN2013.dmp log=/backup/exp_AUD_OLDER_JAN2013.log
SQL> delete from sys.aud$ where ntimestamp# < ('01-Jan-2013');
SQL> COMMIT;
Do the same for FGA_LOG$
Note: Exporting a system table like SYS.AUD$ should be done using EXP utility, you cannot use EXPDP utility to export tables under SYS schema.
Freez/UnFreez Auditing using ORADEBUG:
With using ORADEBUG you can freeze the auditing on the database including audit features like Audit SYS operations and Vault
without the need of restarting the instance:
-- Get the Audit process address:
SQL> select fsv.KSMFSNAM,sga.* from x$ksmfsv fsv, x$ksmmem sga where sga.addr=fsv.KSMFSADR and fsv.ksmfsnam like 'kzaflg_%';
KSMFSNAM ADDR INDX INST_ID KSMMMVAL
------------------------------ ---------------- ---------- ---------- ----------------
kzaflg_ 0000000060036ED8 28123 1 0000000000000001
Freeze Auditing using above address: <The poke command will be written to the audit trail>
SQL> oradebug setmypid
SQL> oradebug poke 0x60036ED8
1 0
BEFORE: [060036ED8, 060036EDC) = 00000001
AFTER: [060036ED8, 060036EDC) = 00000000
UN-Freeze Auditing:
SQL> oradebug setmypid
SQL> oradebug poke 0x60036ED8
1 1
BEFORE: [060036ED8, 060036EDC) = 00000000
AFTER: [060036ED8, 060036EDC) = 00000001
Note: The only way to avoid having ORADEBUG to do such powerful activities which can compromise the security on your database is by setting parameter "_fifteenth_spare_parameter" to "
restricted" to restrict powerful ORADEBUG commands like "poke" or by setting it to "
all" to disable
all ORADEBUG commands, "
NONE" is the default which allows everything.
