Ubuntu中Oracle数据库如何实现数据加密

Transparent Data Encryption (TDE)
TDE is the most widely used method for encrypting data at rest in Oracle databases. It automatically encrypts data before writing it to disk and decrypts it when read, requiring no changes to application code. TDE supports two primary modes: column-level encryption (for specific sensitive columns) and tablespace-level encryption (for entire tablespaces).

Prerequisites for TDE
Before configuring TDE, ensure the following:

• The database COMPATIBLE initialization parameter is set to 11.2.0.0 or higher (supports AES128, AES192, AES256, and 3DES168 algorithms).
• A software keystore is configured to store the TDE master encryption key (default location: $ORACLE_BASE/admin/<db_name>/wallet).
• Sufficient disk space is available (online encryption requires auxiliary data files for the encrypted tablespace).

Step 1: Configure the Software Keystore
The keystore secures the TDE master key. Use the following SQL commands to set it up:

-- Create a software keystore (if not exists)
ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/u01/app/oracle/admin/ORCL/wallet' IDENTIFIED BY "YourKeystorePassword";

-- Open the keystore (required for key management)
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "YourKeystorePassword" CONTAINER=ALL;

-- Set the TDE master encryption key (generate a new key if none exists)
ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "YourMasterKeyPassword" WITH BACKUP USING 'backup_file.txt';

Replace "YourKeystorePassword" and "YourMasterKeyPassword" with strong passwords. The WITH BACKUP clause creates a backup of the keystore (critical for disaster recovery).

Step 2: Column-Level Encryption (Encrypt Specific Columns)
Use the ENCRYPT keyword in CREATE TABLE or ALTER TABLE statements to encrypt sensitive columns (e.g., SSN, credit card numbers). Supported data types include VARCHAR2, NUMBER, DATE, BLOB, and CLOB (via SecureFiles).

Create a Table with Encrypted Columns:

CREATE TABLE employees (
  emp_id NUMBER PRIMARY KEY,
  name VARCHAR2(100),
  ssn VARCHAR2(11) ENCRYPT,  -- Encrypt the SSN column
  salary NUMBER(10,2) ENCRYPT USING 'AES256'  -- Specify AES256 algorithm
);

Encrypt Existing Columns:
For existing tables, add a new encrypted column or modify an existing one:

-- Add a new encrypted column
ALTER TABLE employees ADD (encrypted_salary NUMBER(10,2) ENCRYPT);

-- Migrate existing data to the encrypted column
UPDATE employees SET encrypted_salary = salary;
ALTER TABLE employees DROP COLUMN salary;
ALTER TABLE employees RENAME COLUMN encrypted_salary TO salary;

Notes:

• Column-level encryption may impact performance (encryption/decryption overhead).
• Avoid encrypting columns used in indexes, foreign keys, or range scans.

Step 3: Tablespace-Level Encryption (Encrypt Entire Tablespaces)
For broader protection, encrypt entire tablespaces (e.g., USERS, DATA). This method encrypts all data stored in the tablespace without modifying application code.

Create an Encrypted Tablespace:

CREATE TABLESPACE encrypted_ts
DATAFILE '/u01/app/oracle/oradata/ORCL/encrypted_ts.dbf' SIZE 1G
ENCRYPTION USING 'AES256'  -- Specify encryption algorithm
DEFAULT STORAGE (ENCRYPT);

Encrypt an Existing Tablespace (Online):
For online encryption (no downtime), use the ALTER TABLESPACE command:

-- Check keystore status (must be OPEN)
SELECT STATUS FROM V$ENCRYPTION_WALLET;

-- Encrypt the tablespace (creates auxiliary data files)
ALTER TABLESPACE users ENCRYPTION ONLINE ALGORITHM 'AES256';

Decrypt a Tablespace:
To reverse encryption, use:

ALTER TABLESPACE encrypted_ts DECRYPTION;

Notes:

• Online encryption requires additional disk space (auxiliary files).
• Tablespace-level encryption does not support NOLOGGING operations (to prevent data loss).

Using DBMS_CRYPTO Package (Custom Encryption)
For advanced encryption needs (e.g., encrypting application-layer data), use the DBMS_CRYPTO package. This provides low-level control over encryption algorithms, keys, and initialization vectors (IVs).

Example: Encrypt and Decrypt Data

DECLARE
  l_key RAW(32) := UTL_RAW.CAST_TO_RAW('Your32ByteEncryptionKey1234'); -- 256-bit key for AES256
  l_iv RAW(16) := UTL_RAW.CAST_TO_RAW('Your16ByteIV123456');           -- 128-bit IV for CBC mode
  l_plaintext RAW(32767) := UTL_RAW.CAST_TO_RAW('Sensitive Data');
  l_ciphertext RAW(32767);
  l_decryptedtext RAW(32767);
BEGIN
  -- Encrypt data (AES256 in CBC mode with PKCS5 padding)
  l_ciphertext := DBMS_CRYPTO.ENCRYPT(
    src => l_plaintext,
    typ => DBMS_CRYPTO.ENCRYPT_AES256 + DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5,
    key => l_key,
    iv => l_iv
  );
  DBMS_OUTPUT.PUT_LINE('Encrypted: ' || RAWTOHEX(l_ciphertext));

  -- Decrypt data
  l_decryptedtext := DBMS_CRYPTO.DECRYPT(
    src => l_ciphertext,
    typ => DBMS_CRYPTO.ENCRYPT_AES256 + DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5,
    key => l_key,
    iv => l_iv
  );
  DBMS_OUTPUT.PUT_LINE('Decrypted: ' || UTL_I18N.RAW_TO_CHAR(l_decryptedtext, 'AL32UTF8'));
END;
/

Notes:

• Manually manage keys and IVs (store them securely, e.g., in a keystore or environment variables).
• Use strong keys (at least 256 bits for AES) and unique IVs for each encryption operation.

File System-Level Encryption (Optional Layer)
For additional security, encrypt the underlying file system where Oracle data files reside. On Ubuntu, use LUKS (Linux Unified Key Setup) or eCryptfs:

LUKS Encryption:

1. Install LUKS tools:

   sudo apt update && sudo apt install cryptsetup
   

2. Encrypt a partition (e.g., /dev/sdb1):

   sudo cryptsetup luksFormat /dev/sdb1
   sudo cryptsetup open /dev/sdb1 oracle_encrypted
   sudo mkfs.ext4 /dev/mapper/oracle_encrypted
   sudo mount /dev/mapper/oracle_encrypted /u01/app/oracle/oradata/ORCL
   

3. Add the encryption to /etc/crypttab for automatic mounting at boot.

Notes:

• File system encryption protects against physical disk theft but does not encrypt data in memory or during transmission.
• Combine with TDE for comprehensive data protection.

Key Management Best Practices

• Backup the Keystore: Regularly back up the keystore (e.g., orapki wallet export) and store it securely (offline or in a secure vault).
• Restrict Access: Limit access to the keystore to the Oracle database administrator (DBA) only.
• Rotate Keys: Periodically rotate the TDE master key (using ADMINISTER KEY MANAGEMENT ROTATE KEY) to reduce the risk of key compromise.
• Audit Key Usage: Enable auditing for keystore operations (e.g., AUDIT ADMINISTER KEY MANAGEMENT) to track key access.