CYA Auditing

Enable Auditing

-- Run as SYSDBA

-- Enable Auditing
ALTER SYSTEM SET AUDIT_TRAIL=DB SCOPE=SPFILE;
SHUTDOWN IMMEDIATE
STARTUP

Set Auditing Configuration

-- Reset auditing
-- Statements
NOAUDIT ALL; 
-- Privileges
NOAUDIT ALL PRIVILEGES; 
 -- Objects
NOAUDIT ALL ON DEFAULT;
-- Network
NOAUDIT NETWORK;



-- Turn on session auditing LOGON / LOGOFF
AUDIT SESSION;


-- User changes
AUDIT USER;


-- Audit role creation/grants
AUDIT ROLE;
AUDIT GRANT ANY ROLE BY ACCESS;
AUDIT ALTER, GRANT ON DEFAULT;
AUDIT SYSTEM GRANT;

-- Audit Grant of Sys Privileges
AUDIT GRANT ANY PRIVILEGE BY ACCESS;
AUDIT GRANT ANY OBJECT PRIVILEGE BY ACCESS;

-- Audit Object Creation/ALTER/Drop
AUDIT DATABASE LINK;
AUDIT PUBLIC DATABASE LINK;

AUDIT DIRECTORY;
AUDIT INDEX;
AUDIT MATERIALIZED VIEW;
AUDIT PROCEDURE;
AUDIT TABLE;
AUDIT PROFILE;
AUDIT TRIGGER;
AUDIT VIEW;
AUDIT CREATE ANY JOB;
AUDIT ALTER DATABASE;
AUDIT ALTER SYSTEM;




-- Grant/Revoke on Future objects
audit grant any object privilege by access;
audit grant any privilege by access;
audit grant any role by access;
audit system grant;

-- Grant/Revoke on Present Tables
SET SERVEROUTPUT ON
DECLARE
  myCountSuccess NUMBER := 0;
  myCountFailure NUMBER := 0;
BEGIN

  FOR mycurs IN (select 'audit grant on '||owner||'.'||table_name as sqlstmt from dba_tables WHERE OWNER<>'SYS') LOOP
    BEGIN
      execute immediate mycurs.sqlstmt;
      myCountSuccess := myCountSuccess + 1;
    EXCEPTION
      WHEN OTHERS THEN
        myCountSuccess := myCountSuccess - 1;
        myCountFailure := myCountFailure + 1;
        DBMS_OUTPUT.PUT_LINE(mycurs.sqlstmt);
    END;
    
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Success'||myCountSuccess||'   Failure:'||myCountFailure);

END;
/

Set up purge jobs

-- Drop all pending jobs
SET SERVEROUTPUT ON
DECLARE
  X NUMBER;
  Cnt NUMBER;
BEGIN
  FOR cur IN (SELECT JOB
                FROM DBA_JOBS 
               WHERE WHAT LIKE '%Q_AUDIT_PURGE%' OR WHAT LIKE '%Q_AUDIT_PURGE8I9I%')
  LOOP
    SYS.DBMS_JOB.REMOVE( cur.JOB );
    DBMS_OUTPUT.PUT_LINE('Dropping job: '||cur.JOB);
  END LOOP;
  COMMIT;
END;
/

DROP PROCEDURE AUDIT_PURGE;

CREATE OR REPLACE PROCEDURE SYS.AUDIT_PURGE IS 

-- Purpose: Purge audit records from SYS.AUD$ 
-- Author : David Mann
-- Changes: Listed in chronological order: 
--          15-NOV-2010 - Created initial version
--          02-JUN-2011 - Reworked procedure to set archive at 7 days and 
--                        make proc 8i,9i,10g,11g compatible
--                        8i9i - timestamp# - date
--                        10g11g - ntimestamp# - timestamp

  -- Number of days to retain
  myRetainDays  NUMBER := 7;
  mySQL VARCHAR2(80);
  myVersion VARCHAR2(30);

BEGIN

  select substr(version,1,1)
    into myVersion
    from v$instance;

  -- Difference compare column used for 8i9i / 10g11g
  IF myVersion in ('8','9') THEN
    mySQL := 'DELETE FROM sys.aud$ WHERE timestamp# < (SYSDATE-'||myRetainDays||')';
  ELSE
    mySQL := 'DELETE FROM sys.aud$ WHERE ntimestamp# < (SYSDATE-'||myRetainDays||')';
  END IF;

  EXECUTE IMMEDIATE mySQL;
  COMMIT;

END;
/



-- Create job
SET SERVEROUTPUT ON
DECLARE
  X NUMBER;
  Cnt NUMBER;
BEGIN
  SELECT COUNT(*) INTO Cnt FROM DBA_JOBS WHERE WHAT LIKE '%AUDIT_PURGE%';
  
  IF CNT = 0 THEN

    SYS.DBMS_JOB.SUBMIT
        ( job       => X,
          what      => 'BEGIN
  SYS.Q_AUDIT_PURGE;
END;'
   ,next_date => SYSDATE
   ,interval  => 'TRUNC(SYSDATE+1)+2/24'
   ,no_parse  => FALSE
  );
  SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
  END IF;
COMMIT;
END;
/

Review Auditing Configuration

-- Ensure DB is one of the Audit Trail destinations
SELECT VALUE FROM V$PARAMETER WHERE NAME='audit_trail';


-- View overall Auditing Options
SELECT * FROM DBA_PRIV_AUDIT_OPTS; 


-- View Object Specific Auditing Options
-- Object-level auditing. The value of each privilege column is a code for / where the '/' is 
-- constant and the option value codes are '-' = no audit, 'A' audit by access, 'S' = audit by session. So a SEL value of '-/S' 
-- means "create an audit event, summarized by session, when any session attempts to SELECT from this object and failes due to 
-- not having SELECT privileges". 
SELECT * FROM DBA_OBJ_AUDIT_OPTS;


-- Verify Fine Grained Auditing does not have any policies defined or enabled
SELECT * FROM DBA_AUDIT_POLICIES; 

Turning off auditing

SET SERVEROUTPUT ON
DECLARE
  myCountSuccess NUMBER := 0;
  myCountFailure NUMBER := 0;
BEGIN
 
  FOR mycurs IN (select 'noaudit all on '||owner||'.'||object_name as sqlstmt from DBA_OBJ_AUDIT_OPTS) LOOP
    BEGIN
      execute immediate mycurs.sqlstmt;
      myCountSuccess := myCountSuccess + 1;
    EXCEPTION
      WHEN OTHERS THEN
        myCountSuccess := myCountSuccess - 1;
        myCountFailure := myCountFailure + 1;
        DBMS_OUTPUT.PUT_LINE(mycurs.sqlstmt);
    END;
     
  END LOOP;
 
  DBMS_OUTPUT.PUT_LINE('Success'||myCountSuccess||'   Failure:'||myCountFailure);
 
END;
/