Hello and welcome to the SAP HANA Academy.
The topic of this video tutorial series is SAP HANA 2.0 SPS 02 - What’s New
and in this video we will discuss security.
Hi, I am Denys van Kempen.
We’ve mentioned it before, SPS 02 is a bit of a minor release,
so also for security we are more looking at improvements, enhancements, not so much new material.
What is completely new, though, is the concept of user groups.
It does not exist for any other database out there, to my knowledge,
so let’s take a look at what it does and what it does not do.
You might be tempted to think, as I was, that it would be like operating system groups:
to manage privileges.
But that’s not the case.
They are not meant for authorization purposes.
User groups don’t control data access in any way.
But they do allow you to implement separation of duties for user management.
You can, for example, create a user group for technical users, for management, or for,
say, temporary users, contracted parties.
Each group can have it’s own administrator to create and remove users, reset passwords,
and so on.
Optionally, you can make this administrator exclusive, that is, the only one to perform
these activities.
So, even a superuser like SYSTEM will not be able to interfere.
According to the road map, in a future release, we may also be able to apply, say, a different
password policy to a particular user group.
So, that's were user groups are heading.
Here we have some SQL that covers the topic.
As with pretty much each feature, we first get the SQL and then later we get the UI,
the buttons, in cockpit.
Here, I am creating a user group management and disable user admin, so this is for exclusive
management.
I also create a user management_admin, the operator of the group.
Same for tempstaff, this time not exclusive.
The COMMENT ON statement is also new, by the way, for SPS 02.
We will talk about it in a minute.
We can look at our work by querying the new USERGROUPS view.
For the management group, user admin is disabled.
Then, we can do a little test.
First, as management admin, I will create a user for my group: daisy.
Then, as tempstaff admin, try to change the password.
This will give us: insufficient privileges, not authorized.
That's good.
Now, let’s also create a user for the temp staff group: donald.
This time, we connect as the management admin and try to change the password of a tempstaffer.
This will also return an error, of course.
We have not disabled USER ADMIN for tempstaff but that does not mean any admin can come
in and play around.
It just means that the super user with USER ADMIN privileges is not allowed to interfere.
So, let’s connect as this super user.
Yes, no problem changing the password of someone from tempstaff.
However, no access to management.
You cannot overrule this in any way by trying to drop the group, for example.
What you need to do is connect as the management admin, in our example and first drop the user.
Once the group is empty, you can go ahead as super user and drop the group.
For the tempstaff group, user admin is not disabled, so it can perform all the necessary
activities.
That should give you an idea about the new user group feature.
Next, let’s take a look at the SAP HANA cockpit.
We have discussed backup encryption in some detail for the previous release.
If you missed this, you might want to watch the video on the topic.
You can find the link here on my What’s New Security blog.
I just mentioned that for the user group feature, SPS 02 brings us the SQL but not yet the UI.
Well, for backup encryption, SPS 01 brought us the SQL but now with SPS 02, or actually
with cockpit SP 03, we have the user interface for backup encryption.
It is simple.
You just switch it on or off.
You get a message informing you that backup encryption applies to backups.
For data snapshots, as storage snapshots are called in SAP HANA 2.0, you would need to
enable data volume encryption.
It’s on the same tile, just like redo log encryption.
So, you can instantly see the state of data-at-rest encryption.
Select the tile to open the Data Encryption Configuration page.
Here we find the same switches but also the time stamp for the root key change and the
last time encryption was changed period with the status of the services.
Also, very conveniently, we have a link to open the Manage Keys page.
This is completely new as well and provides all the information you need, in a single
view, about the status of the master key and root keys for the different areas.
You can set the root key backup password.
No need to open a command shell to the HANA server for this.
A warning will remember you to keep it safe and not to lose this key.
You can backup the root keys and you can change the root keys.
The advantage of using cockpit here is that it will guide you to respect SAP’s best
practice on key management.
So, first set the root key backup password, then change the key - this will run the ALTER
SYSTEM ENCRYPTION CREATE KEY WITHOUT ACTIVATE command for you in the background.
Make a backup of the keys and then finally activate the key.
That’s ALTER SYSTEM ENCRYPTION ACTIVATE NEW ROOT KEY.
Encryption key management is documented in the Security Guide - the concepts - and in
the Administration Guide - the tasks - so, make sure you are familiar with this before
you actually start to implement any data-at-rest encryption.
It is not difficult but it is not child play either.
You need to do it right, otherwise you might not be able to recover your database.
Conversations to be avoided.
Anything else?
Well, each HANA 2.0 release brings us more LDAP integration.
Typical enhancement topic.
You can now include a NESTED GROUP LOOKUP URL clause to the CREATE LDAP PROVIDER or
ALTER LDAP PROVIDER statements.
Here we have the SQL; no UI for this as of yet.
It will be poetry for those familiar with LDAP.
For the rest of us, just take note that LDAP support has been enhanced in SPS 02.
Another example is that you can now also select LDAP as restricted feature for a tenant database.
If we open the Manage Database page.
We have a link to configure Manage Restricted Features.
This now includes LDAP.
So you can disable LDAP for your tenant, should this be appropriate.
Another enhancement is LDAP auditing.
We do this here, in the System DB Security section.
Create Audit Policy.
Add Actions, filter on LDAP.
So, create, alter, validate, drop - all can be audited.
Or, under audited objects, select any of the LDAP-related objects.
This is all new.
When creating a new user, here under Manage Users, + , Create User, you can now select
LDAP as authorization mode.
Before, only SQL was available to enable this.
There is one thing you need to be aware of.
An LDAP user gets his granted roles from the LDAP group it belongs to.
You cannot grant your LDAP user any other roles or privileges.
So you lose a bit of flexibility here, you might argue.
However, from a security point of view, it is much more coherent to have privileges defined
on a single location and not left, right, and centre.
So, in my view, it is a good thing.
Finally, I should mention, should there be any issues with LDAP authorisation, as of
SPS 02, for troubleshooting and support purposes, a database trace component has been added
for LDAP, which will allow you or support to obtain diagnostic information not only
from HANA, but also from the OpenLDAP library used.
To enable this, open Configure System Properties under DB Administration.
Open the global.ini configuration file.
Search for trace.
You may need to select More here at the bottom.
If ldap is not listed, you need to add the parameter.
Here at the plus sign. Add parameter to trace.
And then add the key value pair, ldap with the value debug for example.
There is no UI for this yet.
Used to be a little friendlier in HANA studio, here on the Trace Configuration tab.
Just select the pencil, Edit Configuration.
Filter on LDAP.
If it is not enabled, you need to select Show All Components.
Default trace level is showed and you can select the trace level you want.
Now if I pause the video for a moment, we can see in a split second the SQL being executed;
so this is also always an option of course.
In the end, it is all SQL.
Anything else?
Well, yes but not for LDAP.
For tenant database administration, we now have separate system privileges to start and
stop a tenant database.
So, if we create or edit a role and go to the system privileges tab and select edit
and then add.
Search on database.
Before, we just had database admin, which allows us to do pretty much anything with
a tenant database.
Now, we have added two database operation privileges: just for start and stop.
You could grant this to some technical user for automation purposes, for example, and
of course, in such case, you always want to apply the principle of least privilege.
Just to be able to do what needs to be done and nothing more.
To keep us all out of trouble.
Finally, it is mentioned in the What’s New guide under the Security header but it could
just as well have been filed under Development or Database Administration.
You can now add comments to database objects and this not only including tables, columns
and views but also - and I guess this makes it a security feature as well - to users,
roles, and, of course, the new user groups.
The SQL for this is COMMENT ON object_name IS and then between quotes the comment.
As you can see in this example, a hyphen (the minus sign) is now supported in user names.
It used to be part of this list Unpermitted Characters in User Names, but as of SPS 02,
it no longer is.
For the hdbuserstore utility, which allows you to create secure keys on the client side,
so you can, for example, enable a script to connect to the database without a password
stored inside the script - that would not be very secure obviously.
Well, here, underscores, hyphens and periods are now supported for the key name.
I am using underscore back hyphen up period oper here, that’s probably a bit over the
top, but just to show that you have a bit more flexibility here on the naming side.
We have a couple of videos about hdbuserstore and hdbsql and how you can use them together
here on the Academy, for those less familiar with these client-side tools.
That’s it for SAP HANA 2.0 SPS 02 security.
There is a full playlist on the topic and we will be adding new videos for HANA 2.0
later this year.
User groups, data-at-rest encryption configuration in HANA cockpit, LDAP support enhancements,
tenant database administration support enhancements - that pretty much covers it.
For more information with links to the documentation and other resources, please check out my blog
on this topic.
You can find more video tutorials on our YouTube channel.
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 we also have a page on Facebook and Google+.
If you are watching this video o n YouTube, do not hesitate to leave your comments to
the video page and, if you like, give us your vote on his video.
Thank you for watching.