Question about row-level security

Reply
Occasional Contributor
Posts: 8

Question about row-level security

Hi everyone,

I am facing a small issue when trying to implement a row-level security in information map. I have setup filter and done all the prerequisities and it works with a small exception. The queries in Marketing Automation return wrong number of rows.

Let me explain. I have a security association table called A that looks like this:

PersonStatus

John

1
Mike0
Jane1
George0
Tom0

And then I have a data table that is used in Marketing Automation, lets call that B where column Status has value 1 in every row:

Data1Data2Status
animaldog1
animalcat1
animalhorse1
fishsalmon1

Idea is that users who have status 1 can query all the rows in the table B and users who don't have status 1 cannot query anything.

Let's say my name is John and when I execute query to select all the animals from tabel B then I will get 8 rows, instead of 4. Based on the queries, I analyzed, then the join between table A and B takes 2x4 rows.

Does anybody know how to have a distinct applied to joins in information map studio or should I rebuild the logic behind this security model?

Thanks in advance.

Contributor
Posts: 41

Re: Question about row-level security

Hi,

It would help if you would show the query that is being generated.

Offhand I'd say the following should work.

select * from A , B where "&userid" = A.Person and A.status = B.status

But if your situation is that you only have users that are allowed to see everything and users that shouldn't see anything I wouldn't use row level security at all.

I'd create a metadata group with as its members the users that are allowed everything, and limit the access to the information map to that group.

Frank Poppe

Occasional Contributor
Posts: 8

Re: Question about row-level security

Posted in reply to FrankPoppe

Hi Frank,

actually the query is more complex- I tried to generalize the issue by using two simple tables. Now this query is directly from Infromation Map Studio test:

PROC SQL;

Create table temp as

SELECT  count(table1.GENDER)

FROM

  cidmxe.SAS_USERS table0 Inner join (

SELECT

  table2.GENDER,

  table2.PREFIX

FROM

  cidmxe.HOT table2 Inner join (

SELECT  DISTINCT

  table4.PREFIX,

  table4.GENDER

FROM

  cidmxe.SAS_USERS table5 Inner join cidmxe.HOT table4 on table5.PREFIX = table4.PREFIX

WHERE

  ( table5.IDENTNAME )  = 'John Doe'

) table3 on  (table2.PREFIX = table3.PREFIX AND table2.GENDER = table3.GENDER) 

) table1 on table0.PREFIX = table1.PREFIX

GROUP BY

  1;

quit;

when I remove filter then query will look like this:

PROC SQL;

Create table temp as

SELECT  count(table0.GENDER)

FROM

  cidmxe.HOT table0

GROUP BY

  1;

quit;

SAS_USERS table looks like table A in my first post- IDENTNAME is user's name and PREFIX is either 1 or 0. table HOT contains multiple columns where one of the columns is GENDER. Another column is PREFIX where all the values are set to 1.

In total there should be 418 000 rows where GENDER='F' and 372 000 rows where GENDER='M'. However, the query returns numbers 3 times bigger than actual.

There are more than two tables in information map so we only want to limit access to one table for certain users, all the other tables can be accessed without limitations. In that sense I think your second suggestion wouldn't work, If I understood correctly.

Rain

Contributor
Posts: 41

Re: Question about row-level security

Rain,

If your query is actually much more complicated then in your original posting I think you can hardly expect to get good specific suggestions on why the query gives unexpected results.

Having seen the complete query there will undoubtedly be some join that has multiple matches. Perhaps using left or right joins can help.

Butt I still think a solution with metadata groups is much simpler. Organise all access through Information Maps. You apparently have some simple IM's on single tables, to which all users have access. Additionaly you have a more complicated IM to which only a subset has access.

It is very well possible that a table with a simple IM to which everybody has access also figures in another IM to which not all users have access.

Frank

Occasional Contributor
Posts: 17

Re: Question about row-level security

Hi,

Many to Many relationship to join tables is always time consuming, less effecient and not advisable . So I suggest create two groups (STATUS1 and STATUS0)

Put John and Jane in STATUS1 group and remaining all in STATUS0.

  • create a Information Map with table B no need to join A
  •   Select Insert ► Filter to open the New Filter dialog box in Information map studio and name it as "one filter".
  • put the condition where STATUS is equal to 1
  • Create another filter("zero filter") and put condition STATUS = 0
  • then right click information map -> authorisation -> click STATUS1 group -> add condition -> add the filter " one filter" to the STATUS1 group and the same way do it for STATUS0 group also.

As per you example, if john runs a infomap he will get four records and zerop record will come if Tom runs it.

Hope this will resolve your issue

Occasional Contributor
Posts: 8

Re: Question about row-level security

AkilanR,

do I understand correctly that the method you refer to limits access to whole information map, not only to certain table?  Or can this be used for a single table also?

Thank you,

Rain

Occasional Contributor
Posts: 8

Re: Question about row-level security

Thanks for the suggestions, I'll see what I come up with.

New Contributor
Posts: 2

Re: Question about row-level security

this should work it appears something is not set up correctly

in your example if your name is John you would not be getting 2x4 (two from table a, four from table b) but 1x4 because John only has 1 record on tabel a.

i have used somethign similar for store number where a manager each see's their own store and am conbining tables with 230 stores and 8000 store records and some managers have multiple store assignements and some can see more than one and it still all works. i think you have the filters set incorrectly in the map

Occasional Contributor
Posts: 8

Re: Question about row-level security

Thanks for the suggestions.

I even contacted SAS Support about my issue and one soluton they provided was to create duplicate information map for a second group of users and remove the data items that those users shouldn't see. This is because you canno't apply metadata security on information map data item.

One thing I was thinking was about creating a second database user with limited access inside database server- this way one group will get an error when trying to query restricted tables. Currently we have only one Oracle user that is tied to a group where the users are who can access data in CI Studio.


Ask a Question
Discussion stats
  • 8 replies
  • 693 views
  • 0 likes
  • 4 in conversation