TECHNOLOGY: Database Cloud
Hide from Prying EyesBy Arup Nanda
Use Data Redaction in Oracle Database 12c to hide sensitive data automatically.
John, the lead DBA at Acme Bank, is listening patiently to the visitors in his office today. To comply with several mandates and regulations, the bank must make sure that some types of data—such as Social Security numbers and the date of the last withdrawal—in Acme’s database tables are altered to hide their true values from all visitors. This masking must be done at the database level—not by the user interface tools that are pulling the data. (Masking data to protect it from prying eyes is called redaction.) Jill, the lead developer at Acme Bank, emphasizes an important requirement: the data must remain intact in the database tables; it is only the displayed information that must be redacted. Further, she adds, the application user accounts must show the real account information without redaction when account holders pull it.
Traditionally, meeting this redaction requirement has meant creating views on tables and assigning privileges to users on the views, not on the actual database tables. Although the view approach works for redaction, it is complex, error-prone, and subject to performance issues. Jill asks if there is a simple and fast mechanism for setting up redaction.
Yes, there is, John informs her, with Oracle Advanced Security’s Data Redaction feature in Oracle Database 12c.
Jill explains that all of the bank’s data is stored in the TSBS schema, named after the application (Total Standard Banking System). A table named SAVINGS stores the details of the savings accounts. She shows everyone the structure of the SAVINGS table:
Name Null? Type ——————————— ———— ————————————————— ACCNO NUMBER ACCNAME VARCHAR2(20) ID_NO VARCHAR2(9) LAST_DEP_DT DATE FOLIOID NUMBER EMAIL VARCHAR2(200)
Table 1: Redaction requirements for Acme Bank’s SAVINGS table
Have no worries, John assures her. Acme can use Data Redaction in Oracle Database 12c to accomplish her objectives easily. The feature enables Acme to define a set of rules on what data can be redacted in a specific table, plus how and when. This set of rules is called a redaction policy, or just a policy for short. The supplied PL/SQL package, DBMS_REDACT, includes all the functionality for creating and maintaining policies on a table.
To set up a demonstration, John uses the setup.sql script. John then uses the code shown in Listing 1 to set up a redaction policy on the SAVINGS table.
create user tsbs identified by tsbs / grant create session, create table, unlimited tablespace to tsbs / create user app identified by app / conn tsbs/tsbs create table savings ( accno number, accname varchar2(20), id_no varchar2(9), last_dep_dt date, folioid number, email varchar2(200) ) / insert into savings values (101,'John Smith', '123456789', sysdate-1, 1234567, 'firstname.lastname@example.org') / insert into savings values (102, 'Jane Smith', '234567890', sysdate-2, 2345678, 'email@example.com') / insert into savings values (103, 'Jane Doe', '345678901', sysdate-3, 3456789, 'firstname.lastname@example.org') / commit / grant insert, select, delete, update on savings to app /
1 begin 2 dbms_redact.add_policy ( 3 object_schema => 'TSBS', 4 object_name => 'SAVINGS', 5 policy_name => 'Savings_Redaction', 6 expression => 'USER!=''TSBS''', 7 column_name => 'ID_NO', 8 function_type => dbms_redact.partial, 9 function_parameters => 'VVVVVVVVV,VVVVVVVVV,*,1,5' 10 ); 11 -- subsequent columns will need to be added 12 dbms_redact.alter_policy ( 13 object_schema => 'TSBS', 14 object_name => 'SAVINGS', 15 policy_name => 'Savings_Redaction', 16 action => dbms_redact.add_COLUMN, 17 column_name => 'FOLIOID', 18 function_type => dbms_redact.random 19 ); 20 dbms_redact.alter_policy ( 21 object_schema => 'TSBS', 22 object_name => 'SAVINGS', 23 policy_name => 'Savings_Redaction', 24 action => dbms_redact.add_COLUMN, 25 column_name => 'LAST_DEP_DT', 26 function_type => dbms_redact.partial, 27 function_parameters => 'MDy1900' 28 ); 29 dbms_redact.alter_policy ( 30 object_schema => 'TSBS', 31 object_name => 'SAVINGS', 32 policy_name => 'Savings_Redaction', 33 action => dbms_redact.add_COLUMN, 34 column_name => 'EMAIL', 35 function_type => dbms_redact.regexp, 36 regexp_pattern => dbms_redact.re_pattern_email_address, 37 regexp_replace_string => dbms_redact.re_redact_email_name, 38 regexp_position => dbms_redact.re_beginning, 39 regexp_occurrence => dbms_redact.re_all 40 ); 41 end;
John explains the redaction mechanism, by referring to the line numbers in Listing 1.
In line 8, he sets the extent of the redaction on the ID_NO column, which is partial (DBMS_REDACT.PARTIAL)—only a part of the value is to be redacted, not the entire value. (If he had wanted to redact the entire value, he would have used DBMS_REDACT.FULL here.)
Line 9 shows the redaction formats. There are five masking parameters, separated by commas, John explains. The first—VVVVVVVVV—is the input value; it is the actual value stored in the ID_NO column. In this case, each V represents a number. The second value—VVVVVVVVV—shows what to display. The third value, which in this case is *, shows the value to use in place of the redacted value. Because Jill wants to show * for the redacted values, John uses that character here. The fourth value indicates the position in the input value where the redaction should start, which, in this case is 1—meaning that the redaction should start in the first position. The fifth and final value shows the number of characters to be redacted from the starting position. Because Jill wants to redact the first five numbers, John uses 5 here. With this setting, if the input value is 123456789, the redaction will be between the first and fifth positions, inclusive, and * will be used to mask actual values, so the final redacted value will be *****6789.
But ID_NO is not the only column Acme needs to redact. To add the other columns to the redaction policy, John alters the policy by executing another procedure in the DBMS_REDACT package—alter_policy—shown in line 12 of Listing 1. The action parameter in line 16 specifies the type of alteration—add_column—and line 17 specifies the column that needs to be added to the policy—FOLIOID. Because the FOLIOID column should be completely redacted with random values, the next parameter—function_type—in line 18, specifies dbms_redact.random.
John adds the other columns to the policy in the same way. For the LAST_DEP_DT column, where only the year needs to be redacted, he sets the function_type parameter to dbms_redact.partial in line 26 of Listing 1. In line 27, he sets the function_parameters value to MDy1900. The use of uppercase M and D means that the month/day component of the date value should remain unaltered during redaction. The use of the lowercase y means that the year should be redacted and that the number immediately following that y—1900—is the value substituted for the actual value. The net effect of the parameter is that the date and month in the LAST_DEP_DT column value remain the same and the year is replaced everywhere with 1900.
The redaction requirement for the EMAIL column is a bit more complex. The redaction policy must detect a pattern in the value and redact only part of it. In this case, the policy must redact only the portion before the @ sign and leave the rest intact. Jill worries that it might require complex coding. On the contrary, John explains, it is quite easy with the built-in functions for regular expressions, which are designed for partial matching. In Listing 1, line 35, he sets the function_type parameter to a constant named dbms_redact.regexp, which instructs the redaction package to use regular expressions. The redaction package includes some predefined templates for different types of values, including e-mail addresses. In line 36, he specifies that EMAIL column values are in e-mail address format. In line 37, he specifies replacement of the name portion—the part before the @ sign. It’s that simple to include the appropriate parameters, he explains, and the dbms_redact package does the rest.
After John executes the code in Listing 1, the Savings_Redaction redaction policy on the SAVINGS table is now active on the ID_NO, LAST_DEP_DT, FOLIOID, and EMAIL columns. To test the redaction, Jill logs in to an application that connects as the APP user, and she selects from the SAVINGS table, as shown in Listing 2. Jill compares the results with her original requirements in Table 1 and pronounces them completely compliant. To complete the test, she logs in as the schema owner—TSBS—and selects from the SAVINGS table in the application. The results, shown in Listing 3, display the original values without redaction.
Code Listing 2: Selection by the APP user
SQL> conn app/app SQL> select * from tsbs.savings; ACCNO ACCNAME ID_NO LAST_DEP_DT FOLIOID EMAIL ————— —————————— ————————— ——————————— ——————— ———————————————————— 101 John Smith *****6789 21-SEP-00 3434562 email@example.com 102 Jane Smith *****7890 20-SEP-00 3452092 firstname.lastname@example.org 103 Jane Doe *****8901 19-SEP-00 4529012 email@example.com
SQL> conn tsbs/tsbs SQL> select * from tsbs.savings; ACCNO ACCNAME ID_NO LAST_DEP_DT FOLIOID EMAIL ————— —————————— ————————— ——————————— ——————— ———————————————————— 101 John Smith 123456789 21-SEP-13 1234567 firstname.lastname@example.org 102 Jane Smith 234567890 20-SEP-13 2345678 email@example.com 103 Jane Doe 345678901 19-SEP-13 3456789 firstname.lastname@example.org
READ more about DBMS_REDACT
LEARN more about Oracle Database security
DOWNLOAD Oracle Database 12c
That’s simple, John answers. Redaction protects data from being visible for examination, but it does not prevent application logic. Data Redaction, he explains, does not change the underlying data in the database; it redacts the data only when it is displayed. To demonstrate, he performs a simple update as the APP user, as shown in Listing 4. He also selects the data before and after the update. The update works perfectly without any issues, but the displayed data is redacted in all cases. What this means, John clarifies, is that the application needs no change whatsoever to enable redaction. Jill is greatly relieved to hear that.
Code Listing 4: Effect of redaction on update
SQL> select id_no, FOLIOID from tsbs.savings where accno=101; ID_NO FOLIOID —————————— —————————————— *****6789 7420987 SQL> update tsbs.savings set FOLIOID = 500 where id_no='123456789'; 1 row updated. SQL> select id_no, FOLIOID from tsbs.savings where accno=101; ID_NO FOLIOID —————————— —————————————— *****6789 7590112
Jack, another developer, is concerned that when a user creates another table by selecting data from the SAVINGS table, whose data is redacted—and, in some cases, replaced by completely random values—the new table may contain wrong data and create confusion among users, which is quite a serious issue. John assures him that that will not be the case and demonstrates:
SQL> create table my_savings as select * from tsbs.savings; create table my_savings as select * from tsbs.savings * ERROR at line 1: ORA-28081: Insufficient privileges - the command references a redacted object.
The realm of protection is not just for the table, John explains. If the APP user creates a view on the SAVINGS table, the view will also contain the redacted data.
Using the Data Redaction feature in Oracle Database 12c, Acme can add policies to tables to mask data in any column to a desired format quickly and easily. Acme does not need to change any existing application code to enable the redaction, and UPDATE, INSERT, and DELETE operations continue to work as before for authorized users. And, most importantly, Acme does not need to create, use, and maintain views.
Everyone is satisfied with the solution, and the meeting is adjourned.
Arup Nanda (email@example.com) has been an Oracle DBA since 1993, handling all aspects of database administration, from performance tuning to security and disaster recovery. He was Oracle Magazine’s DBA of the Year in 2003 and received an Oracle Excellence Award for Technologist of the Year in 2012.