in our sas ebi 9.3 environment we solve row-revel security on query action with the information map studio.
Example subset of a star schema:
In the Information Map we identify with the authenticated user ID (SAS.UserID) and have the possibility to define a join condition to ensure each UserID can only access the allowed "Sks" / data. I think this is a typical solution in the sas ebi world.
Now we want to implement row-revel security in sas visual analytics.
The requirement is to do all actions in memory (LASR) to prevent bad performance - so a stored process or the normal data query are no solutions.
My next idea was to built a simple etl to identify all possible values for a user in a big table.
(with some logic i optimized the etl to save much memory - IdentityGroups & users with the same permissions)
I used sas-set-metadata-access to set the permissions
UserID='SUB::SAS.PersonName' or UserID IN ('SUB::SAS.IdentityGroups')
My idea was to build a star schema (proc imstat) to join the individual permission table with our normal data (SK=SK)
with the option "create output as view" i wanted to save memory but then i found this
"A view is static. For example, if you append rows to the fact table, the append operation succeeds and every new access to the fact table can use the appended rows. However, the view is not affected by the addition of rows to the fact table. The view resolves to the state of the fact table when the view was formed."
Maybe anybody know how to implement row-level security with the requirements:
- save memory
- fast performance
or is willing to share their experiences with va row-level security
I'm not a SME on row-level security but in terms of "share experiences with va row-level security" (as you requested), have you seen these write-ups yet?
Beginner type scenario write-up:
Intermediate type scenario write-up:
Advanced type scenario write-up:
Hope these help.
Ted Stolarczyk, SAS Customer Loyalty team
sorry for the late reply.
We have a solution also we take a loss in performance and flexibility.
We build a Data Integration Studio Job which extracts all users from the permission table and define a permission condition for the
Because the metadata-access script runs 2-4 seconds for every user and we have > 1000 users, we implement a logic to process only real changes.
Let me know if anybody is interested in more details.
I am looking forward to know additional details, I am sure many other people can get benefits from your experience!
Also, I wonder, why you are implementing the security based on all the thousand of users and not on groups, which I guess it would reduce the total amount of security actions from your side. Have you tried it already?
There was an interesting presentation at the UK SAS forum recently
I'm very interested in your conversation because I'm fighting like hell with what I thought would be simple to use & built in functionality...
I just want to filter my row using conditional grant, on the userid variable. In fact, I have in my dataset a column called "login" containing the user id (which is the active directory login), and just want to say " login contains ("SUB::SAS.Userid") as clearly described in this document :
The point is that this DOES NOT WORK (returns 0 data when loggin in with the appropriate user).
So first, is there a way to be sure of the value of these systems variables ? SUB::SAS.Userid for example or "SUB::SAS.IdentityName" ?
Because when I'm doing a manuel filter : login contains 'myuserid' , it WORKS.
Thanks a lot for your support !
I am using the web interface of SAS Analytics 7.3, "administration" module, LASR folder, right-click "Authorization" on the table I want to set the "conditionnal grant". See attached.
This is clearly explained in the PDF I submitted in the previous post, but does not work.
hi VictorM - Thank you for the clarifications. I see that the PDF that you are using does not indicate that the substitution operators are available only in the batch tool. It just has a general statement that the batch editor supports more complex operators than are supported in SAS Visual Analytics Administrator.
In the VA 7.3 admin guide , the outline for the security chapter (page 35) shows a separate section for supported syntax for each of the interfaces. In that document, the substitution operators are listed only for the batch editor, not for the editor in SAS Visual Analytics Administrator. That matches my understanding, but I will check with the authors of the PDF that you are using to find out whether they found (or intend to imply) support for the substitution operators from within SAS Visual Analytics Administrator.
One thing to check is that your login IDs in your data has been UPCASED. On our environment the SUB :: SAS.UserId returns a UPPER CASE value so I assume that's the case everywhere. If your data contains 'myuserid' then either change it to MYUSERID or use the UpCase function on your login category. To use UpCase in conditional grants you'll need to apply it through the batch tools since the editor doesn't have it listed under the Text(Simple) operators.
BTW -- I'm the Jason in the paper you reference. 🙂
See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.
Find more tutorials on the SAS Users YouTube channel.