Hello and welcome to the SAP HANA Academy.
The topic of this video tutorial series is SAP HANA Administration and in this video
I will show you how to rotate column encryption keys for 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 how to backup and import keys.
We have now arrived at the last step: Rotate column encryption keys regularly and re-encrypt
your data using the most current one.
Key copies must be created for users who need access to data.
So how, does this work?
Well, it is pretty straight forward.
First, let’s take a look at the CEKs, the column encryption keys, we have got.
I have cobbled together a bit of SQL where we query the column keys and the key pairs
view together with table columns.
—if you are watching this video on YouTube, I will add the code to the description of this video, below—
and here we can see that we have a master key, the original column encryption key created
by Key Admin, HRAPP_CEK1, encrypted with the client key pair of the Key Administrator,
KEY_ADMIN_CKP (on the Linux client), create time, for the hrapp.employees.ssn column currently encrypted.
Key Admin also created a copy of the CEK, this time encrypted with the client key pair
of HR Manager on a Windows client.
First, as Key Admin, we need to create a new column encryption key.
Let’s call it hrapp_cek2 and encrypt it with the same client key pair of key admin on this Linux client.
Of course, at this stage, you could also decide to create a new client key pair, locally,
but this is not required by any means.
We have seen this before, if the SQL contains the clause CLIENTSIDE ENCRYPTION we will need
to execute it on a client that supports it, any ODBC or JDBC client (hdbsql, SAP HANA studio),
but not the SQL Prompt of the SAP HANA Database Explorer.
That’s a Node.js client, a web client, which is currently not supported.
OK - Let’s run this and then also create a key copy for CEK this time encrypted with
the client keys of HR Manager.
If we run our encryption key uber-query again, what do we see?
CEK #2 is not listed.
Why is that?
Well, this query joins TABLE_COLUMNS, so only looks at the active keys.
Key Admin just created the new column encryption key but they are not active yet.
If we just query the keys, we see all four listed.
I have added the create time for both the CEK and the CKP.
Note that when joining just the client-side encryption system views, we can only show
the object where the keys are attached to, the schema.
If you want to know exactly on what column the column key encryption is being used, you
would need to query TABLE_COLUMS as well — and, as we just saw, only CEK #1 is currently active
OK - Let’s make CEK #2 active.
For this, I will use Data Admin
The SQL contains the CLIENTSIDE ENCRYPTION clause, so we will need to execute this on
a client that supports it.
Note that, because we are on the same client, we also use the same password.
The key store is not a personal key store for Key Admin, even though the key pair has its name.
Data Admin can use the key store just as well.
However, Key Admin does not have an ALTER object privilege on hrapp.employees, so we
will need to do this with Data Admin.
OK - Very well, let’s query the active CEK again.
Exactly, this is now CEK #2.
Now, we could leave it as-is but we could also drop the old CEK.
For this, we should switch to Key Admin again.
Drop CEK #1.
OK
And if we query all our column encryption keys, we can see that only #2 is left.
We have rotated the column keys.
To resume.
1. create a new column encryption key as Key Administrator
2. Make a copy of that key for the other key pair clients.
3. Update the table to use the new CEK, and
4. Optionally, drop the old CEK.
That’s it —So much for rotating column encryption keys.
In the next video, we will address some other aspects of column encryption key management.
Rotating 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.