/*
|| 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;
/