dbi Blog

Introduction

Other blogs that I wrote on OKV subject:

The aim of this new post is to describe how to:

Download and configure the REST API
Declare the endpoint into Oracle Key Vault
Enroll the endpoint
Create a wallet for the endpoint
Add Transparent Database Encryption (TDE) directly into Oracle key vault

All steps are executed from the database server, using RESTfull API commands, so no connection to Oracle Key vault interface.

As prerequisite, I assume that the Oracle Key Vault is installed, and the RESTfull API is enabled at OKV server, otherwise all commands will fail.

By scripting all commands, this method can be used to full configure a new database server.

Naming and conventions

Oracle Key Vault server is named okvsrv.oracle.com

SADMIN is the System Admin user defined at Oracle Key Vault level.

All commands are made at database server level as oracle user (which is in the shell prompt).There is only one command executer as root.

The database server is a Oracle Linux 8 and the database version is 19.20.

The database SID is DBTDEOKV.

Download the RESTfull API from OKV server

The RESTfull API is installed in /home/oracle/okvrestcli directory.

[oracle@ ~]$ mkdir -p $HOME/okvrestcli/log

[oracle@ ~]$ mkdir $HOME/okvrestcli/wallet

[oracle@ ~]$ curl -k -O https://okvsrv.oracle.com:5695/okvrestclipackage.zip

[oracle@ ~]$ unzip okvrestclipackage.zip -d okvrestcli
Archive:  okvrestclipackage.zip
   creating: okvrestcli/lib/
   creating: okvrestcli/bin/
  inflating: okvrestcli/bin/okv
  inflating: okvrestcli/bin/okv.bat
   creating: okvrestcli/conf/
  inflating: okvrestcli/conf/okvrestcli.ini
  inflating: okvrestcli/conf/okvrestcli_logging.properties
  inflating: okvrestcli/lib/okvrestcli.jar

To make the life easier I create an env file to keep all used variables:

[oracle@ ~]$ echo 'export OKV_REST_CLI_CONFIG=$HOME/okvrestcli/conf' > $HOME/okvrestcli/set_okv_rest_api_env.sh

[oracle@ ~]$ echo "export JAVA_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_5/jdk" > $HOME/okvrestcli/set_okv_rest_api_env.sh

[oracle@ ~]$ echo 'export PATH=$PATH:$HOME/okvrestcli/bin' >> $HOME/okvrestcli/set_okv_rest_api_env.sh

[oracle@ ~]$ source $HOME/okvrestcli/set_okv_rest_api_env.sh

Configure the RESTfull API

Uncomment line export OKV_RESTCLI_CONFIG=$OKV_RESTCLI_DIR/conf/okvrestcli.ini in $HOME/okvrestcli/bin/okv shell.

For logging change the line java.util.logging.FileHandler.pattern = $HOME/okvrestcli/log/okv%u.log in $HOME/okvrestcli/conf/okvrestcli_logging.properties file.

For more log traces you can chenge the level of traces, in the same file, to ALL.

Finally update $HOME/okvrestcli/conf/okvrestcli.ini

[Default]
log_property=/home/oracle/okvrestcli/conf/okvrestcli_logging.properties
server=okvsrv.oracle.com
#okv_client_config=./conf/okvclient.ora
user=sadmin
client_wallet=/home/oracle/okvrestcli/wallet

And create the autologin wallet to avoid to enter all the time the sadmin user password.

[oracle@ ~]$ okv admin client-wallet add --client-wallet $HOME/okvrestcli/wallet --wallet-user sadmin
Password:
{
  "result" : "Success"
}

Create and enroll the endpoint

The endpoint is the database server itself. Enrolling the endpoint, give the ability to this server (endpoint) to make calls (get TDE keys) from Oracle Key Vault.

An endpoint type ORACLE_DB need some directories and variables to be set:

