Hi, I've implemented a scheme for column level encryption that uses table triggers (isrt/update) to encrypt the input data, and a view To perform the decrypt. It's working ok, but I'm having trouble altering my objects because of the dependents. To implement the scheme, I have to generate the view, table trigger (isrt/updt), and a trigger function.
Currently the decrypt functions are embedded in the views which I want to get rid of. Can I implement them as a select rule? If the select rule directs the queries to the same table, does recursion occur? View is below. What are the challenges, etc. Doug Little Sr. Data Warehouse Architect | Enterprise Data Management | Orbitz Worldwide 500 W. Madison, Suite 1000 Chicago IL 60661| Office 312.260.2588 | Fax 312.894.5164 | Cell 847-997-5741 douglas.lit...@orbitz.com<mailto:douglas.lit...@orbitz.com> [cid:image002.jpg@01CA7FD5.13BDD510] orbitz.com<http://www.orbitz.com/> | ebookers.com<http://www.ebookers.com/> | hotelclub.com<http://www.hotelclub.com/> | cheaptickets.com<http://www.cheaptickets.com/> | ratestogo.com<http://www.ratestogo.com/> | asiahotels.com<http://www.asiahotels.com/> -- View: ods_views.customer_payment_profile -- DROP VIEW ods_views.customer_payment_profile; CREATE OR REPLACE VIEW ods_views.customer_payment_profile AS SELECT customer_payment_profile.customer_payment_profile_id, customer_payment_profile.ref_point_of_sale_id, customer_payment_profile.last_used_date, CASE WHEN owwpiiview() THEN dba_work.owwdecrypt(customer_payment_profile.pi2_pii_card_number, customer_payment_profile.customer_payment_profile_id::text)::character varying(128) ELSE '**************************************************'::character varying::character varying(128) END AS pii_card_number, CASE WHEN owwpiiview() THEN dba_work.owwdecrypt(customer_payment_profile.pi2_pii_cardholder_name, customer_payment_profile.customer_payment_profile_id::text)::character varying(200) ELSE '**************************************************'::character varying::character varying(200) END AS pii_cardholder_name, customer_payment_profile.default_ind, customer_payment_profile.ref_payment_type_code, customer_payment_profile.expiration_date, customer_payment_profile.active_ind, customer_payment_profile.customer_member_id, CASE WHEN owwpiiview() THEN dba_work.owwdecrypt(customer_payment_profile.pi2_pii_address1, customer_payment_profile.customer_payment_profile_id::text)::character varying(200) ELSE '**************************************************'::character varying::character varying(200) END AS pii_address1, customer_payment_profile.address2, customer_payment_profile.address3, customer_payment_profile.address4, customer_payment_profile.city, customer_payment_profile.ref_state_province_code, customer_payment_profile.ref_country_code, customer_payment_profile.ref_postal_code, customer_payment_profile.po_box_ind, customer_payment_profile.intl_phone_dialing_code, CASE WHEN owwpiiview() THEN dba_work.owwdecrypt(customer_payment_profile.pi2_pii_phone, customer_payment_profile.customer_payment_profile_id::text)::character varying(200) ELSE '**************************************************'::character varying::character varying(200) END AS pii_phone, customer_payment_profile.phone_extension, customer_payment_profile.create_date, customer_payment_profile.modified_date, customer_payment_profile.ref_phone_country_code, customer_payment_profile.oltp_deleted_timestamp, customer_payment_profile.ods_load_timestamp, customer_payment_profile.ref_cc_type_code, customer_payment_profile.cvn_valid_ind, customer_payment_profile.issue_date, customer_payment_profile.pii_issue_number FROM customer.customer_payment_profile;
<<inline: image002.jpg>>
