Обсуждение: column level encryption & select rules
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.Little@orbitz.com
orbitz.com | ebookers.com | hotelclub.com | cheaptickets.com | ratestogo.com | 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;