Security in an Oracle Sharding Environment

Using TCPS Protocol and Transport Layer Security

To secure the communication between the various Oracle Sharding components in a distributed environment, Oracle recommends that you use Oracle Database Native Network Encryption or the TCPS protocol and Transport Layer Security (TLS) for all connections to, and between, the shard catalog and shards.

For information about configuring this security feature, see the documents based on the types of database you plan to run shards on.

Autonomous Database

For Oracle Autonomous Database, TLS is already enabled by default, and you only need to create the remaining security infrastructure, such as vaults, keys, and certificate resources on OCI.
Base Database Service

For Base Database Service on OCI you will need to enable TLS using the information in Configure TCP/IP with SSL/TLS for Sharding – GSM OCI Mode (Doc ID 2881390.1)
On-Premises

For on-premises databases, see Configure TCP/IP with SSL/TLS for Sharding – GSM JDBC THIN MODE (Doc ID 2881420.1)

More information is also available in Configuring Oracle Database Native Network Encryption and Data Integrity and Configuring Secure Sockets Layer Authentication

Using Wallets with Oracle Sharding

Beginning with Oracle Database Release 21c, sharding-specific Oracle wallets are an important part of any sharded database deployment. All primary databases and their replicas within the sharding configuration must have a sharding-specific wallet file present to ensure proper operation.

These wallets are created during the deployment of a sharded database and enable encrypted data to be sent between the shard catalog and individual shards. The process by which the wallets are created establishes a trust relationship between the different components of a sharded database deployment and prevents unauthorized operations from occurring on a shard.

The wallets themselves are created on the shard catalog and any shard catalog replicas when the GDSCTL command CREATE SHARDCATALOG is issued, and the wallets are created on the shards when the GDSCTL command DEPLOY is issued.

After a successful deployment, the wallet files contain information needed for shard catalogs and shards to connect to one another to perform operations such as DDL processing, user context propagation, and the passing of other sensitive data. The information stored in the wallet includes sharding-specific encryption and decryption keys, connect strings, and encrypted passwords. Any command issued from GDSCTL or SQL*Plus which changes this data will automatically cause the wallet to be updated with the new information.

Compatibility and Migration from Oracle Database 19c

For existing Oracle Sharding configurations which are being upgraded from a previous Oracle Database release, perform the steps in Post-Upgrade Steps for Oracle Sharding 21c after the database upgrade.

Locating the Wallet

The location of the wallet files is under the directory specified by the wallet_root database initialization parameter. If wallet_root is not set before issuing CREATE SHARDCATALOG or DEPLOY, then wallet_root is set to $ORACLE_BASE/admin/db_unique_name on the shard catalog or shards, respectively.

For example, assume the following after logging into the shard catalog or into a shard.

SQL> select guid from v$pdbs where con_id = sys_context('userenv','con_id');

GUID
--------------------------------
C23E7C78D5B77D50E0537517C40ACE4A

SQL> select value from v$parameter where name='wallet_root';

VALUE
--------------------------------------------------------------------------------
your-path-to-keystore

Given these values, the sharding-specific wallet file name is your-path-to-keystore/C23E7C78D5B77D50E0537517C40ACE4A/shard/cwallet.sso.

Wallets on Shard Catalog Replicas

If a standby database is created as a replica of the shard catalog, the shard wallet for the catalog must be manually copied from the primary shard catalog wallet. Find the location of the primary wallet using the above method, and make a copy to the correct location on the standby shard catalog database.

Note:

The value of wallet_root may be different on the standby shard catalog, and may not be set. Remember to set the value of wallet_root before copying the wallet to the standby location

The wallet only exists on the primary shard catalog after the GDSCTL create shardcatalog command is run. If a standby shard catalog database is created before running create shardcatalog, then first run create shardcatalog to create the shard wallet on the primary shard catalog, then copy the wallet to the standby shard catalog.

The shard catalog database also requires a wallet for CDB$ROOT. When copying and backing up shard wallets for a shard catalog database, you should also copy the shard wallet for CDB$ROOT regardless of which PDB is being used for the shard catalog.

Wallet Life Cycle Management

Once a sharded database has been deployed, it is crucial that the shard wallet is maintained throughout the life cycle of the shard catalog, the shards, and their replicas. Specifically, the shard wallet should be included in all backup and restore operations for each database, just as if it were a database data file.

