Database tables often contain sensitive information such as credit card details, contact information, and personal bank details, which require safeguarding against unauthorized access. To address this concern, Oracle 12c and later versions offer the DBMS_REDACT procedure, allowing the masking of specific columns within tables. This blog post delves into the implementation of dynamic data masking using the prebuilt PLSQL procedure “DBMS_REDACT.”
In this blog, We are going to discuss about dynamic data masking technique using prebuilt PLSQL procedure “DBMS_REDACT”.
There are various procedures inside DBMS_REDACT package which are listed below.
- DBMS_REDACT.ADD_POLICY
- DBMS_REDACT.ALTER_POLICY
- DBMS_REDACT.APPLY_POLICY_EXPR_TO_COL
- DBMS_REDACT.CREATE_POLICY_EXPRESSION
- DBMS_REDACT.DISABLE_POLICY
- DBMS_REDACT.DROP_POLICY
- DBMS_REDACT.DROP_POLICY_EXPRESSION
- DBMS_REDACT.ENABLE_POLICY
- DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES
- DBMS_REDACT.UPDATE_POLICY_EXPRESSION
In this post, We are going to discuss about “DBMS_REDACT.ADD_POLICY ” and “DBMS_REDACT.ALTER_POLICY” procedures only. .
To demonstrate dynamic data masking, we are using following schemas.
security_admin –> We will manage DBMS_REDACT policies using this schema. We have given execute privilege on “DBMS_REDACT” procedure.
bank_admin —> this account will have tables on which we are performing data masking.
sales_rep & support_rep –> these are two basic accounts we are using to test data masking.
Create accounts which are needed for demonstrating data masking concept:
Connect to PDB as sys and create required accounts.
SQL> conn sys/Oracle123@orcl21c as sysdba
Connected.
SQL> create user security_admin identified by Oracle123 default tablespace users quota 100m on users;
User created.
SQL> grant connect,resource to security_admin;
Grant succeeded.
SQL> grant execute on DBMS_REDACT to security_admin;
Grant succeeded.
SQL> create user bank_admin identified by Oracle123 default tablespace users quota 100m on users;
User created.
SQL> grant connect,resource,create table to bank_admin;
Grant succeeded.
SQL> create user sales_rep identified by Oracle123 default tablespace users quota 100m on users;
SQL> create user support_rep identified by Oracle123 default tablespace users quota 100m on users;
SQL> grant connect,resource to sales_rep;
SQL> grant connect,resource to support_rep;
SQL>
Create Table “customer_order_info” and insert data into that table which is under bank_admin user.
SQL> conn bank_admin/Oracle123@orcl21c
Connected.
SQL> CREATE TABLE customer_order_info(
first_name varchar2(20),
last_name varchar2(20),
address varchar2(30),
city varchar2(30),
state varchar2(3),
zip varchar2(5),
cc_num varchar(19),
cc_exp varchar2(7));
Table created.
SQL> INSERT INTO customer_order_info VALUES (‘John’,’Dough’,’39 Mockingbird Lane’, ‘San Francisco’, ‘CA’, 94114, ‘5111 1998 0533 5100’, ’10/2018′);
SQL> INSERT INTO customer_order_info VALUES (‘Mathew’,’Hightower’,’2319 Maple Street’, ‘Sonoma’, ‘CA’, 95476, ‘5222 1122 1134 1118’, ’03/2019′);
SQL> INSERT INTO customer_order_info VALUES (‘Herbert’,’Donahue’,’292 Winsome Way’, ‘San Francisco’, ‘CA’, 94117, ‘5454 5454 5454 5454′, ’08/2018’);
SQL> commit;
Commit complete.
SQL> select count(*) from customer_order_info;
COUNT(*)
———-
3
Grant select on table to user “security_admin” who will manage data masking related policies. And also give grant on table “bank_admin.customer_order_info” to two different test accounts sales_rep & support_rep.
SQL> conn sys/Oracle123@orcl21c as sysdba
SQL> grant select on bank_admin.customer_order_info to security_admin;
Grant succeeded.
SQL> grant select on bank_admin.CreditCardInfo to security_admin;
Grant succeeded.
SQL> grant select on bank_admin.customer_order_info to sales_rep;
SQL> grant select on bank_admin.customer_order_info to support_rep;
Add Policy to enable data masking on table column cc_num [Creditcard Number]
SQL> conn security_admin/Oracle123@orcl21c
Connected.
SQL> BEGIN DBMS_REDACT.ADD_POLICY(
object_schema => ‘bank_admin’,
object_name => ‘customer_order_info’,
column_name => ‘cc_num’,
policy_name => ‘redact_cust_cc_info’,
function_type => DBMS_REDACT.REGEXP,
function_parameters => NULL,
expression => ‘1=1’,
regexp_pattern => DBMS_REDACT.RE_PATTERN_CCN,
regexp_replace_string => DBMS_REDACT.RE_REDACT_CCN,
regexp_position => NULL,
regexp_occurrence => NULL,
regexp_match_parameter => NULL,
policy_description => ‘Partially redacts credit card info’,
column_description => ‘cc_num_number lists credit card numbers’);
END;
/
PL/SQL procedure successfully completed.
SQL>
| DBMS_REDACT.RE_PATTERN_CCN | Matches credit card numbers other than American Express credit card numbers. The appropriate regexp_replace_string setting to use with this format is DBMS_REDACT.RE_REDACT_CCN. The end result is a redaction of all the digits except the last 4. |
| DBMS_REDACT.RE_PATTERN_CCN | Matches credit card numbers other than American Express credit card numbers. The appropriate regexp_replace_string setting to use with this format is DBMS_REDACT.RE_REDACT_CCN. The end result is a redaction of all the digits except the last 4. |
| To set a redaction policy to redact all data in the column, you must set the function_type parameter to DBMS_REDACT.FULL.
By default, NUMBER data type columns are replaced with zero (0) and character data type columns are replaced with a single space ( ).
|
|
=====> Alter policy to Hide Specific column “FIRST_NAME” from specific users.
SQL> conn security_admin/Oracle123@orcl21c
Connected.
SQL> BEGIN DBMS_REDACT.ALTER_POLICY (
object_schema => ‘bank_admin’,
object_name => ‘customer_order_info’ ,
policy_name => ‘redact_cust_cc_info’,
function_type => DBMS_REDACT.FULL, ###Hide all Column data
action => DBMS_REDACT.ADD_COLUMN,
column_name => ‘FIRST_NAME’ );
END;
/
PL/SQL procedure successfully completed.
Modify the policy to view redated values info by sales_rep user and to view actual data by support_rep user.
SQL> BEGIN
DBMS_REDACT.ALTER_POLICY(
object_schema => ‘bank_admin’,
object_name => ‘customer_order_info’,
policy_name => ‘redact_cust_cc_info’,
action => DBMS_REDACT.MODIFY_EXPRESSION,
expression => ‘SYS_CONTEXT(”USERENV”,”SESSION_USER”) = ”SALES_REP”’);
END;
/
PL/SQL procedure successfully completed.
SQL>
If you observe below details, sales_rep couldn’t able to see CC_NUM data. But same data visible to support_rep user even both users having select privilege on table customer_order_info.
SQL> conn sales_rep/Oracle123@orcl21c
Connected.
SQL> select first_name,cc_num from bank_admin.customer_order_info;
FIRST_NAME CC_NUM
——————– ————————————————–
************5100
************1118
************5454
SQL> conn support_rep/Oracle123@orcl21c
Connected.
SQL> select first_name,cc_num from bank_admin.customer_order_info;
FIRST_NAME CC_NUM
——————– ————————————————–
John 5105 1051 0510 5100
Mathew 5111 1111 1111 1118
Herbert 5454 5454 5454 5454
SQL>
Hope You could able to understand how to mask table specific column info.
