For Your Eyes Only - Redact to Protect
In a previous blog post, I provided a recipe for enforcing encryption for data-at-rest. However, it is important to remember that Transparent Data Encryption (TDE) protects your data from bad actors, who might have illegally gained access to the data files. Data exists unencrypted when loaded and accessed in the database. Malicious attacks can come from internal sources as well. When storing user confidential information, data stewards need to take greater responsibility in ensuring that data is only seen by staff who are authorized and require access to privileged information. That could mean creating tiered access and other protections like data redaction.
For example, an e-commerce website that handles credit card transactions, may require call centre staff to have access to the last four digits of a credit card number for verifying customers' identity when support call are received. Or an insurance claims website that requires claimants to submit their health identity number. The application displays the partially or fully-masked ID, enough to suggest that the data has been captured, but insufficient for a passerby to steal a glance.
Oracle's Data Redaction makes this possible, and with the latest release of Oracle Database 18c Express Edition, this technology is now acessible to developers for FREE! If you are new to Data Redaction, here's a simple recipe to get started!
Staging the Example
Create Table PAYMENTINFO
Create a simple table called paymentinfo to store customers' names and credit card information.
create table paymentinfo (
transactionid number generated always as identity
, first_name varchar2(200) not null
, last_name varchar2(200) not null
, ccard_number varchar2(30) not null
)
/
Add Mock Data
There are many sites and applications out there that help you generate mock data. Here are two examples:
Both sites help generate various types of data. For the purpose of demo, I generated a 100 rows of first names, last names and fictitious credit card numbers (any similarities is pure coincidence). Load the data.
insert into paymentinfo (first_name, last_name, ccard_number) values ('Brigham', 'Raftery', '3558886053942589');
insert into paymentinfo (first_name, last_name, ccard_number) values ('Willi', 'Milksop', '3566444037931078');
insert into paymentinfo (first_name, last_name, ccard_number) values ('Spencer', 'Toy', '3578657283591918');
insert into paymentinfo (first_name, last_name, ccard_number) values ('Eleanor', 'Sherlaw', '201745575867996');
insert into paymentinfo (first_name, last_name, ccard_number) values ('Matelda', 'Joskowicz', '3567589132290124');
insert into paymentinfo (first_name, last_name, ccard_number) values ('Hyacinth', 'Haville', '201542493937096');
insert into paymentinfo (first_name, last_name, ccard_number) values ('Cristi', 'Yakubowicz', '3535186423765655');
insert into paymentinfo (first_name, last_name, ccard_number) values ('Bess', 'Atte-Stone', '501891982886122702');
insert into paymentinfo (first_name, last_name, ccard_number) values ('Madeleine', 'Spacy', '5299626156704075');
insert into paymentinfo (first_name, last_name, ccard_number) values ('Alick', 'Lilly', '4041597161949423');
Now query the data.
SQL> column first_name format a20
SQL> column last_name format a20
SQL> column ccard_number format a20
SQL> select * from paymentinfo where rownum <= 10;
TRANSACTIONID FIRST_NAME LAST_NAME CCARD_NUMBER
------------- -------------------- -------------------- --------------------
1 Brigham Raftery 3558886053942589
2 Willi Milksop 3566444037931078
3 Spencer Toy 3578657283591918
4 Eleanor Sherlaw 201745575867996
5 Matelda Joskowicz 3567589132290124
6 Hyacinth Haville 201542493937096
7 Cristi Yakubowicz 3535186423765655
8 Bess Atte-Stone 501891982886122702
9 Madeleine Spacy 5299626156704075
10 Alick Lilly 4041597161949423
10 rows selected.
Data Redaction
When working with Oracle Application Express (APEX) or any other programming languages, we would typically use database views to query data for display, e.g. Interactive Reports. We could attempt to "mask" the data with a query something like this:
create or replace force view vw_paymentinfo
as
select
transactionid
, first_name
, last_name
, case
when length(ccard_number) > 4 then
regexp_replace(
substr(ccard_number, 0, length(ccard_number)-4)
, '\d'
, '*'
)
|| substr(ccard_number, -4)
else
regexp_replace(ccard_number,'\d','*')
end as ccard_number
from paymentinfo
;
Then query the data using the view.
SQL> select * from vw_paymentinfo where rownum <= 10;
TRANSACTIONID FIRST_NAME LAST_NAME CCARD_NUMBER
------------- -------------------- -------------------- --------------------
1 Brigham Raftery ************2589
2 Willi Milksop ************1078
3 Spencer Toy ************1918
4 Eleanor Sherlaw ***********7996
5 Matelda Joskowicz ************0124
6 Hyacinth Haville ***********7096
7 Cristi Yakubowicz ************5655
8 Bess Atte-Stone **************2702
9 Madeleine Spacy ************4075
10 Alick Lilly ************9423
10 rows selected.
Great! The credit card numbers are successfully masked. However, there are some key concerns with this approach. When implementing security for applications and system, I generally subscribe to a few "golden rules".
- Avoid reinventing the wheel - the same is said with data encrypting methods. It's always preferred that we apply commonly used, well-tested and accepted algorithms. There are many credit card options. Designing a comprehensive regular expression or string replacement scheme to cover all the available number formats is not easy. Why not rely on off-the-shelf solutions from companies that have invested significantly and has a lot of experience dealing with these issues, for handling these monumental and critical tasks.
- Separation of duties - if developers have no need to view the data of the applications they build, then perhaps they should not. Creating data security policies and enforcement should preferably be the responsibility of a separate person or team.
Oracle's Gift
When Oracle Database 12c was released, the company restructured its Advanced Security Option offering. Secure database connections became a "feature" and a new technology, Data Redaction, was introduced and became part of the for-fee product. We, Oracle Database Developers, are very fortunate that this premium functionality is now available for free with the Oracle Database 18c XE.
With Data Redaction, I am relying on Oracle's vast experiences implementing security and compliance with well-known, well-defined security requirements. It offers predefined redaction rules that not only mask credit card numbers, but national identification numbers, phone numbers, email addresses and even IP addresses.
Redaction policies are created at the "database level", so that means they apply regardless of how you access the data. Running a query in SQL Developer, SQLcl or through an APEX application yields the exact same results.
As a best practice (separation of duties), Oracle encourages customers to use a different user/role for managing redaction policies. Let's begin by creating a user sec_admin for this task. We note the following requirements for creating such a user (from the docs):
- To create redaction policies, you must have the EXECUTE privilege on the DBMS_REDACT PL/SQL package.
- You do not need any privileges to access the underlying tables or views that will be protected by the policy.
Let's do that. As SYS user, execute the following commands:
create user sec_admin identified by "sec_admin";
grant create session to sec_admin;
grant execute on dbms_redact to sec_admin;
Note that the user sec_admin, would have rights to create and enforce data redaction policies, but will not have access to the data it is tasked to protect.
Next, create a data redaction policy for masking the data in column ccard_number.
begin
dbms_redact.add_policy(
object_schema => 'secure_user'
, object_name => 'paymentinfo'
, policy_name => 'ccardnum_mask'
, column_name => 'ccard_number'
, function_type => dbms_redact.regexp
, function_parameters => null
, regexp_pattern => dbms_redact.re_pattern_ccn
, regexp_replace_string => dbms_redact.re_redact_ccn
, regexp_position => dbms_redact.re_beginning
, regexp_occurrence => dbms_redact.re_first
, regexp_match_parameter => dbms_redact.re_case_insensitive
, expression => '1=1'
);
end;
/
Run the query again.
SQL> select * from paymentinfo where rownum <= 10;
TRANSACTIONID FIRST_NAME LAST_NAME CCARD_NUMBER
------------- -------------------- -------------------- --------------------
1 Brigham Raftery ************2589
2 Willi Milksop ************1078
3 Spencer Toy ************1918
4 Eleanor Sherlaw ************7996
5 Matelda Joskowicz ************0124
6 Hyacinth Haville ************7096
7 Cristi Yakubowicz ************5655
8 Bess Atte-Stone ************2702
9 Madeleine Spacy ************4075
10 Alick Lilly ************9423
10 rows selected.
Applying Data Redaction in APEX
Data Redaction policies are enforced regardless of how the data is accessed. In the previous examples, data was queried using SQLcl as the schema owner. Let's try accessing the data using a simple Classic Report that runs the same query against the table paymentinfo. Below is a short screen recording of data redaction in action.
Notice that the data is masked regardless of who accesses the application as the expresion condition is 1=1.
Selectively Applying Policies
In this next and final section, we will use Expressions to determine if the data should or should not be masked, depending on the user's role in the application.
We begin by adding an Application Item the should be restricted and may not be set by the browser. Set the application state using a Post-Authentication procedure in the application's current Authentication Scheme.
Next, update the expression attribute for the ccardnum_mask policy to use the Application Item's session state.
begin
dbms_redact.alter_policy(
object_schema => 'secure_user'
, object_name => 'paymentinfo'
, policy_name => 'ccardnum_mask'
, column_name => 'ccard_number'
, action => dbms_redact.modify_expression
, expression => 'v(''APP_ROLE'') != ''Administrator'''
);
end;
/
Watch the screen recording of how the results of the Classic Report now differs between a User and an Administrator.
Using an Expression for Determining When Data Redaction Policy is AppliedEven with this approach, a potential loophole remains. A malicious actor (developer) could alter the session state of this item or how it is set, and gain access to the data unprotected. Think about combining data redaction (using SYS_XS_CONTEXT in the expression) with Oracle's Real Application Security (RAS) and you'll have a near air-tight solution. RAS incidentally, is also available to use in XE, and if you'd like to learn more, check out Dimitri Gielis' blog post on enabling RAS in APEX.
Closing Remarks
To remove a policy, run the following procedure and specify the object's schema, name and the policy name to drop.
begin
dbms_redact.drop_policy(
object_schema => 'secure_user'
, object_name => 'paymentinfo'
, policy_name => 'ccardnum_mask'
);
end;
/
Finally, through this article, I hope I have given you a preview of what the technology is capable of, and have sparked some interest There's much to know and learn about Oracle Data Redaction, the full spectrum of what it offers and its limitations. You are highly encouraged to read the official documentation, test out various scenarios and consider using this technology, especially if you are a custodian of confidential data.