Likewise, if a PDB is cloned, relocated, or otherwise moved, then the shard wallet should accompany the PDB to its new location. Note that in the case of PDB cloning specifically, the GUID for the PDB changes during the cloning operation, and therefore the path to the wallet will change as described above.

Updating a Wallet

If the shard wallet becomes lost, out of date. or is no longer accessible, a newly populated wallet can be created using the following GDSCTL command:

gdsctl sync database -database shard_name

Attempting to perform certain operations when the wallet is not present, or its contents are out of date, results in one or more of the following errors.

ORA-03873: unable to encrypt DDL statement with error ...

ORA-03874: unable to encrypt GSMUSER password with error ...

ORA-03876: error ... when attempting to generate a temporary key to add new shards

ORA-03894: "Failed to send keys to shard %s with error ...."

ORA-03896: Unable to load the sharding wallet successfully.

ORA-00600: internal error code, arguments: [gwsec_get_latest_key]

Using Application Contexts During Cross-Shard Operations

The ability to use several Oracle security features such as Virtual Private Database (VPD), Unified Auditing, and Oracle Label Security (OLS) typically depend upon the use of session-level application contexts.

Before Oracle 21c, any cross-shard operations such as cross-shard queries or DMLs initiated by the shard catalog would not send session-level application context values to the affected shards. Therefore, features that depended on the context values being passed from the shard catalog session to the shards were not supported in a sharded environment.

Starting with Oracle 21c, any database session-based application context values set before a cross-shard query or DML are sent securely to all shards involved in the operation. This is how features such as VPD, auditing, and OLS are supported in a sharding environment.

For example, if a user connects to the shard catalog or a query coordinator from SQL*Plus and calls the DBMS_SESSION.SET_CONTEXT procedure to set a context value, then that value is sent to any shards involved in subsequent cross-shard operations initiated from the SQL*Plus session on the shard catalog. Calling the SYS_CONTEXT function on the shard will return the value originally set on the shard catalog as you would expect.

Note the following limitations when you attempt to use application contexts for cross-shard operations:

The maximum length of a context value is 1968 bytes, as opposed to 4000 bytes in non-sharded environments.
The maximum length of a context attribute name is 32 bytes, as opposed to 128 bytes in non-sharded environments.
Only database session-based contexts initialized locally are currently supported.
All of the shards in the configuration must be Oracle Database 21c or later releases for the context value to be passed during cross-shard operations.

For more information see Using Application Contexts to Retrieve User Information.

Sharding-Specific Behavior Differences

In general, database limits on a per-user or per-schema basis are not aggregated across all databases in the sharded database, but only apply on a per-database level.

From an application perspective, a sharded database acts a single, logical database in most respects. However, a sharded database itself consists of several independent, loosely-coupled Oracle Database instances acting as shard catalogs, query coordinators, and shards. As a result, some behavior that you would expect from a typical Oracle Database is modified in the context of a database sharded with Oracle Sharding.

For example, if a sharded user is created and a user profile is assigned to the user with the SQL statement CREATE PROFILE, the limits set in the profile do not apply to the sharded database as a whole. Rather, they apply to each database that is a part of the larger, virtual sharded database.

Therefore, if you set the maximum number of failed login attempts to 20 for a sharded user, that limit does not apply to the entire sharded database but rather applies to each individual database in the configuration. If 20 failed attempts are reached when logging into a particular shard, then those failures do not count against the limits on the other shards or the shard catalog.

Using Transparent Data Encryption with Oracle Sharding

Oracle Sharding supports Transparent Data Encryption (TDE), but to successfully move chunks in a sharded database with TDE enabled, all of the shards must share and use the same encryption key for the encrypted tablespaces.

A sharded database consists of multiple independent databases and a shard catalog database. For TDE to work properly certain restrictions apply, especially when data is moved between shards. For chunk movement between shards to work when data is encrypted, you must ensure that all of the shards use the same encryption key.

There are two ways to accomplish this:

Create and export an encryption key from the shard catalog, and then import and activate the key on all of the shards individually.
Store the wallet in a shared location and have the shard catalog and all of the shards use the same wallet.

The following TDE statements are automatically propagated to shards when run on the shard catalog with shard DDL enabled:

