10-18-2012 09:21 AM
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:
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:
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.
10-19-2012 09:42 AM
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.
10-22-2012 07:52 AM
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:
Create table temp as
cidmxe.SAS_USERS table0 Inner join (
cidmxe.HOT table2 Inner join (
cidmxe.SAS_USERS table5 Inner join cidmxe.HOT table4 on table5.PREFIX = table4.PREFIX
( table5.IDENTNAME ) = 'John Doe'
) table3 on (table2.PREFIX = table3.PREFIX AND table2.GENDER = table3.GENDER)
) table1 on table0.PREFIX = table1.PREFIX
when I remove filter then query will look like this:
Create table temp as
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.
10-22-2012 02:52 PM
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.
10-19-2012 04:53 PM
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.
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
10-22-2012 07:57 AM
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?
11-05-2012 04:11 PM
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
11-19-2012 01:33 AM
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.