/*
|| Oracle 10g Security Listing 1
||
|| Demonstrates new Oracle 10gR2 Security features, including:
|| - Setting up Oracle 10g Fine Grained Auditing (FGA) features
|| - Monitoring enforcement of FGA and standard auditing features
||
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| This script is provided to demonstrate various features of Oracle 10g
|| security enhancements, and it should be carefully proofread before
|| executing it against any existing Oracle database to insure that no
|| potential damage can occur.
||
*/

/* 
|| Listing 1.1: Create a prototype Accounts Payable (AP) system and populate
|| it with test data
*/

@APSetup.sql;
@APInitialization.sql;
@APBatchProcessing.sql;
@sp_fga_handler.prc;

/* 
|| Listing 1.2: Create Fine-Grained Auditing (FGA) Policies
*/

-----
-- Create FGA policy VENDORS_LO, which audits:
--  1.) Table AP.VENDORS
--  2.) Columns ACTIVE_IND, CREDIT_CARD, +or+ CREDIT_LIMIT
--  3.) SELECT SQL statements only
--  4.) Inactive vendors only
-- Note that this policy is initially ENABLED.
-----
BEGIN
    DBMS_FGA.ADD_POLICY(
         object_schema => 'AP'
        ,object_name => 'VENDORS'
        ,policy_name => 'VENDORS_LO'
        ,audit_condition => 'ACTIVE_IND <> ''Y'''
        ,audit_column => 'ACTIVE_IND,CREDIT_CARD,CREDIT_LIMIT'
        ,handler_schema => NULL
        ,handler_module => NULL
        ,enable => TRUE
        ,statement_types => 'SELECT'
        ,audit_trail => DBMS_FGA.DB_EXTENDED
        ,audit_column_opts => DBMS_FGA.ANY_COLUMNS
    );
END;
/

-----
-- Create FGA policy VENDORS_HI, which audits:
--  1.) Table AP.VENDORS
--  2.) Columns CREDIT_CARD +or+ CREDIT_LIMIT
--  3.) INSERT and UPDATE SQL statements only
--  4.) Unconditionally
-- Note that this policy is initially ENABLED.
-----
BEGIN
    DBMS_FGA.ADD_POLICY(
         object_schema => 'AP'
        ,object_name => 'VENDORS'
        ,policy_name => 'VENDORS_HI'
        ,audit_condition => NULL
        ,audit_column => 'CREDIT_CARD,CREDIT_LIMIT'
        ,handler_schema => NULL
        ,handler_module => NULL
        ,enable => TRUE
        ,statement_types => 'INSERT,UPDATE'
        ,audit_trail => DBMS_FGA.DB_EXTENDED
        ,audit_column_opts => DBMS_FGA.ANY_COLUMNS
    );
END;
/

-----
-- Add FGA policy INVOICES_HI. It audits:
--  1.) Table AP.INVOICES
--  2.) INVOICE_TYPE column changes
--  3.) UPDATE statements only
--  4.) With no conditions
-- Note that this policy is initially ENABLED.
-----
BEGIN
    DBMS_FGA.ADD_POLICY(
         object_schema => 'AP'
        ,object_name => 'INVOICES'
        ,policy_name => 'INVOICES_HI'
        ,audit_condition => NULL
        ,audit_column => 'INVOICE_TYPE'
        ,handler_schema => NULL
        ,handler_module => NULL
        ,enable => TRUE
        ,statement_types => 'UPDATE'
        ,audit_trail => DBMS_FGA.DB_EXTENDED
        ,audit_column_opts => DBMS_FGA.ANY_COLUMNS
    );
END;
/

-----
-- Add FGA policy RV_INVOICE_LO. It will audit:
--  1.) View AP.RV_INVOICE_DETAILS
--  2.) ALL AP columns listed
--  3.) SELECT SQL statements only
--  4.) Unconditionally
-- Note that this policy is initially DISABLED. It will
-- need to be ENABLED before it records any transactions.
-----
BEGIN
    DBMS_FGA.ADD_POLICY(
         object_schema => 'AP'
        ,object_name => 'RV_INVOICE_DETAILS'
        ,policy_name => 'RV_INVOICE_LO'
        ,audit_condition => NULL
        ,audit_column => 'VENDOR_NAME,INVOICE_ID,EXTENDED_AMT,VENDOR_CREDIT_LIMIT'
        ,handler_schema => NULL
        ,handler_module => NULL
        ,enable => FALSE
        ,statement_types => 'SELECT'
        ,audit_trail => DBMS_FGA.DB_EXTENDED
        ,audit_column_opts => DBMS_FGA.ALL_COLUMNS
    );
