|
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 | 500 W. Douglas.Little@xxxxxxxxxx
-- 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; |
