Before we go far; this is the first part of a 5 part blog around the subject of managing keys for use with DBMS_CRYPTO. Of course as I write this blog the links to the next 4 parts do not exist; I hope to come back and add these very soon. The five proposed blog parts are:
- Part 1: (This blog) - Discuss the problem in general and the need to encrypt data in the database
- Part 2 - We will present a simple solution design to store an encryption key for use with DBMS_CRYPTO and discuss some of its flaws
- Part 3 - Lets build a key vault
- Part 4 - Protecting the key vault
- Part 5 - Using the key vault with DBMS_CRYPTO and SQL
This method of using Oracle built in packages to encrypt data differs from TDE (Transparent Database Encryption) in that TDE is automatic as it either decrypts blocks of data returned to the SGA from data files on disk for tablespace encryption or it returns encrypted blocks and decrypts columns of data from rows of data as it needs to on demand for column based encryption.
Both TDE mechanisms are automatic and from the users perspective (The SQL executor) the data is still manipulated in clear text as normal but is in fact stored on disk encrypted. With TDE the keys are managed automatically and are generally in a wallet. There are also three layers of keys 1: Passphrase/password to open the wallet or autologin, 2: Master key stored in the wallet, 3: column keys or tablespace keys stored locally encrypted. These final keys are used to encrypt and decrypt the actual data and these keys are encrypted/decrypted by the master key. TDE uses synchronous two way encryption and whilst its ease of use is fantastic the problem - if you can call it a problem - is that the data is still available transparently via a SQL interface where the user of that SQL has rights to access encrypted data. So, if a user can select from a table in an encrypted tablespace or from a column of data that is encrypted he/she still can and can see clear text data.
The purpose of TDE is to protect the data in the datafiles at rest.
The one open gateway if you will is that the data files are encrypted - good - but the software owner (can be "oracle") is usually the only user able to access these files at the file system level - BUT that user can also simply connect to the database "/ as sysdba" and access any protected data. To prevent this in a database using TDE you must also limit the access "as sysdba" to the encrypted data. You must also use other mechanisms from normal object rights to tools such as Database Vault or VPD to limit access to the tables and therefore the encrypted data.
Of course we mentioned a wallet above used as an encryption key store but Oracle also supports the use of a Hardware Security Module (HSM) to store and protect the master key. In this case the customer can use a HSM from a third party where the master key never leaves the HSM and a C library is provided that allows TDE to talk to the HSM using standard wallets and wallet access software silently via the database. In fact in TDE the commands to open the wallet/HSM and pass the keys to be used is done silently in the background.
So, TDE is a great product and makes encrypting data easy without much effort and it also provides a way to protect data files at rest BUT what if you want to make sure that the data held in the database is normally presented encrypted to any non-authorised random access?
This is also a solution to the problem presented earlier and it means that even if someone has SQL access like with TDE they do not see clear text data unless also authorised to decrypt the data
The solutions provided by Oracle are the DBMS_CRYPTO package and in older databases the DBMS_OBFUSCATION_TOOLKIT package. The thing that stands out immediately with DBMS_CRYPTO and earlier with DBMS_OBFUSCATION_TOOLKIT is there there is no built in key management and no simple way to use or access a standard wallet. There are no mechanisms to store the encryption keys securely or provide management of keys so that a password / passphrase that unlocks the master key could be changed / cycled when needed but leaving the master key and the data encrypted with the original keys; or there is no way to cycle the master key so that again the column or row keys are left encrypted but the master key can be changed and the column keys decrypted/encrypted to allow the change but no change to the data. Finally there is no easy built in way to cycle the column keys.
Further there is no thoughts to backup of data and keys or data and old data that is encrypted say on tape with old keys.
We will not get into semantics of the use of DBMS_CRYPTO; the algorithms and other factors available as well as all of the concerns around the problems of actually encrypting data such as a join from a column that is encrypted to a clear text column or indexes on encrypted columns or the performance of dealing with now encrypted data or any changes to storage requirements such as column data types or lengths of data.
All of these are valid issues but as part of this blog and following blogs we are not even in a position to discuss these items until we can securely use DBMS_CRYPTO and keys. Performance, storage and joins are moot if we simply have to hard code a single key or pass it in. I am going to focus on the problem of keys and managing them for DBMS_CRYPTO.
Oracle does not appear to have any interfaces that would allow us to use a wallet to store and retrieve a master key for use in PL/SQL and with DBMS_CRYPTO. A quick search of the database shows:
SQL> col object_name for a30
SQL> col owner for a30
SQL> col procedure_name for a30
SQL> set lines 220
SQL> select owner,object_name,procedure_name
2 from dba_procedures
3 where procedure_name like '%WALLET%'
4 or object_name like '%WALLET%';
OWNER OBJECT_NAME PROCEDURE_NAME
------------------------------ ------------------------------ ------------------------------
SYS UTL_HTTP SET_AUTHENTICATION_FROM_WALLET
SYS UTL_HTTP SET_WALLET
SYS DBMS_ISCHED GET_AGENT_WALLET_LOCATION
SYS DBMS_GSM_FIX UPDATEWALLETFORBACKUP
SYS DBMS_NETWORK_ACL_ADMIN GET_WALLET_ACLID
SYS DBMS_NETWORK_ACL_ADMIN SET_WALLET_ACL
SYS DBMS_NETWORK_ACL_ADMIN REMOVE_WALLET_ACE
SYS DBMS_NETWORK_ACL_ADMIN APPEND_WALLET_ACL
SYS DBMS_NETWORK_ACL_ADMIN APPEND_WALLET_ACE
SYS DBMS_NETWORK_ACL_ADMIN UNASSIGN_WALLET_ACL
SYS DBMS_NETWORK_ACL_ADMIN ASSIGN_WALLET_ACL
OWNER OBJECT_NAME PROCEDURE_NAME
------------------------------ ------------------------------ ------------------------------
SYS KUPU$UTILITIES CHECK_ENCRYPTION_WALLET
SYS KUPU$UTILITIES_INT CHECK_ENCRYPTION_WALLET
XDB DBMS_XDB_ADMIN INSTALLDEFAULTWALLET
14 rows selected.
SQL>
Nothing stands out as a simple interface to create a wallet, store a key and retrieve a key as needed. We could get around this by orapki/openssl and creating a wallet and adding a key to a bucket. We could then use Java and the bouncy castle API to access the wallet and expose this to PL/SQL.
This is not ideal;
We really would like at least 3 layers of keys, one to open the wallet / vault, a master key we never see and then actual data/column keys encrypted/decrypted by the master key. Oracle with TDE does similar so we should aim for a similar goal. We also should have commands (APIs) to allow the password, master key or column keys to be changed.
We also must consider stealing of the keys or data at all stages of storage or in-flight
In other wards we want a vault that manages keys and the use of DBMS_CRYPTO.
I have done this and designed a key/crypto vault; we will go into the design in more details in the next post. It allows a password to be provided to open the vault; this is the first key and is not stored in the database or in the vault. This is similar to a wallet in that a password is used to open the wallet and this password is not stored in the wallet. We then generate and store master keys and column keys and can manage old keys through use of a manifest of sorts
Oracle provides a simple interface to encryption via DBMS_CRYPTO but leaves the hardest part to you.
Similar to audit trails in the Oracle database; all tools are provided but you need to decide what to audit and how to review that audit trail for attack
similar to securing the data; all tools are provided but you need to actually design and implement the data security.
I mention these two similar problems in other areas because we need to solve them also as part of the key vault. We need to protect and secure this critical data (keys) and also audit access.
#oracleace #sym_42 #oracle #data #encryption #security #datasecurity #keys #vault #audit #audittrails
