Nov 05 2008
Designer XI 3 New Feature: Class Restrictions
Designer has provided the option to create “stub joins” in a universe for quite a few years. A stub join is a permanent where clause on a table that appears as a small stub rather than a full join connecting two tables. This feature has often been used to apply security to a table via a reference to the @Variable(‘BOUSER’) value.
However this strategy had one major flaw in that if the table was not referenced in the query then the security would not be applied. I now have a much more flexible (and powerful) option in Designer 3: Class Restrictions.
Stub Join Review
Here is what a stub join looks like in the structure window.
The extra stub hanging off of the table includes the following syntax:
RESORT_COUNTRY.COUNTRY_ID = 1
The intent of this simple restriction is to apply a restriction so that the only country allowed to be displayed in this universe is the US, or Country ID of 1. Does it work? Sort of.
If I create this query:
… here is the SQL code generated:
SELECT
RESORT.resort,
SERVICE_LINE.service_line,
SERVICE.service
FROM
SERVICE,
SERVICE_LINE,
RESORT
WHERE
( RESORT.RESORT_ID=SERVICE_LINE.RESORT_ID )
AND ( SERVICE_LINE.SL_ID=SERVICE.SL_ID )
If I include the country object in the query, then this is the SQL code generated:
SELECT
RESORT_COUNTRY.country,
RESORT.resort,
SERVICE_LINE.service_line,
SERVICE.service
FROM
SERVICE,
SERVICE_LINE,
COUNTRY RESORT_COUNTRY,
RESORT
WHERE
( RESORT_COUNTRY.COUNTRY_ID=RESORT.COUNTRY_ID )
AND ( RESORT.RESORT_ID=SERVICE_LINE.RESORT_ID )
AND ( SERVICE_LINE.SL_ID=SERVICE.SL_ID )
AND ( RESORT_COUNTRY.COUNTRY_ID = 1 )
The important difference is that the first query does not include the intended restriction. Why not? Because the stub join is only included when the affected table is included in the query. This is a fairly substantial drawback to this technique.
Class Restrictions
Here is where this new feature shines. I can create a class restriction that is applied to every object from the class. The philosophy is similar; a stub join is applied to every element from a table; the class restriction extends the concept to a universe class. Is this powerful?
If I create the same query as above:
… here is the SQL generated:
SELECT
RESORT.resort,
SERVICE_LINE.service_line,
SERVICE.service
FROM
SERVICE,
SERVICE_LINE,
COUNTRY RESORT_COUNTRY,
RESORT
WHERE
( RESORT_COUNTRY.COUNTRY_ID=RESORT.COUNTRY_ID )
AND ( RESORT.RESORT_ID=SERVICE_LINE.RESORT_ID )
AND ( SERVICE_LINE.SL_ID=SERVICE.SL_ID )
AND
( RESORT_COUNTRY.COUNTRY_ID = 1 )
Look carefully, and you will see that the RESORT_COUNTRY table is not included in the SELECT clause at all, yet the restriction is still applied to the query. How was this done?
Building a Class Restriction
I start the process using the same technique that I would use to build a standard predefined condition in the universe. I create the new condition with the appropriate logic, as shown in the screen here. The new features are configured using the options on the lower corner of the dialog box.
All I have to do is check the “manditory” box and select Apply on Class and the condition is applied to every object from that class. If I create additional objects and insert them into that class, these new objects will automatically inherit the same condition restriction without additional work from me.
And it gets better. 🙂
Building a Universe Filter
Here is a closer view of the options on the dialog box:
There are two choices: Apply on Universe and Apply on Class. Hmm.. I wonder what the first choice does? 🙂
I selected the Apply on Universe option and created a query that referenced several different tables in order to derive the sales revenue. Here is the SQL that was generated:
SELECT
sum(INVOICE_LINE.DAYS * INVOICE_LINE.NB_GUESTS * SERVICE.PRICE)
FROM
INVOICE_LINE,
SERVICE,
SERVICE_LINE,
COUNTRY RESORT_COUNTRY,
RESORT
WHERE
( RESORT_COUNTRY.COUNTRY_ID=RESORT.COUNTRY_ID )
AND ( RESORT.RESORT_ID=SERVICE_LINE.RESORT_ID )
AND ( SERVICE_LINE.SL_ID=SERVICE.SL_ID )
AND ( SERVICE.SERVICE_ID=INVOICE_LINE.SERVICE_ID )
AND
( RESORT_COUNTRY.COUNTRY_ID = 1 )
This is powerful stuff. I could create a hidden class in my universe, create any number of predefined conditions as universe filters, and leverage the @variable(‘BOUSER’) restriction to apply security to the entire universe. No matter which objects a user selects for their query, my security would get applied 100% of the time. I like it. 😎
Conclusion
Stub joins were often used to apply security via a reference to the BOUSER variable. The problem with this solution is that the security was only applied to that table. If the query did not reference that table, the security didn’t get applied. The work required by the designer at this point was substantial… he or she would have to work through the entire universe and add that table to every object reference.
As a universe designer I now have the option to create a special condition at a class level. I can even create a condition that will be applied accross the entire universe. This is good stuff, and a feature that I am sure will be well received by universe designers everywhere.
This feature was one of several mentioned in my Designer Essentials presentation delivered at the 2008 GBN conference. This particular feature was on page 11.
Nice coverage Dave. I’m looking forward to using this new feature, just haven’t found the opportunity yet! 🙂
– Josh
Hi, Josh, thanks for your comment. This is, to be honest, one of my favorite new features in the 3.x release of Universe Designer. With this new options universe designers don’t have to go to every object and force them to include a specific table just to get security applied.
This feature has been extremely useful in the executive dashboard project that I’m currently working on, forcing a condition using @Variable(‘BOUSER’) to achieve row-level security on Xcelsius 2008 dashboards using Query as a Web Service (QaaWS). Especially because QaaWS does not yet support (even in XI 3.1) universe restrictions, another common way to add row-level security to universes. BTW, when you make the condition mandatory, Designer automatically hides the condition object from the end-user allowing designers the flexibility to place the condition in the class where it makes the most sense. As an enhancement, though, it would be nice if Designer made a visual distinction between a mandatory condition and a condition object that I hid manually.
Question:
Let’s say I have a universe with 2 contexts (C1 and C2). A table TabA belongs to only one of them (C1). I create a universe filter based on TabA. Now I will create a query that is covered only by C2. What will happen? Will the universe filter not be applied to the query? Will it end up with a warning/error message? Or will I get a Cartesian product?
I can not test it because I dont have XI 3.
Thank you.
I suspect you will get the famous “Incompatible combination of objects” message if you used a universe filter in that case. You have created a scenario that can never work (a universe filter on a table that doesn’t exist in your context).
But this is just a guess. 🙂
Dave,
Thanks for this great post! I have been asked by at least 4 different clients about this security set up and with XI R2, the best we get was just half-baked. I can’t wait to get my hands dirty with XI 3.0 now!
Cheers!
I’m using exactly this approach for row-level security in a current project. I really appreciate the usefulness of this new XI 3.0 feature. MUCH easier than the stub join + all of the object maintenance for forced tables. Plus, the checkbox makes it easy to turn on/off for testing.
This is a good feature.. i worked a lot on stub joins ..but they were never helpful.
Now this is a good feature !
I’m wondering how different this newly introduced class restriction from the existing XIR2 security functionality.
Here is my illustration of a typical row level user restriction case .
Suppose we have 3 fact tables fct-1,fct-2,fct-3 and an user security table “sec_tbl” with two columns, country_cd and user_id
option-1: class restriction method
1. sec_tbl need to be joined to all three fact tables in designer
2. create filter with class restriction like sec_tbl.user_id = @Variable(’BOUSER’) applied to only fct-1,fct-2,fct-3 class.
now pull any object from fct class. the query will be like:
select fct-1.xx
from fct-1
,sec_tbl
where fct-1.country_cd = sec_tbl.country_cd
and sec_tbl.user_id = @Variable(’BOUSER’)
(The security table wont be that huge hence the extra join doesnt affect the performance of query.)
option-2: using BO designer’s manage security option:
1.Creat three restriction sets to the three fct tables like
fct-1.country_cd in (select distinct country_cd from sec_tbl where sec_tbl.user_id = @Variable(’BOUSER’))
fct-2.country_cd in (select distinct country_cd from sec_tbl where sec_tbl.user_id = @Variable(’BOUSER’))
fct-3.country_cd in (select distinct country_cd from sec_tbl where sec_tbl.user_id = @Variable(’BOUSER’))
2. Assign the above restriction set to group(s) in designer.
Now here is the query:
select fct-1.xx
from fct-1
where fct-1.country_cd in (select distinct country_cd from sec_tbl where sec_tbl.user_id = @Variable(’BOUSER’))
(Here the subqury is also not that costly)
One advantage of option-1 is, filter is applied even the filter table is not referenced, but to make the force join work the security table needs to be joined to required tables in universe.
Take an example of addition of new fact table..both the options need changes.option-1 add a new join and that of option-2 add a new restriction set.
2nd example: add a new object to the existing class, option-1 works great so as option-2.
Am i missing any advantage of the new feature over the existing one..
Hi Dave, Thanks for the useful info. I have one question though.
Does the restriction automatically gets applied to the Sub Classes as well?
hi dave,
i have a question:
I have three classes. ‘Event’ class, ‘Pmt’ class, ‘Tkt’ class.
Event class has
EventNo, EventName, EventID, VenueID.
Rest of the classes, Pmt and Tkt classes having measure objects.
My qurey’s are:
1) Select *
from event, tkt
where
event.ctseventid = tkt.ctseventid and venueid = 81
2) Select *
from event, pmt
where
event.ctseventid = pmt.ctseventid and venueid = 82
Note: I’ve joined this ‘tkt’ table to some other tables also. If I am going to give the condition ‘venueid = 81’ in the ‘tkt’ table, that will restrict the result for some other reports. That I don’t want.
Same for ‘Pmt’ table also.
If the only choice is ‘event’ table, can you pls tell me how it is possible??
sorry… mistakenly i typed as table instead of class. read those as classes.. ie event class, pmt class and tkt class. no tables.
Thanks Dave, nice summary, will love this feature in xi r3. i have so many universes using stub join to have security join. With this feature, the functionality now very simple.
Sounds good and interesting..!!! Both the features and your presentation on it..
there is one options (Apply on List of Values)near to medetory option.
What it generally does it is not in form of radio button.
Thanks Dave,Its a very nice and informative post.
I have question…when i apply class restrictions, Administrator group users are also got restricted which is contrary to restrictions applied through manage security aptions(Note : i haven’t included Administrator group users in security table).
Is this something usuall or am i going wrong ?
Hi, I answered you on BOB, but since the question is relevant to this post I will answer it here as well. Class restrictions are not bound by group membership as they are simply part of the universe. That means if you create a class restriction, it will be applied to every user, no matter what their group membership or profile looks like. In other words, class restrictions are not intended to be used for “variable” security, but instead for permanent security.
Hi Dave could you help me please? I have a webi report with this information:
accountnumber storename amount
User A should have rights to see alll of the three columns when he access the report to refresh it.
User B should not be allowed to see the account number when he access the report to refresh it.
I guessed that in Universe Designer the option that would allow me to do that was Manage security, in the tab of object restrictions.
However when user B tries to refresh the report it doesn’t refresh anythin it only throws an error message like: you do not have permissions to all the objects or one of the objects, or something like that.
is there any other option to achieve what i want? or is it that I’m thinking that object tab is for something else it’s not supposed to be for?
The security setting does not resolve content, it resolves object use only. Meaning if you publish a report with a restricted column, only people with the proper security clearance will be able to run that report. If they do not have clearance, what you would like to happen is the report would run without the offending object, but that’s not the way it works. We have accomplished this in the past by creating a view in the database that is smart enough to know who I am (via the @Variable(‘BOUSER’) technique) and applies a case statement to the column in question (account number in your case) so that the object works for everyone, but only shows data for those that are allowed to see it.
Hi Dave,
We are trying to implement column level security similar to what is described in the above post by padawangirl. We tried the following approaches:
1. Restricted the object by applying “access restriction” in the Information Design Tool and also the Universe Design Tool separately.
2. We also defined the security access level of the object to Private in the universe and a corresponding user as Public in the CMC.
With either of these approaches, the following error message shows up on refreshing a document with the restricted object: “Your security profile does not permit you to refresh the document”
Can you kindly elaborate on the approach you have written about by using @variable(‘BOUSER’)?
Would it be possible to show the report without the columns on which the user has restricted access?
Thanks!
Hi Dave the above article or explanation (Designer XI 3 New Feature: Class Restrictions) is really awesome. This will help me a lot…Thanks..
Thank You dave
Hi Dave,
I have a requirement for multilingual report in which report labels(column titles) need to be displayed based on the logged in user language(English or France). we have a user table in the database which contains UserID and Language.
please suggest me to implement this.
the universe contains 4 dimension tables and 1 fact table in which all dimension tables connected to fact table.
Thanks in advance
Narendra
Hi, your comment really doesn’t have anything to do with this post. But you should look at the translation manager process to see if it fits your needs.
Hi Dave,
How can i set a folder level security in the universe?
I am using universe designer tool to built the universe. In my universe i have a folder under which there are many subclass and each subclass has may objects. I created a security group which contains 7 users. Now my requirement is only users in that security group should be able to see that entire folder while trying to access the report built on top of that universe and for other users(remaining thousand users) not in the group,the folder should be hidden. Help me to achieve this !
Hi, this is a standard security requirement. You need to set up groups in CMC, assign users to those groups, and then apply the restrictions in Universe Designer so that members of the broader group are prevented from seeing your special class. Members of the more restricted group do not have the same limit. But this is different from the topic of this blog post, so that’s as much detail as I will go in here. Hope it helps you get started at least.