[2.0 SPS 03] SAP HANA Administration: Client-Side Data Encryption, Export – SAP HANA Academy


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.

Leave a Reply

Your email address will not be published. Required fields are marked *