DBA Hub

📋Steps in this guide1/6

Data Redaction (DBMS_REDACT) in Oracle Database 12c Release 1 (12.1)

Use data redaction to protect sensitive data in Oracle Database 12c Release 1 (12.1).

oracle 12cconfigurationintermediate
by OracleDba
14 views
1

Setup

We create two test users. We need to make sure the first test user has access to the package. The example code in this article requires the following test table.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
conn sys/SysPassword1@//localhost:1521/pdb1 as sysdba

--drop user testuser1 cascade;
create user testuser1 identified by testuser1 quota unlimited on users;
grant create session, create table to testuser1;

--drop user testuser2 cascade;
create user testuser2 identified by testuser2 quota unlimited on users;
grant create session to testuser2;

grant execute on sys.dbms_redact to testuser1;

conn testuser1/testuser1@//localhost:1521/pdb1

drop table payment_details purge;

create table payment_details (
  id          number       not null,
  customer_id number       not null,
  card_no     number       not null,
  card_string varchar2(19) not null,
  expiry_date date         not null,
  sec_code    number       not null,
  valid_date  date,
  constraint payment_details_pk primary key (id)
);

insert into payment_details values (1, 4000, 1234123412341234, '1234-1234-1234-1234', trunc(add_months(sysdate,12)), 123, null);
insert into payment_details values (2, 4001, 2345234523452345, '2345-2345-2345-2345', trunc(add_months(sysdate,12)), 234, null);
insert into payment_details values (3, 4002, 3456345634563456, '3456-3456-3456-3456', trunc(add_months(sysdate,12)), 345, null);
insert into payment_details values (4, 4003, 4567456745674567, '4567-4567-4567-4567', trunc(add_months(sysdate,12)), 456, null);
insert into payment_details values (5, 4004, 5678567856785678, '5678-5678-5678-5678', trunc(add_months(sysdate,12)), 567, null);
commit;

alter session set nls_date_format='dd-mon-yyyy';

column card_no format 9999999999999999

select *
from   payment_details
order by id;

        ID CUSTOMER_ID           CARD_NO CARD_STRING         EXPIRY_DATE   SEC_CODE VALID_DATE
---------- ----------- ----------------- ------------------- ----------- ---------- -----------
         1        4000  1234123412341234 1234-1234-1234-1234 28-OCT-2015        123
         2        4001  2345234523452345 2345-2345-2345-2345 28-OCT-2015        234
         3        4002  3456345634563456 3456-3456-3456-3456 28-OCT-2015        345
         4        4003  4567456745674567 4567-4567-4567-4567 28-OCT-2015        456
         5        4004  5678567856785678 5678-5678-5678-5678 28-OCT-2015        567

5 rows selected.

SQL>
2

Add a new Policy

Creating a new redaction policy is done using the procedure in the package. A policy is made up of several distinct sections. - Identify the object : The , and parameters identify the column to be redacted. - Give it a name : The parameter assigns a name to the policy. - What should happen? : The parameter determines the type of redaction that should take place. The allowable values are listed here . Depending on the type of redaction selected, you may be required to specify the or various parameters. When should it happen? : The parameter determines when the redaction should take place. For example, an expression of "1=1" means the redaction will always take place. Alternatively, situational expressions can be defined using the function. - When should it happen? : The parameter determines when the redaction should take place. For example, an expression of "1=1" means the redaction will always take place. Alternatively, situational expressions can be defined using the function. The following example is about as simple as it gets. A full redaction policy is placed on the column with an expression of "1=1". We can see the column is now redacted to the number "0". The value displayed by full redaction is based on the data type defaults for the function type. You can see the default values by querying the REDACTION_VALUES_FOR_TYPE_FULL view, shown by the following example that uses the redaction_columns.sql script. These default values can be altered using the procedure, but you will need to start the instance for the updates to be visible. Information about existing policies is displayed using the REDACTION_COLUMNS and REDACTION_POLICIES views, used in the redaction_policies.sql and redaction_columns.sql scripts below. Other variations on redaction policies are described in the following section. These are equally applicable during redaction policy creation.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
conn testuser1/testuser1@//localhost:1521/pdb1

