Hello and welcome to the SAP HANA Academy.
The topic of this video tutorial series is SAP HANA 2.0 SPS 03 What’s New?
And in this video I will discuss a new security feature: client-side data encryption.
Hi, I am Denys van Kempen.
There are many ways how data is encrypted for SAP HANA.
Some encryption occurs automatically.
Most of the data that’s in-flight, that is being transferred over the network, is
encrypted out-of-the-box, there is nothing we need to do.
Here, for example, when the nameserver process of the system database communicates with the
indexserver of a tenant database: that will be encrypted.
Or between different hosts of a distributed system, a multi-host environment.
Or communication between systems when system replication is enabled, Dynamic Tiering, and
so on.
In all those cases, network traffic will be encrypted.
In other cases, you might have to flip a switch yourself, for example, to enable encryption
between client and server when using SAP HANA cockpit or SAP HANA studio.
This also applies to encryption for data-at-rest.
We can encrypted the data area, the log area, and the data and log backups.
Not enabled by default, but very easy to implement —here we have the SAP HANA cockpit overview
page, where can enable data at rest.
All you need to do is flip a switch, literally.
It is that easy.
We can also encrypt security-relevant application data inside the database (credit cards, social
security numbers come to mind).
There is a special built-in, native SAP HANA encryption service that application developers
can leverage to make sure data is not exposed.
So not only, do we encrypt data in-flight, data-at-rest, but also data in-memory.
However, there is one thing that all these encryption types have in common: they are
server-side.
Which means that, at least in theory, if you have root access to the server, you can access
the Secure Store on the File System (the SSFS stores we can see here), storing the different
encryption root keys, and with those keys decrypt the data.
For on-premise and trusted systems, this will be very unlikely indeed as authorizations,
procedures and protocols will strictly control access to the different components, but what
about cloud?
What about database-as-a-service?
How can be 100% sure, in those scenarios, that no unauthorized access occurs?
Well, for this, as of SAP HANA 2.0 SPS 03, we now have client-side data encryption.
With client-side encryption, the data is encrypted and decrypted on the client, not on the server.
Server-side, the data will be encrypted in-flight, at-rest and in-memory and there is no way
to decrypt the data without the involvement of the SAP HANA client.
So, as long as your client is in a fully trusted environment, for example in your own data
center, it does not matter whether the server-side can be trusted 100% or 99.9, sensitive data
will always be encrypted.
There is a whole playlist about client-side data encryption, here on the SAP HANA Academy
where we guide through the setup, configuration and how to get started, following along with
the procedure, creating local keys pairs, creating column encryption keys, making backups,
exporting and importing keys, and so on.
And I have also posted the code snippets to a GitHub repository with some comments, so
you can dry run the procedure yourself on your own system.
It is not very difficult at all, as long as you know how it works and what you are doing.
For the rest of this video, I will just briefly show you how client-side data encryption works
from a more conceptual point of view.
OK —I am connected here with the SAP HANA Database Explorer and here we have a table
(employees) with client-side data encryption active on a column, salary.
Database Explorer does not show (yet) whether columns are encrypted, but with a simple query
on TABLE_COLUMNS we can see that this is, in fact, the case.
Data Admin has created the table and has ALTER TABLE privileges, so a simple select on a
regular column will return data.
SELECT NAME
However, a SELECT on an encrypted column returns an error.
In this case, it is feature not supported.
The SAP HANA database explorer is a Node.js client and currently client-side data encryption
is only supported for JDBC and ODBC clients.
So, lets switch to SAP HANA studio.
I am connect here with data admin
And now a SELECT on the encrypted columns, returns
A password is required to open the key store.
The JDBC or ODBC client will have to pass a parameter to the connection string containing
the key store password.
If we connect as HR Manager, for example, we can query the data
What’s the difference?
For HR Manager, we have set the cseKeyStorePassword property for JDBC clients.
Here we are using SAP HANA studio but this would be the same for any JDBC client.
Should we add the property to the connection string of data admin, he or she can also view
the data.
For example, lets use the ODBC client hdbsql this time
—for ODBC, the property or flag is -Z CLIENTSIDE_ENCRYPTION_KEYSTORE_PASSWORD= I am using an environment variable for the
password here, so I do not have to enter it on the prompt
And here we have our data.
On this SAP HANA client, and this SAP HANA client alone, anyone with the local key store
password and SELECT privilege on the table, of course —the usual authorization requirements
apply— can decrypted data.
It does not matter which client we use, ODBC, JDBC, they both use the same key store.
Also, as we saw, it also does not matter which user we use, if we have the privilege to access
the data and if we are on a client which stores a keypair that can decrypt the data and we
know the password to that key store, then we can view the data.
If we want to decrypt the data on another SAP HANA client, then we have to create a
local key pair on that client as well.
This could be another Windows client, this could be a Linux client, any supported SAP
HANA client.
So how does this work?
Lets draw our HRAPP Employees table: couple of column, couple of rows.
Data Admin created the HRAPP schema and created the table.
Then we also have HR Manager, who has the SELECT, INSERT and UPDATE privilege on the
table.
Now, we also need a Key Administrator.
Key Admin has the system privilege to create local key pairs.
You do this on the client and they are stored on this client in the key store.
So, Key Admin creates a local key pair, a public and private key.
The public key also stored on the SAP HANA server.
Next, Data Admin will have to grant Key Admin the privilege to create column encryption
keys on the HRAPP schema.
Key Admin cannot access the HRAPP schema, cannot view the data, but with this privilege
he or she can create a column encryption on the data.
It is a schema privilege.
Key Admin can create a single column encryption key for all HRAPP tables, or maybe create
a different column encryption key for each and every column that we want to encrypt in
that schema.
Key Admin creates the column encryption key (the CEK), locally with this client key pair
(CKP).
The CEK will be stored on the SAP HANA server, encrypted with the client key pair.
At this stage, all we have done is create key pairs and column encryption keys; no data
is encrypted yet.
Next, Data Admin is going to change to definition of the table, an ALTER TABLE, and encrypt
a column using the column encryption key that Key Admin created.
Say the Social Security Number.
Now, Key Admin grants HR Manager the privilege to create a local client key pair as well.
HR Manager creates the keys and Key Admin, creates a copy of the Column Encryption Key
encrypted with the key pair of HR Manager.
So, for the HRAPP schema, we now have a master column encryption key encrypted with the key
pair of Key Admin and a copy of the CEK encrypted with the key of HR Manager.
The CEK master will be on client A, the CEK copy on client B and from both clients, we
can access, we can decrypt, the HRAPP Employees SSN column.
Assuming we have the required privileges to access the table, SELECT, INSERT, UPDATE,
DELETE, etc.
And assuming we know the password to the local key store.
There is no local key store on the server, so we cannot decrypt data from there.
We need to be on a SAP HANA client and on that client there has to be a key store with
a key pair that has access, that can decrypt, the column encryption key used to encrypt
the column.
OK—so much for this overview.
In the next couple of videos, we will go through the procedure step-by-step.
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.