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 some of the SQL DDL statements 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 a previous video in this tutorial series, we already looked at DML, the data
manipulation language for SQL: INSERT, UPDATE, DELETE
In this video, we will cover some of the DDL, the data definition language for SQL and that’s
CREATE, ALTER, and DROP.
Let’s dive straight in.
We have already covered two DDL examples.
One was when Data Admin created the employees table —CREATE TABLE— and we immediately
switched column encryption on for the SSN column
CLIENTSIDE ENCRYPTION ON WITH
The other was when we looked into rotating column encryption keys and did an
ALTER TABLE, ALTER column, ALTER CLIENTSIDE ENCRYPTION WITH ( that’s three ALTERs)
and note the difference between enabling client-side encryption: ON WITH and rotating, just WITH.
To disable client-side encryption for that particular column, it is CLIENTSIDE ENCRYPTION OFF.
Maybe, you want to do a bulk load without using prepared statements, for example.
Then, to switch encryption on again for that column, it is ON WITH plus the CEK that you
want to use to encrypt the column.
Part of the ALTER CLIENTSIDE ENCRYPTION ON WITH clause is the optional RANDOM / DETERMINISTIC part.
What’s the difference?
Random is the most secure, it is also the default.
Each time, you encrypt the data, albeit with the same column encryption key,
the encrypted values will be different.
That’s great, of course.
Very secure.
However, it also does limit a bit what we can do with the data.
With deterministic encryption, the encrypted value will be the same,
each time we encrypt the same data with the same key and this allows for some operations,
like equality comparisons on encrypted data.
Is it the same security number of not?
However, particularly for columns storing low-cardinality data, think status flags or
boolean values —yes/no, 0/1– or classifications like gender, it won’t be all that difficult
to figure out what the encrypted value represents if deterministic encryption is being used.
Again, default is random, so unless you need the encrypted value to be deterministic because
you need to compare encrypted values, best to leave it at that.
OK.
Finally, also an example of adding an encrypted column.
It is the same thing, really.
Just change the name of the column, then it is ENCRYPTION ON WITH and then the name of
the column encryption key.
Could be the same, could be different, that’s between you and your security requirements and
if we then run our encryption status query on the table, we can see that for both the
SSN and for the SALARY column, encryption has now been enabled.
We used the same CEK, the same column encryption key, and for this CEK we already created a
key copy encrypted with the key pair of HR Manager (in a previous video), so HR Manager
will also instantly have access to this column.
If that is not what you want, you should use another CEK and not create a copy for HR Manager.
That’s how this works.
Well, short topic.
That’s pretty much it —DDL with client-side encryption.
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.