AutoUpgrade and Secure External Password Store Enables Complete Automation

Many commands that involve Transparent Data Encryption (TDE) require inputting the TDE keystore password. Also, when you use AutoUpgrade. on an encrypted Oracle Database you probably need to store the TDE keystore password using the -load_password option.

Manually inputting passwords is unsuitable for an environment with a high degree of automation. In Oracle Database it is solved by Secure External Password Store (SEPS) (as of Oracle Database 12.2). In a previous blog post, I showed how you could use it to your advantage.

This blog post is about how to use AutoUpgrade together with SEPS.

Good News

As of version 22.2 AutoUpgrade fully supports Oracle Database with a Secure External Password Store. If SEPS contains the TDE keystore password, you don’t have to input the password using the -load_password option.

If you are using AutoUpgrade in some sort of automation (like Ansible), you should look into SEPS. AutoUpgrade can use SEPS when the TDE keystore password is needed, and you can upgrade and convert completely unattended.

How To

The Oracle Database DB12 is encrypted and on Oracle Database 12.2. I want to upgrade, convert, and plug it into CDB2 on Oracle Database 19c.

  1. Ensure that your Oracle Databases DB12 and CDB2 are properly configured with a Secure External Password Store and it contains the TDE keystore password.
  2. Ensure that AutoUpgrade is version 22.2 or higher:
    $ java -jar autoupgrade.jar -version
    
  3. Create your AutoUpgrade config file and set global.keystore as specified in a previous blog post:
    global.autoupg_log_dir=/u01/app/oracle/cfgtoollogs/autoupgrade
    global.keystore=/u01/app/oracle/admin/autoupgrade/keystore
    	
    upg1.log_dir=/u01/app/oracle/cfgtoollogs/autoupgrade/DB12
    upg1.source_home=/u01/app/oracle/product/12.2.0.1
    upg1.target_home=/u01/app/oracle/product/19
    upg1.sid=DB12
    upg1.target_cdb=CDB2
    
  4. Analyze:
    $ java -jar autoupgrade.jar -config DB12.cfg -mode analyze
    
  5. The summary report tells me everything is fine; just go ahead. I don’t need to input the TDE keystore passwords:
    [Stage Name]    PRECHECKS
    [Status]        SUCCESS
    [Start Time]    2022-03-30 10:28:38
    [Duration]       
    [Log Directory] /u01/app/oracle/cfgtoollogs/autoupgrade/DB12/DB12/100/prechecks
    [Detail]        /u01/app/oracle/cfgtoollogs/autoupgrade/DB12/DB12/100/prechecks/db12_preupgrade.log
    				Check passed and no manual intervention needed
    
  6. Optionally, I can use the -load_password prompt to check the TDE configuration:
    $ java -jar autoupgrade.jar -config DB12.cfg -load_password
    	
    TDE> list
    +----------+---------------+------------------+-----------+------------------+
    |ORACLE_SID|Action Required|      TDE Password|SEPS Status|Active Wallet Type|
    +----------+---------------+------------------+-----------+------------------+
    |      CDB2|               |No password loaded|   Verified|               Any|
    |      DB12|               |No password loaded|    Unknown|        Auto-login|
    +----------+---------------+------------------+-----------+------------------+
    
    Action Required is empty and verifies that I don’t need to input the TDE keystore passwords. AutoUpgrade checked SEPS in CDB2 and found that it works. It is impossible to check SEPS in DB12 because it is on Oracle Database 12.2. The functionality was added in Oracle Database 19c.
  7. Start the upgrade and conversion:
    $ java -jar autoupgrade.jar -config DB12.cfg -mode deploy
    
  8. That’s it!

What Happens

  • You must configure an AutoUpgrade keystore. Even though you are not loading any TDE keystore passwords, it is still required. Some commands require a passphrase (or transport secret) and AutoUpgrade must store them in its keystore.
  • Whenever a database is using SEPS, and a TDE keystore password is required, AutoUpgrade will use the IDENTIFIED BY EXTERNAL STORE clause.

What Else