END;
/

-----
-- Show all currently active FGA Policies in the database
-----
TTITLE 'Currently Active FGA Policies|(From DBA_AUDIT_POLICIES)'
COL object_schema	FORMAT A10      HEADING 'Object|Schema'
COL object_name     FORMAT A20      HEADING 'Object Name' WRAP
COL policy_name     FORMAT A16      HEADING 'Policy Name' WRAP
COL policy_text     FORMAT A24      HEADING 'Policy Text' WRAP
COL policy_column   FORMAT A16      HEADING 'Policy Column' WRAP
COL enabled         FORMAT A05      HEADING 'On?'
COL siud_options    FORMAT A04      HEADING 'SIUD|Set'
SELECT 
     policy_name
    ,policy_text
    ,policy_column
    ,enabled
    ,object_schema
    ,object_name
    ,DECODE(sel,'YES','Y','N') || DECODE(ins,'YES','Y','N')|| 
     DECODE(upd,'YES','Y','N') || DECODE(del,'YES','Y','N') siud_options
  FROM dba_audit_policies
;
TTITLE OFF

Mon Oct 23                                                                                           page    1

                                        Currently Active FGA Policies
                                          (From DBA_AUDIT_POLICIES)

                                                                 Object                          SIUD
Policy Name      Policy Text              Policy Column    On?   Schema     Object Name          Set
---------------- ------------------------ ---------------- ----- ---------- -------------------- ----
TEX_EMPS_RO                               LAST_NAME        YES   HR         BV_TEX_EMPS          YNNN
VENDORS_LO       ACTIVE_IND <> 'Y'        ACTIVE_IND       YES   AP         VENDORS              YNNN
VENDORS_HI                                CREDIT_CARD      YES   AP         VENDORS              NYYN
INVOICES_HI                               INVOICE_TYPE     YES   AP         INVOICES             NNYN
RV_INVOICE_LO                             VENDOR_NAME      YES   AP         RV_INVOICE_DETAILS   YNNN

5 rows selected.

/* 
|| Listing 1.3: Unit-testing the FGA Policies
*/

-----
-- These statements will be audited because the selection criteria
-- against AP.VENDORS.ACTIVE_IND specified in auditing policy
-- AP.VENDORS_LO is present:
-----
SELECT name, credit_card, credit_limit
  FROM ap.vendors
 WHERE active_ind <> 'Y';

SELECT *
  FROM ap.vendors
WHERE active_ind <> 'Y';

-----
-- This INSERT statement will be audited because it references the
-- columns listed in auditing policy AP.VENDORS_HI. Note that the 
-- auditing occurs even though the INSERT statement is rolled back!
-----
INSERT INTO ap.vendors 
VALUES(
     201
    ,'Y'
    ,'Cranium Imitation Products GmBH'
    ,'c/o BodyParts Limited, Inc. '
    ,'2202 Dr. Acula Drive'
    ,NULL
    ,'Hollywood'
    ,'CA'
    ,3728760140003780
    ,45500.00
);

ROLLBACK;

-----
-- These UPDATE statements will be audited because they reference
-- the columns listed in auditing policy AP.VENDORS_HI.
-----
BEGIN
    UPDATE ap.vendors
       SET
         credit_card = 7890784410003809
        ,credit_limit = 20000.00
     WHERE vendor_id = 201;
       
    UPDATE ap.vendors
       SET
         active_ind = 'Y'
     WHERE vendor_id = 201;
     
    C0MMIT;
END;
/

