/*
|| Oracle 10g Security Listing 2
||
|| Demonstrates new Oracle 10gR2 Security features, including:
|| - Setting up Oracle 10g Virtual Private Database (VPD) features
|| - Building an application context function to enforce VPD security
|| - Monitoring enforcement of VPD security with V$VPD_SECURITY
||
|| 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 2.1: Creating new user accounts to control Virtual
|| Private Database functions
*/
-----
-- Create users for testing against Accounts Payable VPD policies:
-- APCLERK: Accounts Payable Clerk
-- APTLEAD: Accounts Payable Team Lead
-- APDIR: Accounts Payable Director
-----
DROP USER apclerk CASCADE;
CREATE USER apclerk
IDENTIFIED BY apclerk
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON example
QUOTA UNLIMITED ON users
PROFILE DEFAULT;
DROP USER aptlead CASCADE;
CREATE USER aptlead
IDENTIFIED BY aptlead
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON example
QUOTA UNLIMITED ON users
PROFILE DEFAULT;
DROP USER apdir CASCADE;
CREATE USER apdir
IDENTIFIED BY apdir
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON example
QUOTA UNLIMITED ON users
PROFILE DEFAULT;
GRANT CONNECT TO apclerk;
GRANT ap_secured TO apclerk;
GRANT CONNECT TO aptlead;
GRANT ap_secured TO aptlead;
GRANT CONNECT TO apdir;
GRANT ap_secured TO apdir;
/*
|| Recreate reporting view for read-only reporting against
|| AP prototype system to include AP.VENDORS.CREDIT_CARD
*/
CREATE OR REPLACE VIEW ap.rv_invoice_details (
vendor_name
,vendor_credit_card
,vendor_credit_limit
,invoice_id
,invoice_status
,line_id
,taxable_ind
,product_id
,qty
,price
,extended_amt
)
AS
SELECT
V.name
,V.credit_card
,V.credit_limit
,I.invoice_id
,I.active_ind
,ID.line_item_nbr
,ID.taxable_ind
,ID.product_id
,ID.qty
,ID.price
,ID.Extended_Amt
FROM
ap.vendors V
,ap.invoices I
,ap.invoice_items ID
WHERE ID.invoice_id = I.invoice_id
AND I.vendor_id = V.vendor_id
WITH READ ONLY
;
GRANT SELECT ON ap.rv_invoice_details TO ap_read_only;
GRANT ap_read_only TO sh;
/*
|| Listing 2.2: Creating an application security function to
|| implement Row-Level Security (RLS)
*/
CREATE OR REPLACE PACKAGE ap.app_security_context
/*
|| Package Specification: AP.APP_SECURITY_CONTEXT
|| Purpose: Specifies public interface to application
|| security contexts
*/
IS
PROCEDURE setuserinfo;
FUNCTION credit_limit (
object_schema IN VARCHAR2 DEFAULT NULL
,object_name IN VARCHAR2 DEFAULT NULL
)
RETURN VARCHAR2;
FUNCTION credit_memo (
object_schema IN VARCHAR2 DEFAULT NULL
,object_name IN VARCHAR2 DEFAULT NULL
)
RETURN VARCHAR2;
END;
/
CREATE OR REPLACE PACKAGE BODY ap.app_security_context
/*
|| Package Body: AP.APP_SECURITY_CONTEXT
|| Purpose: Implements public interface to application
|| security contexts
*/
IS
PROCEDURE setuserinfo
/*
|| Procedure: setuserinfo
|| Purpose: Sets up appropriate security information in the
|| VPD_CONTEXT context area based on values retrieved
|| during initial login to the database
*/
IS
username VARCHAR2(64);
action VARCHAR2(64);
module VARCHAR2(64);
service_name VARCHAR2(64);
client_info VARCHAR2(64);
cclimit NUMBER(9);
crdmemo VARCHAR2(3);
BEGIN
-----
-- Get user information from the USERENV context
-----
SELECT
SYS_CONTEXT ('USERENV','SESSION_USER')
,SYS_CONTEXT ('USERENV','ACTION')
,SYS_CONTEXT ('USERENV','MODULE')
,SYS_CONTEXT ('USERENV','SERVICE_NAME')
,SYS_CONTEXT ('USERENV','CLIENT_INFO')
INTO
username
,action
,module
,service_name
,client_info
FROM dual;
-----
-- Set the appropriate restrictions based on the user account
-----
CASE username
WHEN 'APCLERK' THEN
cclimit := 25000;
crdmemo := 'NO';
WHEN 'APTLEAD' THEN
BEGIN
cclimit := 150000;
crdmemo := 'YES';
END;
WHEN 'APDIR' THEN
BEGIN
cclimit := NULL;
crdmemo := 'YES';
END;
ELSE
BEGIN
cclimit := 0;
crdmemo := 'NO';
END;
END CASE;
-----
-- Set context values based on which user has logged on and
-- the business rules that have been applied
-----
DBMS_SESSION.SET_CONTEXT('vpd_context', 'cc_limit', cclimit);
DBMS_SESSION.SET_CONTEXT('vpd_context', 'crd_memo', crdmemo);
END setuserinfo;
FUNCTION credit_limit (
object_schema IN VARCHAR2 DEFAULT NULL
,object_name IN VARCHAR2 DEFAULT NULL
)
RETURN VARCHAR2
/*
|| Function: credit_limit
|| Purpose: Creates a predicate to be applied against a
|| query or DML statement to filter out any records
|| in AP.VENDORS with the specified credit limit
*/
IS
predicate VARCHAR2(4000);
BEGIN
IF SYS_CONTEXT('VPD_CONTEXT','CC_LIMIT') IS NOT NULL THEN
predicate := 'credit_limit <= ' || SYS_CONTEXT('VPD_CONTEXT','CC_LIMIT');
ELSE
predicate := NULL;
END IF;
RETURN predicate;
END credit_limit;
FUNCTION credit_memo (
object_schema IN VARCHAR2 DEFAULT NULL
,object_name IN VARCHAR2 DEFAULT NULL
)
RETURN VARCHAR2
/*
|| Function: credit_memo
|| Purpose: Creates a predicate to be applied against a
|| query or DML statement to filter out any records
|| in AP.INVOICES with a value of (D)ebit for the
|| INVOICE_TYPE column
*/
IS
predicate VARCHAR2(4000);
BEGIN
IF SYS_CONTEXT('VPD_CONTEXT','CRD_MEMO') IS NOT NULL THEN
IF SYS_CONTEXT('VPD_CONTEXT','CRD_MEMO') = 'NO' THEN
predicate := 'invoice_type <> ''D''';
ELSE
predicate := NULL;
END IF;
ELSE
predicate := NULL;
END IF;
RETURN predicate;
END credit_memo;
END;
/
-----
-- Grant execution privileges to security context package
-- for only those users who've been granted access to the
-- AP_SECURED role
-----
GRANT EXECUTE ON ap.app_security_context TO ap_secured;
/*
|| Listing 2.3: Implementing Row-Level Security (RLS) via
|| an AFTER LOGON trigger
*/
-----
-- Create a CONTEXT workspace to retain information in memory
-- about user sessions.
-----
DROP CONTEXT vpd_context;
CREATE CONTEXT vpd_context USING ap.app_security_context;
-----
-- Create a trigger that fires whenever a user establishes a
-- connection with the database. This trigger will post information
-- into context VPD_CONTEXT whenever a user session is deemed to
-- require the application of row-level security rules.
-----
CREATE OR REPLACE TRIGGER ap.on_logon
AFTER LOGON
ON DATABASE
-----
-- Trigger: AP.ON_LOGON
-- Firing: AFTER LOGON
-- Purpose: Implements VPD security
-----
BEGIN
-- Set appropriate security based on user's logon ID
CASE user
WHEN 'APDIR' THEN
AP.APP_SECURITY_CONTEXT.SETUSERINFO();
WHEN 'APCLERK' THEN
AP.APP_SECURITY_CONTEXT.SETUSERINFO();
WHEN 'APTLEAD' THEN
AP.APP_SECURITY_CONTEXT.SETUSERINFO();
ELSE
AP.APP_SECURITY_CONTEXT.SETUSERINFO();
END CASE;
END;
/
/*
|| Listing 2.4: Creating Row-Level Security (RLS) Policies
*/
-----
-- Policy Name: AP_CREDIT_CARD
-- Policy Type: DYNAMIC
-- Status: Enabled
-- Purpose: Allow only SELECTs against:
-- - AP.VENDORS.CREDIT_CARD
-- - AP.VENDORS.CREDIT_LIMIT
-- as long as the credit limit is below the user's specified
-- minimum credit limit. All rows will be returned, but these
-- columns will be displayed with NULL values because column-level
-- VPD has been enabled via the SEC_RELEVANT_COLS_OPT parameter.
-----
BEGIN
DBMS_RLS.ADD_POLICY(
object_schema => 'AP'
,object_name => 'VENDORS'
,policy_name => 'AP_CREDIT_CARD'
,function_schema => 'AP'
,policy_function=> 'APP_SECURITY_CONTEXT.CREDIT_LIMIT'
,statement_types => 'SELECT'
,update_check => FALSE
,enable => TRUE
,static_policy => FALSE
,policy_type => DBMS_RLS.CONTEXT_SENSITIVE
,sec_relevant_cols => 'CREDIT_CARD,CREDIT_LIMIT'
,sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS
,long_predicate => TRUE
);
END;
/
-----
-- Policy Name: AP_CREDIT_LIMIT
-- Policy Type: DYNAMIC
-- Status: Enabled
-- Purpose: Allow only SELECTs, INSERTs or UPDATEs against AP.VENDORS
-- when a Vendor's credit limit is below the specified
-- value.
-----
BEGIN
DBMS_RLS.ADD_POLICY(
object_schema =>'AP'
,object_name => 'VENDORS'
,policy_name => 'AP_CREDIT_LIMIT'
,function_schema =>'AP'
,policy_function => 'APP_SECURITY_CONTEXT.CREDIT_LIMIT'
,statement_types => 'SELECT,INSERT,UPDATE'
,update_check => TRUE
,enable => TRUE
,static_policy => FALSE
,policy_type => DBMS_RLS.CONTEXT_SENSITIVE
,sec_relevant_cols => 'CREDIT_CARD,CREDIT_LIMIT'
,long_predicate => FALSE
);
END;
/
-----
-- Policy Name: AP_CREDIT_MEMO
-- Policy Type: DYNAMIC
-- Status: Enabled
-- Purpose: Blocks creation of (a) a new AP.INVOICE entry, or (b)
-- a change to an existing AP.INVOICE entry when a user
-- session is not permitted to create a Credit Memo.
-----
BEGIN
DBMS_RLS.ADD_POLICY(
object_schema =>'AP'
,object_name => 'INVOICES'
,policy_name => 'AP_CREDIT_MEMO'
,function_schema =>'AP'
,policy_function => 'APP_SECURITY_CONTEXT.CREDIT_MEMO'
,statement_types => 'INSERT,UPDATE'
,update_check => TRUE
,enable => TRUE
,static_policy => FALSE
,policy_type => DBMS_RLS.CONTEXT_SENSITIVE
,sec_relevant_cols => 'INVOICE_TYPE'
,long_predicate => FALSE
);
END;
/
-----
-- Policy Name: AP_RPTG_READONLY
-- Policy Type: DYNAMIC
-- Status: Enabled
-- Purpose: Blocks the ability to view data from the AP_VENDORS,
-- AP_INVOICES, and AP.INVOICE_DETAILS table when a query
-- attempts to access:
-- - AP.VENDORS.CREDIT_CARD
-- - AP.VENDORS.CREDIT_LIMIT
-- Any query will return only the rows based on the enforced
-- restrictions, and the CREDIT_CARD column will be filled
-- with NULLs if viewing it is not permitted.
-----
BEGIN
DBMS_RLS.ADD_POLICY(
object_schema =>'AP'
,object_name => 'RV_INVOICE_DETAILS'
,policy_name => 'AP_RPTG_READONLY'
,function_schema =>'AP'
,policy_function => 'APP_SECURITY_CONTEXT.CREDIT_LIMIT'
,statement_types => 'SELECT'
,update_check => FALSE
,enable => FALSE
,static_policy => FALSE
,policy_type => DBMS_RLS.DYNAMIC
,sec_relevant_cols => 'VENDOR_CREDIT_CARD,VENDOR_CREDIT_LIMIT'
,long_predicate => TRUE
);
END;
/
/*
|| Listing 2.5: Unit-testing the RLS Policies Against Queries
*/
-----
-- Can queries access the secured base tables?
-----
COL vendor_id FORMAT 9999999 HEADING 'Vendor|ID'
COL name FORMAT A30 HEADING 'Vendor Name'
COL active_ind FORMAT A1 HEADING 'A|C|T|?'
COL credit_card FORMAT A16 HEADING 'Credit Card #'
COL credit_limit FORMAT 999,999.99 HEADING 'Credit|Card|Limit'
COL invoice_id FORMAT 9999999 HEADING 'Invoice|ID'
COL invoice_type FORMAT A1 HEADING 'T|Y|P'
COL balance_due FORMAT 999,999.99 HEADING 'Balance|Due'
TTITLE 'Details of Vendor Information|(From AP.VENDORS)'
SELECT
vendor_id
,name
,active_ind
,credit_card
,credit_limit
FROM ap.vendors
ORDER BY vendor_id
;
TTITLE OFF
TTITLE 'Detailed Invoices Information|(From AP.VENDORS and AP.INVOICES)'
SELECT
I.invoice_id
,I.vendor_id
,I.invoice_type
,V.credit_limit
,V.credit_card
,I.balance_due
FROM
ap.vendors V
,ap.invoices I
WHERE V.vendor_id = I.vendor_id
ORDER BY V.vendor_id, I.invoice_id
;
TTITLE OFF
-----
-- Can queries access the combined reporting view?
-----
COL vendor_id FORMAT 999999 HEADING 'Vendor|ID'
COL vendor_name FORMAT A25 HEADING 'Vendor Name'
COL vendor_credit_card FORMAT A16 HEADING 'Credit Card #'
COL vendor_credit_limit FORMAT 999,999.99 HEADING 'Credit|Card|Limit'
COL invoice_id FORMAT 9999999 HEADING 'Invoice|ID'
COL invoice_status FORMAT A1 HEADING 'T|Y|P'
COL line_id FORMAT 9999 HEADING 'Line|#'
COL product_id FORMAT 99999 HEADING 'Product|ID'
COL qty FORMAT 99999 HEADING 'Qty'
COL price FORMAT 999.99 HEADING 'Price|Per|Item'
COL extended_amt FORMAT 99,999.99 HEADING 'Extended|Amount'
TTITLE 'Query Against All Columns in AP.RV_INVOICE_DETAILS'
SELECT *
FROM ap.rv_invoice_details
ORDER BY vendor_name, invoice_id
;
TTITLE OFF
TTITLE 'Query Against Selected Columns in AP.RV_INVOICE_DETAILS|(Credit Card and Limit Included)'
SELECT
vendor_name
,vendor_credit_card
,vendor_credit_limit
,invoice_id
,invoice_status
,product_id
,extended_amt
FROM ap.rv_invoice_details
ORDER BY vendor_name, invoice_id
;
TTITLE OFF
TTITLE 'Query Against Selected Columns in AP.RV_INVOICE_DETAILS|(Credit Card and Limit Omitted)'
SELECT
vendor_name
,invoice_id
,invoice_status
,product_id
,extended_amt
FROM ap.rv_invoice_details
ORDER BY vendor_name, invoice_id
;
TTITLE OFF
/*
|| Listing 2.6: Unit-Testing the RLS Policies Against DML Statements
*/
SET SERVEROUTPUT ON
BEGIN
-----
-- Add new Vendors
-----
BEGIN
DBMS_APPLICATION_INFO.SET_MODULE(
module_name => 'AP_UAT'
,action_name => 'CreateVendors'
);
INSERT INTO ap.vendors
VALUES(
201
,'Y'
,'MoneyMakersRUs, Inc.'
,'1000 Easy Street'
,NULL
,NULL
,'Schaumburg'
,'IL'
,3428179310003712
,500000.00
);
INSERT INTO ap.vendors
VALUES(
202
,'Y'
,'EmbezzlersRUs, Inc.'
,'2000 Easy Street'
,NULL
,NULL
,'Schaumburg'
,'IL'
,3728179310003722
,155000.00
);
INSERT INTO ap.vendors
VALUES(
203
,'Y'
,'MoneyMakersRUs, Inc.'
,'3000 Easy Street'
,NULL
,NULL
,'Schaumburg'
,'IL'
,3928179310003732
,4995.99
);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unexpected error during Vendor Creation!');
END;
-----
-- Update Vendors' Credit Card Number
-----
BEGIN
DBMS_APPLICATION_INFO.SET_MODULE(
module_name => 'AP_UAT'
,action_name => 'CreditCardUpdates'
);
UPDATE ap.vendors
SET credit_card = '4096300010240511'
WHERE vendor_id = 201;
UPDATE ap.vendors
SET credit_card = '4096300010240522'
WHERE vendor_id = 202;
UPDATE ap.vendors
SET credit_card = '4096300010240533'
WHERE vendor_id = 203;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unexpected error during Vendor Credit Card Updates!');
END;
-----
-- Update Vendors' Credit Limit
-----
BEGIN
DBMS_APPLICATION_INFO.SET_MODULE(
module_name => 'AP_UAT'
,action_name => 'UpdateCreditLimits'
);
UPDATE ap.vendors
SET credit_limit = 150001.91
WHERE vendor_id = 201;
UPDATE ap.vendors
SET credit_limit = 100000.00
WHERE vendor_id = 202;
UPDATE ap.vendors
SET credit_limit = 5001.93
WHERE vendor_id = 203;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unexpected error during Vendor Credit Limit Updates!');
END;
-----
-- Invoice maintenance unit testing
-----
BEGIN
DBMS_APPLICATION_INFO.SET_MODULE(
module_name => 'AP_UAT'
,action_name => 'CreateInvoices'
);
-- Create a new Invoice as a (C)redit
INSERT INTO ap.invoices
VALUES(901, 'Y', 'C', 201, 0, 0);
INSERT INTO ap.invoice_items
VALUES(901, 1, 'Y', 100, 50, 0.50, 12.50, 'N');
-- Create a new Invoice as a (D)ebit
INSERT INTO ap.invoices
VALUES(902, 'Y', 'D', 202, 0, 0);
INSERT INTO ap.invoice_items
VALUES(902, 1, 'Y', 100, 50, 0.50, 12.50, 'N');
-- Create a new Invoice as a (D)ebit
INSERT INTO ap.invoices
VALUES(903, 'Y', 'D', 203, 0, 0);
INSERT INTO ap.invoice_items
VALUES(903, 1, 'Y', 100, 50, 0.50, 12.50, 'N');
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unexpected error during Invoice Creation!');
END;
BEGIN
DBMS_APPLICATION_INFO.SET_MODULE(
module_name => 'AP_UAT'
,action_name => 'ResetInvoiceType'
);
UPDATE ap.invoices
SET invoice_type = 'D'
WHERE invoice_id = 601;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unexpected error during Invoice Reset!');
END;
-----
-- Reset application information
-----
DBMS_APPLICATION_INFO.SET_MODULE(
module_name => NULL
,action_name => NULL
);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unexpected error! Exiting unit test.');
END;
/
/*
|| Listing 2.7: Query Results for APDIR User Account
*/
>>>>>
>> Results of query testing against APTDIR user account:
>> 1.) All queries returned a complete set of data in the AP schema.
>>>>>
Details of Vendor Information
(From AP.VENDORS)
A
C Credit
Vendor T Card
ID Vendor Name ? Credit Card # Limit
-------- ------------------------------ - ---------------- -----------
101 FacesRUs, Inc. Y 3428179310003762 100,000.00
102 Scared Silly Makeup Gmbh Y 5968100071234567 75,000.00
103 Blood Imitation Products, LLC Y 6879110044283978 35,000.00
104 Tri-Color Corn NA Y 5967100039131004 22,500.00
105 Fright Night, Inc. N 38271780012103 100,000.00
201 MoneyMakersRUs, Inc. Y 4096300010240511 149,999.91
202 EmbezzlersRUs, Inc. Y 4096300010240522 149,999.92
203 MoneyMakersRUs, Inc. Y 4096300010240533 5,001.93
8 rows selected.
Detailed Invoices Information
(From AP.VENDORS and AP.INVOICES)
T Credit
Invoice Vendor Y Card Balance
ID ID P Limit Credit Card # Due
-------- -------- - ----------- ---------------- -----------
501 101 C 100,000.00 3428179310003762 415.00
502 101 C 100,000.00 3428179310003762 420.00
503 102 C 75,000.00 5968100071234567 577.35
504 102 C 75,000.00 5968100071234567 337.50
505 102 C 75,000.00 5968100071234567 7,000.00
506 103 C 35,000.00 6879110044283978 475.03
507 104 C 22,500.00 5967100039131004 1,083.75
508 105 C 100,000.00 38271780012103 14,016.85
509 105 C 100,000.00 38271780012103 4,385.73
901 201 C 149,999.91 4096300010240511 .00
902 202 D 149,999.92 4096300010240522 .00
903 203 D 5,001.93 4096300010240533 .00
12 rows selected.
Query Against All Columns in AP.RV_INVOICE_DETAILS
Credit T Price
Card Invoice Y Line Product Per Extended
Vendor Name Credit Card # Limit ID P # T ID Qty Item Amount
------------------------- ---------------- ----------- -------- - ----- - ------- ------ ------- ----------
Blood Imitation Products, 6879110044283978 35,000.00 506 Y 3 N 212 140 .47 66.22
LLC
Blood Imitation Products, 6879110044283978 35,000.00 506 Y 2 Y 103 1 193.81 193.81
LLC
Blood Imitation Products, 6879110044283978 35,000.00 506 Y 1 Y 901 10 21.50 215.00
LLC
EmbezzlersRUs, Inc. 4096300010240522 149,999.92 902 Y 1 N 100 50 .50 12.50
FacesRUs, Inc. 3428179310003762 100,000.00 501 Y 1 N 750 50 .50 25.00
FacesRUs, Inc. 3428179310003762 100,000.00 501 Y 2 N 775 10 21.50 215.00
FacesRUs, Inc. 3428179310003762 100,000.00 501 Y 3 N 203 10 17.50 175.00
FacesRUs, Inc. 3428179310003762 100,000.00 502 Y 2 Y 809 1700 .10 170.00
FacesRUs, Inc. 3428179310003762 100,000.00 502 Y 1 Y 298 25 10.00 250.00
Fright Night, Inc. 38271780012103 100,000.00 508 Y 4 Y 403 1 129.85 129.85
Fright Night, Inc. 38271780012103 100,000.00 508 Y 2 Y 401 10000 .95 9,500.00
Fright Night, Inc. 38271780012103 100,000.00 508 Y 3 Y 402 100 31.37 3,137.00
Fright Night, Inc. 38271780012103 100,000.00 508 Y 1 Y 400 100 12.50 1,250.00
Fright Night, Inc. 38271780012103 100,000.00 509 Y 3 Y 221 405 2.98 1,206.90
Fright Night, Inc. 38271780012103 100,000.00 509 Y 2 N 976 1626 .98 1,593.48
Fright Night, Inc. 38271780012103 100,000.00 509 Y 1 N 641 813 1.95 1,585.35
MoneyMakersRUs, Inc. 4096300010240511 149,999.91 901 Y 1 N 100 50 .50 12.50
MoneyMakersRUs, Inc. 4096300010240533 5,001.93 903 Y 1 N 100 50 .50 12.50
Scared Silly Makeup Gmbh 5968100071234567 75,000.00 503 Y 5 Y 795 12 5.95 71.40
Scared Silly Makeup Gmbh 5968100071234567 75,000.00 503 Y 1 Y 391 10 21.50 215.00
Scared Silly Makeup Gmbh 5968100071234567 75,000.00 503 Y 2 Y 603 5 5.00 25.00
Scared Silly Makeup Gmbh 5968100071234567 75,000.00 503 Y 3 Y 781 1 139.95 139.95
Scared Silly Makeup Gmbh 5968100071234567 75,000.00 503 Y 4 Y 782 1400 .09 126.00
Scared Silly Makeup Gmbh 5968100071234567 75,000.00 504 Y 1 Y 503 1 122.50 122.50
Scared Silly Makeup Gmbh 5968100071234567 75,000.00 504 Y 2 Y 897 10 21.50 215.00
Scared Silly Makeup Gmbh 5968100071234567 75,000.00 505 Y 1 Y 507 10000 .07 7,000.00
Tri-Color Corn NA 5967100039131004 22,500.00 507 Y 2 Y 503 340 1.99 676.60
Tri-Color Corn NA 5967100039131004 22,500.00 507 Y 1 Y 293 17 23.95 407.15
28 rows selected.
Query Against Selected Columns in AP.RV_INVOICE_DETAILS
(Credit Card and Limit Included)
Credit T
Card Invoice Y Product Extended
Vendor Name Credit Card # Limit ID P ID Amount
------------------------- ---------------- ----------- -------- - ------- ----------
Blood Imitation Products, 6879110044283978 35,000.00 506 Y 212 66.22
LLC
Blood Imitation Products, 6879110044283978 35,000.00 506 Y 103 193.81
LLC
Blood Imitation Products, 6879110044283978 35,000.00 506 Y 901 215.00
LLC
EmbezzlersRUs, Inc. 4096300010240522 149,999.92 902 Y 100 12.50
FacesRUs, Inc. 3428179310003762 100,000.00 501 Y 750 25.00
FacesRUs, Inc. 3428179310003762 100,000.00 501 Y 775 215.00
FacesRUs, Inc. 3428179310003762 100,000.00 501 Y 203 175.00
FacesRUs, Inc. 3428179310003762 100,000.00 502 Y 809 170.00
FacesRUs, Inc. 3428179310003762 100,000.00 502 Y 298 250.00
Fright Night, Inc. 38271780012103 100,000.00 508 Y 403 129.85
Fright Night, Inc. 38271780012103 100,000.00 508 Y 401 9,500.00
Fright Night, Inc. 38271780012103 100,000.00 508 Y 402 3,137.00
Fright Night, Inc. 38271780012103 100,000.00 508 Y 400 1,250.00
Fright Night, Inc. 38271780012103 100,000.00 509 Y 221 1,206.90
Fright Night, Inc. 38271780012103 100,000.00 509 Y 976 1,593.48
Fright Night, Inc. 38271780012103 100,000.00 509 Y 641 1,585.35
MoneyMakersRUs, Inc. 4096300010240511 149,999.91 901 Y 100 12.50
MoneyMakersRUs, Inc. 4096300010240533 5,001.93 903 Y 100 12.50
Scared Silly Makeup Gmbh 5968100071234567 75,000.00 503 Y 795 71.40
Scared Silly Makeup Gmbh 5968100071234567 75,000.00 503 Y 391 215.00
Scared Silly Makeup Gmbh 5968100071234567 75,000.00 503 Y 603 25.00
Scared Silly Makeup Gmbh 5968100071234567 75,000.00 503 Y 781 139.95
Scared Silly Makeup Gmbh 5968100071234567 75,000.00 503 Y 782 126.00
Scared Silly Makeup Gmbh 5968100071234567 75,000.00 504 Y 503 122.50
Scared Silly Makeup Gmbh 5968100071234567 75,000.00 504 Y 897 215.00
Scared Silly Makeup Gmbh 5968100071234567 75,000.00 505 Y 507 7,000.00
Tri-Color Corn NA 5967100039131004 22,500.00 507 Y 503 676.60
Tri-Color Corn NA 5967100039131004 22,500.00 507 Y 293 407.15
Query Against Selected Columns in AP.RV_INVOICE_DETAILS
(Credit Card and Limit Omitted)
T
Invoice Y Product Extended
Vendor Name ID P ID Amount
------------------------- -------- - ------- ----------
Blood Imitation Products, 506 Y 901 215.00
LLC
Blood Imitation Products, 506 Y 103 193.81
LLC
Blood Imitation Products, 506 Y 212 66.22
LLC
EmbezzlersRUs, Inc. 902 Y 100 12.50
FacesRUs, Inc. 501 Y 203 175.00
FacesRUs, Inc. 501 Y 750 25.00
FacesRUs, Inc. 501 Y 775 215.00
FacesRUs, Inc. 502 Y 298 250.00
FacesRUs, Inc. 502 Y 809 170.00
Fright Night, Inc. 508 Y 402 3,137.00
Fright Night, Inc. 508 Y 401 9,500.00
Fright Night, Inc. 508 Y 400 1,250.00
Fright Night, Inc. 508 Y 403 129.85
Fright Night, Inc. 509 Y 641 1,585.35
Fright Night, Inc. 509 Y 221 1,206.90
Fright Night, Inc. 509 Y 976 1,593.48
MoneyMakersRUs, Inc. 901 Y 100 12.50
MoneyMakersRUs, Inc. 903 Y 100 12.50
Scared Silly Makeup Gmbh 503 Y 782 126.00
Scared Silly Makeup Gmbh 503 Y 795 71.40
Scared Silly Makeup Gmbh 503 Y 781 139.95
Scared Silly Makeup Gmbh 503 Y 603 25.00
Scared Silly Makeup Gmbh 503 Y 391 215.00
Scared Silly Makeup Gmbh 504 Y 897 215.00
Scared Silly Makeup Gmbh 504 Y 503 122.50
Scared Silly Makeup Gmbh 505 Y 507 7,000.00
Tri-Color Corn NA 507 Y 293 407.15
Tri-Color Corn NA 507 Y 503 676.60
28 rows selected.
/*
|| Listing 2.8: Query Results for APTLEAD User Account
*/
>>>>>
>> Results of query testing against APTLEAD user account:
>> Only Vendors with a credit limit under $150000 are selected (and since none are that
>> low, all data was returned).
>>>>>
Details of Vendor Information
(From AP.VENDORS)
A
C Credit
Vendor T Card
ID Vendor Name ? Credit Card # Limit
-------- ------------------------------ - ---------------- -----------
101 FacesRUs, Inc. Y 3428179310003762 100,000.00
102 Scared Silly Makeup Gmbh Y 5968100071234567 75,000.00
103 Blood Imitation Products, LLC Y 6879110044283978 35,000.00
104 Tri-Color Corn NA Y 5967100039131004 22,500.00
105 Fright Night, Inc. N 38271780012103 100,000.00
201 MoneyMakersRUs, Inc. Y 4096300010240511 149,999.91
202 EmbezzlersRUs, Inc. Y 4096300010240522 149,999.92
203 MoneyMakersRUs, Inc. Y 4096300010240533 5,001.93
8 rows selected.
<<< remainder of data matches that from Listing 2.8 queries >>>
>>>>>
>> Results of DML testing against APTLEAD user account:
>> 1.) Adding or updating a vendor results in a thrown exception.
>>>>>
INSERT INTO ap.vendors
2 VALUES(
3 301
4 ,'Y'
5 ,'MoneyMakersRUs, Inc.'
6 ,'1000 Easy Street'
7 ,NULL
8 ,NULL
9 ,'Schaumburg'
10 ,'IL'
11 ,3428179310003712
12 ,500000.00
13 );
INSERT INTO ap.vendors
*
ERROR at line 1:
ORA-28115: policy with check option violation
UPDATE ap.vendors
2 SET credit_limit = 155000.00
3 WHERE vendor_id = 201;
UPDATE ap.vendors
*
ERROR at line 1:
ORA-28115: policy with check option violation
/*
|| Listing 2.9: Query Results for APCLERK User Account
*/
>>>>>
>> Results of query testing against APCLERK user account:
>> 1.) Only Vendors with a credit limit under $25000 are selected.
>> 2.) However, when leaving out the CREDIT_LIMIT or CREDIT_CARD columns
>> when querying against AP.RV_INVOICE_DETAILS, all column detail was
>> visible!
>>>>>
Details of Vendor Information
(From AP.VENDORS)
A
C Credit
Vendor T Card
ID Vendor Name ? Credit Card # Limit
-------- ------------------------------ - ---------------- -----------
104 Tri-Color Corn NA Y 5967100039131004 22,500.00
203 MoneyMakersRUs, Inc. Y 4096300010240533 5,001.93
Detailed Invoices Information
(From AP.VENDORS and AP.INVOICES)
T Credit
Invoice Vendor Y Card Balance
ID ID P Limit Credit Card # Due
-------- -------- - ----------- ---------------- -----------
507 104 C 22,500.00 5967100039131004 1,083.75
903 203 D 5,001.93 4096300010240533 .00
Query Against All Columns in AP.RV_INVOICE_DETAILS
Credit T Price
Card Invoice Y Line Product Per Extended
Vendor Name Credit Card # Limit ID P # T ID Qty Item Amount
------------------------- ---------------- ----------- -------- - ----- - ------- ------ ------- ----------
MoneyMakersRUs, Inc. 4096300010240533 5,001.93 903 Y 1 N 100 50 .50 12.50
Tri-Color Corn NA 5967100039131004 22,500.00 507 Y 1 Y 293 17 23.95 407.15
Tri-Color Corn NA 5967100039131004 22,500.00 507 Y 2 Y 503 340 1.99 676.60
Query Against Selected Columns in AP.RV_INVOICE_DETAILS
(Credit Card and Limit Included)
Credit T
Card Invoice Y Product Extended
Vendor Name Credit Card # Limit ID P ID Amount
------------------------- ---------------- ----------- -------- - ------- ----------
MoneyMakersRUs, Inc. 4096300010240533 5,001.93 903 Y 100 12.50
Tri-Color Corn NA 5967100039131004 22,500.00 507 Y 293 407.15
Tri-Color Corn NA 5967100039131004 22,500.00 507 Y 503 676.60
Query Against Selected Columns in AP.RV_INVOICE_DETAILS
(Credit Card and Limit Omitted)
T
Invoice Y Product Extended
Vendor Name ID P ID Amount
------------------------- -------- - ------- ----------
Blood Imitation Products, 506 Y 901 215.00
LLC
Blood Imitation Products, 506 Y 103 193.81
LLC
Blood Imitation Products, 506 Y 212 66.22
LLC
EmbezzlersRUs, Inc. 902 Y 100 12.50
FacesRUs, Inc. 501 Y 203 175.00
FacesRUs, Inc. 501 Y 750 25.00
FacesRUs, Inc. 501 Y 775 215.00
FacesRUs, Inc. 502 Y 298 250.00
FacesRUs, Inc. 502 Y 809 170.00
Fright Night, Inc. 508 Y 402 3,137.00
Fright Night, Inc. 508 Y 401 9,500.00
Fright Night, Inc. 508 Y 400 1,250.00
Fright Night, Inc. 508 Y 403 129.85
Fright Night, Inc. 509 Y 641 1,585.35
Fright Night, Inc. 509 Y 221 1,206.90
Fright Night, Inc. 509 Y 976 1,593.48
MoneyMakersRUs, Inc. 901 Y 100 12.50
MoneyMakersRUs, Inc. 903 Y 100 12.50
Scared Silly Makeup Gmbh 503 Y 782 126.00
Scared Silly Makeup Gmbh 503 Y 795 71.40
Scared Silly Makeup Gmbh 503 Y 781 139.95
Scared Silly Makeup Gmbh 503 Y 603 25.00
Scared Silly Makeup Gmbh 503 Y 391 215.00
Scared Silly Makeup Gmbh 504 Y 897 215.00
Scared Silly Makeup Gmbh 504 Y 503 122.50
Scared Silly Makeup Gmbh 505 Y 507 7,000.00
Tri-Color Corn NA 507 Y 293 407.15
Tri-Color Corn NA 507 Y 503 676.60
28 rows selected.
>>>>>
>> Results of DML testing against APCLERK user account:
>> 1.) Adding a new Vendor with a credit limit of over $250000 is prohibited.
>> 2.) Updating an existing Vendor that's already over the credit limit of $25000
>> is also blocked, but with no rows returned!
>>>>>
INSERT INTO ap.vendors
2 VALUES(
3 401
4 ,'Y'
5 ,'MoneyMakersRUs, Inc.'
6 ,'4000 Easy Street'
7 ,NULL
8 ,NULL
9 ,'Schaumburg'
10 ,'IL'
11 ,3428179310003744
12 ,5001.00
13 );
INSERT INTO ap.vendors
*
ERROR at line 1:
ORA-28115: policy with check option violation
UPDATE ap.vendors
SET credit_limit = 5001.00
WHERE vendor_id = 201;
0 rows updated.