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:
- One table (to expose via a view) , and another table to control the security
- A calculation view (so we can apply the AP - row level security)
- a stored procedure (to retrieve the relationship of a user against the data)
- an AP (to specify the view(s) and also the conditions we need to filter the view rows by)
- 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)
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
As mentioned before we can only apply the Analytic Privileges to a database view (in my case a calculation view)
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)
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
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)
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.
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.
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!
- SAP Managed Tags:
5 Comments