-----
-- These UPDATE statements will be audited because they reference
-- the INVOICE_TYPE column as specified in auditing policy 
-- AP.INVOICES_HI.
-----
BEGIN
    UPDATE ap.invoices
       SET invoice_type = 'D'
     WHERE invoice_id IN (501,502)
       AND invoice_type <> 'D';
       
    UPDATE ap.invoices
       SET invoice_type = 'C'
     WHERE invoice_id IN (501,502)
       AND invoice_type <> 'C';
       
END;
/

-----
-- This statement won't be audited because the selection criteria
-- against AP.VENDORS.ACTIVE_IND specified in auditing policy
-- AP.VENDORS_LO is not present
-----
SELECT *
  FROM ap.vendors
 WHERE active_ind = 'Y';

-----
-- This statement won't be audited because none of the columns
-- specified in auditing policy AP.VENDORS_HI are present
-----
UPDATE ap.vendors
   SET 
     name = 'Fright Night, Inc.'
    ,address_line_1 = '1313 Burial Plot Lane'
    ,city = 'Bethesda'
    ,state = 'MD'
 WHERE vendor_id = 105;

COMMIT;

/* 
|| Listing 1.4: Viewing FGA Audit Trails
*/

-----
-- Show Fine-Grained Auditing results so far
-----
TTITLE 'Current Fine-Grained Auditing (FGA) Results'
COL audit_date      FORMAT A10      HEADING 'Audit|Date'
COL policy_name     FORMAT A16      HEADING 'Policy Name' WRAP
COL object_schema	FORMAT A10      HEADING 'Object|Schema'
COL object_name     FORMAT A20      HEADING 'Object Name' WRAP
COL db_user      	FORMAT A10      HEADING 'DBUser'
COL sql_text        FORMAT A36      HEADING 'SQL Text' WRAP
SELECT
     TO_CHAR(timestamp,'mm/dd/yyyy hh24:mi:ss') audit_date
    ,db_user
    ,object_schema
    ,object_name
    ,policy_name
    ,sql_text
  FROM dba_fga_audit_trail
 ORDER BY timestamp
;
TTITLE OFF

Mon Oct 23                                                                                           page    1

                                 Current Fine-Grained Auditing (FGA) Results

Audit                 Object
Date       DBUser     Schema     Object Name          Policy Name      SQL Text
---------- ---------- ---------- -------------------- ---------------- ------------------------------------
10/22/2006 AP         AP         RV_INVOICE_DETAILS   RV_INVOICE_LO    select * from ap.rv_invoice_details
 12:58:27

10/22/2006 AP         AP         VENDORS              VENDORS_LO       select * from ap.vendors where activ
 12:59:56                                                              e_ind <> 'Y'

10/22/2006 AP         AP         VENDORS              VENDORS_HI       update ap.vendors set credit_limit=1
 13:00:54                                                              50000.00 where vendor_id=105

10/22/2006 AP         AP         VENDORS              VENDORS_HI         UPDATE ap.vendors
 17:05:21                                                                     SET
                                                                                credit_card = 7890784410003
                                                                       809
                                                                               ,credit_limit = 20000.00
                                                                            WHERE vendor_id = 201

10/22/2006 AP         AP         VENDORS              VENDORS_HI           UPDATE ap.vendors
 17:06:11                                                                     SET
                                                                                credit_card = 7890784410003
                                                                       809
                                                                               ,credit_limit = 20000.00
                                                                            WHERE vendor_id = 201

10/22/2006 AP         AP         VENDORS              VENDORS_LO       SELECT *
 17:11:09                                                                FROM ap.vendors

10/22/2006 AP         AP         INVOICES             INVOICES_HI      UPDATE AP.INVOICES SET INVOICE_TYPE
 17:25:50                                                              = 'D' WHERE INVOICE_ID IN (501,502)
                                                                       AND INVOICE_TYPE <> 'D'

10/22/2006 AP         AP         INVOICES             INVOICES_HI      UPDATE AP.INVOICES SET INVOICE_TYPE
 17:25:50                                                              = 'C' WHERE INVOICE_ID IN (501,502)
                                                                       AND INVOICE_TYPE <> 'C'


8 rows selected.

