Authorization on HANA Tables for Select, Insert, Update

[Former Member]

Hello,

We are using Analytic privileges for controlling the data access from Analytic & Calculation views. But we also perform Insert / Update statements directly on the tables, which I want to control.

1. What are the SQL objects to be used to control INSERT / UPDATE on a specific table for specific dataset?

Eg.

Table: SALES_COUNTRY

Fields: COUNTRY, ORDERNO, CUSTOMERNO, QTY, AMT

Expected Authorization on the same table:

User A --> Country- DE --> should be able to Delete/Insert/Update for only DE

User B --> Country- IN --> should be able to Delete/Insert/Update for only IN

2. What are the minimum authorization (roles) / SQL object etc. to be assigned to an User to view data from a Table & Analytic View?

   -- Any pointers / link will be helpful

Thanks & Regards,
Chathia.

[Former Member]

Hi Chathia,

I understand that you are using analytic privilege to restrict country field in analytic views. Therefore,

as per below user only be able to view data for country restricted in analytic privilege.

User A --> Country- DE - Analytic Privilege A

User B --> Country- IN - Analytic Privilege B

This is mainly for information models (views). "Information views are often used for analytical use cases such as operational data mart scenarios or multidimensional reporting on revenue, profitability, and so on." Page 42 of SAP Hana Modelling Guide

With above statement, I believe you cannot use views to perform operations other than SELECT or EXECUTE on underlying tables.

To suggest SQL object privileges on tables we should know how the users will be performing operations other than SELECT?

Regards

Angad

[Former Member]

Hi Angad,

As you rightly said, we dont use Views to perform Insert/Update etc. We have SQL Script procedures (invoked by XS) which performs the Insert/Update/Delete directly on the Tables.

Regards,

Chathia.

[Former Member]

Chathia,

I am not much familiar with SQLScript but I am thinking of following logic.

You may create two design time roles (for logical separation) RoleA and RoleB. Later, add required table under Object privilege. Assign insert/delete/update privilege for that table. In your sql script you may mention

IF user is in RoleA then SELECT and INSERT based on country DE

IF user is in RoleB then SELECT and INSERT based on country IN

You can also refer to following OpenSAP material

http://help.sap.com/openSAP/HANA1/openSAP_HANA1_Week_02_Unit_03_Authorizations_Presentation.pdf

http://help.sap.com/openSAP/HANA1/openSAP_HANA1_Week_03_Unit_02_Procedure_SELECT_Statement_Presentat...

Regards

Angad

[Former Member]

Hi Chathia,

I only can answer your question for data modeling specific, not with Update/Insert/Delete authorization.

You would need to use MANDT (Client) field as a key in your SALES_COUNTRY table, in which you control a Client ID as Country specific authorization (similar to NW Client ID concept).

After that, you would need to set each user with different Session Client to restrict the analytic viewing. You may need to figure out on how to handle the actual insert/update/delete restriction probably via stored procedure for user session specific, so I will leave this for you or others to figure out.

Here is a simple test that I did:

1. Create table TEST4 with MANDT and COL1 columns, and enter 3 different values:

2. Update the user security setting with Session Client

3. Create Attribute view using TEST4 table:

4. Save & Activate, and then run data preview (you will only see Client 100 data, and not Client 200 or 300):

Hope this info helps.

Regards,

Ferry

[Former Member]

Hi Ferry,

In any real projects, the authorization will be dependent on multiple dimensions(eg. Country, Customer, Org etc.). So this mandt concept will not work then. Also mandt is for SAP ERP on HANA cases or even to run dev/quality on same hana box.

Thanks,

Chathia.

[virenp_devi]

Hi,

I think you have two requirements one SQL auths and other Analytic privilege.

May be you can create different roles one with country DE analytic priviege and SQL auth on Schema (in scope) and provide required databse operations on that.

Similarly another role with country IN and required SQL auths.

Regards,

Viren

[Former Member]

Hi Viren,

We are already using analytic privilege for reading the data. But the question is for Insert/Update/Delete data from Table..

I need to know the SQL autho. objects to be used to control that.. Pls chk the qn again.

Thanks,

Chathia.

[virenp_devi]

Hi,

I can think of basic auths to start with,

Object previleges (SQL)

_sys_BI  -   execute, select,(insert/update/delete/Drop/ in case of changes)

_sys_BIC-  execute, select,(insert/update/delete/Drop/ in case of changes)

<Schema in scope> - execute, select,(insert/update/delete/Drop/ in case of changes)

Package previleges

package privilege (suitable package)

As you test you will realise what else is missing. Mostly above should cover basic Read auth from tables from the schema.

Regards,

Viren