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 talk about client-side data encryption and how to import client key pairs and column encryption keys.
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 we looked at why you need to
make a backup of your local client key pairs and the column encryption keys, storing both in a safe place.
The column encryption key, the CEK, we exported using the regular SAP HANA export command,
EXPORT KEY AS CSV and for the purpose of this exercise, we also exported the table with
data and even dropped the table, so we can simulate moving a table with encrypted column
from one database to the next, maybe from development to production, for example.
We also briefly looked at the SQL file that the EXPORT command generated with the commands
to recreate the column encryption key, the copy, and the local client key pairs used
to encrypt them.
This is all server-side.
Then, we switch to the SAP HANA client, and also, although these operations are not necessarily
related, I mean, you could export you column encryption key and leave it at that.
It is not required that you instantly export your client key pair as well and store them together.
In fact, we have seen that it is not all that difficult to re-create a client key pair and
make a column encryption key copy with it, a topic we will discuss in some detail when
we dive into rotating column encryption keys.
OK - exporting local client key pairs is done on the client.
For this, we are using the hdbkeystore EXPORT command, and in our example here, we also
deleted, removed the local key pair leaving the key store empty.
At this point, we could discard this SAP HANA client.
Maybe replace some hardware, switch to a new system.
On the condition, that we have stored the export file some place safe.
Here it is in /tmp - which, come to think of it, is probably about the worst place to keep your backups.
So let’s quickly proceed to import the client key pair, before some batch job cleans up /tmp.
The command to export is EXPORT, the command to import is IMPORT,
the command to list the contents is LIST.
It is all pretty straight forward.
Any interaction with the key store requires the key store password, and, as a reminder,
this is a local client key store.
The password is the same for all users on this client, it is not per-user, it is per-client.
We named our key key-admin because Key Administrator created the key but if Data Admin has the
local client key store password, Data Admin could also use this key pair to decrypt the
column encryption key and with that, decrypt the data for that column.
It is important to understand this, as with SSH, secure shell, or PGP, for securing your
e-mail, the private/public key pair is personal, for your eyes only.
That’s not how client-side encryption works.
It is a local client key, not a personal key.
OK - So, let’s say we have replaced our client system, reinstalled the SAP HANA client,
copied over the export file, run the import and now we have our client key pair back in place.
Next, we are going to import the table and the column encryption key.
For this, the user will need the IMPORT system privilege.
You may recall that we granted this to Data Admin, so let’s switch to the SQL Console
of the SAP HANA Database Explorer.
Here, we see the output from a query on the CLIENTSIDE_ENCRYPTION_COLUMN_KEYS view:
No Data.
We saw this in the previous video, when, after the export, Key Admin, dropped the column encryption key.
What we are going to do now is import the keys back again.
Keys, that’s plural, because we had one column encryption key encrypted with the key
pair of Key Admin and a copy of the that column encryption key encrypted with the key pair
of HR Manager.
Again, this is regular SAP HANA EXPORT/IMPORT.
IMPORT object object_name FROM location WITH REPLACE, being optional
First the key, then the table.
The other way around would not work, as the SSN column is encrypted with a column key,
so that object needs to be present in the database first.
So, for EXPORT/IMPORT we do not have to work from the client.
This is a server-side command.
But the data is fully encrypted, in-flight, in-memory and at-rest, so that’s perfectly fine.
Only on the client can we decrypt data.
For this, let’s also grant the business user, HR Manager, his privileges back again,
before I forget.
OK - and now, if we review our column encryption keys, we can see that they are back in place.
Job done.
OK - So much for importing client-side encryption client key pairs and column encryption keys.
In the next video, we will look at how we can rotate column encryption keys.
Importing 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.