At database level the WALLET_ROOT parameter must be set and and endpoint scripts from OKV will be installed in WALLET_ROOT/okv
WALLET_ROOT/tde directory will not contain the physical wallet (as the keys are stored in OKV) but in case of auto_login, the cwallet.sso need to be present in this directory.
$ORACLE_HOME and $ORACLE_BASE variables must be defined at the moment of endpoint enrolment.

Create directories (/u01/app/oracle/tde_wallet/DBTDEOKV path will be the WALLET_ROOT at the database level)

[oracle@ ~]$ mkdir /u01/app/oracle/tde_wallet/DBTDEOKV

[oracle@ ~]$ mkdir /u01/app/oracle/tde_wallet/DBTDEOKV/okv

[oracle@ ~]$ mkdir /u01/app/oracle/tde_wallet/DBTDEOKV/tde

[oracle@ ~]$ mkdir /u01/app/oracle/tde_wallet/DBTDEOKV/tde_seps

At OKV level we are going to set the endpoint name using the database name (DBTDEOKV_DB).

First the endpoint is created:

[oracle@ ~]$ okv admin endpoint create --generate-json > /tmp/t.json

[oracle@ ~]$ cat /tmp/t.json
{
  "service" : {
    "category" : "admin",
    "resource" : "endpoint",
    "action" : "create",
    "options" : {
      "endpoint" : "DBTDEOKV_DB",
      "description" : "DBTDEOKV database",
      "platform" : "LINUX64"
      "type" : "ORACLE_DB",
      "subgroup" : "NO SUBGROUP",
      "strictIpCheck" : "FALSE",
    }
  }
}

[oracle@ ~]$  okv admin endpoint create --from-json /tmp/t.json
{
  "result" : "Success",
  "value" : {
    "status" : "PENDING",
    "locatorID" : "3DCA30C0-D7DC-42F7-AA1D-07B3A62523A4"
  }
}

Then is enrolled:

[oracle@ ~]$ echo $ORACLE_HOME
/u01/app/odaorahome/oracle/product/19.0.0.0/dbhome_5
[oracle@ ~]$ echo $ORACLE_BASE
/u01/app/odaorabase/oracle
[oracle@ ~]$ echo $ORACLE_SID
DBTDEOKV

[oracle@ ~]$ okv admin endpoint provision --generate-json > /tmp/t.json
[oracle@ ~]$ cat /tmp/t.json
{
  "service" : {
    "category" : "admin",
    "resource" : "endpoint",
    "action" : "provision",
    "options" : {
      "endpoint" : "DBTDEOKV_DB",
      "location" : "/u01/app/oracle/tde_wallet/DBTDEOKV/okv",
      "autoLogin" : "FALSE"
    }
  }
}

[oracle@ ~]$ okv admin endpoint provision --from-json /tmp/t.json
Enter Oracle Key Vault endpoint password:      enpoint_password
{
  "result" : "Success"
}

The okvclient.jar was downloaded in the "location" : "/u01/app/oracle/tde_wallet/DBTDEOKV/okv" path. This is an archive which need to be extracted. At the end the file okvclient.jar will be erased so if you need to reinstall the endopoint or you will need to enroll it again.

The word “enpoint_password” means the password used for this endpoint.

[oracle@ ~]$ ls /u01/app/oracle/tde_wallet/DBTDEOKV/okv
okvclient.jar

[oracle@ ~]$ $ORACLE_HOME/jdk/bin/java -jar /u01/app/oracle/tde_wallet/DBTDEOKV/okv/okvclient.jar -d /u01/app/oracle/tde_wallet/DBTDEOKV/okv -v
Detected JAVA_HOME: /u01/app/odaorahome/oracle/product/19.0.0.0/dbhome_5/jdk
Detected ORACLE_HOME: /u01/app/odaorahome/oracle/product/19.0.0.0/dbhome_5
Detected ORACLE_BASE: /u01/app/odaorabase/oracle
Using OKV_HOME: /u01/app/oracle/tde_wallet/DBTDEOKV/okv
Please set environment variables ORACLE_HOME, ORACLE_BASE, and OKV_HOME
consistently across processes.
Enter new Key Vault endpoint password (<enter> for auto-login):      enpoint_password
Confirm new Key Vault endpoint password:                             enpoint_password
The endpoint software for Oracle Key Vault installed successfully.
Deleted the file : /u01/app/oracle/tde_wallet/DBTDEOKV/okv/okvclient.jar