begin
  dbms_redact.add_policy(
    object_schema => user,
    object_name   => 'payment_details',
    column_name   => 'card_no',
    policy_name   => 'redact_card_info',
    function_type => dbms_redact.full,
    expression    => '1=1'
  );
end;
/

alter session set nls_date_format='dd-mon-yyyy';
column card_no format 9999999999999999

select *
from   payment_details
order by id;

        ID CUSTOMER_ID           CARD_NO CARD_STRING         EXPIRY_DATE   SEC_CODE VALID_DATE
---------- ----------- ----------------- ------------------- ----------- ---------- -----------
         1        4000                 0 1234-1234-1234-1234 28-OCT-2015        123
         2        4001                 0 2345-2345-2345-2345 28-OCT-2015        234
         3        4002                 0 3456-3456-3456-3456 28-OCT-2015        345
         4        4003                 0 4567-4567-4567-4567 28-OCT-2015        456
         5        4004                 0 5678-5678-5678-5678 28-OCT-2015        567

5 rows selected.

SQL>

conn sys/SysPassword1@//localhost:1521/pdb1 as sysdba

@redaction_value_defaults.sql

NUMBER_VALUE BINARY_FLOAT_VALUE BINARY_DOUBLE_VALUE CHAR_VALUE VARCHAR_VA NCHAR_VALU NVARCHAR_V DATE_VALUE           TIMESTAMP_VALUE             TIMESTAMP_WITH_TIME_ZONE_VALUE   BLOB_VALUE           CLOB_VALUE NCLOB_VALU
------------ ------------------ ------------------- ---------- ---------- ---------- ---------- -------------------- --------------------------- -------------------------------- -------------------- ---------- ----------
           0                  0                   0                                             01-JAN-2001 00:00:00 01-JAN-2001 01:00:00.000000 01-JAN-01 01.00.00.000000 +00:00 5B72656461637465645D [redacted] [redacted]

1 row selected.

SQL>

conn sys/SysPassword1@//localhost:1521/pdb1 as sysdba

@redaction_policies

OBJECT_OWNER         OBJECT_NAME                    POLICY_NAME                    EXPRESSION                     ENABLE  POLICY_DESCRIPTION
-------------------- ------------------------------ ------------------------------ ------------------------------ ------- --------------------
TESTUSER1            PAYMENT_DETAILS                redact_card_info               1=1                            YES

1 row selected.

SQL>


@redaction_columns testuser1 payment_details

OBJECT_OWNER         OBJECT_NAME                    COLUMN_NAME                    FUNCTION_TYPE               FUNCTION_PARAMETERS            REGEXP_PATTERN                 REGEXP_REPLACE_STRING          REGEXP_POSITION REGEXP_OCCURRENCE REGEXP_MAT COLUMN_DESCRIPTION
-------------------- ------------------------------ ------------------------------ --------------------------- ------------------------------ ------------------------------ ------------------------------ --------------- ----------------- ---------- --------------------
TESTUSER1            PAYMENT_DETAILS                CARD_NO                        FULL REDACTION                                                                                                                         0                 0

1 row selected.

SQL>
3

Alter an Existing Policy

The procedure allows you to make changes to an existing policy. The type of change being made is controlled using the parameter. Depending on the action required, the relevant parameters must be specified. The following example changes the previously created redaction policy so that it uses partial redaction. Notice the are now specified to give instructions how the partial redaction should take place. For a numeric data type we specify a comma separated list of three elements (value to redact to, start point, end point), so in this case we want the first 12 characters of the number to always display as "111111111111". We can add another column to the redaction policy to protect the string representation of the card number. The following example redacts the expiry date using partial redaction, converting the day and month values to 1st of January. We can also amend the policy so it does not affect the schema owner. The following example uses the function in the parameter to determine the current user, making the application of the redaction policy conditional. As expected, the redaction policy no longer applies to the TEST user. Details of the FUNCTION_PARAMETERS formats is available here . There are also predefined Partial Fixed Character Redaction Formats listed here .

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
conn testuser1/testuser1@//localhost:1521/pdb1

