📋Steps in this guide1/18

Implementing Oracle Advanced Security: Encryption and Data Redaction

Learn how to implement Oracle Advanced Security features like Transparent Data Encryption (TDE) and Data Redaction to protect sensitive data and ensure compliance with GDPR, HIPAA, and PCI-DSS.

oracle configurationintermediate

by OracleDba

28 views

Why Oracle Advanced Security Matters

Let’s first understand the “why.” Cybersecurity Ventures predicts that global cybercrime costs will reach $10.5 trillion annually by 2025 . Oracle databases are used by banks, telecoms, governments, making them prime targets for hackers. Real-world example? In 2023, an Asian retail chain faced a data breach through a stolen physical backup. The database wasn’t encrypted, and nearly 3 million customer records were leaked. A simple TDE setup could have prevented this disaster. So if you’re handling production databases – your responsibility doesn’t stop at backups and uptime . You must implement security at the data level.

Transparent Data Encryption (TDE)

TDE is used to encrypt data at rest , this includes tablespaces, datafiles, redo logs, and backups . What makes it special is that it doesn’t require changes to your application code. When someone tries to access encrypted data at the OS or file level, they’ll see random junk . Only authorized users connecting through the Oracle DB instance can view the real data.

Key Features of TDE:

- Encrypts tablespaces or specific columns - Supports AES256, AES192, AES128, 3DES168 algorithms - Fully supported by RMAN backups - Integrated with Oracle Wallet or Oracle Key Vault

1. Configure Wallet Location

In your sqlnet.ora, specify where to store the wallet: This wallet holds your encryption keys. If it’s missing, data can’t be decrypted .

Code/Command (click line numbers to comment):

1
2

ENCRYPTION_WALLET_LOCATION =
 (SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/u01/app/oracle/wallet)))

2. Create and Open the Wallet

This creates a secure keystore and opens it for operations.

Code/Command (click line numbers to comment):

1
2

ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/u01/app/oracle/wallet' IDENTIFIED BY "MyWalletPass";
ADMINISTER KEY MANAGEMENT OPEN KEYSTORE IDENTIFIED BY "MyWalletPass";

3. Set the TDE Master Key

This master key is used to encrypt/decrypt data keys inside the database.

Code/Command (click line numbers to comment):

ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "MyWalletPass" WITH BACKUP;

4. Encrypt the Tablespace

You can also migrate existing tables to encrypted tablespaces using Data Pump .

Code/Command (click line numbers to comment):

CREATE TABLESPACE secure_tbs 
  DATAFILE '/u01/oradata/secure_tbs01.dbf' SIZE 100M 
  ENCRYPTION USING 'AES256' 
  DEFAULT STORAGE(ENCRYPT);

Bonus Tip:

You can verify encryption using: And for column-level encryption (legacy method):

Code/Command (click line numbers to comment):

1
2
3

SELECT tablespace_name, encrypted FROM dba_tablespaces;

ALTER TABLE customers MODIFY (card_number ENCRYPT USING 'AES256');

Real-World Usage of TDE

In a project I handled for a US-based fintech startup , we implemented TDE across all tables with customer PII – name, SSN, address, card details. Not only did this secure their data, but it also made PCI-DSS audits smoother , saving them both time and compliance penalties . Another client in the Middle East couldn’t allow the DBA team to see salary data – even from internal backups. With TDE + wallet separation, they achieved complete data confidentiality , even from sysadmins.

Oracle Data Redaction: Protecting Data in Use

Let’s shift focus. While TDE encrypts data on disk, what about when users query the data? Suppose your support team queries the customer table – do you want them to see full credit card or Aadhar numbers? That’s where Oracle Data Redaction comes into play. It dynamically masks data at runtime , based on user roles, conditions, or environments – all without changing the base data or application logic.

Types of Data Redaction

Type Description Example Full Entire data replaced 4111-XXXX-XXXX-XXXX Partial Some parts visible XXXX-XXXX-XXXX-1234 Random Shows random value 9841-xxxx-xxxx-0192 Regular Expr. Masks based on regex “Dr. [Redacted]” No Redaction Applied conditionally Visible to admin only

How to Apply Data Redaction

Let me walk you through a quick example.

1. Grant privileges

Code/Command (click line numbers to comment):

GRANT EXECUTE ON DBMS_REDACT TO security_admin;

2. Add redaction policy

This shows only the last 4 digits of the SSN to non-admin users.

Code/Command (click line numbers to comment):

BEGIN
  DBMS_REDACT.ADD_POLICY(
    object_schema => 'HR',
    object_name   => 'EMPLOYEES',
    column_name   => 'SSN',
    policy_name   => 'redact_ssn',
    function_type => DBMS_REDACT.PARTIAL,
    expression    => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''HR_ADMIN''',
    regexp_pattern => NULL,
    regexp_replace_string => NULL,
    regexp_position => NULL,
    regexp_occurrence => NULL,
    regexp_match_parameter => NULL,
    redaction_type => DBMS_REDACT.PARTIAL,
    redaction_function => NULL,
    redaction_format => 'XXXXXX####');
END;

Bonus Tip:

You can monitor policy effects using:

Code/Command (click line numbers to comment):

SELECT * FROM dba_redaction_policies;

Real Use Case: Healthcare Industry

A hospital chain in India used Oracle Redaction to hide patient IDs and mobile numbers from reception staff. Only doctors had full access. This helped them comply with HIPAA and Indian data protection laws while still running their Oracle Forms-based legacy system.

TDE vs Data Redaction – When to Use What?

Feature TDE Data Redaction Protects Data at rest (disk, backups) Data in use (queries, reports) Affects Storage Displayed output Overhead Minimal Slight query parsing overhead Best for Disks, backups, storage theft Users with SELECT access Complexity Low (wallet setup) Medium (policy creation, testing) Pro tip: Use TDE + Redaction together for full-circle protection.

Final Recommendations

Here’s my guidance if you’re just starting out: - Start with a risk analysis – Identify tables and columns with sensitive data. - Enable TDE in non-prod first, test backup/restore thoroughly. - Apply Redaction to non-admin user roles, especially in support/reporting. - Back up the wallet securely – If it’s lost, encrypted data is inaccessible. - Automate wallet management using Oracle Key Vault for large environments. - Document everything for compliance audits.

Comments (0)

Please to add comments

No comments yet. Be the first to comment!