In our previous blog, we described some of the features and benefits of Oracle Data Redaction and described some of the use cases for redaction, also known as dynamic masking. For this installment, we will cover some of the improvements introduced with Oracle Database 23ai and provide some examples of how you can take advantage of these new capabilities today.
Improved SQL support
With Oracle Database 23ai, Oracle Data Redaction offers robust new options for protecting sensitive data by masking it as it is returned from a database query. Use of data redaction can help organizations protect data that is displayed on a screen or used in a report and can help them meet data privacy compliance requirements to limit exposure. Some of the enhancements we introduced include support for redacting the following:
- Data in views: Redact columns in the target list of views, including both CREATE VIEW and inline views.
- Base columns of virtual columns: Redact a base column in a virtual column.
- Inline views with SET Operators: Use common table expressions (WITH clause), OUTER JOINs, and aggregate functions such as COUNT and MAX, on columns with Data Redaction policies.
- Data from queries including DISTINCT, GROUP BY and ORDER BY: Queries that include expressions on redacted columns in both the GROUP BY and SELECT lists are now fully supported.
Examples of redaction in action
Here’s a detailed look at the new features, as well as examples using the Oracle sample schema. If you want to follow along with your own Oracle Database you may install the sample schema, sales history (SH). You can find the latest copy here. Note these examples require Oracle Database 23.6.
First, create a read only user and grant it the appropriate privileges on SH schema objects.
BEGIN
DBMS_REDACT.ADD_POLICY(
object_schema => 'SH',
object_name => 'CUSTOMERS',
policy_name => 'REDACT_SENSITIVE_DATA',
expression => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''SH'''
);
END;
/
Redacting data in views
Database views are frequently used to simplify data access as a form of data security, data abstraction, data aggregation, or data transformation. Data Redaction now lets you include expressions on redacted columns in the target list of views, including both CREATE VIEW and inline views. For example, let’s say you want to create a simplified view of customer data called CUSTOMER_VIEW. This view will transform first and last names, as well as the material status, into a format your reporting tools will understand.
CREATE OR REPLACE VIEW sh.customer_view AS
SELECT
cust_id,
UPPER(cust_first_name) AS FIRST_NAME,
UPPER(cust_last_name) AS LAST_NAME,
CASE
WHEN cust_marital_status IS NULL OR TRIM(cust_marital_status) = '' THEN 'UNKNOWN'
ELSE UPPER(cust_marital_status)
END AS marital_status,
cust_gender AS GENDER,
cust_email AS EMAIL,
cust_postal_code AS POSTAL_CODE,
cust_credit_limit AS CREDIT_LIMIT
FROM sh.customers;
Apply the redaction policy to the CUST_MARITAL_STATUS column of the base table:
BEGIN DBMS_REDACT.ALTER_POLICY ( object_schema => 'SH', object_name => 'CUSTOMERS', policy_name => 'REDACT_SENSITIVE_DATA', column_name => 'CUST_MARITAL_STATUS', action => DBMS_REDACT.ADD_COLUMN, function_type => DBMS_REDACT.FULL); END; /
Next, connect as SH_VIEWER and query the CUSTOMER_VIEW:
SELECT CUST_ID, FIRST_NAME, LAST_NAME, EMAIL, MARITAL_STATUS FROM sh.customer_view WHERE cust_id IN (101, 103, 176, 201);
When you run the query, you will see the data returned with the MARITAL_STATUS column redacted.
SELECT CUST_ID, FIRST_NAME, LAST_NAME, EMAIL, MARITAL_STATUS
FROM sh.customer_view
WHERE cust_id IN (101, 103, 176, 201);
CUST_ID FIRST_NAME LAST_NAME EMAIL MARITAL_STATUS
---------- --------------- --------------- -------------------------------------- --------------
101 LOU FITZ lou.fitz@company2.example.com
103 AILEEN NEWKIRK aileen.newkirk@company2.example.com
176 CARIN LIGHTFOOT carin.lightfoot@company2.example.com
201 ALYSSA CLIPP alyssa.clipp@company2.example.com
The view we you created applies the required transformations to the FIRST_NAME and LAST_NAME columns and modifies the marital status column format while maintaining the redaction policy on the underlying data. The ability to redact data in view allows your users to see the data masked the same way whether they query from the view or from the base table.
Redacting the base columns of virtual columns
Rather than requiring your users to query individual columns, you may want to combine multiple columns into a single virtual column. For example, let’s say you created a virtual column to combine CUST_STREET_ADDRESS, CUST_CITY, CUST_STATE_PROVINCE, and CUST_POSTAL_CODE into FULL_ADDRESS. However, you may not want to display the customer’s postal code in the query results when your users query the virtual column, FULL_ADDRESS. Previously, you would not be able to apply a redaction policy to the CUST_POSTAL_CODE column, however you can with Oracle Database 23ai. Here is an example:
ALTER TABLE sh.customers ADD (full_address AS
(cust_street_address || ', ' || cust_city ||
', ' || cust_state_province || ' ' || cust_postal_code));
BEGIN
DBMS_REDACT.ALTER_POLICY(
object_schema => 'SH',
object_name => 'CUSTOMERS',
column_name => 'CUST_POSTAL_CODE',
policy_name => 'REDACT_SENSITIVE_DATA',
function_type => DBMS_REDACT.FULL
);
END;
/
In previous releases, you could not apply a redaction policy to a column that is part of a virtual column. You would receive this error message:
ORA-28073: The column "CUST_POSTAL_CODE" has an unsupported data type or attribute.
If you tried to add the virtual column after having a redaction policy, you would see an error like this:
ORA-28083: A redacted column was referenced in a virtual column expression. With Oracle Database 23ai, SH_READER will see a result like this:
SELECT cust_id, cust_last_name, full_address
FROM sh.customers
WHERE cust_id IN (101, 103, 176, 201);
CUST_ID CUST_LAST_NAME FULL_ADDRESS
---------- -------------- ------------
101 FITZ
103 NEWKIRK
176 LIGHTFOOT
201 CLIPP
Note you cannot apply redaction policies to the virtual column itself, but only the base columns.
Now, when you query the REDACTION_COLUMNS view, you will see two columns for 19c and two columns for 23.6.
COLUMN column_name FORMAT a25 COLUMN function_type FORMAT a25 SELECT column_name, function_type FROM REDACTION_COLUMNS WHERE OBJECT_OWNER = 'SH' AND OBJECT_NAME = 'CUSTOMERS'; COLUMN_NAME FUNCTION_TYPE -------------------- ----------------- CUST_MARTIAL_STATUS FULL REDACTION CUST_POSTAL_CODE FULL REDACTION
Redacting data in inline views with SET operators
Let’s say a line of business wants to use production data to create a report identifying the number of customers above and below the average credit limit. Furthermore, they want to be careful not to expose actual credit limit values in their report. To accomplish this, a data analyst uses a complex query that includes common table expressions (WITH clause), OUTER JOINs, and aggregate functions such as COUNT and MAX. With Oracle 23ai, the DBA can define a Data Redaction policy that will selectively mask these values in the report.
In this example, the data analyst user (SH_READER) should be able to see the CUST_POSTAL_CODE column. So we will drop that column from the Data Redaction policy and add the CUST_CREDIT_LIMIT column as a fully redacted column.
BEGIN
DBMS_REDACT.ALTER_POLICY (
object_schema => ‘SH’,
object_name => ‘CUSTOMERS’,
policy_name => ‘REDACT_SENSITIVE_DATA’,
column_name => ‘CUST_POSTAL_CODE’,
action => DBMS_REDACT.DROP_COLUMN);
END;
/
BEGIN
DBMS_REDACT.ALTER_POLICY (
object_schema => ‘SH’,
object_name => ‘CUSTOMERS’,
policy_name => ‘REDACT_SENSITIVE_DATA’,
column_name => ‘CUST_CREDIT_LIMIT’,
action => DBMS_REDACT.ADD_COLUMN,
function_type => DBMS_REDACT.FULL);
END;
/
The following query will create a table that groups customers by postal code and displays the number of customers above and below the credit limit, while redacting the highest, lowest, or average credit limit data in the output.
COLUMN cust_postal_code HEADING 'Postal|Code'
COLUMN total_customer_count HEADING 'Total|Customer|Count'
COLUMN num_customers_below_avg HEADING 'Num Customers|Below Avg'
COLUMN num_customers_above_avg HEADING 'Num Customers|Above Avg'
COLUMN highest_credit_limit HEADING 'Highest|Credit Limit'
COLUMN lowest_credit_limit HEADING 'Lowest|Credit Limit'
COLUMN average_credit_limit HEADING 'Average|Credit Limit'
WITH avg_credit_limits AS (
SELECT cust_postal_code, AVG(cust_credit_limit) AS avg_credit_limit
FROM sh.customers
WHERE cust_postal_code IN ('55787', '63736', '38082')
AND cust_credit_limit IS NOT NULL
GROUP BY cust_postal_code
)
SELECT
c.cust_postal_code,
COUNT(*) AS total_customer_count,
COUNT(CASE WHEN c.cust_credit_limit < acl.avg_credit_limit THEN 1 END) AS num_customers_below_avg,
COUNT(CASE WHEN c.cust_credit_limit > acl.avg_credit_limit THEN 1 END) AS num_customers_above_avg,
MAX(c.cust_credit_limit) AS highest_credit_limit,
ROUND(MIN(c.cust_credit_limit), 0) AS lowest_credit_limit,
ROUND(AVG(c.cust_credit_limit), 0) AS average_credit_limit
FROM sh.customers c
JOIN avg_credit_limits acl ON c.cust_postal_code = acl.cust_postal_code
WHERE c.cust_postal_code IN ('55787', '63736', '38082')
GROUP BY c.cust_postal_code
ORDER BY c.cust_postal_code;
The schema user (SH) will see the unredacted credit limit data when running the report.
Total Postal Customer Num Customers Num Customers Highest Lowest Average Code Count Below Avg Above Avg Credit Limit Credit Limit Credit Limit ---------- ---------- ------------- ------------- ------------ ------------ ------------ 38082 695 359 336 15000 1500 5947 55787 612 301 311 15000 1500 6223 63736 637 334 303 15000 1500 5830
However, when the data analyst user (SH_READER) runs the query, actual credit limits will be redacted.
Total Postal Customer Num Customers Num Customers Highest Lowest Average Code Count Below Avg Above Avg Credit Limit Credit Limit Credit Limit ---------- ---------- ------------- ------------- ------------ ------------ ------------ 38082 695 359 336 0 0 0 55787 612 301 311 0 0 0 63736 637 334 303 0 0 0
This report displays the column averages but not the unredacted credit limit data.
Note: Data Redaction does not place any restriction on the WHERE clause of ad hoc SQL, so the WHERE clause could be used in an iterative fashion to infer the actual data even when there is a Data Redaction policy on the queried column and only the redacted value is displayed. But if you have pre-defined analytics reports like above, you can reduce the risk of sensitive data being exposed without rewriting the query or updating the reporting tool.
Redacting queries with DISTINCT, GROUP BY and ORDER BY clauses
Most analytic or advanced queries rely on support for DISTINCT, GROUP BY and ORDER BY clauses. Queries that include expressions on redacted columns in both the GROUP BY and SELECT lists are now fully supported with Oracle Database 23ai. Additionally, queries using SELECT DISTINCT and ORDER BY clauses can now include redacted columns, helping to prevent exposure of sensitive data without the need to re-write the query.
Our final example demonstrates how you can redact sensitive data in these analytic-type queries. This query selects distinct values from a redacted column and orders them, applying redaction policies to the sensitive column data while sorting.
COLUMN cust_postal_code HEADING 'Postal|Code' COLUMN total_credit_limit HEADING 'Total|Credit' SELECT cust_postal_code, SUM(cust_credit_limit) AS TOTAL_CREDIT_LIMIT FROM sh.customers GROUP BY cust_postal_code ORDER BY total_credit_limit DESC FETCH FIRST 10 ROWS ONLY; ; With previous database releases, you would have received an error like the following: ORA-28094: SQL construct not supported by data redaction With Oracle 23ai, you can now view the data sorted by the aggregate total of the salaries for each department. COLUMN cust_postal_code HEADING 'Postal|Code' COLUMN total_credit_limit HEADING 'Total|Credit' SELECT cust_postal_code, SUM(cust_credit_limit) AS total_credit_limit FROM sh.customers GROUP BY cust_postal_code ORDER BY total_credit_limit DESC FETCH FIRST 10 ROWS ONLY; The schema user (SH) will see the credit limitdata. Postal Total Code Credit ---------- ---------- 38082 4133000 78558 3904500 55787 3808500 48346 3741000 63736 3713500 45704 3680500 69776 3647500 67843 3606500 72860 3277500 80841 3262500 10 rows selected. The data analyst user (SH_READER) will not see the credit limit data in the returned query results. Postal Total Code Credit ---------- ---------- 38082 0 78558 0 55787 0 48346 0 63736 0 45704 0 69776 0 67843 0 72860 0 80841 0
10 rows selected.
Restore the SH schema to its original configuration
To clean-up, we will drop the view, redaction policy and virtual column with the following SQL:
DROP VIEW sh.customer_view;
BEGIN
DBMS_REDACT.DROP_POLICY(
object_schema => 'SH',
object_name => 'CUSTOMERS',
policy_name => 'REDACT_SENSITIVE_DATA'
);
END;
/
ALTER TABLE sh.customers DROP COLUMN full_address;
Try Oracle Data Redaction Today
These updates to Oracle Data Redaction provide powerful new tools for database administrators and developers, ensuring that sensitive data remains secure and compliant with industry standards. By integrating these features into your data management strategy, you can enhance both the security and functionality of your Oracle databases.
To learn more about Data Redaction, visit the Advanced Security product page on the Oracle web site. Get hands-on experience with Oracle Data Redaction by exploring one of our free Redaction LiveLabs. Visit Oracle LiveLabs and search for “redaction” to access interactive labs that showcase redaction capabilities in action.