Skip to Content
author's profile photo Former Member
Former Member

Authorization on HANA Tables for Select, Insert, Update

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.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • Posted on Dec 27, 2013 at 01:50 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • 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

  • author's profile photo Former Member
    Former Member
    Posted on Jan 03, 2014 at 01:02 AM

    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


    Add a comment
    10|10000 characters needed characters exceeded

    • 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.

  • author's profile photo Former Member
    Former Member
    Posted on Jan 03, 2014 at 03:41 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.