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 use DML with 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 - DML, data manipulation language, that’s INSERT, UPDATE, DELETE when using SQL.
How does that work?
In the previous video about Getting Started With Client-Side Encryption,
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.
Data Admin created the data structures, Key Admin managed the access to the column
encryption keys and we created local key pairs for the Windows
client for HR Manager and for Key Admin on a Linux client.
The table, so far, has been empty and in this video we will be inserting some data.
Now, as already discussed,
there are some limitations about what you can do with client-side encryption
you can find them documented here in the Security Guide
but this also applies to DML
For starters, the application must use prepared statements
but also on the DML-side,
you should check the documentation about what you can do and what is not supported.
What we can do, and I will now switch back again to the SAP
HANA studio on our Windows client
I will first set the schema You don’t need to do this, of course, if
you fully qualify the object name in the statement so, schema_name . table_name,
Then, for the INSERT, we will be using questions
marks and then from the menu, I will choose, Prepare Statement
Now, for those not familiar, prepare statement is not exclusive to client-side encryption at all.
Prepare statement, you would typically use with bind variables
It is a bit off topic to go into any detail, but you can look it up on Wikipedia.
This is generic SQL, works with any database.
Here we have our question marks for the placeholders or bind variables
and this is also documented, of course, for the specifics concerning SAP HANA
in the Client Interface Programming Reference Prepared Statement Caching
for how you would this using JDBC and ODBC Again, generic stuff, nothing specific for
client-side encryption just know that you have to use prepared statements.
OK - SAP HANA studio has Prepare Statement as a context menu item (Ctrl+Shift+P)
and what this will do, is open a new tab where we can enter our parameters.
Fast-forward a bit and execute
Statement executed successfully.
If we then do a SELECT *
We get to see our data.
What about Key Admin and Data Admin?
Well, Key Admin, you may recall, only had the
CLIENTSIDE ENCRYPTION COLUMN KEY ADMIN privilege
on the HRAPP schema but not SELECT
So, if we do SELECT * as Key Admin
Let’s fully qualify the object this time
We get insufficient privilege: Not authorized.
This should not come as a surprise but just to show, that the fact that Key Admin
has the column key admin privilege on the schema
does not mean that Key Admin can see the data.
What about Data Admin?
Well, Dat a Admin created the schema, is the object owner, so certainly would have access.
So, when we query the name column from the employees table, which is not encrypted,
the value is returned to Data Admin but when we query the social security number
from the employees table, which is encrypted HANA returns ‘Could not execute statement’
Invalid key store password provided
If we then copy the key store password from the HR Manager connection properties
we saw this in the previous video
it is a JDBC property valid for any JDBC connection to SAP HANA
This is just the way how you can define JDBC connection properties in SAP HANA studio
Other JDBC clients will do this differently,
OK - and then paste it in for the connection of Data Admin
Open a new SQL Console
Run the query again Statement executed successfully.
This may come as a surprise
but the client-side encryption key store is not user specific but specific to this particular
SAP HANA client and because Data Admin can provide the password to the keystore,
she can access the HR Manager key pair and
send the value to the HANA server to decrypt the column encryption key and with
that, decrypt the column.
The same as we can list, or export, the contents of the keystore, provided we have the keystore password.
OK - Fair enough, let’s do an update.
Works the same way Prepare statement
Paste in the parameters --make sure you put them in the right place--
and execute. You can also use this in a SELECT.
paste in the variable
and here we have our data.
OK - If you are already familiar with prepared statements when using a JDBC or ODBC client,
using them for client-side encryption will be nothing new.
If not, you might need to read up a bit about the topic, again,
it is generic SQL, generic ODBC prepare statement.
OK - So much for working with DML and client-side encryption.
It is documented, I already showed you, in the SAP HANA Security Guide
and the SAP HANA Administration Guide.
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.