Jump to content
Prabhat Sahu

unexpected behaviour with DBMS_REDACT.PARTIAL

Recommended Posts

Hi,

I have question with following case where I have use the function_parameters => 'm12DYh11m11s11' but the value are not REDACTed as per the documentation.

Where I am expecting something like '01-dec-4712 11:11:11 am bc'.

Steps to reproduce:

SQL> conn scott/tiger

 

SQL> CREATE TABLE tab1(dt_range DATE);

SQL> INSERT INTO tab1 values(to_date('01-jan-4712 01:23:45 am bc','dd-mon-yyyy hh:mi:ss am ad'));

SQL> INSERT INTO tab1 values(to_date('31-dec-9999 02:34:56 pm ad','dd-mon-yyyy hh:mi:ss am ad'));

 

SQL> CREATE USER user1 IDENTIFIED BY user1;

SQL> GRANT CREATE SESSION TO user1;

SQL> GRANT SELECT ON tab1 to user1;

 

Values Before redaction:

SQL> select TO_CHAR(DT_RANGE,'dd-mon-yyyy hh:mi:ss am ad') from tab1;

TO_CHAR(DT_RANGE,'DD-MON-Y

--------------------------

01-jan-4712 01:23:45 am bc

31-dec-9999 02:34:56 pm ad

 

SQL> conn scott/tiger

 

SQL> BEGIN

DBMS_REDACT.add_policy(

object_schema => 'scott',

object_name => 'tab1',

policy_name => 'redactPolicy_001',

policy_description => 'redactPolicy_001 for tab1 table',

column_name => 'dt_range',

column_description => 'dt_range value in tab1 table',

function_type   => DBMS_REDACT.PARTIAL,

function_parameters => 'm12DYh11m11s11', 

expression => '1=1',

enable => TRUE);

END;

/

 

PL/SQL procedure successfully completed.

 

SQL> conn user1/user1

 

-- Values After redaction:

SQL> select TO_CHAR(DT_RANGE,'dd-mon-yyyy hh:mi:ss am ad') from scott.tab1;

 

TO_CHAR(DT_RANGE,'DD-MON-Y

--------------------------

01-dec-4712 01:23:45 am bc

31-dec-9999 02:34:56 pm ad

Is this expected? Or I am missing something here, Please suggest.

Share this post


Link to post
Share on other sites

My testcase is base on that documentation only.

 I am using below Oracle environment:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced

As I understood I should be able to redact all part in DATE field which we will mention as lower case in function_parameter.

But I am not able to redact my "hour" "minute" and "second" part with function_parameter => m12DYh11m11s11"

 

Regards, 

Prabhat Sahu

Share this post


Link to post
Share on other sites

Hi Prabhat,

First, Boobal proved it worked as documented on a supported release of Oracle.

Why are you using a deprecated release of Oracle?

Finally, what did Oracle support say about your issue?

Please don’t respond if you have not bothered to check with Oracle support.  

If you don’t care, why should we?

Good luck!

Share this post


Link to post
Share on other sites

Thanks team, 

Now I got it. It is working as expected with the upgraded version of oracle. 

I was trying to use the behaviour with my older branch/release of oracle in which DATA REDACTION was working fine but this specific feature was not implemented. 

Thanks a lot for your support.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×