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 set up client-side data encryption, part two.
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.
In the previous video, we looked at the procedure
how to set up client-side encryption and we created our main characters:
Key Admin, Data Admin and HR Manager.
With key admin, we created a key pair and with that key pair created a column encryption key.
Column encryption keys (CEKs) are always attached to a particular schema and in our case,
Data Admin created this schema and we called it HRAPP.
Data Admin also created the employee table where we enabled encryption
for the social security number column using the CEK, the column encryption key created by Key Admin.
Client-side encryption in SAP HANA 2.0 SPS 03 supports both ODBC and JDBC clients and
in the first video we used the SAP HANA Interactive Terminal (hdbsql) as ODBC client on a Linux system
In this video, we will be using a Windows client and SAP HANA studio, which is, as most
of you probably will know, a Java application which connects to SAP HANA using JDBC.
OK - Let’s first do a quick check, so we are all on the same page.
As User Admin, let’s take a look at the privileges for HR Manager.
No system privileges. Object privileges for the HRAPP EMPLOYEES object,
which happens to be a table.
SELECT, UPDATE, and INSERT.
Data Admin granted HR Manager these privileges after the Employee table was created.
OK - Let’s connect with HR Manager > Open the Catalog
HRAPP >Tables >EMPLOYEES
Let’s generate a SELECT statement.
First, I will comment out the encrypted social security number.
Let’s run this.
No connection to database - HR Manager - OK
Run
Statement successfully executed.
There is no data in the table yet, so we get empty rows back but privilege-wise, this is looking good.
OK - Now, let’s include the encrypted column and see what happens.
Could not execute.
Column encryption key with ID cf9e5da4, etc., could not be retrieved from the server
and here we have it: No local client key pairs have access to this key.
HR Manager does not have a local client key pair with access to the column encryption key
and without access to the column encryption key, HR Manager cannot decrypt the column.
So, we are not getting X’s or NULLs or something, this is not data masking, we are getting an error.
No problem - let’s fix this.
In the first video, we granted the system privilege CREATE CLIENTSIDE ENCRYPTION KEYPAIR
to Key Admin WITH ADMIN OPTION.
This means that Key Admin is authorized to grant this system privilege to other users.
Let’s switch to the SQL Console for Key Admin - and connect - OK
and grant the create key pair privilege.
OK - Successfully executed.
Now, you may recall - let’s briefly switch to the ODBC Linux client we used for Key Admin
Here we used the -Z CLIENTSIDE_ENCRYPTION_PARAMETER=password
to authenticate ourselves for the HDBKeyStore.
So, how would this work for JDBC?
Well, the exact implementation will depend, of course, on the JDBC client used.
SAP HANA studio allows us to pass additional properties for the JDBC connection using this
tab, here, and the parameter, for JDBC, is called, cseKeyStorePassword=password.
CSE, client-side encryption.
OK - Let’s switch to the SQL Console for HR Manager and execute the CREATE KEYPAIR statement.
Statement successfully executed.
Very well.
If we query the CLIENTSIDE_ENCRYPTION_KEYPAIRS view, we can see that we now have two key pairs:
one from Key Admin and one from HR Manager.
The view has more columns, by the way. Let’s do a SELECT *
Now we also get the ENCODED_PUBLIC_KEYVALUE.
Encoded, meaning encrypted.
The public key is encrypted and the encryption algorithm used is RSA-OAEP-2048.
You can look it up on Wikipedia - but it is a pretty strong one.
Currently, the only one supported to encrypt the public key portion of the key pair in the database.
OK - Now, we also should be able to take a look in the HDBKeyStore of the SAP HANA client
on this Windows computer.
Let’s open a Command Prompt.
Change directory to the SAP HANA client, hdbclient Here we have our executables, hdbkeystore.exe
Without parameters, we get the usage information and to list the contents of this local key store,
enter hdbkeystore -p password list.
If you do not want to enter the password on the command line, use an environment variable,
as we did on our Linux client, same thing.
Unlike hdbuserstore, hdbkeystore does not have a -i option for interactive entry, so
we can avoid that anyone looking over our shoulder or anyone with access to the history file
can see the password.
OK - Now HR Manager has her key pair.
That’s one step.
But you may recall that Key Admin created a column encryption key with her own key pair.
So, how do we link the key pair of HR Manager with the column encryption key?
In other words, how do we authorize HR Manager to
decrypt the column encryption key with her own local keys?
Well, for this, we need to go back to Key Admin and use the ADD KEYCOPY clause of the
CLIENTSIDE ENCRYPTION COLUMN KEY command.
Key Admin’s local key pair is on the Linux client, so we have to switch to our PuTTY connection.
Of course, we co uld create a local key pair for Key Admin on the Windows client as well,
in which case Key Admin could use SAP HANA studio, the JDBC client, but let’s keep
it simple here, and assume, each user has her own SAP HANA client.
ALTER CLIENTSIDE ENCRYPTION COLUMN KEY ADD KEYCOPY ENCRYPTED WITH KEYPAIR hr_manager_ckp;
Very well, if we now check the CLIENTSIDE_ENCRYPTION_COLUMN_KEYS again,
We can see that we now have two column entries for the HRAPP_CEK1 column key but encrypted
with a different key pair ID.
Actually, let’s run this with a SELECT *. Schema name, column key name and column key
ID are the same but the column key is encrypted with a different keypair resulting in a different
encrypted column key value.
This time, the encryption algorithm is AES-256-CBC.
Again, you can look it up on Wikipedia, for the specifics.
That’s the algorithm used to encrypt the column encryption key.
So, we use AES-256-CBC to encrypt the CEK and RSA-OAEP-2048 to encrypt the public key
of the client keypair (CKP), which is stored in catalog of the SAP HANA database.
Good - If we now return to HR Manager and run the SELECT again,
the statement executes successfully.
OK - we are done with our setup.
In the next video, we are going to insert some data, well we - HR Manager, that is, of course.
That’s it.
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.
Thankb you for watching.