[oracle@ ~]$ ls  /u01/app/oracle/tde_wallet/DBTDEOKV/okv/
bin  conf  csdk  jlib  lib  log  ssl

# execute as root 
[root@ ~]# /u01/app/oracle/tde_wallet/DBTDEOKV/okv/bin/root.sh

Finally, create an profile, I called it DBTDEOKV, for this endpoint in the RESTfull API, in case when we have several endpoints on the same server. From this moment we can use the parameter –profile to all RESTfull API calls. If this is the only endpoint on the server we cans use the DEFAULT profile.

-- create the profile, DBTDEOKV, for REST api for this database 
[oracle@ ~]$ cat $HOME/okvrestcli/conf/okvrestcli.ini
#Provide absolute path for log_property, okv_client_config properties
[Default]
log_property=/home/oracle/okvrestcli/conf/okvrestcli_logging.properties
server=160.210.31.85
user=sadmin
client_wallet=/home/oracle/okvrestcli/wallet

[DBTDEOKV]
okv_client_config=/u01/app/oracle/tde_wallet/DBTDEOKV/okv/conf/okvclient.ora


-- list endpoints 
[oracle@ ~]$ okv admin endpoint list --profile DBTDEOKV
{
  "result" : "Success",
  "value" : {
    "endpoints" : [ {
      "commonNameOfCertificateIssuer" : "CA",
      "clusterSubgroup" : "No Cluster Subgroup",
      "createdBy" : "SADMIN",
      "creatorNode" : "okvsrv.oracle.com",
      "creationTime" : "2023-11-14 09:40:10",
      "defaultWallet" : "",
      "description" : "DBTDEOKV database",
      "endpoint" : "DBTDEOKV_DB",
      "endpointCertificateExpiration" : "2024-11-13 09:40:18",
      "enrollmentToken" : "",
      "ipAddress" : "160.210.32.20",
      "nameStatus" : "ACTIVE",
      "platform" : "Linux",
      "status" : "Enrolled",
      "type" : "Oracle Database"
.....

Create a wallet for the endpoint

Let’s create a wallet for this endpoint. The wallet is an virtual wallet as it is located in OKV and not locally on disk. The wallet name will be DBTDEOKV_DB_WALLET.

# add a wallet for this endpoint
[oracle@ ~]$ okv manage-access wallet create --generate-json-input > /tmp/t.json
[oracle@ ~]$ cat /tmp/t.json
{
  "service" : {
    "category" : "manage-access",
    "resource" : "wallet",
    "action" : "create",
    "options" : {
      "wallet" : "DBTDEOKV_db_wallet",
      "description" : "wallet for DBTDEOKV database"
    }
  }
}
[oracle@ ~]$ okv manage-access wallet create --from-json /tmp/t.json
{
  "result" : "Success",
  "value" : {
    "status" : "PENDING",
    "locatorID" : "71025B76-7773-4F2E-9383-BC3E672AF867"
  }
}

# grant access for endpoint to manage the wallet
[oracle@ ~]$ okv manage-access wallet add-access --generate-json-input > /tmp/t.json
[oracle@ ~]$ cat /tmp/t.json
{
  "service" : {
    "category" : "manage-access",
    "resource" : "wallet",
    "action" : "add-access",
    "options" : {
      "wallet" : "DBTDEOKV_db_wallet",
      "endpoint" : "DBTDEOKV_DB",
      "access" : "RM_MW"
    }
  }
}
[oracle@ ~]$  okv manage-access wallet add-access --from-json /tmp/t.json
{
  "result" : "Success"
}

Configure the encryption on the database to use OKV

For commodity, I create a simple script to list the status of the encryption.

-- create the script tde.sql for easy usage of the query 
[oracle@ ~]$ cat tde.sql
set pages 200
set line 300
col WRL_PARAMETER format a50
col status forma a10
col pdb_name  format a20
select pdb_id, pdb_name, guid from dba_pdbs;
select * from v$encryption_wallet where con_id != 2;

The database is a pluggable database and the TDE will be configured in ISOLATED mode (each PDB will have it’s own encryption key).

The PDB wallet root path will be WALLET_ROOT/<pdbguid>.

Remember the okv endpoint software was installed in WALLET_ROOT/okv. The PDB will search the the okv endpoint software in WALLET_ROOT/<pdbguid>/okv path. We have two choices:

Create another endpoint for each PDB and install the endpoint software in WALLET_ROOT/<pdbguid>/okv path.
Use the same endpoint for the whole CDB. In this case We can use the same okv endpoint software.

I choose the second method for simplicity, so I create a database link from WALLET_ROOT/okv to WALLET_ROOT/<pdbguid>/okv

SQL> select pdb_id, pdb_name, guid from dba_pdbs;

    PDB_ID PDB_NAME             GUID
---------- -------------------- --------------------------------
         3 PDB1                 0887FBD2D436F52AE0631420D5B4B292
         2 PDB$SEED             0887DD6BF35FA66EE0631420D5B48934

[oracle@ ~]$ mkdir/u01/app/oracle/tde_wallet/DBTDEOKV/0887FBD2D436F52AE0631420D5B4B292

[oracle@ DBTDEOKV]$ mkdir /u01/app/oracle/tde_wallet/DBTDEOKV/0887FBD2D436F52AE0631420D5B4B292/tde

[oracle@ ~]$ cd /u01/app/oracle/tde_wallet/DBTDEOKV/0887FBD2D436F52AE0631420D5B4B292/

[oracle@ 0887FBD2D436F52AE0631420D5B4B292]$ ln -s /u01/app/oracle/tde_wallet/DBTDEOKV/okv .

Configure the CDB$ROOT database for TDE:

-- some usefull best practice parameters for TDE
SQL> alter system set encrypt_new_tablespaces='ALWAYS' scope=spfile;

SQL> alter system set tablespace_encryption='AUTO_ENABLE' scope=spfile;

-- configure the WALLET_ROOT
SQL> alter system set wallet_root='/u01/app/oracle/tde_wallet/DBTDEOKV' scope=spfile;

System altered.

-- restart the database
SQL> shutdown immediate;

SQL> startup;

-- configure the TDE to use OKV for keys and local FILE for autologin
SQL> alter system set tde_configuration="KEYSTORE_CONFIGURATION=OKV|FILE" scope=both;

-- at this level no keystore open 
SQL> @tde.sql

WRL_TYPE   WRL_PARAMETER                            STATUS     WALLET_TYPE          WALLET_OR KEYSTORE FULLY_BAC     CON_ID
---------- ---------------------------------------- ---------- -------------------- --------- -------- --------- ----------
FILE       /u01/app/oracle/tde_wallet/DBTDEOKV/tde/ NOT_AVAILA UNKNOWN              SINGLE    NONE     UNDEFINED          1
                                                    BLE

OKV                                                 CLOSED     UNKNOWN              SINGLE    NONE     UNDEFINED          1
FILE                                                NOT_AVAILA UNKNOWN              SINGLE    UNITED   UNDEFINED          3
                                                    BLE

OKV                                                 CLOSED     UNKNOWN              SINGLE    UNITED   UNDEFINED          3

-- open the keystore => goes to state OPEN_NO_MASTER_KEY
SQL> administer key management set keystore open identified by "enpoint_password";

SQL>  @tde.sql

WRL_TYPE   WRL_PARAMETER                            STATUS     WALLET_TYPE          WALLET_OR KEYSTORE FULLY_BAC     CON_ID
---------- ---------------------------------------- ---------- -------------------- --------- -------- --------- ----------
FILE       /u01/app/oracle/tde_wallet/DBTDEOKV/tde/ NOT_AVAILA UNKNOWN              SINGLE    NONE     UNDEFINED          1
                                                    BLE

OKV                                                 OPEN_NO_MA OKV                  SINGLE    NONE     UNDEFINED          1
                                                    STER_KEY

FILE                                                NOT_AVAILA UNKNOWN              SINGLE    UNITED   UNDEFINED          3
                                                    BLE

OKV                                                 CLOSED     UNKNOWN              SINGLE    UNITED   UNDEFINED          3

-- create the MEK (Master Encryption Key). Use tag is a best practice
SQL> administer key management set key using tag "CDB:DBTDEOKV MEK first" identified by "enpoint_password";
SQL> @tde.sql

WRL_TYPE   WRL_PARAMETER                            STATUS     WALLET_TYPE          WALLET_OR KEYSTORE FULLY_BAC     CON_ID
---------- ---------------------------------------- ---------- -------------------- --------- -------- --------- ----------
FILE       /u01/app/oracle/tde_wallet/DBTDEOKV/tde/ NOT_AVAILA UNKNOWN              SINGLE    NONE     UNDEFINED          1
                                                    BLE

OKV                                                 OPEN       OKV                  SINGLE    NONE     UNDEFINED          1
FILE                                                NOT_AVAILA UNKNOWN              SINGLE    UNITED   UNDEFINED          3
                                                    BLE

OKV                                                 CLOSED     UNKNOWN              SINGLE    UNITED   UNDEFINED          3

--configure the  autologin 
SQL>  administer key management add secret 'enpoint_password'  for client 'OKV_PASSWORD' to local auto_login keystore '/u01/app/oracle/tde_wallet/DBTDEOKV/tde'

-- check 
SQL> !ls /u01/app/oracle/tde_wallet/DBTDEOKV/tde
cwallet.sso

Configure the PDB for TDE. Applying the TDE configuration at PDB level will automatically switch to ISOLTAED mode.

SQL> alter session set container=pdb1;

SQL> alter system set tde_configuration="KEYSTORE_CONFIGURATION=OKV|FILE" scope=both;
SQL> @tde

WRL_TYPE   WRL_PARAMETER                            STATUS     WALLET_TYPE          WALLET_OR KEYSTORE FULLY_BAC     CON_ID
---------- ---------------------------------------- ---------- -------------------- --------- -------- --------- ----------
FILE       /u01/app/oracle/tde_wallet/DBTDEOKV/0887 OPEN_NO_MA LOCAL_AUTOLOGIN      SINGLE    ISOLATED UNDEFINED          3
           FBD2D436F52AE0631420D5B4B292/tde/        STER_KEY

OKV                                                 OPEN_NO_MA OKV                  SINGLE    ISOLATED UNDEFINED          3
                                                    STER_KEY

SQL> administer key management set key using tag "CDB:DBTDEOKV:PDB1 MEK first" identified by "enpoint_password";

SQL> @tde

WRL_TYPE   WRL_PARAMETER                            STATUS     WALLET_TYPE          WALLET_OR KEYSTORE FULLY_BAC     CON_ID
---------- ---------------------------------------- ---------- -------------------- --------- -------- --------- ----------
FILE       /u01/app/oracle/tde_wallet/DBTDEOKV/0887 NOT_AVAILA UNKNOWN              SINGLE    ISOLATED UNDEFINED          3
           FBD2D436F52AE0631420D5B4B292/tde/        BLE

OKV                                                 OPEN       OKV                  SINGLE    ISOLATED UNDEFINED          3

-- configure autologin the client name must be OKV_PASSWORD 
SQL>   administer key management add secret 'enpoint_password'  for client 'OKV_PASSWORD' to local auto_login keystore '/u01/app/oracle/tde_wallet/DBTDEOKV/0887FBD2D436F52AE0631420D5B4B292/tde';

-- check
SQL> !ls /u01/app/oracle/tde_wallet/DBTDEOKV/0887FBD2D436F52AE0631420D5B4B292/tde/
cwallet.sso

-- reastart to validate
SQL> shutdown immediate;
Pluggable Database closed.
SQL> startup;
Pluggable Database opened.
SQL> @tde

RL_TYPE   WRL_PARAMETER                            STATUS     WALLET_TYPE          WALLET_OR KEYSTORE FULLY_BAC     CON_ID
---------- ---------------------------------------- ---------- -------------------- --------- -------- --------- ----------
FILE       /u01/app/oracle/tde_wallet/DBTDEOKV/0887 NOT_AVAILA UNKNOWN              SINGLE    ISOLATED UNDEFINED          3
           FBD2D436F52AE0631420D5B4B292/tde/        BLE

OKV                                                 OPEN       OKV                  SINGLE    ISOLATED UNDEFINED          3

Check if the keys are accessible at OKV level:

[oracle@ ~]$ /u01/app/oracle/tde_wallet/DBTDEOKV/okv/bin/okvutil list
Enter Oracle Key Vault endpoint password:
Unique ID                               Type            Identifier
2E1A81B8-E8DA-4FA6-BF6F-28F48B274A7E    Symmetric Key   TDE Master Encryption Key: TAG CDB:DBTDEOKV MEK first
6010F986-4A55-4F8D-BF22-891DF4DAD290    Symmetric Key   TDE Master Encryption Key: TAG CDB:DBTDEOKV:PDB1 MEK first

Make a full restart to validate the whole:

SQL> startup force

SQL> @tde

WRL_TYPE   WRL_PARAMETER                            STATUS     WALLET_TYPE          WALLET_OR KEYSTORE FULLY_BAC     CON_ID
---------- ---------------------------------------- ---------- -------------------- --------- -------- --------- ----------
FILE       /u01/app/oracle/tde_wallet/DBTDEOKV/tde/ OPEN_NO_MA LOCAL_AUTOLOGIN      SINGLE    NONE     UNDEFINED          1
                                                    STER_KEY

OKV                                                 OPEN       OKV                  SINGLE    NONE     UNDEFINED          1
FILE       /u01/app/oracle/tde_wallet/DBTDEOKV/0887 OPEN_NO_MA LOCAL_AUTOLOGIN      SINGLE    ISOLATED UNDEFINED          3
           FBD2D436F52AE0631420D5B4B292/tde/        STER_KEY

OKV                                                 OPEN       OKV                  SINGLE    ISOLATED UNDEFINED

The autologin file has the status OPEN_NO_MASTER_KEY. This can be explained by the fact that autologin cwallet.sso file is locally and the key on on OKV server. I cannot say if this is a normall behaviur or is a display bug.

Conclusion

Even it seems complicated, all the described procedure can be easily scripted and implemented as a post script, for a database server deployment.

Using OKV to keep the keys is one of the best solution. Otherwise you have to do it by your own, as you cannot user Oracle tools like RMAN to backup them.

OKV will keep the track of all keys and theyr dates even if in case of rekey process.

Loosing keys leads to database lost. Even Oracle cannot help.

Post Views: 1,966

Add Oracle database server to Oracle Key vault (OKV) from scratch, only with RESTfull API.

Introduction

Naming and conventions

Download the RESTfull API from OKV server

Configure the RESTfull API

Create and enroll the endpoint

Create a wallet for the endpoint

Configure the encryption on the database to use OKV

Conclusion

Alexandre Nestor

Leave a Reply:

Related blog articles

Introduction

Naming and conventions

Download the RESTfull API from OKV server

Configure the RESTfull API

Create and enroll the endpoint

Create a wallet for the endpoint

Configure the encryption on the database to use OKV

Conclusion

Alexandre Nestor

Leave a Reply:

Related blog articles

ExaCC OCI integration with OEM 13.5 Cloud Control installed On-premises.

OEM Cloud Control Release update to 13.5.0.23

Automatic Transaction Rollback in Oracle Database 23ai

Hot Clone of a Remote PDB in a Dataguard Environment 19c