By Franck Pachot

.
Oracle Transparent Data Encryption is available without option on the Oracle Public Cloud: Standard Edition as well as Enterprise Edition (EE, EE-HP EE-EP, ECS). More than that, the DBaaS enforces TDE for any user tablespace even when not specifying in the CREATE TABLESPACE. It you are not familiar with TDE key management (wallets) then you have probably encountered ORA-28374: typed master key not found in wallet.
Rather than another tutorial on TDE I’ll try to explain it from the errors you may encounter when simply creating a tablespace.

I have created a new pluggable database PDB2 from the command line:


SQL> create pluggable database PDB2 admin user admin identified by "admin";
Pluggable database PDB2 created.
 
SQL> alter pluggable database PDB2 open read write;
Pluggable database PDB2 altered.
 
SQL> show pdbs
CON_ID  CON_NAME  OPEN MODE   RESTRICTED
------- --------- ----------- ----------
2       PDB$SEED  READ ONLY   NO
3       PDB1      READ WRITE  NO
7       PDB2      READ WRITE  NO

I go to the PDB2 container and try to create a tablespace:


SQL> alter session set container=PDB2;
Session altered.
 
SQL> create tablespace mytablespace;
 
Error starting at line 1 in command -
create tablespace mytablespace
Error report -
ORA-28374: typed master key not found in wallet
28374. 0000 -  "typed master key not found in wallet"
*Cause:    You attempted to access encrypted tablespace or redo logs with
           a typed master key not existing in the wallet.
*Action:   Copy the correct Oracle Wallet from the instance where the tablespace
           was created.

So, this message is related with TDE wallet.

encrypt_new_tablespaces

I didn’t specify any encryption clause in the CREATE TABLESPACE command but it is activated by default by the following parameter:


SQL> show parameter encrypt_new_tablespaces
 
NAME                    TYPE   VALUE
----------------------- ------ ----------
encrypt_new_tablespaces string CLOUD_ONLY

The values can be DDL (the old behavior where encryption must be defined in the CREATE TABLESPACE statement), ALWAYS (AES128 encryption by default), or CLOUD_ONLY which is the same as ALWAYS when the instance is on the Cloud, or as DDL if the instance is on-premises. The default is CLOUD_ONLY.
This parameter has been introduced in 12.2 and has been backported to 11.2.0.4 and 12.1.0.2 with bug 21281607 that is applied on any Oracle Public Cloud DBaaS instance.

So, one solution to create our tablespace is to set encrypt_new_tablespaces to DDL but as it is recommended to encrypt all user tablespaces, let’s continue with it.

ORA-28374: typed master key not found in wallet

So the error message means that I don’t have a master key in the wallet for my newly created PDB because in multitenant each PDB has it’s own master key (but there’s only one wallet for the CDB).
The wallet is opened:


SQL> select * from v$encryption_wallet;
 
WRL_TYPE  WRL_PARAMETER  STATUS              WALLET_TYPE  WALLET_ORDER  FULLY_BACKED_UP  CON_ID
-------- --------------- ------------------- ------------ ------------- ---------------- ------
FILE                     OPEN_NO_MASTER_KEY  AUTOLOGIN    SINGLE        UNDEFINED        7

But empty (I’m still in the PDB2 container)


SQL> select * from v$encryption_keys order by creation_time;
no rows selected

SET KEY

So the idea is to set a key:


SQL> administer key management set key identified by "Ach1z0#d";

but:


Error starting at line 1 in command -
administer key management set key identified by "Ach1z0#d"
Error report -
ORA-28417: password-based keystore is not open
28417. 0000 -  "password-based keystore is not open"
*Cause:    Password-based keystore was not opened.
*Action:   Close the auto login keystore, if required, and open a
           password-based keystore.

Ok. An error because the wallet is not opened. Let’s try to open it:

SQL> administer key management set keystore open identified by "Ach1z0#d";
 
Error starting at line 1 in command -
administer key management set keystore open identified by "Ach1z0#d"
Error report -
ORA-28354: Encryption wallet, auto login wallet, or HSM is already open
28354. 0000 -  "Encryption wallet, auto login wallet, or HSM is already open"
*Cause:    Encryption wallet, auto login wallet, or HSM was already opened.
*Action:   None.