You can mix and match. If only one database uses SEPS, you can input the other TDE keystore password manually using the -load_password option. AutoUpgrade will check your database configuration and ask only for the needed TDE keystore passwords.

Other Blog Posts in This Series

How to Stop Hardcoding Your TDE Keystore Password

When you encrypt your databases, you will often end up needing the keystore password to perform certain operations. For instance, cloning an encrypted PDB will require the keystore password:

CREATE PLUGGABLE DATABASE ... KEYSTORE IDENTIFIED BY S3cr3t;

This is not very secure because the keystore password is now visible in clear text. Further, if you have the command in a script, ansible, cron job or the like, you will also have it there in clear text. Or, if your organization has implemented separation of duties, and the operational DBAs don’t have access to the keystore password.

Enter Secure External Password Store (SEPS)

Using the example above, what if you could just write the following:

CREATE PLUGGABLE DATABASE ... KEYSTORE IDENTIFIED BY EXTERNAL STORE;

And the database would get the secret keystore password without involving you! Your problems would be solved.

That is what you can do with Secure External Password Store. Whenever you need to specify the keystore password using KEYSTORE IDENTIFIED BY you can use the EXTERNAL STORE clause, and avoid typing the password.

SEPS was introduced in 10g, however, since Oracle Database 12.2 you can store keystore credentials in it. SEPS is similar to an auto-login keystore. It is an encrypted file stored in the file system that is encrypted by a password that only the database knows. You as a DBA can’t extract the information from the database.

Configuration

First, tell the database where SEPS is located:

SQL> ALTER SYSTEM 
     SET EXTERNAL_KEYSTORE_CREDENTIAL_LOCATION = '$ORACLE_BASE/admin/$ORACLE_SID/wallet/tde_seps'
     SCOPE=SPFILE;
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP

Next, add the keystore password (in this case S3cr3t) into SEPS as a secret for the client TDE_WALLET:

SQL> ADMINISTER KEY MANAGEMENT
     ADD SECRET 'S3cr3t' FOR CLIENT 'TDE_WALLET'
     USING TAG 'TDE keystore password' 
     TO LOCAL AUTO_LOGIN KEYSTORE '$ORACLE_BASE/admin/$ORACLE_SID/wallet/tde_seps’;

Finally, replace the keystore password in your commands with SEPS using the EXTERNAL STORE clause:

SQL> --No longer need this
SQL> CREATE PLUGGABLE DATABASE ... KEYSTORE IDENTIFIED BY S3cr3t;
SQL> --Now you can do this
SQL> CREATE PLUGGABLE DATABASE ... KEYSTORE IDENTIFIED BY EXTERNAL STORE;

You can use KEYSTORE IDENTIFIED BY EXTERNAL STORE on most ADMINISTER KEY MANAGEMENT commands, like exporting and importing encryption keys, but for security reasons some ADMINISTER KEY MANAGEMENT commands still require that you type in the keystore password.

When you specify the location of SEPS, I strongly recommend that you stick to the default location, $ORACLE_BASE/admin/$ORACLE_SID/wallet/tde_seps. If you are also using WALLET_ROOT parameter, then SEPS must be stored in the default location.

If you are using Oracle Key Vault or a dedicated Hardware Security Module instead of a software keystore, you can store those credentials in SEPS as well. Respectively, use CLIENT set to OKV_PASSWORD or HSM_PASSWORD.

RAC

SEPS works fine on a RAC database as well.

  • For simplicity, I would place the SEPS keystore on shared storage. Then all nodes can access the same keystore.
  • When you create the keystore, do not use a local auto-login keystore. The keystore must be a regular auto-login keystore, if all nodes should be able to access it.
    ADMINISTER KEY MANAGEMENT ... TO AUTO_LOGIN KEYSTORE 
    

Conclusion

You can simplify commands that involve the keystore by using SEPS. In addition, it will be more secure because you can avoid typing or hardcoding the keystore password in cleartext.

I have produced a small demo that you can watch on YouTube:

If you like the video and want more, remember to hit the subscribe button on our YouTube channel.

References