we are trying to understand the concept of a security join table built in a SQL database and then used in a universe. This would contain the organizations hierarchical security for reports with webi and crystal and deski.
We understand the concept is to build one table and that table is checks a users login id and password against rows of information in the underlying database or up in the universe.
Can anyone explain this further? That would be greatly appreciated.
Actually, itâs not. Methods in that paper that do not use Supervisor at all. If you are building DeskI or WebI reports, the tables described in the presentation are completely valid.
You will have to add some generic conditions to your universe, but the data all comes from the table.
I Thank you for your patience and consideration. But we are way new to this. Your ppt is a little over our heads. Can you help explain the process a bit more. How does one use a security table that to me doesnât have a way to link back to the dw tables?
You mentioned there would have to be some conditions, what do you mean by that?
Your security table has to have some relationship to the DW tables or it doesnât work. You either have to join it in or be able to use subqueries against it. If you canât do either, then it isnât a valid table for your application.
But, fundamentally, you need a table with 2 columns: userid, value.
userid: will correspond to the BO user id.
value will be the secured value.
If you are securing divisions, then the table will hold 1 row for each each division the user has access to.[list]e.g.
skrandel div1
skrandel div2
skrandel div4[/list]
The SQL code you want BO to generate is: [list]select col1, col2âŚ
from
table1, table2, division_table
where joins here
and division_table.div in (select value from security_table where userid=@variable(âBOUSERâ)[/list]
Your division would be equivalent to department etc.? If this is so then you could secure information per department, but what about detail information, are you able to handle that through the security table then?
Have you considered actually trying any of this stuff. Itâs very simple. I donât think we can continue to explain things until you actually try something and then have us help you fix it.
The subqueries would be built as restrictions in Supervisor. The universe doesnât care. Itâs just SQL code.
Yes I have convinced the team to set up trials. So we set up our first and the prompts come up correctly but the filter isnât passed down. The security tables are not linked into the dw. They are just being referenced in the where clauses of fields. Hereâs the code we came up with. You see there is not any links into the data, its just testing out the security tables.
UPPER(@Variable('BOUSER')) IN (SELECT DISTINCT LAN_ID FROM WAREHOUSE.FV_D_TODD_SU WHERE FINANCE_FLAG = 1)
OR
UPPER(@Variable('BOUSER')) IN (SELECT DISTINCT LAN_ID FROM WAREHOUSE.FV_D_TODD_OU WHERE FINANCE_FLAG = 1 AND OPERATING_UNIT = @Prompt('Operating Unit','A',,mono,free) )
OR
UPPER(@Variable('BOUSER')) IN (SELECT DISTINCT LAN_ID FROM WAREHOUSE.FV_D_TODD_DEPT WHERE FINANCE_FLAG = 1 AND OPERATING_UNIT = @Prompt('Operating Unit','A',,mono,free) AND DEPARTMENT = @Prompt('Department','A',,mono,free) )
So now I am trying to figure out how to use this and get it to use it as a filter as well as check security. If they are prompted for Oper_id and they put in 20300 and the other prompt is asking for dept_id and they put in 2003 then the records returned to webi or cr should just be those records pertaining to input and if they have security rights.
By the way, Thanks very much for helping fledling developers!!!*
(BOB member since 2006-01-04)