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 work with user groups.
This video has been recorded on release SAP HANA 2.0 SPS 03 and SAP HANA Cockpit SP 06,
both 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.
User groups allow you to manage related users together.
This was introduced in SAP HANA 2.0 SPS 02 and in the latest version, SPS 03,
the user group feature has been further enhanced by enabling user group-specific password policies.
It is important to note that user groups do not control data access in any way.
For this, the roles and privileges of that user apply.
So keep that in mind. It is just to manage related users together.
OK
I am connected here with the SAP HANA Database Explorer SQL Console to the tenant database T01
and I am connected with the user USER_ADMIN. This user only has the USER ADMIN system privilege
and with that you can create users and user groups but not much else.
Security best practice: principle of least privilege.
Here we have the syntax for the CREATE, ALTER, and DROP USERGROUP statements.
The parameter set works a bit like with the CREATE USER statement, where you can specify
the client or the email address, for example and for the user group, we can specify the
‘password policy’ parameter set. This is the only one implemented, so far.
The syntax parser for the Database Explorer has not been updated yet with the USERGROUP keyword,
hence the error indication here but the syntax is perfectly fine - we can ignore this.
Let’s create our user group TECHNICAL_USERS
Specifying DISABLE USER ADMIN and NO GRANT TO CREATOR
This is the most restrictive.
So only the group administrator will be able to make changes to this group.
OK
Now, let’s add two parameters related to password policies:
password_layout and minimal_password_lenght
You could do this directly in the create statement - and there is a catch.
DISABLE USER ADMIN, remember?
We created the group but we no longer have any privileges to make changes.
Fortunately, we can still drop the group, so let’s do this properly.
CREATE USERGROUP TECHNICAL_USERS
This time, first set the password policy parameters
we can still add NO GRANT TO CREATOR, in fact, we can only add the NO GRANT when we create the group
and now we can alter the group to disable access for user administrators.
Now, when we try to create a user and add it to the group, this will return an error.
Not authorized. USER ADMIN cannot add users to this group.
Only the group administrator can. OK
So, we first need to create a usergroup operator for this group.
Let’s call the user TECHNICAL_USERS_ADMIN and grant the user the USER ADMIN system privilege.
This is required to create and change users.
Next, we also need to grant the usergroup operator object privilege to TECHNICAL_USERS_ADMIN
The WITH GRANT OPTION is optional. Depends on what you want.
Let’s connect to a SQL Console with TECHNICAL_USERS_ADMIN
and let’s try to create the user again.
Well, let’s first try the password policy. Removing one character here
So now, we have a password with 11 chararcters. That should return an error.
And it does. Invalid password layout: minimal password length is 12.
OK - Add one character back in again, and try again.
“If at first you don’t succeed, Mr. Kidd” and we are good.
Let’s go back and take a look at some of the system views.
USERGROUPS Name, ID, Creator, IS_USER_ADMIN_ENABLED
USERGROUP_PARAMETERS - There is only one, as mentioned, to date: password policy.
We only specified, changed the default values,
for password layout and minimal length. Here we see all the options available.
Just standard SAP HANA password policy parameters, nothing special related to user groups.
The USERS view, includes a USERGROUP NAME column
This will give you an indication that a special password policy might be in place but
for the effective password policy for any particular user, you can always query the
effective password policy view, of course. OK
Let’s switch to the SAP HANA cockpit. Scroll down a bit. Manage users.
Let’s look up SAPADM, the user we just created. Note that there is an indication of the user
group TECHNICAL USERS. We can also see it mentioned here under General Information.
Name and User Group are greyed out, we cannot change this, as expected.
We can enter an email address but then when we try to save it, we get an error.
Show Details Not authorized.
OK - Let’s see if we can deactivate SAPADM as generic user admin,
this should return an error and it does.
Let’s see if we can delete SAPADM.
Make that Cascade - Nope
Error Not authorized.
However, and we already did mention this, usergroups do not control privileges.
So, if I, as a generic user admin, select Assign Privileges.
Click Edit - Click Add
Let’s pick a system privilege, Catalog Read, for example.
Save - User updated
This will not return any error.
This maybe a bit counterintuitive but that’s how usergroups work - OK
User Groups are documented in the SAP HANA Administration Guide and the SAP HANA Security Guide.
The CREATE USERGROUP Statement,
and the USERGROUPS and USERGROUP_PARAMETERS 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.