User deletion causes all granted authorizations by this user to be deleted.

[Former Member]

Hello,

from the security guide,

http://help.sap.com/hana/SAP_HANA_Security_Guide_en.pdf

'The database owner concept stipulates that when a database user is deleted, all objects created by that user

and privileges granted to others by that user are also deleted. If the owner of a schema is deleted, all objects in

the schema are also deleted even if they are owned by a different user. All privileges on these objects are also

deleted.'

we learned, whenever a user is deleted all grants performed by this user are also deleted.

We tested and this is, and it is even the case when you delete a user using restrict.

From an audit perspective we cannot use one generic user that performs central security management.

We are an enterprise company and got a central SAP security department,

which is supposed to cover HANA security handing as far as possible and it must be traceable who performed which grant.

They will create roles, assign privileges and other roles to these and assign these roles to our HANA users and

right now they use their personal HANA DB user for this purpose, which perfectly logs who was the granter.

From testing and reading the security guide we learned that when one of the group members leaves the security department,

and the related HANA account is deleted, all the work performed with his/her user ID is being lost.

So we see two options right now, which are both not perfect.

a.) never delete a user of this team on HANA, just deactivate the account.

b.) create a generic account and have the entire team work with this account to perform all grants.

Right now we will prefer option a.)

Does anyone see a different option...and/or how do you handle these 'stipulates'?

Regards

Florian

[richard_bremer · Accepted solution]

Hi Florian,

first, I assume that the actual "problem" at hand is the management of users and privileges, i.e. the granting of privileges to database users in SAP HANA.

For this scenario, there are two technically different methodologies, of which one has the drawbacks you describe, the other one does not.

• As a database user with the correct privileges, you can grant a so-called catalog role (i.e. a role that you create either via a "create role" statement; or with the role editor in SAP HANA studio), or privileges to other database users, by means of the SQL "grant" statement (or in the user editor of SAP HANA studio).
  I am simply going to call this "the database way" of managing privileges. This way has the following implications (guaranteed to not be complete):
  • If you want to grant a role, you need system privege ROLE ADMIN, which also allows you to create new roles or drop roles.
  • Only the grantor of a role can revoke this granting. That is: two users A and B both have the ROLE ADMIN privilege. A grants role_x to user C. Now A is on vacation and B tries to revoke the role. If he does this in SQL, he will even get a success message, because he would be allowed to revoke the role, and at the end of statement execution, user C does not have role_x granted by B. But B cannot undo the granting through user A. Hence despite the success message, C still has role_x (granted by A). Even funnier (but logically a direct consequence), if both A and B grant the role to C, both of them have to revoke it.
  • If you want to grant a privilege (to a user or to a role), you must already have the privilege (be a grantee or own the privilege) including grant/admin option, i.e. including the permission to grant it to others. Especially for database users who build catalog roles, this means that they need to have all privileges that they have to place into roles.
  • For revoking privileges, the same rules apply as above: only the grantee can revoke.
  • If a user A grants privilege P1 to user C, the database has the following view of the situation: User C only has P1 because it has been given by A. Therefore, if user A does not exist anymore, the reason for C having P1 does not exist, and thence P1 will implicitly be revoked from C. The same is true if somebody revokes P1 from A. This revoking will implicitly also revoke P1 from C.
  • This is technical database behavior. It's implemented in a logical manner, but it is not appropriate for managing named database users if typical security requirements such as segregation of duties or similar apply.
  • The above behavior is coupled to the ownership concept of the database: whoever creates an object in the database catalog is the object owner. Typicall, objects have privileges (object privileges for database schemas and anything that can be placed in a schema, e.g. SELECT, INSERT, EXECUTE, DROP, ...), and initially, only the object owner has these privileges (including grant option).
• The second way, which I am going to call "the repository way" solves most of the above issues. It is based on a simple principle established in the "development framework" of SAP HANA:
  • You can create objects, including certain privilege types and including roles, in the repository of SAP HANA. In this way, you first build a design-time object (the source code), which is placed into the repository. A real database object (runtime object) is only created when you activate the repository object. Regardless of which named user triggers this activation (clicks the button), the technical activation process, i.e. the creation of the database run time object, is executed by a technical database user named _SYS_REPO. Therefore, _SYS_REPO is the owner of the runtime object and also (initially) the only user holding privileges on the object.
  • In this discussion, the most important object to create in the repository is a "repository role". With repository roles, you define the role contents, check them into the repository and then activate them. During activation, a more or less regular database role is created. One important difference to "catalog roles" is that the activated repository role cannot be granted using the "grant" statement, and cannot be dropped using the "drop role" statement. That is, even if a user has the ROLE ADMIN system privilege, he cannot grant activated repository roles.
  • Instead, the database provides stored procedures "grant_activated_role" and "revoke_activated_role". A user with the execute privilege on the stored procedures can grant and revoke any activated role to and from any database user. The stored procedures are owned by _SYS_REPO and are built in the "definer" sql security mode, i.e. regardless of who triggers procedure execution, the procedure will internally be executed by the _SYS_REPO user. Thus technically, all these grant and revoke actions are performed by the same database user (_SYS_REPO).
  • Because of this implementation, the drawbacks of the "catalog way" are avoided: if A grants something to C, user B can revoke this. If user A is deleted, all objects created by A in the repository and all privileges granted by A "in the repository way" are retained and remain active in the database.
  • If you want to know how to technically create repository roles, this is described in the SAP HANA Developer Guide.

If you do not want to work with the repository, you either have to use an identity management solution that supports SAP HANA (NetWeaver IDM and GRC Access control 10.1), or you have to basically create some IDM aspects in a small application, so that you can in the end have a technical database user who does the actual privilege management, and who is invoked through this application. For the reasons of auditability, you will then have to rely on the auditing capabilities of the "application", because technically in the database, all grants/revokes will be performed by the same technical database user.

Best regards,

Richard

[Former Member · Accepted solution]

Hi Richard,

the second way sounds like a working approach for our HANA sidecar solution.

Since we also got HANA systems running for NW. 7.40 SP6 based SAP suite systems, we found out that the configuration of DBMS User Management for SAP HANA, could be a key to archive a centralized and auditable HANA user management. We have plans to configure this on all of our HANA based SAP suite systems and run varies tests on how this will allow us move on from this point.

Thanks both of you Lars and Roland, for your input. I will have some internal discussions, tests and trials and keep this post updated.

Florian

[Former Member · Accepted solution]

This message was moderated.

[lbreddemann]

Hi Florian,

deactivating users is probably the best solution generally.

In anyway you might want to check out the security role setup guide () by my colleagues .

Creating security management and design users is key in this guide.

- Lars