> ## Documentation Index
> Fetch the complete documentation index at: https://docs.futurex.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Verify the integration

> Encrypt a column and a tablespace with the CryptoHub-backed TDE master encryption key, and confirm that encrypted data is unreadable while the HSM key store is closed.

Encrypt test data, and then confirm that the database cannot read it without CryptoHub.

<Note>
  Oracle Database does not encrypt objects that `SYS` owns (`ORA-28336: cannot encrypt SYS owned objects`). Create the test objects under a separate user.
</Note>

In the commands on this page, replace `TEST_USER_PASSWORD` with a password for the test user, and replace `CRYPTOHUB_ENDPOINT_PIN` with the CryptoHub endpoint PIN.

## Create a test user

In `sqlplus / as sysdba`, create the test user:

```sql wrap theme={null}
CREATE USER tdetest IDENTIFIED BY "TEST_USER_PASSWORD";
GRANT CREATE SESSION, CREATE TABLE, UNLIMITED TABLESPACE TO tdetest;
```

## Encrypt a column

<Steps>
  <Step>
    Connect as the test user, and then create a table with an encrypted column:

    ```sql wrap theme={null}
    CONNECT tdetest/"TEST_USER_PASSWORD"
    CREATE TABLE tde_test (
      id    NUMBER,
      name  VARCHAR2(50),
      ssn   VARCHAR2(11) ENCRYPT
    );
    INSERT INTO tde_test VALUES (1, 'Test User', '123-45-6789');
    COMMIT;
    SELECT * FROM tde_test;
    ```

    <Check>
      The query returns the row with the `ssn` value in plain text.
    </Check>
  </Step>

  <Step>
    Connect as `SYSDBA`, and then confirm that the column is encrypted:

    ```sql wrap theme={null}
    CONNECT / AS SYSDBA
    SELECT owner, table_name, column_name, encryption_alg FROM dba_encrypted_columns;
    ```

    <Check>
      The output shows the `SSN` column of `TDETEST.TDE_TEST`. The default algorithm is `AES 192 bits key`.
    </Check>
  </Step>
</Steps>

## Encrypt a tablespace

<Steps>
  <Step>
    As `SYSDBA`, create an encrypted tablespace:

    ```sql wrap theme={null}
    CREATE TABLESPACE tde_tbs
      DATAFILE '/u01/app/oracle/oradata/ORCL/tde_tbs01.dbf' SIZE 50M
      ENCRYPTION USING 'AES256'
      DEFAULT STORAGE (ENCRYPT);

    SELECT tablespace_name, encrypted FROM dba_tablespaces WHERE tablespace_name = 'TDE_TBS';
    ```

    <Check>
      The output shows `TDE_TBS` with `ENCRYPTED` set to `YES`.
    </Check>
  </Step>

  <Step>
    Create a table in the encrypted tablespace:

    ```sql wrap theme={null}
    CREATE TABLE tdetest.tbs_test (id NUMBER, secret VARCHAR2(40)) TABLESPACE tde_tbs;
    INSERT INTO tdetest.tbs_test VALUES (1, 'tablespace-secret');
    COMMIT;
    ```
  </Step>
</Steps>

## Confirm that access depends on CryptoHub

Close the HSM key store, and then confirm that the encrypted data is unreadable.

<Steps>
  <Step>
    As `SYSDBA`, close the HSM key store:

    ```sql wrap theme={null}
    ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY "CRYPTOHUB_ENDPOINT_PIN";
    SELECT WRL_TYPE, STATUS FROM v$encryption_wallet WHERE WRL_TYPE = 'HSM';
    ```

    <Check>
      The HSM key store `STATUS` is `CLOSED`.
    </Check>
  </Step>

  <Step>
    As the test user, try to read the encrypted data:

    ```sql wrap theme={null}
    CONNECT tdetest/"TEST_USER_PASSWORD"
    SELECT * FROM tde_test;
    SELECT * FROM tbs_test;
    ```

    <Check>
      Both queries fail with `ORA-28365: wallet is not open`.
    </Check>
  </Step>

  <Step>
    As `SYSDBA`, open the HSM key store again:

    ```sql wrap theme={null}
    CONNECT / AS SYSDBA
    ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "CRYPTOHUB_ENDPOINT_PIN";
    ```
  </Step>

  <Step>
    As the test user, read the encrypted data again:

    ```sql wrap theme={null}
    CONNECT tdetest/"TEST_USER_PASSWORD"
    SELECT * FROM tde_test;
    SELECT * FROM tbs_test;
    ```

    <Check>
      Both queries return their rows in plain text.
    </Check>
  </Step>
</Steps>

## Confirm the key store opens after a restart

If you configured auto-login, restart the database, and then confirm that the encrypted data is readable without a manual open:

```sql wrap theme={null}
CONNECT / AS SYSDBA
SHUTDOWN IMMEDIATE;
STARTUP;
SELECT WRL_TYPE, STATUS, WALLET_TYPE FROM v$encryption_wallet;
SELECT * FROM tdetest.tde_test;
SELECT * FROM tdetest.tbs_test;
```

<Check>
  The HSM key store `STATUS` is `OPEN`, and both queries return their rows.
</Check>

If you did not configure auto-login, the HSM key store `STATUS` is `CLOSED` after the restart. Open it with `ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN`, and then run the queries.

## Confirm the master encryption key in CryptoHub

In CryptoHub, find the keys in the Oracle Database TDE service key group. Confirm that a key has a label that starts with `ORACLE.TDE.HSM.MK.06` followed by the `masterkeyid` value that you recorded when you [created the master encryption key](./create-the-tde-master-encryption-key). CryptoHub adds a numeric suffix to the label.

## Remove the test objects

After you finish the verification, remove the test objects:

```sql wrap theme={null}
CONNECT / AS SYSDBA
DROP USER tdetest CASCADE;
DROP TABLESPACE tde_tbs INCLUDING CONTENTS AND DATAFILES;
```

If any check fails, see [Troubleshooting](./troubleshooting).