Nov 05 2008

Designer XI 3 New Feature: Class Restrictions

Categories: 2008 GBN - Dallas, Universe Design Dave Rathbun @ 7:28 am

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.

stub join

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:

Query Objects

… 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:

Query Objects

… 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.

class filter image

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:

zoomed filter image

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. 8-)

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.

26 Responses to “Designer XI 3 New Feature: Class Restrictions”

  1. Comment by Joshua Fletcher

    Nice coverage Dave. I’m looking forward to using this new feature, just haven’t found the opportunity yet! :)

    - Josh

  2. Comment by Dave Rathbun

    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.

  3. Comment by Dallas

    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.

  4. Comment by Marek Chladny

    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.

  5. Comment by Dave Rathbun

    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. :)

  6. Comment by Sarang D

    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!

  7. Comment by Dallas Marks

    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.

  8. Comment by Rizwan SM

    This is a good feature.. i worked a lot on stub joins ..but they were never helpful.
    Now this is a good feature !

  9. Comment by SBHUSAN

    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..

  10. Comment by MKUMAR

    Hi Dave, Thanks for the useful info. I have one question though.
    Does the restriction automatically gets applied to the Sub Classes as well?

  11. Comment by Kiruba

    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??

  12. Comment by Kiruba

    sorry… mistakenly i typed as table instead of class. read those as classes.. ie event class, pmt class and tkt class. no tables.

  13. Comment by Mathi

    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.

  14. Comment by Siva

    Sounds good and interesting..!!! Both the features and your presentation on it..

  15. Comment by Nitin Kumar

    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.

  16. Comment by Satish

    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 ?

  17. Comment by Dave Rathbun

    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.

  18. Comment by padawangirl

    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?

  19. Comment by Dave Rathbun

    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.

  20. Comment by minskc

    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!

  21. Comment by Pankaj

    Hi Dave the above article or explanation (Designer XI 3 New Feature: Class Restrictions) is really awesome. This will help me a lot…Thanks..

  22. Comment by Ganesh

    Thank You dave

  23. Comment by Narendra

    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

  24. Comment by Dave Rathbun

    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.

  25. Comment by chaarulatha

    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 !

  26. Comment by Dave Rathbun

    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.

Leave a Reply

If you want to include formulas or code in your comment, please read my Tips for formatting comments first. Tags you can use are listed below.

XHTML: You can use these tags: <a href="" title=""> <acronym title=""> <blockquote cite=""> <code> <em> <strike> <strong> <sup> <sub> <u>

Confirm submission by clicking only the marked checkbox:

 []                 

Please remember that comments that are not related to this blog post may be ignored or deleted without notice. If you're looking for help on a topic you have already posted on BOB then please do not repost your question here.