Actually, the wallet is opened. We have seen that the opened wallet is AUTOLOGIN:


SQL> select * from v$encryption_wallet;
 
WRL_TYPE  WRL_PARAMETER  STATUS              WALLET_TYPE  WALLET_ORDER  FULLY_BACKED_UP  CON_ID
-------- --------------- ------------------- ------------ ------------- ---------------- ------
FILE                     OPEN_NO_MASTER_KEY  AUTOLOGIN    SINGLE        UNDEFINED        7

On DBaaS an AUTOLOGIN wallet is used to be able to have the database automatically restarted without manual intervention. Without AUTOLOGIN wallet you have to provide the password.

But AUTOLOGIN wallet is limited to use it to access the tablespaces.
When administering the wallet, we need to provide the password manually:

We need to close the AUTOLOGIN one:


SQL> administer key management set keystore close;
Key MANAGEMENT succeeded.

Now that it is closed, we can try to open it and open it with the password:


SQL> administer key management set keystore open identified by "Ach1z0#d";
 
Error starting at line : 1 in command -
administer key management set keystore open identified by "Ach1z0#d"
Error report -
ORA-28417: password-based keystore is not open
28417. 0000 -  "password-based keystore is not open"
*Cause:    Password-based keystore was not opened.
*Action:   Close the auto login keystore, if required, and open a
           password-based keystore.

Oh… it is opened AUTOLOGIN once again:


SQL> select * from v$encryption_wallet;
 
WRL_TYPE  WRL_PARAMETER  STATUS              WALLET_TYPE  WALLET_ORDER  FULLY_BACKED_UP  CON_ID
-------- --------------- ------------------- ------------ ------------- ---------------- ------
FILE                     OPEN_NO_MASTER_KEY  AUTOLOGIN    SINGLE        UNDEFINED        7

CDB$ROOT

You need to open the wallet with password from CDB$ROOT:


SQL> alter session set container=CDB$ROOT;
Session altered.
 
SQL> administer key management set keystore close;
Key MANAGEMENT succeeded.
 
SQL> administer key management set keystore open identified by "Ach1z0#d";
Key MANAGEMENT succeeded.

So here is the right way to start: in CDB$ROOT close the AUTOLOGIN wallet and open it with the password.

PDB

Now ready to go further in the PDB2.


SQL> alter session set container=PDB2;
Session altered.

The wallet is now closed for the PDB:


SQL> select * from v$encryption_wallet;
WRL_TYPE  WRL_PARAMETER  STATUS  WALLET_TYPE  WALLET_ORDER  FULLY_BACKED_UP  CON_ID
--------- -------------- ------- ------------ ------------- ---------------- ------
FILE                     CLOSED  UNKNOWN      SINGLE        UNDEFINED        7

Let’s open it manually:


SQL> administer key management set keystore open identified by "Ach1z0#d";
Key MANAGEMENT succeeded.

We have no encryption key:


SQL> select * from v$encryption_keys order by creation_time;
no rows selected

Let’s do what we want to do from the get-go: create an encryption key for our PDB:


SQL> administer key management set key identified by "Ach1z0#d";
 
Error starting at line 1 in command -
administer key management set key identified by "Ach1z0#d"
Error report -
ORA-46631: keystore needs to be backed up
46631. 00000 -  "keystore needs to be backed up"
*Cause:    The keystore was not backed up. For this operation to proceed, the
           keystore must be backed up.
*Action:   Backup the keystore and try again.

Oh yes. Any change must be backed up. That’s easy:


SQL> administer key management set key identified by "Ach1z0#d" with backup;
Key MANAGEMENT succeeded.

Here we are. The key is there:


SQL> select * from v$encryption_keys order by creation_time;
 
