row level restrcitions with security table

system · July 12, 2005, 12:53am

Hi All,

 I have about 1500 users for whom I have to setup row level security.I have a security table which has the group and user_id info. I have two questions:

When I am creating the security restrcitions,do I have to create 1500 users in different groups manually? eg: @variable(‘BOUSER’) here create this in supervisor as:

dept1_name(group)
nisha(userid)
john(userid)
.
.
.

Add the row_restrcition as follows:

dept_hierarchy.dept1_name IN(select distcinct Security_table.value from security_table where security_table.type = ‘dept1_name’ and user_id = @Vaiable('BOUSER)

My question is Should I create two row restrcitions for the two users(nisha,john) under group(dept1_name) or does it automatically take those two users without mentioning their ids?

If a user changes a group or department,how do we do changes on his secuirty?

Does this have to be done manually or is there an automated process in BO to detect the users security settings if they are changed in the secuirty table.

And finally…I am doing this in dev environment…How do I do the testing on the secuirty settings(how do I check the restrictions applied on the users are working or not)?

Appreciate your response…

Thanks.

nisha_18 (BOB member since 2004-08-10)

system · July 12, 2005, 4:47pm

Hi Nisha,

I’m a little confused as to what you’re trying to do. It seems like you’re mixing the two methods of row-level security (row restriction in Supervisor and security table in designer). What’s the purpose of having Group in the security table?

If you apply a row restriction to the group, it will apply to all users in the group. If you’re using @Variable(‘BOUSER’) in your join, then the security will be applied specific to each user.

Depends on the method you’re using. If you’re using row restriction in Supervisor, with row restrictions applied to the parent group, then the user will inherit whatever permissions apply to the group that he’s moved to. If you’re using a security table in designer with a self-join on the primary table, then the security will apply regardless of which groups the users are in.

You can always check the generated SQL to see if the condition is applied properly.

Joe

joepeters (BOB member since 2002-08-29)

system · July 12, 2005, 5:07pm

Hi Joe,

      I am sorry to confuse you..Actually I have gone thru' the following presentattion and I am planning to implement the third option  from this:

The third option here has the secuirty table and Supervisor option to do the row level secuirty.Hope I am clear on this.

And coming to my question,thanks for the answer.But I wanted to understand much more clearer as suppose if the user changes the group or department,should I implicitly move the user into the changed group or does BO recognise that from the security table update when the user logs back in.

Appreciate your response.

Thanks

nisha_18 (BOB member since 2004-08-10)

system · July 12, 2005, 5:34pm

ah, now I understand. I hadn’t recognized the method that you are using.

You don’t need to apply row restrictions to users using this method, only groups. You would need to create a separate row restriction for each group. A group in this context refers to the dimensions that will be restricted, not individual values. For example, you may have a “Department” group that restricts access to departments, and a “Site” group that restricts access to sites. You wouldn’t have groups to specify restircted values, i.e., “Sales”, “Marketing”, “Operations”, since that’s handled by the security table.

In the Flexible Security Table method, if a user is moving from one department to another, you only need to update the security table. You don’t have to update anything in Supervisor since the group is simply used to hold the row restirction code; it doesn’t identify a specific group. You only need to move a user into a group if the user should be restricted on that dimension. For example, if a user should be restricted to one or more departments, he should be added to the “Department” group, and have values input in the security table.

Anything you do in the security table will apply immediately to users, since the values are picked up each time a query is run.

But maybe you don’t need it to be that complex. Are you using multiple objects for security, or is it simply “Department”?

Joe[/quote]

joepeters (BOB member since 2002-08-29)

system · July 12, 2005, 7:27pm

Hi Joe,

    Thanks for your input...I don't know if I can say I have multiple objects.Let me explain what I have.I have a security table which holds the following info:

Group_id
User_id
Identifier_value
security_type

Now this whole secuirty is for department which has two hierarchies: Mgmt or P/L which is the security_type

Group_id is the group_id for Mgmt or P/L(Group_id holds same name for Mgmt and P/L)

User_id is the user_id

Identifier_value is the code that determines if its Mgmt or P/L

Now when I am adding row level restriction in supervisor,do I take the group_id as the group(I am not clear because there are duplicate values for group_id- one for Mgmt and one for P/L)

Can you tell me what would be the best way to apply row level restrcition.

And regarding the second question—you said that I don’t need change anything in BO supervisor…but what I was thinking was, if a user changes a group,the group characteristics are changed in the security table which is true but don’t I have to move the user from one group_id1 to group_id2 as the user was under group1 before in supervisor.

Also One more question…if a user exists in two different group_ids,should I include them in both of them in Supervisor

Please let me know your inputs regarding this.appreciate your response.

Thanks

nisha_18 (BOB member since 2004-08-10)

system · July 12, 2005, 7:51pm

Hi Nisha,

Does your identifier_value actually determine security access, or is access defined only by group_id (Department)? That is, can you sum up your security requirements by saying

where dimension.department in (select group_id from security where user_id = @Variable('BOUSER') )

Or do you need identifier_value in the WHERE clause?

Joe

joepeters (BOB member since 2002-08-29)

system · July 13, 2005, 11:30pm

Hi Joe,

   Thanks for the response..Seems like my security setup is getting more complex.I just got done with talking with the business..This is what I have:

I have a security table which has the following clomns:

User_id
Active_flag_user(determines if a user is active or not)
group_id
active_flag_group(determines if the group is active or not)
security_type(Mgmt or P/L)
active_flag_type(if the user is active for this secuirty_type)
Identification_value

Now,I have to set up restrcition to the nodes in Mgmt or P/L hierarchy.What I mean by that is I have to check if the all the 3 active_flags are ‘Y’,then the access is given to a particular user for that node. Access might be for Mgmt or P/L or both for a particualr user.
Also,Mgmt and P/L node info is coming from 2 different tables in my universe.

Also there are about 10 nodes each in Mgmt and P/L hierarchy. So the identification value has to be compared to all the nodes and if it matches any node,access has to be given to the user from that node to node10.

For eg: The security table will have multiple records for a particular user in this way:

User_id flag_user Group_id flag_group Security_type flag_type Idt_value

Nisha Y All_Access Y Mgmt Y 458
Nisha Y All_Access Y P/L Y 506
Nisha Y Dt_Access Y Mgmt Y 345
Nisha Y Dt_Access Y P/L N 398

Now, For Nisha,security has to be set up in this way:

Access has to be given only if all the flags are ‘Y’.In the above example,Nish adoes not get access to group_id(Dt_access) - P/L.Once all the flags are ‘Y’,the identification value has to be compared with all the node_ids from the base table(Mgmt Table or P/L Table based on which security_type) and when the identification value matches to a node_id,access has to be given from that node_id to the last node(node10)

Can you please let me know,how can I set this up in BO.Apreciate your response a lot.

Thanks

nisha_18 (BOB member since 2004-08-10)

system · July 14, 2005, 12:34pm

Ok, so you’re doing security on two base tables. That helps. How about this:

Add a self-join to your mgmt table with this condition:

mgmt.node_id IN (SELECT identification_value 
                   FROM security 
                  WHERE user_id = @Variable('BOUSER') 
                        AND flag_user = 'Y' 
                        AND flag_group = 'Y' 
                        AND active_flag_type = 'Y' 
                        AND security_type = 'Mgmt' )

Add a similar self-join for P/L. Note that this method fully implements security in the universe, with no changes to Supervisor. This assumes that all users who will be accessing the universe will be represented in the security table. That is, a user who is not in the security table will not have access to anything.

Not sure about granting access to Node x - Node10. How is the order of the hierarchy defined?

Joe

joepeters (BOB member since 2002-08-29)

system · July 14, 2005, 4:35pm

Hi Joe,

       Thanks for the response. So,what you are saying is,I should add a self join to the table in the universe itself.Then in that case,do I need to force join the tables for all the objects in the universe.(option2 from the Steve's presentation)

Also,does this work for the example that I gave…I mean,does it search for all the records of the users having included in different group_ids.

I didn’t understand what you meant by order of hierarchy(in case of nodes)…But what I meant by giving access is,the identification_value has to be compared to the all the node_ids from the Mgmt or P/L hierarchy tables and once it matches,access has to be given from that node onwards to the last node

Appreciate your response.

Thanks

nisha_18 (BOB member since 2004-08-10)

system · July 14, 2005, 4:55pm

Hi Nisha,

No; there are two methods here. Method 1, which I’m suggesting, is to use the the self-join, which will cause the security restriction to be applied to any query that uses that table.

Method 2 is to add the security table as a separate table in the universe, join it to the fact table, and include the security table in all objects that you want to be restricted. If you use this method, you have to be sure to add the security table to every object; but the advantage is that you could have certain objects that won’t have the restrictions applied (unless the query also includes objects that are restricted).

It should, if I understand your structure correctly. The query will include multiple node_ids if the user has access to the associated group_ids.

When you refer to “that node onwards”, what is “onwards”? That is, how do you determine which other nodes to include if a user has access to one?

Joe

joepeters (BOB member since 2002-08-29)

system · July 14, 2005, 7:42pm

Hi Joe,

Thanks for the response…

Method 1, which I'm suggesting, is to use the the self-join, which will cause the security restriction to be applied to any query that uses that table.

Here…Do I need to include the table in the universe?When you say that it will cause security restrcition to be applied to any query that uses that table…Does that mean that a object from that table(Mgmt or P/L table)has to be included inthe query?

Do I still need to create users in the Superviosr?

And regarding the node_ids,don’t worry about that onwards… I am sorry… confused you…Its like this…we have to compare identification-value to all the node-ids and find the one that matches and give access to that node_id.

Appreciate your response.

Thanks

nisha_18 (BOB member since 2004-08-10)

system · July 15, 2005, 11:58am

Hi Nisha,

You don’t [i]have[/i] to, for this to function. You may want to include it anyway just for a reference, just not joined to anything.

Yes, that’s correct. But isn’t that what you would want anyway since the security is keyed off of those two tables?

Well, yeah, unless you’re using LDAP for authentication. If you’re asking if you have to add the row-level restrictions in Supervisor with this method, the answer is no.

Joe

joepeters (BOB member since 2002-08-29)

system · July 16, 2005, 11:24pm

Hi Joe,

  Thankyou very much for the response. Apprecaite it a lot. Overall,I can achieve this security restriction thru' designer and add the users in Supervisor. This is what I want to get as a where clause when I am pulling data from this Mgmt or P/L table:

where
( node_id1 in (select identification_value from security where security_type = ‘MGMT’ and user_id = @variable(‘BOUSER’)) or
node_id2 in (select identification_value from security where security_type = ‘MGMT’ and user_id = @variable(‘BOUSER’)) or
node_id3 in (select identification_value from security where security_type = ‘MGMT’ and user_id = @variable(‘BOUSER’)) or
.
.
.
.
)

Same thing for P/L table. One user can have access to both MGMT and P/L.How can I attach this where clause to my query everytime I pull in the object from these tables ?
Also,if my security table is added with new users and their access changed,do I need to do anything to update that in BO when the users login.

Appreciate your response…

Thanks

nisha_18 (BOB member since 2004-08-10)

system · July 18, 2005, 12:12pm

So your “nodes” are actually separate columns in the fact tables. Are there 10 columns for the 10 nodes you mentioned? That affects the strategy, since 10 subqueries would most likely hurt performance.

In this case, you may be better off adding the security table to designer, joining it to your fact tables, and then forcing each object to include the security table. This method is somewhat risky, in that you have to remember to add the security table for any new objects that are added, but you wouldn’t end up with queries having 10 subqueries.

If you choose this method, your join condition would be:

security.user_id = @Variable('BOUSER'
AND security.flag_user = 'Y'
AND security.flag_group = 'Y'
AND security.active_flag_type = 'Y'
AND security.security_type = 'Mgmt' 
AND (mgmt.node_id1 = security.identification_value
      OR mgmt.node_id2  = security.identification_value
      OR mgmt.node_id3  = security.identification_value)

If the security table is updated with changes, they will take effect immediately, since the table will be read each time a query is run.

Joe

joepeters (BOB member since 2002-08-29)

system · July 18, 2005, 9:00pm

Hi Joe,

   Thanks for the response.

Are there 10 columns for the 10 nodes you mentioned?[

Yes there are 10 columns for the 10 nodes in both Mgmt and PL tables.And these are the dimension tables which are joining to all the fact tables in my universe.

So what you are saying is create a join between my Mgmt and PL tables and security table for all the nodes so that it automatically comes when the object is pulled.

When you say I have to force each object to include security table…Is that for all the objects in the universe?

Thanks.

nisha_18 (BOB member since 2004-08-10)

system · July 20, 2005, 1:01am

Hi Joe,

   I implemented the method that you suggested but its giving me no data or incorrect results. My security is getting more and more complex day by day.Please help........

With the above method its working only for one hierarchy(Mgmt or PL) and only one node(which is not how its supposed to work)

This is how it supposed to work…

In the security table that I have…the users can have access to Mgmt(1 or more nodes) or/and PL(1 or more nodes)

For Example:

User_id Security_type Ident_value

Nisha MGMT 5120
MGMT 6120
MGMT 7681
PL 2130
PL 7634
Sam MGMT 5489

Now the security for Nisha has to be as follows:

Check whats the top node-id for this user among the 3 differnet nodes from MGMT and the same for PL.Also,user shuld be able to run the report with both MGMT and PL conditions satisfied if they pull in both the objects

Check which node_id matches with ident_value from security table and give access at that node(foreg: 5120 matches at node_id4…then when user checks the data,the amount is displayed at node_id4 level(that is the top node suppose).Now if the user queries at node_id1,2,3…it should still show amount at node-id4 level as that is the top node.Whereas if users wants to query at node_id5,6,7,8…It shuld show the amount at that respective level as the user can drill down from his top node.

So I was thinking something like this(but I don’t know if this is correct)–If the user wants to see the amount @node_id3:

SELECT node3_value FROM MGMT_HIER
WHERE (node_id1 IN (5120,6120,7681)
OR node_id2 IN (5120,6120,7681)
OR node_id3 IN (5120,6120,7681)
OR node_id4 IN (5120,6120,7681)
OR node_id5 IN (5120,6120,7681)
.
.
.)

Same thing applies for PL also and when the users pulls both objects,it has to apply security restrictions based on PL or MGMT or both.

Appreciate your help…Please assist me what should I do in this case.

Thanks

nisha_18 (BOB member since 2004-08-10)

system · July 22, 2005, 2:32pm

Hi Nisha,

I don’t understand what you’re trying to do when you have users querying at different nodes.

Have you successfully manually coded a SQL statement to join the fact table to the security table to do what you want? That would be a good place to start.

Joe

joepeters (BOB member since 2002-08-29)