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 client-side data encryption up.
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 Getting Started video, we
looked at the procedure how to set up client-side encryption.
This covers creating a key administrator, creating a client key, creating a column encryption
key with that client key, encrypt a table column with the column encryption key and, finally,
enable users to access the encrypted data using their own client key.
Backing up, exporting, and rotating keys are also listed here but we will address this
in another video.
Now, to make this a realistic setup and not do everything with the SYSTEM user, I will
start with creating three users.
I am connected as USER ADMIN, an administrator with the USER ADMIN system privilege, so we
can create users.
I am using the SQL Console of the SAP HANA Database Explorer, connected to the tenant
database T01.
For our HR App, we will be creating a key administrator, a data administrator and a
HR manager.
Data admin will get the CREATE SCHEMA system privilege.
Data admin will be responsible for creating tables and, as the owner, granting privileges.
However, data admin will not be able to view encrypted content.
For this, he or she would need the CREATE CLIENTSIDE ENCRYPTION KEYPAIR system privilege
- to start with, you will need more, as we will see -
but this USER ADMIN will grant to key admin and not the data admin
WITH ADMIN OPTION, so key admin can grant
other users as well the privilege to create key pairs.
HR Manager will connect with a different client and will be using her own keys.
Key admin will also get the system privilege DROP CLIENTSIDE ENCRYPTION KEYPAIR
However, this time, without the WITH ADMIN OPTION as with the DROP privilege you can drop anyone’s
key and we like this privilege to stay with key admin.
Now, when we try to execute any GRANT CREATE CLIENTSIDE ENCRYPTION KEYPAIR statement,
HANA will first check if the client driver has been configured correctly.
SQL Consol e of Database Explorer is a Node.js client and clientside encryption is currently
only available on xDBC, so ODBC and JDBC.
That’s the message we are getting here.
So, lets switch to an ODBC client: hdbsql I have already created a key in the HDBUserStore.
Here we see the command.
HDBUserStore is nothing new, has been around since the beginning of SAP HANA, and this
allows us to store the ODBC connection string safely so we do not have to enter user name
and password each and every time.
If we do a little connection check, select database name, we can see we are connected
as the same USER_ADMIN to the same T01 tenant.
If we now run our CREATE and DROP CLIENTSIDE ENCRYPTION KEYPAIR statements, no client driver
error is returned and the system privilege has been granted.
OK - If we look our user up in the SAP HANA cockpit,
connected to the tenant, Manage Users > HRAPP_KEY_ADMIN >
Assign Privileges > System privileges
Here they are listed.
That’s all the system privileges key admin needs to do his job.
OK - Next, we are going to connect as the data admin
to create the schema HRAPP and, this is important, to grant our key administrator
the CLIENTSIDE ENCRYPTION COLUMN KEY ADMIN schema privilege.
This is an object privilege, like SELECT on schema, for example which you need to create
column encryption keys.
Again, this is a statement which contains the CLIENTSIDE ENCRYPTION clause, so we need
to use a HANA client that supports it.
I have also created a user store key for data admin.
First, we need to create a schema.
In most cases, this schema will already exist, of course.
Then, we execute, as data admin
GRANT CLIENTSIDE ENCRYPTION COLUMN KEY ADMIN
ON SCHEMA hrapp, here, TO our hrapp_key_admin.
OK - If we return to the cockpit and refresh the page
Object Privileges we can see that HRAPP_KEY_ADMIN
now also has the CLIENTSIDE ENCRYPTION COLUMN KEY ADMIN privilege on HRAPP,
granted by HRAPP_DATA_ADMIN
Good.
Now, key admin is going to create a key pair
and create a column encryption key encrypted with that key pair.
Again, I will be using a user store key to connect.
As mentioned, do not get confused, this has nothing to do with client-side encryption.
Just to save some time executing statements using hdbsql.
CREATE CLIENTSIDE ENCRYPTION KEYPAIR name of the key pair.
Now, if you have not set this up properly, you will get some errors at this stage.
I will address this in another video, clientside encryption configuration.
For now, let’s assume the HANA client is configured properly, so we can execute the command
CREATE CLIENTSIDE ENCRYPTION KEYPAIR key_admin_ckp
Note that we are passing the hdbsql parameter -Z CLIENTSIDE_ENCRYPTION_KEYSTORE_PASSWORD
I have used a shell environment variable here.
So, we do not have to enter the password in clear text on the command line.
To be clear, there is nothing encrypted, at this stage.
We have just created a key pair.
They are stored on the client and we can use the hdbkeystore command to list them.
hdbkeystore without arguments will return usage information
and the command is hdbkeystore -p password list
This will return the UUID, universal ID, name, and database.
We will be using the hdbkeystore command to export and import key pairs in another video.
In the Getting Started video, we mentioned that the public part of the key pair is stored
in the SAP HANA catalog.
For this, we can query the CLIENTSIDE_ENCRYPTION_KEYPAIRS view.
There are more columns, one containing the public key signature, but here we have
creator, create time, keypair name and ID.
OK - So, we have our key pair, now let’s create
a column key encrypted with this key.
Again, we need to specify the keystore password.
OK and here again, we can query the catalog to get information about this column key
creator, create time, column key name and the key pair ID used to encrypt the column key
Good.
So, now we have a client key pair for key admin and an encrypted encryption column key.
Now, it is the turn of data admin to create a table which includes a column that will be encrypted.
This time, we will be using a little script.
Creating the employees table, employee ID, name, salary
and with the social security number encrypted with the column encryption key
that key admin just created.
There are two different types supported, deterministic and random.
Random is the default.
The exact difference is documented in the SAP HANA Security Guide, we already looked
at that in the Getting Started video.
Then, data admin is going to grant HR Manager the privilege to SELECT, INSERT AND UPDATE
the employees table.
HR Manager, in fact, will be the only one who can actually see the data,
as I will show you in the next video.
OK - The catalog view, TABLE COLUMNS (view columns,
as well) now includes information about client-side encryption: status, encryption column key
identifier and the encryption mode: deterministic.
OK - We have done our homework.
Key admin has created a keypair, a column encryption key encrypted with that key pair
and data admin has created a schema, granted key admin the privileged to create column
encryption keys for that schema, and finally created a table containing a column encrypted
on the client with the column encryption key that key admin created.
Client-side Encryption is documented in the SAP HANA Administration Guide
and the SAP HANA Security Guide.
The CLIENTSIDE ENCRYPTION statements and related system views are documented in the
SAP HANA SQL and System Views Reference.
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.
Thank you for watching.