KEY_ID                                                TAG  CREATION_TIME                           ACTIVATION_TIME                         CREATOR  CREATOR_ID  USER  USER_ID  KEY_USE     KEYSTORE_TYPE      ORIGIN  BACKED_UP  CREATOR_DBNAME  CREATOR_DBID  CREATOR_INSTANCE_NAME  CREATOR_INSTANCE_NUMBER  CREATOR_INSTANCE_SERIAL  CREATOR_PDBNAME  CREATOR_PDBID  CREATOR_PDBUID  CREATOR_PDBGUID                   ACTIVATING_DBNAME  ACTIVATING_DBID  ACTIVATING_INSTANCE_NAME  ACTIVATING_INSTANCE_NUMBER  ACTIVATING_INSTANCE_SERIAL  ACTIVATING_PDBNAME  ACTIVATING_PDBID  ACTIVATING_PDBUID  ACTIVATING_PDBGUID                CON_ID
----------------------------------------------------- ---- --------------------------------------- --------------------------------------- -------- ----------- ----- -------- ----------- ------------------ ------- ---------- --------------- ------------- ---------------------- ------------------------ ------------------------ ---------------- -------------- --------------- --------------------------------- ------------------ ---------------- ------------------------- --------------------------- --------------------------- ------------------- ----------------- ------------------ --------------------------------- ------
AXP3BIrVW0+Evwfx7okZtcgAAAAAAAAAAAAAAAAAAAAAAAAAAAAA       28-NOV-16 08.41.20.629496000 PM +00:00  28-NOV-16 08.41.20.629498000 PM +00:00  SYS      0           SYS   0        TDE IN PDB  SOFTWARE KEYSTORE  LOCAL   NO         CDB1            902797638     CDB1                   1                        4294967295               PDB2             7              96676154        42637D7C7F7A3315E053DA116A0A2666  CDB1               902797638        CDB1                      1                           4294967295                  PDB2                7                 96676154           42637D7C7F7A3315E053DA116A0A2666  7

All is perfect but the wallet is still opened with the password:


SQL> select * from v$encryption_wallet;
WRL_TYPE  WRL_PARAMETER  STATUS  WALLET_TYPE  WALLET_ORDER  FULLY_BACKED_UP  CON_ID
-------- --------------- ------- ------------ ------------- --------------- -------
FILE                     OPEN    PASSWORD     SINGLE        NO               7

In order to get back to the initial state, it is sufficient to close it (from the CDB$ROOT):


SQL> alter session set container=CDB$ROOT;
Session altered.
 
SQL> administer key management set keystore close;
 
Error starting at line 1 in command -
administer key management set keystore close
Error report -
ORA-28389: cannot close auto login wallet
28389. 00000 -  "cannot close auto login wallet"
*Cause:    Auto login wallet could not be closed because it was opened with
           another wallet or HSM requiring a password.
*Action:   Close the wallet or HSM with a password.

Ok. The ‘close’ command needs the password as it was not opened with AUTOLOGIN one.


SQL> administer key management set keystore close identified by "Ach1z0#d";
Key MANAGEMENT succeeded.

It is immediately automatically re-opened with the AUTOLOGIN one:


SQL> select * from v$encryption_wallet;
 
WRL_TYPE  WRL_PARAMETER                           STATUS  WALLET_TYPE  WALLET_ORDER  FULLY_BACKED_UP  CON_ID
--------- --------------------------------------- ------- ------------ ------------- ---------------- ------
FILE      /u01/app/oracle/admin/CDB1/tde_wallet/  OPEN    AUTOLOGIN    SINGLE        NO               1

and from the CDB$ROOT I can see all of them:


SQL> select * from v$encryption_keys order by creation_time;
 