begin
  dbms_redact.alter_policy (
    object_schema       => user,
    object_name         => 'payment_details',
    policy_name         => 'redact_card_info',
    action              => dbms_redact.modify_column,
    column_name         => 'card_no',
    function_type       => dbms_redact.partial,
    function_parameters => '1,1,12'
  );
end;
/
   
alter session set nls_date_format='dd-mon-yyyy';
column card_no format 9999999999999999

select *
from   payment_details
order by id;

        ID CUSTOMER_ID           CARD_NO CARD_STRING         EXPIRY_DATE   SEC_CODE VALID_DATE
---------- ----------- ----------------- ------------------- ----------- ---------- -----------
         1        4000  1111111111111234 1234-1234-1234-1234 28-OCT-2015        123
         2        4001  1111111111112345 2345-2345-2345-2345 28-OCT-2015        234
         3        4002  1111111111113456 3456-3456-3456-3456 28-OCT-2015        345
         4        4003  1111111111114567 4567-4567-4567-4567 28-OCT-2015        456
         5        4004  1111111111115678 5678-5678-5678-5678 28-OCT-2015        567

5 rows selected.

SQL>

begin
  dbms_redact.alter_policy (
    object_schema       => user,
    object_name         => 'payment_details',
    policy_name         => 'redact_card_info',
    action              => dbms_redact.add_column,
    column_name         => 'card_string',
    function_type       => dbms_redact.partial,
    function_parameters => 'vvvvfvvvvfvvvvfvvvv,vvvv-vvvv-vvvv-vvvv,#,1,12'
  );
end;
/

alter session set nls_date_format='dd-mon-yyyy';
column card_no format 9999999999999999

select *
from   payment_details
order by id;

        ID CUSTOMER_ID           CARD_NO CARD_STRING         EXPIRY_DATE   SEC_CODE VALID_DATE
---------- ----------- ----------------- ------------------- ----------- ---------- -----------
         1        4000  1111111111111234 ####-####-####-1234 28-OCT-2015        123
         2        4001  1111111111112345 ####-####-####-2345 28-OCT-2015        234
         3        4002  1111111111113456 ####-####-####-3456 28-OCT-2015        345
         4        4003  1111111111114567 ####-####-####-4567 28-OCT-2015        456
         5        4004  1111111111115678 ####-####-####-5678 28-OCT-2015        567

5 rows selected.

SQL>

BEGIN
  DBMS_REDACT.alter_policy (
    object_schema       => user,
    object_name         => 'payment_details',
    policy_name         => 'redact_card_info',
    action              => DBMS_REDACT.add_column,
    column_name         => 'expiry_Date',
    function_type       => DBMS_REDACT.partial,
    function_parameters => 'm1d1Y'
  );
END;
/

ALTER SESSION SET nls_date_format='DD-MON-YYYY';
COLUMN card_no FORMAT 9999999999999999

SELECT *
FROM   payment_details
ORDER BY id;

        ID CUSTOMER_ID           CARD_NO CARD_STRING         EXPIRY_DATE   SEC_CODE VALID_DATE
---------- ----------- ----------------- ------------------- ----------- ---------- -----------
         1        4000  1111111111111234 ####-####-####-1234 01-JAN-2015        123
         2        4001  1111111111112345 ####-####-####-2345 01-JAN-2015        234
         3        4002  1111111111113456 ####-####-####-3456 01-JAN-2015        345
         4        4003  1111111111114567 ####-####-####-4567 01-JAN-2015        456
         5        4004  1111111111115678 ####-####-####-5678 01-JAN-2015        567

5 rows selected.

SQL>

conn testuser1/testuser1@//localhost:1521/pdb1

begin
  dbms_redact.alter_policy (
    object_schema       => user,
    object_name         => 'payment_details',
    policy_name         => 'redact_card_info',
    action              => dbms_redact.modify_expression,
    column_name         => 'card_no',
    expression          => 'sys_context(''userenv'',''session_user'') != ''TESTUSER1'''
  );
end;
/

