how to use redacted column in packages(it is showing the redacted value)

Question

I would like to use the redacted columns in packages and procedures with raw data but it is showing the redacted only then how to use the actual data in procedures? In below example ename is redacted column and fully redacted as empty.

    BEGIN   DBMS_REDACT.add_policy(
object_schema => 'hr',
object_name   => 'new_emp',
column_name   => 'ename',
policy_name   => 'redact_ename_info',
function_type => DBMS_REDACT.full,
expression    => '1=1' );END;/ --Redaction is done for ename column in new_emp table.

    create or replace procedure new_emp_pro(cust_id in varchar2) as
    v_ename varchar2(200):=null;
    begin
    select ename into v_ename from new_emp where empid=cust_id;
    dbms_output.put_line('v_ename :'||v_ename);
    end;
    /

    PL/SQL procedure successfully completed.

    v_ename :

I'm sorry, I don't understand the question. Could you create a test case which shows what you have, and what you get as a result? The way it looks now, as if ENAME is empty (NULL) for CUST_ID you passed to that procedure. If CUST_ID doesn't exist, you'd get the NO_DATA_FOUND exception. So ... I really don't understand what you are saying. — Littlefoot, May 29 '19 at 05:08
It would help to explain your VPD (?) setup a bit, and which user owns the table, creates the procedure, and executes the procedure - and what rules apply to each (if they are different). — Alex Poole, May 29 '19 at 08:39
@Littlefoot :for example consider the table name card_info with column card_no(4563158794655478) which is partially redacted(4563XXXXXXXX5478) and while fetching the card_no from database im getting the redacted card_no only not the raw card_no. And my question is how will i use (or fetch) the raw data into the packages or procedures. — Karthick Krishnan, May 30 '19 at 01:20
Aha. Thank you for the explanation and code you posted. Now I understand what you are saying. Unfortunately, I've never even heard of DBMS_REDACT package (what an ignoramus, eh?), I thought that you created your own procedure which does that. I'm sorry, I'm unable to help you. I hope someone else will be able to assist. Good luck! — Littlefoot, May 30 '19 at 06:03

score 0 · Answer 1 · answered Mar 02 '20 at 10:57

What you want to do, you have redacted column ename for all users and so when you are trying to print it system will not show the value because that is the purpose of redaction. Internally ename value will be available for processing but redaction applies after all processing. If you are able to print ename then whole purpose of redaction has failed because ultimately user is able to retrieve the data.

You can check whether ename is null or not null

Let me know if you need anymore details on this. I have recently implemented redaction in a production DB.

how to use redacted column in packages(it is showing the redacted value)

1 Answers1