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