-- Test on current user.
alter session set nls_date_format='dd-mon-yyyy';
column card_no format 9999999999999999

select *
from payment_details
order by id;

        ID CUSTOMER_ID           CARD_NO CARD_STRING         EXPIRY_DATE   SEC_CODE VALID_DATE
---------- ----------- ----------------- ------------------- ----------- ---------- -----------
         1        4000  1234123412341234 1234-1234-1234-1234 28-OCT-2015        123
         2        4001  2345234523452345 2345-2345-2345-2345 28-OCT-2015        234
         3        4002  3456345634563456 3456-3456-3456-3456 28-OCT-2015        345
         4        4003  4567456745674567 4567-4567-4567-4567 28-OCT-2015        456
         5        4004  5678567856785678 5678-5678-5678-5678 28-OCT-2015        567

5 rows selected.

SQL>


-- Connect to another user and test.
GRANT SELECT ON testuser1.payment_details TO testuser2;

conn testuser2/testuser2@//localhost:1521/pdb1

ALTER SESSION SET nls_date_format='DD-MON-YYYY';
COLUMN card_no FORMAT 9999999999999999

SELECT *
FROM   testuser1.payment_details
ORDER BY id;

        ID CUSTOMER_ID           CARD_NO CARD_STRING         EXPIRY_DATE   SEC_CODE VALID_DATE
---------- ----------- ----------------- ------------------- ----------- ---------- -----------
         1        4000  1111111111111234 ####-####-####-1234 01-JAN-2015        123
         2        4001  1111111111112345 ####-####-####-2345 01-JAN-2015        234
         3        4002  1111111111113456 ####-####-####-3456 01-JAN-2015        345
         4        4003  1111111111114567 ####-####-####-4567 01-JAN-2015        456
         5        4004  1111111111115678 ####-####-####-5678 01-JAN-2015        567

5 rows selected.

SQL>
4

Drop an Existing Policy

The procedure is used to remove an existing redaction policy. The following example drops the redaction policy and queries the data, showing the redaction is no longer taking place.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
conn testuser1/testuser1@//localhost:1521/pdb1

begin
  dbms_redact.drop_policy (
    object_schema => user,
    object_name   => 'payment_details',
    policy_name   => 'redact_card_info'
  );
end;
/

alter session set nls_date_format='dd-mon-yyyy';
column card_no format 9999999999999999

select *
from   payment_details
order by id;

        ID CUSTOMER_ID           CARD_NO CARD_STRING         EXPIRY_DATE   SEC_CODE VALID_DATE
---------- ----------- ----------------- ------------------- ----------- ---------- -----------
         1        4000  1234123412341234 1234-1234-1234-1234 28-OCT-2015        123
         2        4001  2345234523452345 2345-2345-2345-2345 28-OCT-2015        234
         3        4002  3456345634563456 3456-3456-3456-3456 28-OCT-2015        345
         4        4003  4567456745674567 4567-4567-4567-4567 28-OCT-2015        456
         5        4004  5678567856785678 5678-5678-5678-5678 28-OCT-2015        567

5 rows selected.

SQL>
5

Views

As mentioned previously, you can get information about redaction policies using the following views. - REDACTION_COLUMNS - REDACTION_POLICIES - REDACTION_VALUES_FOR_TYPE_FULL You can see examples scripts using these views here ( redaction_policies.sql , redaction_columns.sql , redaction_value_defaults.sql ).
6

Additional Information

- Redaction will not take place if the user has the system privilege. - If you try to (CTAS) against a redacted table you get the following error message. - The role includes the system privilege. As this role is granted to the DBA role, DBAs are excluded from redaction policies. - Redaction does not apply to the clause, so inference of the value is still possible for those with SQL access. - Enterprise Manager allows you to create and manage custom partial redaction formats, allowing you to build a library of your favourites. This is not part of the core functionality. This functionality is described here . - There have been several high profile bugs related to security holes in redaction. Make sure you are patched. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
ORA-28081: Insufficient privileges - the command references a redacted object.

Comments (0)

Please to add comments

No comments yet. Be the first to comment!