KEY_ID                                                TAG        CREATION_TIME                           ACTIVATION_TIME                         CREATOR  CREATOR_ID  USER  USER_ID  KEY_USE     KEYSTORE_TYPE      ORIGIN  BACKED_UP  CREATOR_DBNAME  CREATOR_DBID  CREATOR_INSTANCE_NAME  CREATOR_INSTANCE_NUMBER  CREATOR_INSTANCE_SERIAL  CREATOR_PDBNAME  CREATOR_PDBID  CREATOR_PDBUID  CREATOR_PDBGUID                   ACTIVATING_DBNAME  ACTIVATING_DBID  ACTIVATING_INSTANCE_NAME  ACTIVATING_INSTANCE_NUMBER  ACTIVATING_INSTANCE_SERIAL  ACTIVATING_PDBNAME  ACTIVATING_PDBID  ACTIVATING_PDBUID  ACTIVATING_PDBGUID                CON_ID
----------------------------------------------------- ---- --------------------------------------- --------------------------------------- -------- ----------- ----- -------- ----------- ------------------ ------- ---------- --------------- ------------- ---------------------- ------------------------ ------------------------ ---------------- -------------- --------------- --------------------------------- ------------------ ---------------- ------------------------- --------------------------- --------------------------- ------------------- ----------------- ------------------ --------------------------------- ------
ATxUk1G7gU/0v3Ygk1MbZj8AAAAAAAAAAAAAAAAAAAAAAAAAAAAA             27-NOV-16 09.02.18.050676000 PM +00:00  27-NOV-16 09.02.18.130705000 PM +00:00  SYS      0           SYS   0        TDE IN PDB  SOFTWARE KEYSTORE  LOCAL   YES        CDB1            902797638     CDB1                   1                        4294967295               CDB$ROOT         1              1               3D94C45E41CA19A9E05391E5E50AB8D8  CDB1               902797638        CDB1                      1                           4294967295                  CDB$ROOT            1                 1                  3D94C45E41CA19A9E05391E5E50AB8D8  1
AWSs1Gr0WE86vyfWc123xccAAAAAAAAAAAAAAAAAAAAAAAAAAAAA             27-NOV-16 09.02.18.089346000 PM +00:00  27-NOV-16 09.02.18.722365000 PM +00:00  SYS      0           SYS   0        TDE IN PDB  SOFTWARE KEYSTORE  LOCAL   YES        CDB1            902797638     CDB1                   1                        4294967295               PDB1             3              2687567370      424FA3D9C61927FFE053DA116A0A85F7  CDB1               902797638        CDB1                      1                           4294967295                  PDB1                3                 2687567370         424FA3D9C61927FFE053DA116A0A85F7  3
AfwqzZP/Rk+5v5WqiNK5nl0AAAAAAAAAAAAAAAAAAAAAAAAAAAAA             28-NOV-16 08.36.43.980717000 PM +00:00  28-NOV-16 08.36.43.980720000 PM +00:00  SYS      0           SYS   0        TDE IN PDB  SOFTWARE KEYSTORE  LOCAL   YES        CDB1            902797638     CDB1                   1                        4294967295               PDB2             5              2602763579      42636D1380072BE7E053DA116A0A8E2D  CDB1               902797638        CDB1                      1                           4294967295                  PDB2                5                 2602763579         42636D1380072BE7E053DA116A0A8E2D  5
AXP3BIrVW0+Evwfx7okZtcgAAAAAAAAAAAAAAAAAAAAAAAAAAAAA             28-NOV-16 08.41.20.629496000 PM +00:00  28-NOV-16 08.41.20.629498000 PM +00:00  SYS      0           SYS   0        TDE IN PDB  SOFTWARE KEYSTORE  LOCAL   NO         CDB1            902797638     CDB1                   1                        4294967295               PDB2             7              96676154        42637D7C7F7A3315E053DA116A0A2666  CDB1               902797638        CDB1                      1                           4294967295                  PDB2                7                 96676154           42637D7C7F7A3315E053DA116A0A2666  7

As you can see I did two attempts with the PDB2 to write this blog post. The previous keys are all in the wallet.

I check that the AUTOLOGIN is opened in PDB2:


SQL> alter session set container=PDB2;
Session altered.
 ;
SQL> select * from v$encryption_wallet;
WRL_TYPE  WRL_PARAMETER  STATUS  WALLET_TYPE  WALLET_ORDER  FULLY_BACKED_UP  CON_ID
--------- -------------- ------- ------------ ------------- ---------------- ------
FILE                     OPEN    AUTOLOGIN    SINGLE        NO               7

And finally I can create my tablespace


SQL> create tablespace mytablespace;
Tablespace MYTABLESPACE created.

Easy, isn’t it?

If you create your PDB with the DBaaS monitor interface all is done automatically with the ‘create PDB’ button:

  • Close the AUTOLOGIN wallet (from CDB$ROOT)
  • Open the wallet with password
  • Create the pluggable database and open it
  • Open the wallet from the PDB, with password
  • Set the masterkey for the PDB
  • Close the wallet to get it opened with AUTOLOGIN
Post Views: 3,449
Thumbnail [60x60]
by
Oracle Team