Welcome back and thank you for reading this blog. this is a continuation of the user security series.

on my previous blog, I explained how to set up the user security for read only as well as for read and write on a user. In this blog, I will explain how analytic privileges (AP) work to showcase the row level security.

Notice that APs can only be applied to views and not to tables

In order to complete this exercise, we will need:

  1. One table (to expose via a view) , and another table to control the security
  2. A calculation view (so we can apply the AP – row level security)
  3. a stored procedure (to retrieve the relationship of a user against the data)
  4. an AP (to specify the view(s) and also the conditions we need to filter the view rows by)
  5. The user we created before on blog 1 (so we can apply the AP to)

My struggles during this exercise were due to the authorization roles I did not have but I worked with my admin to get some of the required roles and even also a system privilege that helped me understand this exercise better. I think this was of huge value because I was able to look at some logs and quickly look at what I needed. I also read the SAP HANA Administration Guide in order to understand the differences between Roles, Privileges (system/object/analytic and package (which I ended up not using this last one ))

here is what the table T1 for the exercise looks like (calc view will have the same output – see the positive test below)

/wp-content/uploads/2016/03/1_918765.jpg

here is the stored procedure for our security. This procedure reads from my Users table and uses the SESSION_USER to return the Role_ID (an integer I need to match against my T1 table above. initially, I had used CURRENT_USER which did not return the correct value so make sure you understand the difference between CURRENT_USER and SESSION_USER. also, make sure you use the SQL SECURITY DEFINER mode instead of the INVOKER mode

/wp-content/uploads/2016/03/2_918703.jpg

As mentioned before we can only apply the Analytic Privileges to a database view (in my case a calculation view)

/wp-content/uploads/2016/03/3_918791.jpg

When creating an AP, first select the Reference Model and find the view you need. (remember to use a view as the table’s will not work on this step)

/wp-content/uploads/2016/03/4_918792.jpg

Associated Attributes Restrictions – this section is to specify which columns will contain a restriction. I selected column “ID” from my view.

Assign Restriction – this is where my row level security comes into play. I have selected the Repository Stored Procedure which will give me the security I need. Other options are Fixed (hardcoded value) and Catalog Procedure

/wp-content/uploads/2016/03/5_918793.jpg

Once you have created the stored procedure and the analytic privilege, now go to the security folder on the system tab > open the Users folder > select a user > assign Analytic Privilege to a user. moreover, the user has to have the SELECT privilege for the _SYS_BI and _SYS_BIC schemas. The user has to also have EXECUTE privileges on the REPOSITORY_REST catalog object (someone with admin privileges has to assign this)

/wp-content/uploads/2016/03/6_918794.jpg

Once the privileges are assigned to the user and also the AP is in place, the rest should be easier to do testing of the AP.

so on the first run of the table there were 7 records, now that the AP has been assigned, there should be less records due to the row security added to this user.

/wp-content/uploads/2016/03/7_918795.jpg

Now, if I run the view with the user who created it and no AP (higher access) I should be able to see more data. This is the same output as selecting from the table but as I mentioned originally, a table cannot contain analytic privileges.

/wp-content/uploads/2016/03/8_918796.jpg

My learnings here were

1) to understand the difference between the INVOKER (caller of the procedure) and the DEFINER ( owner of the db object)

2) Understand the different privileges as the apply to SYSTEM / Objects / Packages, etc. and

3) the Analytic Privilege step on how the view, its columns, and the stored procedure all work together (very neatly).

Thank you again for reading this blog and please share your experiences or questions. In case you are wondering on what is next ?? I will be taking a deeper step on the data security on my next blog – stay tuned!

To report this post you need to login first.

4 Comments

You must be Logged on to comment or reply to a post.

  1. Florian Pfeffer

    Hello Sergio,

    as for your first blog again I have some comments regarding your statements:

    • In general I would be great to mention what kind of analytic privilege you are using. The Classical (XML based) ones for the newer SQL Analytic privileges which are the future way.
    • Maybe the table with the assignment between users and ID would make sense in the blog too, to give the readers a better understanding of the dependency.
    • Is it good to use the SESSION_USER here to determine the “check values” from the table? Think about situations where the session is established with another user (e.g. in Business Suite or S/4 scenarios).
    • For what exactly the REPOSITORY_REST select privilege is necessary for a consuming user?

    Best Regards,

    Florian

    (0) 
    1. Sergio Guerrero Post author

      Florian,

      * I did try to use the SQL privilege on my exercise.

      * the DEFINER mode was used due to the fact that if I use the INVOKER mode then I get a user not authorized error. I traced this error back to the log (index server) and it told me that I should use the DEFINER mode – is this incorrect? if so, what are the cons of using it? is the log incorrect ? I did see a difference myself when I used CURRENT_USER vs SESSION_USER… one worked and one didn’t, that is why I used SESSION_USER … do you have an example where you can share with us on the current_user you mentioned? please do so if you do have one.

      * you are right about the repository_rest execute privilege not needed. I did read somewhere I needed that and I am unable to find the source now.. I will continue looking – thank you for pointing this one out!

      I do apologize if this simple exercises do not meet your personal expectations, however, I think that they may be good resource as Lars mentioned on the other comment that it was for sharing my experience. Thank you for highlighting some of the items I did not need and I will try to be better as I get more experience in this world. It would be great if you can share some other great examples so that myself as well as others people trying to get into blogging would look out for pointers of what is great, what is ok, and what should be avoided – again, thank you for your time and dedication to this space

      (0) 
      1. Florian Pfeffer

        Hello Sergio,

        you are right with the DEFINER mode. Both the classical and new SQL based analytical privileges requires that by default with a hard coded check. That “disables” the usage of CURRENT_USER of course because for e.g. repository procedures it would always be _SYS_REPO. I think my thoughts about to other scenarios above are to exotic or not appropriate because another techniques should be used for such cases.

        By the way, one point I found out is maybe something missing in the documentation or not officially released: In the documentation (Dynamic SQL Analytic Privileges – SAP HANA Modeling Guide for SAP HANA Web Workbench – SAP Library) it is described that the single output parameter can only be of type VARCHAR or NVARCHAR. But as you have shown in your example, it also supports (at least) Integer values.

        Regards,

        Florian

        (0) 
  2. David Colville

    G’Day Sergio,

    After trawling though a million SCN posts and articles, this one of yours was exactly what I was after! 🙂 . We have a security table that we need to use as well.

    I managed to follow it perfectly, but have come unstuck at the stored procedure.

    The error that is being returned when I call the proc is  :

    Could not execute ‘Call “XXXX”.”SP_USER_CENTRE_SECURITY”‘

    SAP DBTech JDBC: [1281]: wrong number or types of parameters in call: TBL_OUT is not bound: line 1 col 15 (at pos 14)

    Was wondering if you could spot the issue I am having ?

    I wrote mine stored proc like this –

    CREATE PROCEDURE “XXXX”.”SP_USER_CENTRE_SECURITY” (OUT tbl_out TABLE(“DIM_SK” INTEGER))

    LANGUAGE SQLSCRIPT

    SQL SECURITY DEFINER

    READS SQL DATA

    AS

    /*********BEGIN PROCEDURE SCRIPT ************/

    BEGIN

    tbl_out = SELECT DIM_SK as DIM_SK

      FROM “XXXX”.”DIM_SEC_CENTRE_USER”

      WHERE UPPER(“USERNAME”) = SESSION_USER;

      END;

    /*********END PROCEDURE SCRIPT ************/

    Cheers

    David

    (0) 

Leave a Reply