We have a security table which stores the username, and the key, this table is joined to one of the table in the universe,
and the challenge we are facing is we have to force select this table in every object defintion, is there a better way to handle this please share you expertise on this
I did looked at the stub join option but am not able to incorporate the same in this case.
I do have a join between the security table and the main table in the universe, but the security does not work unless i bring a object from the secuity table.
This is the not the way i want this to work, whenever the user picks any object it should put the secutity into the sql by default how do i achieve thi
Add a table hint to each of your objects pointing to the security table. In Designer double-click on the universe object, go to tab “Definition”, select the “Tables…” button, add the security table.
Thanks Andreas, we have lots of objects, so this way is not very appealing, i was wondering is there any other way to achieve this globally rather than changing at every object defintion level.
I know this method seems painful but as per my knowledge this is the only way to force a join… I did the same thing while trying to force the security table join with all tables on the universe…
I am not sure… but one way of forcing the join may be “Row level permission” through supervisor.
Instead of reporting on actual table, you can report on a “view” thatis result of security table join. And on this view apply row level restrictions from supervisor.
Row level aggregation can be done from the Supervisor…
Once you click on a group of users, click on the Universe tab at the bottom of the supervisor tab…
Then select the universe you want row level restrictions to be applied…
Click on Resources–>properties from the tool bar
In the Rows tab, click on Add…
Apply the row level aggregation one by one for each table on your Universe… essentially you would join each table to the security table and also say filter for the particular user…
Once this is done, every time one of the mentioned table is used in the query it’s appropriate row level where clause will be appended to the SQL…
Just found out a issue by applying row level aggregation
If in the row level aggregation you join two table (let’s say the acct’s table and the security table) you will obviously put the table name as Acct and the where clause in the row level aggregation as acct.col = security.col
When you pull only the account objects in a report you get the where condition with the join between acct and security which you had defined in the row level aggregation but the security table does not show up in the FROM clause … thus the SQL throws an error and the report will not run…
create a view in DB (by joining acct and security on acct.col = security.col ), and define the object with tis view instead of actual table. This should solve your prob.
(BOB member since 2002-06-20)