-----
-- Use the new combined audit trail view (DBA_COMMON_AUDIT_TRAIL)
-- to see results of both Standard (i.e. AUDIT) and Fine-Grained
-- Auditing (i.e. via DBMS_FGA)
-----
TTITLE 'Combined Standard and Fine-Grained Auditing (FGA) Results|(From DBA_COMMON_AUDIT_TRAIL)'
COL audtype         FORMAT A03      HEADING 'Aud|Typ'
COL db_user      	FORMAT A10      HEADING 'DBUser'
COL object_schema	FORMAT A06      HEADING 'Object|Schema'
COL object_name     FORMAT A20      HEADING 'Object Name' WRAP
COL policy_name     FORMAT A16      HEADING 'Policy Name' WRAP
COL audit_date      FORMAT A10      HEADING 'Audit|Date'
COL sql_text        FORMAT A32      HEADING 'SQL Text' WRAP

SELECT
     DECODE(audit_type,
            'Fine Grained Audit', 'FGA'
           ,'Standard Audit', 'STD'
           ,'UNK') audtype
    ,db_user
    ,object_schema
    ,object_name
    ,policy_name
    ,TO_CHAR(extended_timestamp,'mm/dd/yyyy hh24:mi:ss') audit_date
    ,sql_text
  FROM dba_common_audit_trail
 WHERE db_user NOT IN ('SYS','SYSTEM','DBSNMP','SYSMAN')
 ORDER BY extended_timestamp, db_user, object_schema, object_name
;
TTITLE OFF

Sun Oct 22                                                                                           page    1

                          Combined Standard and Fine-Grained Auditing (FGA) Results

Aud            Object                                   Audit
Typ DBUser     Schema Object Name      Policy Name      Date       SQL Text
--- ---------- ------ ---------------- ---------------- ---------- --------------------------------
FGA AP         AP     RV_INVOICE_DETAI RV_INVOICE_LO    10/22/2006 select * from ap.rv_invoice_deta
                      LS                                 12:58:27  ils

FGA AP         AP     VENDORS          VENDORS_LO       10/22/2006 select * from ap.vendors where a
                                                         12:59:56  ctive_ind <> 'Y'

FGA AP         AP     VENDORS          VENDORS_HI       10/22/2006 update ap.vendors set credit_lim
                                                         13:00:54  it=150000.00 where vendor_id=105

FGA AP         AP     VENDORS          VENDORS_HI       10/22/2006   UPDATE ap.vendors
                                                         17:05:21         SET
                                                                            credit_card = 789078441
                                                                   0003809
                                                                           ,credit_limit = 20000.00

                                                                        WHERE vendor_id = 201

FGA AP         AP     VENDORS          VENDORS_HI       10/22/2006     UPDATE ap.vendors
                                                         17:06:11         SET
                                                                            credit_card = 789078441
                                                                   0003809
                                                                           ,credit_limit = 20000.00

                                                                        WHERE vendor_id = 201

FGA AP         AP     VENDORS          VENDORS_LO       10/22/2006 SELECT *
                                                         17:11:09    FROM ap.vendors

FGA AP         AP     INVOICES         INVOICES_HI      10/22/2006 UPDATE AP.INVOICES SET INVOICE_T
                                                         17:25:49  YPE = 'D' WHERE INVOICE_ID IN (5
                                                                   01,502) AND INVOICE_TYPE <> 'D'

FGA AP         AP     INVOICES         INVOICES_HI      10/22/2006 UPDATE AP.INVOICES SET INVOICE_T
                                                         17:25:49  YPE = 'C' WHERE INVOICE_ID IN (5
                                                                   01,502) AND INVOICE_TYPE <> 'C'


8 rows selected.

/* 
|| Listing 1.5: FGA Policy Maintenance
*/

BEGIN

    -----
    -- Disabling an enabled, existing FGA policY
    -----
    DBMS_FGA.DISABLE_POLICY(
         object_schema => 'AP'
        ,object_name => 'INVOICES'
        ,policy_name => 'INVOICES_HI'
    );

    -----
    -- Dropping an enabled, existing FGA policY
    -----
    DBMS_FGA.DROP_POLICY(
        ,policy_name => 'RV_INVOICE_LOW'
        ,object_name => ' RV_INVOICE_DETAILS'
        ,policy_name => 'RV_INVOICE_LO'
    );

END;
/