Data masking is a crucial technique for securing sensitive data, especially when providing data for non-production environments. Oracle offers robust data masking capabilities to ensure sensitive information remains protected. Here’s a step-by-step example of how to implement data masking in Oracle.

Step 1: Setup Sample Table

First, we’ll create a sample table that contains sensitive data, such as credit card numbers.

CREATE TABLE customers (
customer_id NUMBER PRIMARY KEY,
name VARCHAR2(50),
email VARCHAR2(100),
credit_card_number VARCHAR2(19)
);

INSERT INTO customers (customer_id, name, email, credit_card_number) VALUES
(1, ‘John Doe’, ‘john.doe@example.com’, ‘4111111111111111’),
(2, ‘Jane Smith’, ‘jane.smith@example.com’, ‘5500000000000004’),
(3, ‘Alice Johnson’, ‘alice.johnson@example.com’, ‘340000000000009’);

COMMIT;

Step 2: Create a Data Masking Policy

Next, we’ll define a data masking policy using Oracle’s DBMS_REDACT package. This package allows us to redact sensitive data dynamically.

BEGIN
DBMS_REDACT.ADD_POLICY(
object_schema => ‘YOUR_SCHEMA_NAME’,
object_name => ‘CUSTOMERS’,
column_name => ‘CREDIT_CARD_NUMBER’,
policy_name => ‘REDACT_CC_NUMBER’,
function_type => DBMS_REDACT.FULL,
expression => ‘1=1’ — Apply to all rows
);
END;
/

In this example, the DBMS_REDACT.FULL function type will completely redact the credit card numbers.

Step 3: Verify the Redaction

Now, let’s query the table to see the redacted data in action.

SELECT customer_id, name, email, credit_card_number FROM customers;

The result will look like this: CUSTOMER_ID NAME EMAIL CREDIT_CARD_NUMBER 1 John Doe john.doe@example.com XXXXXXXXXXXXXXXX 2 Jane Smith jane.smith@example.com XXXXXXXXXXXXXXXX 3 Alice Johnson alice.johnson@example.com XXXXXXXXXXXXXXXX

Step 4: Custom Data Masking

You can also apply custom masking logic. For example, let’s partially mask the credit card number, showing only the last four digits.

BEGIN
DBMS_REDACT.UPDATE_POLICY(
policy_name => ‘REDACT_CC_NUMBER’,
function_type => DBMS_REDACT.PARTIAL,
function_parameters => ’12, ”X”, 4′ — Keep the last 4 digits visible
);
END;
/

Step 5: Verify Custom Masking

Run the query again to see the partially masked data.

SELECT customer_id, name, email, credit_card_number FROM customers;

The result will now show: CUSTOMER_ID NAME EMAIL CREDIT_CARD_NUMBER 1 John Doe john.doe@example.com XXXXXXXXXXXX1111 2 Jane Smith jane.smith@example.com XXXXXXXXXXXX0004 3 Alice Johnson alice.johnson@example.com XXXXXXXXXXXX0009

Conclusion

Data masking is an effective way to protect sensitive information in non-production environments. By using Oracle’s DBMS_REDACT package, you can easily implement dynamic data masking to secure your data. This ensures that sensitive information is not exposed, while still allowing applications to function normally with realistic data.

Feel free to adjust the schema and examples to fit the context and audience of your blog.