Hello and welcome to the SAP HANA Academy.
The topic of this video tutorial series is SAP HANA Administration and in this video
we will show you how to backup and export keys with client-side data encryption.
This video has been recorded on release SAP HANA 2.0 SPS 03, released in April 2018.
For earlier or later releases, please check the Administration playlist on the SAP HANA Academy.
Hi, I am Denys van Kempen.
OK - In the previous videos about Getting Started With Client-Side Encryption, we looked
at the procedure how to set up client-side encryption and mentioned here as part of the
procedure is the requirement to back up the local client key pair, export the column encryption key,
and storing both in a safe place.
Why is that?
Well, client-side encryption, as the name implies, is controlled on the client side.
To decrypt the column data, as we have seen, you need access to the local client key store.
If your local client key pair gets lost somehow, it is not a major disaster.
You can always create a new one —assuming you still have the privilege— and get the
key administrator to create a new copy of the column encryption key this time encrypted
with your new client key pair.
Until both steps have been executed — you creating your CKP and Key Admin creating your
CEK copy— you will not have access to the data from that SAP HANA client.
If there are other clients with key pairs that can access the column encryption key,
business will not be interrupted at all so it depends a bit on how things have been setup.
Still, you might need to update the client computer or replace it altogether, and it
is much easier, of course, just to export and import the client key pair, then to create
new ones as this probably involves —you would expect— some approval process.
Your data would not be very secure, if client key pairs are to be found on each and every client.
Same applies to the column encryption key, the CEK.
Should you want to move your table to a new database, we can just export the key, export
the data and drop the object on the old system, and the reimport the key and reimport the
data on the new system.
You can’t really drop the column encryption key as long as there are client key pairs
attached to it, so the risk of losing the column encryption key somehow is not all that big.
OK - enough said.
Let’s get to work.
First, we need to grant our Data Admin the export system privilege.
I am using the SAP HANA cockpit for this, you can use studio, you can use SQL, same thing.
I will also grant the import system privilege, we will need this in the next video.
To export, we can use any SQL interface.
No need to be on the client with a key store.
I am connect as Data Admin to the tenant.
EXPORT CLIENTSIDE ENCRYPTION COLUMN KEY (name of key)
AS CSV INTO (file name - this is on the HANA server)
WITH REPLACE;
WITH REPLACE is optional, just in case the file is already there as this would return an error.
For the rest, it is just a regular EXPORT command.
Most of you will be familiar with this.
Same for the next command EXPORT table and, for this example, I will also drop the table,
just to show how this would work when you move a table with encrypted column key from
one database to the next.
To view the files, we would have to switch to the SAP HANA server.
The EXPORT command creates a whole file hierarchy and here we have the SQL file to recreate the key.
CREATE CLIENTSIDE ENCRYPTION KEYPAIR for Key Admin
CREATE CLIENTSIDE ENCRYPTION COLUMN KEY with Key Admin’s KEYPAIR
CREATE CLIENTSIDE ENCRYPTION KEYPAIR for HR Manager, and
ALTER CLIENTSIDE ENCRYPTION COLUMN KEY ADD KEYCOPY with the KEYPAIR of HR Manager
It’s all there.
Next, it is Key Admin’s turn.
To export or backup local client key pairs, you need to use the hdbkeystore command.
For this, we need to switch to the client where Key Admin created the local client key pair (CKP),
in our case, this was a Linux server with a SAP HANA client installed.
Again, we have seen this before, run the command without parameters to get USAGE information.
To run the command you will need to provide the password with -p.
As you never know who is looking over your shoulder and to keep the history file clean,
I will be using an environment variable here:
CSE (client-side encryption) KS (KeyStore) PWD.
This is entirely optional.
If it is secure to enter passwords on the command line in your environment, go ahead.
If you prefer to call the variable with a different name, that’s up to you.
This is just an example.
OK - hdbkeystore LIST, shows the contents of the keystore: Universal ID, name and database.
hdbkeystore EXPORT, name of key, file name — exports the key
And then hdbkeystore REMOVE, name of key — will remove the key.
hdbkeystore LIST — Yep, Key Store is empty.
We can no longer use this client to decrypt.
Next, Key Admin will proceed and also drops the CEK, the column encryption key.
Note that the fact that Data Admin just dropped the EMPLOYEES table,
does not remove our Key Admin’s CEK.
It could have been used for any table in the HRAPP schema.
CEKs are attached to a schema, not to any particular table or table column, although
you could very well create dedicated CEKs for each column that you want to encrypt,
CEKs remain independent objects.
OK - If we now switch to the SQL Console of the SAP HANA Database Explorer, why not, and
check the CLIENTSIDE_ENCRYPTION_COLUMN_KEYS view
—here we have the result set before we started dropping things—
Run again.
No data.
Our column keys are gone.
OK - So much for client-side encryption backups and exports.
In the next video, we will be using the IMPORT command to recreate our objects.
Exporting client-side encryption keys is documented in the SAP HANA Security Guide for the concepts,
the SAP HANA Administration Guide for the activities, and the SQL Reference for the command syntax.
Thanks for watching.
You can find more video tutorials on our YouTube channel
and if you would like to be informed about new video tutorials,
please subscribe to our channel.
You can connect with us on LinkedIn or follow us on Twitter, as well for updates
and if you are watching this video on YouTube, do not hesitate to leave your comments to
the video page and, if you like, give us your vote on this video.
Thank you for watching.