Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Oracle Transparent Data Encryption and Amazon Aurora MySQL Encryption and Column Encryption

Oracle Usage

Oracle uses Transparent Data Encryption (TDE) to encrypt data stored on media in order to provide data at rest protection. Although Oracle uses authentication, authorization, and auditing to secure data in the database, TDE provides additional security at the operating system level.

As the name implies, encryption operations are performed automatically and are transparent to client applications. However, TDE does not address data in transit, which must be handled by network security protocols.

Characteristics of TDE include:

Examples

Configure the Master Encryption Key

Specify the location of the encryption wallet using the ENCRYPTION_WALLET_LOCATION parameter. Use one of the following options:

Register the key file in the ASM disk group.

ENCRYPTION_WALLET_LOCATION=
    (SOURCE=
        (METHOD=FILE)
            (METHOD_DATA=
                (DIRECTORY=+ASM_file_path_of_the_diskgroup)))

Create a Software Keystore

Use one of the following three types of software keystores:

Create a password-based software keystore. The user must have the ADMINISTER KEY MANAGEMENT or SYSKM privilege.

sqlplus c##sec_admin as syskm
Enter password: password
Connected.

ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/etc/ORACLE/WALLETS/orcl' IDENTIFIED BY password;

keystore altered.

Open a Keystore

When you use a password-based keystore, make sure that you open it before creating TDE master encryption keys or accessing the keystore. Keystores are automatically opened when using auto-login or local auto login.

sqlplus c##sec_admin as syskm
Enter password: password
Connected.

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY password;

keystore altered.

Set the Software Master Encryption Key

The master encryption key protects the TDE table and tablespace encryption keys. By default, the master encryption key is generated by TDE. To set the master encryption key, ensure the database is open in READ WRITE mode, connect with a user account having the required privileges (see the preceding example), and create the master key.

sqlplus c##sec_admin as syskm
Enter password: password
Connected.

ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY keystore_password WITH BACKUP USING 'emp_key_backup';

keystore altered.

Encrypt Data

Create an encrypted column.

CREATE TABLE employee (
    FIRST_NAME VARCHAR2(128),
    LAST_NAME VARCHAR2(128),
    EMP_ID NUMBER,
    SALARY NUMBER(6) ENCRYPT);

Column data types support for encryption include BINARY_DOUBLE, BINARY_FLOAT, CHAR, DATE, INTERVAL DAY TO SECOND, INTERVAL YEAR TO MONTH, NCHAR, NUMBER, NVARCHAR2, RAW (legacy or extended), TIMESTAMP (includes TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE), VARCHAR2 (legacy or extended).

Column encryption can’t be used with the following features:

You can change the encryption algorithm using the NO SALT clause to encrypt without an algorithm or the USING clause to specify an algorithm.

CREATE TABLE EMPLOYEE (
    FIRST_NAME VARCHAR2(128),
    LAST_NAME VARCHAR2(128),
    EMP_ID NUMBER ENCRYPT NO SALT,
    SALARY NUMBER(6) ENCRYPT USING '3DES168');

Change the algorithm on an existing table.

ALTER TABLE EMPLOYEE REKEY USING 'SHA-1';

Remove column encryption.

ALTER TABLE employee MODIFY (SALARY DECRYPT);

sqlplus sec_admin@hrpdb
Enter password: password
Connected.

CREATE TABLESPACE encrypt_ts
DATAFILE '$ORACLE_HOME/dbs/encrypt_df.dbf' SIZE 1M
ENCRYPTION USING 'AES256'
DEFAULT STORAGE (ENCRYPT);
CREATE TABLESPACE securespace_2
DATAFILE '/home/user/oradata/secure01.dbf'
SIZE 150M

ENCRYPTION
DEFAULT STORAGE(ENCRYPT);

For more information, see Introduction to Transparent Data Encryption in the Oracle documentation.

MySQL Usage

Amazon provides the ability to encrypt data at rest (data stored in persistent storage). When data encryption is turned on, it automatically encrypts the database server storage, automated backups, read replicas, and snapshots using the AES-256 encryption algorithm. Amazon Key Management Service (Amazon KMS) performs the encryption. For more information, see Amazon Key Management Service.