ALTER SYSTEM SET ENCRYPTION WALLET [OPEN|CLOSE] IDENTIFIED BY password
ALTER SYSTEM SET ENCRYPTION KEY
ADMINISTER KEY MANAGEMENT SET KEYSTORE [OPEN|CLOSE] IDENTIFIED BY password
ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY password
ADMINISTER KEY MANAGEMENT USE KEY IDENTIFIED BY password
ADMINISTER KEY MANAGEMENT CREATE KEYSTORE IDENTIFIED BY password

Limitations

The following limitations apply to using TDE with Oracle Sharding.

For GDSCTL MOVE CHUNK to work, all of the shard database hosts must be on the same platform.
MOVE CHUNK cannot use compression during data transfer, which may impact performance.
Only encryption on the tablespace level is supported. Encryption on specific columns is not supported.

For more information about TDE see Introduction to Transparent Data Encryption

Creating a Single Encryption Key on All Shards

To propagate a single encryption key to all of the databases in the sharded database configuration, you must create a master encryption key on the shard catalog, then use wallet export, followed by wallet import onto the shards, and activate the keys.

This procedure assumes that the keystore password and wallet directory path are the same for the shard catalog and all of the shards. If you require different passwords and directory paths, all of the commands should be issued individually on each shard and the shard catalog with shard DDL disabled, using the shard’s own password and path.

These steps should be done before any data encryption is performed.

Create an encryption key on the shard catalog.

With shard DDL enabled, issue the following statements.
```
ADMINISTER KEY MANAGEMENT CREATE KEYSTORE wallet_directory_path
 IDENTIFIED BY keystore_password;
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY keystore_password;
```
The value for keystore_password should be the same if you prefer to issue wallet open and close commands centrally from the shard catalog.

The wallet directory path should match the WALLET_ROOT in the corresponding initialization parameter file.
With shard DDL disabled, issue the following statement to activate the encryption key.
```
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN
 IDENTIFIED BY keystore_password;
ADMINISTER KEY MANAGEMENT USE KEY master_key_id
 IDENTIFIED BY keystore_password WITH BACKUP;
```
All of the shards and the shard catalog database now have the same encryption key activated and ready to use for data encryption. On the shard catalog, you can issue TDE DDLs (with shard DDL enabled), such as:
- Create encrypted tablespaces and tablespace sets.
- Create sharded tables using encrypted tablespaces.
- Create sharded tables containing encrypted columns (with limitations).

Validate that the key IDs on all of the shards match the ID on the shard catalog.

SELECT KEY_ID  FROM V$ENCRYPTION_KEYS 
WHERE ACTIVATION_TIME =
 (SELECT MAX(ACTIVATION_TIME) FROM V$ENCRYPTION_KEYS
  WHERE ACTIVATING_DBID = (SELECT DBID FROM V$DATABASE));

```
ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY keystore_password WITH BACKUP;
```
An encryption key is created and activated in the shard catalog database’s wallet.

If you issue this statement with DDL enabled, it will also create encryption keys in each of the shards’ wallets, which are different keys from that of the shard catalog. For data movement to work, you cannot use different encryption keys on each shard.

Get the master key ID from the shard catalog keystore.

SELECT KEY_ID  FROM V$ENCRYPTION_KEYS 
WHERE ACTIVATION_TIME =
 (SELECT MAX(ACTIVATION_TIME) FROM V$ENCRYPTION_KEYS
  WHERE ACTIVATING_DBID = (SELECT DBID FROM V$DATABASE));

With shard DDL disabled, export the catalog wallet containing the encryption key.

ADMINISTER KEY MANAGEMENT EXPORT ENCRYPTION KEYS WITH SECRET secret_phrase TO
 wallet_export_file IDENTIFIED BY keystore_password;

Physically copy the wallet file to each of the shard hosts, into their corresponding wallet export file location, or put the wallet file on a shared disk to which all of the shards have access.

With shard DDL disabled, log on to each shard and import the wallet containing the key.

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY keystore_password;
ADMINISTER KEY MANAGEMENT IMPORT ENCRYPTION KEYS WITH SECRET secret_phrase FROM
 wallet_export_file IDENTIFIED BY keystore_password WITH BACKUP;

Restart the shard databases.
Activate the key on all of the shards on the shard catalog with shard DDL enabled.