Assigning privileges individually is not either SAP or industry recommended approach due to complex maintenance of privileges. The alternative and best approach to manage the privilege assignment is through “The Privilege based roles” and assign these to the users.

This is quite common, if all the relevant privileges are not assigned to the user & when the _SYS_REPO user doesn’t have authorization to grant them to the user. Please note, _SYS_REPO is the global granting guy. Any object that you wish to grant to the users should be assigned to _SYS_REPO first with grantability option.

Let me explain in-detailed. The owner of the object (role, or a privilege) can only assign it to the other users. Incase if you are granted with the authorization to assign to object to other users, you will be able to assign it without any errors. This mostly happens with the objects that are owned by SYSTEM, and SYS. Hence, it is always recommended to create the objects in design-time and activating them would assign the object to user SYS_REPO.

Option # 1 – After implementation, create a master admin ID and assign all the SAP delivered roles, and privileges with “Grantable to others” option to yes. (Not recommend)

Option # 2 – Create a role in design-time with the relevant SAP delivered roles, and privileges and activate it. A role that you create in the HANA system can have the SAP delivered roles and privileges. This can be assigned to the user by any of the Security admin as the activated objects doesn’t look for Grantability option.

After activating the design-time role definition, you can grant the resulting runtime role object to application developers, for example, by executing the _SYS_REPO procedure GRANT_ACTIVATED_ROLE. The call requires the parameters: ROLENAME (the name of the runtime role object you want to assign) and USERNAME (the name of the user to whom you want to assign the new runtime role).

call “_SYS_REPO”.“GRANT_ACTIVATED_ROLE”(‘Role name’,’User Name’);

Before creating a role, please note that the role can be either a Repository Role or a Catalog Role.

Below are the steps to create a role in Catalog (Run-time) mode:

To create a role:

• Right-click on Security, Role and select New role.
• Then assign the relevant privileges/roles and publish the roles.

Below table gives you a quick understanding on which privileges are required for every target user:

A role in the Repository (Design-time) mode can be created from the Project Explorer.

When you create a role in Run-time, the owner of the role will be the creator only. Incase if other users should get authorization to grant this role to any other users, then the owner should provide that authorization.

When a role is created as a repository role, the repository version belongs to the owner, and the activated version (run-time) belongs to SYS_REPO.

GRANT_ACTIVATED_ROLE privilege in HANA gives authorization to users to grant Repository roles that are owned by _SYS_REPO.  Below are the differences between a Catalog role and a Repository role:

To create roles in design time, you need to have a project created under the Project Explorer. Note that the Administration perspective will allow you to create only run-time roles. To create the design-time roles, you need to be in the HANA Development perspective.

Use the below SQL statement:

SELECT * FROM “PUBLIC”.”EFFECTIVE_PRIVILEGES” where USER_NAME = ‘RAGHUB’;

Use the below SQL statement:

SELECT * FROM “PUBLIC”.”EFFECTIVE_PRIVILEGES” where USER_NAME = ‘RAGHUB’ AND OBJECT_TYPE = ‘ANALYTICALPRIVILEGE’;

You need a database user with either DATA ADMIN or CATALOG READ system privilege to query about other users. All users can query their own data, no special privileges required for the same.

Instead of using the authorization management tab, grant the SQL privileges via SQL commands.

Examples (replace <user_name> by the name of the user or role that should receive the privilege):

grant SELECT on _SYS_BI.BIMC_CUBES to <user_name>;
grant SELECT on _SYS_BI.BIMC_DIMENSIONS to <user_name>;
grant SELECT on _SYS_BI.BIMC_PROPERTIES to <user_name>;
grant SELECT on _SYS_BI.BIMC_VARIABLE to <user_name>;
grant EXECUTE on SYS.REPOSITORY_REST to <user_name>;
grant EXECUTE on _SYS_REPO.GRANT_PRIVILEGE_ON_ACTIVATED_CONTENT to <user_name>;
grant EXECUTE on _SYS_REPO.GRANT_ACTIVATED_ANALYTICAL_PRIVILEGE to <user_name>;
grant SELECT on _SYS_REPO.OBJECT_HISTORY to <user_name>;
grant SELECT on _SYS_REPO.ACTIVE_OBJECT to <user_name>;
grant SELECT on _SYS_REPO.ACTIVE_OBJECTCROSSREF to <user_name>;

