Good database security should go well beyond ensuring your database has proper password policies in place, the operating system in hardened and firewalls are in place. It also go’s way beyond enabling the FIPS 140-2 parameters in your database to ensure that the security is in accordance to the FIPS 140-2 standard.
Database security and the security of the data inside the database is largely depending on the quality of the code running against the database and inside the database. It is largely depending on the datamodel architecture. When storing data in a database it is commonly understood that not all users should have access to all data that resides in the database. In general database architects and application developers try to resolve these kinds of challenges with granting and revoking rights to tables, creating intermediate views and limiting applications from accessing or displaying certain part of the information. The above mentioned solutions are serving their purpose and should form part of the overall datamodel architecture and data security architecture.
Oracle however provides a number of other options to resolve data access challenges. One of them is Oracle Data Redaction, a database solution helps database architects and database administrators to implement extremely fine grained data redaction (or masking). Oracle Data Redaction enables you to mask (redact) data that is returned from queries issued by applications. Oracle Database applies the redaction at runtime, at the moment application users attempt to access the data (that is, at query-execution time). This solution works well in a dynamic production system in which data is constantly changing. During the time that the data is being redacted, all of the data processing is performed normally, and the back-end referential integrity constraints are preserved. Additional benefit is that you can implement the data redaction in a way that can be changed in a non-intrusive way when the needs for redaction and data security change due to a change in policy or for example when a user changes his role in an organization and should have a changed level of access to data.
Oracle Data Redaction provides 5 options of redaction;
Full redaction. Full redaction will strip the data presented to the user. Meaning, character data will contain only a single space while numeric data is represented as a 0.
Partial redaction. This will only redact a part of the value. As an example you could redact a bank account number with asterisks with excpetion of the first 5 positions
Regular expressions. Regular expressions will provide you all the freedom you are used to with regular expressions and you can redact in any form you would like your data redacted.
Random redaction. Random redaction replaces the values with random data.
No redaction. Primarily used in redaction policies during testing of policies.
As an example we have created a database solution which keeps track of medicine distributions to patients in a hospital. Within this example we have two users who both need access to the database however who do not have the same level of access to the data at this moment.
The part of the model used in this example consists out of two tables and one view. The user C##USER2 has access to all data while the user C##USER1 will only have access to a certain set of the data. To shield some parts of the data from C##USER1 Oracle Data Redaction has been implemented.
When logged into the database as C##USER2 we can query the two tables and we will be able to see all the information.
SELECT * FROM C##USER1.patient_details
SELECT * FROM C##USER1.medicine_distribution
As you can see the owner of the tables is C##USER1 however we want to limit the same user from seeing this data. More specifically we do want to protect the email address and the social security number in the “patient_details” table and we want to protect the comments in the “medicine_distribution” table from C##USER1.
For the table “patient_details” it has been decided that the data in “patient_ssn” and “patient_mail” are to be redacted for user C##USER1. The email address needs to me masked completely and the social security number in “patient_ssn” need to be “stripped” from the last four numbers. To do so the below redaction policies are implemented based upon standard available Oracle Data Redaction options.
BEGIN DBMS_REDACT.ADD_POLICY( object_schema => 'C##USER1', object_name => 'PATIENT_DETAILS', column_name => 'PATIENT_SSN', policy_name => 'maskPatientDetails', function_type => DBMS_REDACT.PARTIAL, function_parameters => DBMS_REDACT.REDACT_US_SSN_L4, expression => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') = ''C##USER1''' ); END; BEGIN DBMS_REDACT.alter_policy( object_schema => 'C##USER1', object_name => 'PATIENT_DETAILS', column_name => 'PATIENT_EMAIL', policy_name => 'maskPatientDetails', function_type => dbms_redact.regexp, regexp_pattern => dbms_redact.re_pattern_email_address, regexp_replace_string => dbms_redact.RE_REDACT_EMAIL_ENTIRE, regexp_position => dbms_redact.re_beginning, regexp_occurrence => dbms_redact.re_all, regexp_match_parameter => 'i', expression => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') = ''C##USER1''' ); END;
When the user C##USER1 now queries the data both the email address and the social security number are masked in the way that is required as can be seen from the below example:
To protect the comments in the “medicine_distribution” table from C##USER1 we implement a redaction policy by executing the below statement. This statement includes the expression to ensure that the data in the comments column will be redacted fully if the user who initiated the session is user C##USER1.
BEGIN DBMS_REDACT.ADD_POLICY( object_schema => 'C##USER1', object_name => 'medicine_distribution', column_name => 'comments', policy_name => 'maskmedicinecomments', function_type => DBMS_REDACT.full, expression => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') = ''C##USER1''' ); END;
The above means that everyone with the exception of user C##USER1 will be able to see the comments data in the “medicine_distribution” table. If we now login as C##USER1 and we query the table we will see that there is no data shown where we would previously see the comments data. However, C##USER1 is still able to create queries and prepare analysis with SQL without seeing the real data itself.
If we now login as a different user, in our case C##USER2 and we execute the same query we will still be able to see the data as shown below:
In general Oracle Data Redaction is a very useful solution to redact some parts of the data for certain specific users, users with a specific role and specific groups of application users. This is specially the case when you need additional data redaction on top of an already implemented data security model. It is known that some flaws exist in the current version of Oracle Data Redaction, even though some flaws have been identified and are currently being worked on by Oracle who are expected to have this resolved in the upcoming time, Oracle Data Redaction is in general a very useful addition in securing the data and enabling a more fine-grained and flexible solution for data protection.
The above used examples, including the tables, data and policies are available as opensource examples and can be downloaded from Github.
For more information about this topic, feel free to contact Johan Louwers directly via firstname.lastname@example.org