Once enabled, Amazon transparently encrypts and decrypts the data without any impact on performance or any user intervention. There is no need to modify clients to support encryption.

Table encryption can now be managed globally by defining and enforcing encryption defaults. The default_table_encryption variable defines an encryption default for newly created schemas and general tablespace. The encryption default for a schema can also be defined using the DEFAULT ENCRYPTION clause when creating a schema. By default a table inherits the encryption of the schema or general tablespace it is created in.

Encryption defaults are enforced by enabling the table_encryption_privilege_check variable. The privilege check occurs when creating or altering a schema or general tablespace with an encryption setting that differs from the default_table_encryption setting or when creating or altering a table with an encryption setting that differs from the default schema encryption. The TABLE_ENCRYPTION_ADMIN privilege permits overriding default encryption settings when table_encryption_privilege_check is enabled. For more information, see Defining an Encryption Default for Schemas and General Tablespaces in the MySQL documentation.

Creating an Encryption Key

To create your own key, follow these steps.

Now you can launch your instance.

Enabling Encryption

As part of the database settings, you will be prompted to enable encryption and select a master key.

You can turn on encryption for an Amazon RDS DB instance only during the instance creation.

You can select the default key provided for the account or define a specific key based on an IAM KMS ARN from your account or a different account.

SSE-S3 Encryption Feature Overview

Server-side encryption with Amazon S3-managed encryption keys (SSE-S3) uses multi-factor encryption. Amazon S3 encrypts its objects with a unique key and it also encrypts the key itself with a master key that rotates periodically.

SSE-S3 uses AES-256 as its encryption standard.

After you turn on the server-side encryption for an Amazon S3 bucket, the data will be encrypted at rest. Make sure that all API calls now include the special header as shown following: -x-amz-server-side-encryption.

For more information, see Specifying Amazon S3 encryption and s3.

To turn on SSE-S3

From this point forward, the only way to access the files is to use Amazon CLI s3 with the --sse switch, or by adding x-amz-server-side-encryption to your API calls.

Usage of Column Encryption

Aurora MySQL provides encryption and decryption functions similar to Oracle with a much less elaborate security hierarchy that is easier to manage.

The encryption functions require the actual key as a string, so you must take extra measures to protect the data. For example, hashing the key values on the client.

Aurora MySQL supports the AES and DES encryption algorithms. You can use the following functions for data encryption and decryption:

Syntax

General syntax for the encryption functions is shown following:

[A|D]ES_ENCRYPT(<string to be encrypted>, <key string> [,<initialization vector>])
[A|D]ES_DECRYPT(<encrypted string>, <key string> [,<initialization vector>])

For more information, see AES_ENCRYPT in the MySQL documentation.

It is highly recommended to use the optional initialization vector to circumvent whole value replacement attacks. When encrypting column data, it is common to use an immutable key as the initialization vector. With this approach, decryption fails if a whole value moves to another row.

Consider using SHA2 instead of SHA1 or MD5 because there are known exploits available for the SHA1 and MD5. Passwords, keys, or any sensitive data passed to these functions from the client are not encrypted unless you are using an SSL connection. One benefit of using Amazon Identity and Access Management (IAM) is that database connections are encrypted with SSL by default. For more information, see Users and Roles.

Examples

The following example demonstrates how to encrypt an employee social security number.

Create an employees table.

CREATE TABLE Employees (
    EmployeeID INT NOT NULL PRIMARY KEY,
    SSN_Encrypted BINARY(32) NOT NULL);

Insert the encrypted data.

INSERT INTO Employees (EmployeeID, SSN_Encrypted)
VALUES (1, AES_ENCRYPT('1112223333', UNHEX(SHA2('MyPassword',512)), 1));

Verify decryption.

SELECT EmployeeID, SSN_Encrypted,
    AES_DECRYPT(SSN_Encrypted, UNHEX(SHA2('MyPassword', 512)), EmployeeID) AS SSN
    FROM Employees

EmployeeID  SSN_Encrypted     SSN
1           ` ©> +yp°øýNZ~Gø  1112223333

For more information, see Encryption and Compression Functions in the MySQL documentation.