SAS Visual Analytics 7.3 - Row-Level Security

[mariusg]

Hello guys,

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:

FACT_1

SK_DIM_PRODUCT

SK_DIM_ORGANISATION

Value_1

Value_2

...

DIM_PRODUCT

SK_DIM_PRODUCT

PRODUCT_NAME

PRODUCT_STATUS

...

DIM_ORGANISATION

SK_DIM_ORGANISATION

ORGANISATION_TYPE

ORGANISATION_REGION

...

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)

Permissions_Table

UserId SK_DIM_ORGANISATION

0001  1

0001  2

0001  3

....

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

http://support.sas.com/documentation/cdl/en/inmsref/67213/HTML/default/viewer.htm#p0qtu1ckl3ymi5n1ey...

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

kind regards

marius

[TedStolarczyk]

mariusg:

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:

http://support.sas.com/resources/papers/proceedings15/SAS1779-2015.pdf

Intermediate type scenario write-up:

http://support.sas.com/resources/papers/proceedings15/3298-2015.pdf

Advanced type scenario write-up:

http://support.sas.com/resources/papers/proceedings16/11701-2016.pdf

Hope these help.

Sincerely,

Ted Stolarczyk, SAS Customer Loyalty team

[mariusg]

Hello Ted,

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 

sas-sas-metadata-access scripts.

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.

king regards

marius 

[JuanS_OCS]

Hi,

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?

[AbulHaque]

Hi Marius,

I am interested about your implemation logic to detect the real changes in your script.

It would be very nice if you include an explanation here. 

Thanks in advance! 

Kind regards,

Abul Haque

[itchyeyeballs]

There was an interesting presentation at the UK SAS forum recently

http://www.sas.com/content/dam/SAS/en_gb/doc/other1/sas-forum-2016/presentations/paul-johnson-sopra-...

[VictorM]

Hello guys,

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 :

http://support.sas.com/resources/papers/proceedings16/SAS6660-2016.pdf

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 !

[catherine_sas]

hi I think those substitution operators work only via the metadata batch tools, not via typing text into the GUI. Not sure which interface you are using...

[VictorM]

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.

Thanks !!

[catherine_sas]

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.

[JBS_SAS]

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