Regular user can use HANA Studio & Web IDE to login to the HANA DB. Wherein, a restricted user can login only thru the Reporting tool/component.

By using a SAP HANA database table for the audit trail, it is possible to query and analyze auditing information quickly. Additionally, it provides a secure and tamper-proof storage facility.

However, the audit log growth will be huge and is dependent on the audit profiles are enabled. As a result, it is recommended to back up the audit logs periodically and truncate existing tables.

To back up the audit log to a temporary location, use the below SQL statement:

EXPORT “PUBLIC”.”AUDIT_LOG” INTO ‘/tmp/’

(or)

‘EXPORT “_SYS_AUDIT”.”CS_AUDIT_LOG_” INTO ‘/tmp/’

In case if you are experiencing an error that says the logs can’t be exported, perform the following activities:

CREATE COLUMN TABLE AUDIT_LOG_FOR_EXPORT AS (SELECT * FROM “PUBLIC”.”AUDIT_LOG”);

NOTE: Use should have AUDIT OPERATOR or AUDIT ADMIN system privilege perform this activity.

Audit logs in Tenant Databases can be enabled the same way, i.e., from Security  Audit tab. However, tenant database administrators cannot configure audit trail targets such as Syslog, or CSV since the underlying system properties are in the default configuration change blacklist (multidb.ini). The default target for all audit trails in tenant databases is internal database table.

Here is how you can truncate AUDIT_LOG table:

If the audit trail target is a database table, you can avoid the audit table growing indefinitely by deleting audit entries created up until a certain time and date. You can do this using the Security editor of the SAP HANA studio.

NOTE: It is recommended to archive the audit entries before proceeding with these steps. Also, you must have the system privilege AUDIT OPERATOR to perform this activity.

use the SQL command ALTER SYSTEM CLEAR AUDIT LOG ALL to completely clear the table contents.

Incase if you wish to remove the audit logs for a specific period, use the below:

ALTER SYSTEM CLEAR AUDIT LOG UNTIL ‘2022-01-15 23:59:59’;

If you wish to delete the audit logs from a specific audit policy, you may use the below syntax:

ALTER SYSTEM CLEAR AUDIT LOG FOR AUDIT POLICY “Policy Name” UNTIL ‘2021-06-15 23:59:59’;

Also, refer to the stored procedure created by Lars to simplify this activity and automate the process at:

https://answers.sap.com/questions/239823/cleanup-auditlog-table-data-older-than-30days.html

Below are the common authorizations that are required by every user:

• SYS_BIC (Object Privilege) with SELECT
• SYS_BI (Object Privilege) with SELECT
• PUBLIC (Role) ** Only required, if the user has to login to HANA through HANA Studio, or Web IDE.
• REPOSITORY_REST (Object Privilege)

** SYS_BI_CP_ALL (only if the user is an admin or a developer) – This privilege gives authorization to all the classical Analytic Privileges.

Because, public will give authorization to view all the catalog objects. Since a restricted user shouldn’t view them thru any means, we don’t assign this authorization.

Analytic Privileges will limit the authorization of the users on a specific Information View (IV). The same IV can be assigned to multiple users with different authorizations. For eg: User A when executes the IV, he/she will only see the data related to one region, and User B is restricted to a different region.

In simple, Analytic Privileges are used to restrict the user authorization on specific sub-set of data.

To create an Analytic Privilege, you need:

• Information View (CVs)
• Type of AP that needs to be created
• Object (Attribute) on which restriction should be applied
• Restricted Value

Conceptually there are 3 types:

1. SQL Based
2. Classic AP
3. Dynamic APs (Stored procedure) which assigns authorization to either SQL or classic AP. Hence it is not considered as a type of AP.

Technically, there are only 2, SQL Based, and Classic (XML)

NOTE: On a particular IV, you can either have SQL based or XML based APs. Information view can’t have both the APs.