Row-level Security Trick with WebIntelligence

Posted byDavid Taylor 8 Comments on Row-level Security Trick with WebIntelligence

Can I Get Row-level Security with WebIntelligence and Still Avoid a Document Refresh?

Sure!

As a follow-up to my post yesterday I wanted to step back and say that normally you do want to handle row-level security from within the semantic layer. The problem is that you can’t get the benefit of the speed of data that comes from historical instances without requiring each user to run the report for themselves.

I did discover that you can provide the same type of security within WebIntelligence. WebIntelligence also has a function which also returns the name of the current user. The function is CurrentUser.

1. The first thing I need to do is build the semantic layer to include my security table.

Universe View

2. Next, create a new variable which returns a 1 or 0 based on whether or not the user should see the data or not.

=If(CurrentUser()=”Administrator” Or CurrentUser() = [Username];1;0)

Define the Varliable

3. Create the report and apply the filter to the entire report or to the appropriate data block.

Report View

4. Here is what Ron’s sees, when he looks at an historical instance of the WebIntelligence report.

Ron's view of the same instance

Notice that the Data Summary on the left hand side shows me that there were 71 records but even so, the restricted user ron is only allowed to 3 of the 71.

The beauty of this is that when I look at this data via BusinessObjects LiveOffice, I will see exactly the same restrictions enforced.

REMEMBER: Normally if you need row-level security, then just apply it to the universe. IF, however, you need row-level security on historical instances without requiring them to be refreshed, then use this great trick.

8 replies on “Row-level Security Trick with WebIntelligence”

  1. Hi,

    what happens if the webI interactive mode is enabled and the end user goes and remove the filter?

    thanks,

    Stefano.

  2. If you allow the user to edit the report then there is always a chance that the user would be able to delete the filter and see all the data, therefore when using this technique the report should always be read-only. In this case the priority is avoiding the refresh, so we need to make the report read-only. If the user needs to be able to edit the report, then the only option is to require a refresh on open and guarantee that any restricted records are removed from the data set.

    Great point. Thanks Stefano!

  3. I’m going crazy trying to figure a method of creating dynamic row level security. Scenario, I have a table of records for all users on a system. The users are logged in via SQL Server security settings, not active directory, windows nt, etc. The users are logged into the system under an extremely complex hashing algorithm against a seperate database so the actual username becomes useless beyond the login screen. Therefore, we have our own system filtering methods for access of specific rows on the table based on record ID of the user. How can I create a Universe on that table and yet provide row access restriction? In a standard crystal report, I update the record selection formula by saying something like “Table.ID IN (1,2,3,4,5)” so the report only displays those records. If you have any suggestions I would greatly appreciate it.

  4. Hi Dave

    I have about 35 different groups of users, I want to restrict the rows based on which group the user belongs. How can I define that in the
    variable ?

    If(CurrentUser()=”Administrator” Or CurrentUser() = [Username];1;0)

    Thanks
    Molla

  5. Hi dave

    I am new to BO. can you suggest me few tips in how i can improve my skills in BO?

    thanks in advance.

    regards
    paddy

  6. Hi Paddy… A lot of it will depend on your role (admin, developer, end-user) and the product (Crystal Reports, Xcelsius). Personally I learn by example so I love any books that have lots of examples. If you are a newbie to BusinessObjects, then there are a lot of good books on Amazon. If you are a hard-core developer, then the main one I recommend is: http://www.amazon.com/Pro-Crystal-Enterprise-BusinessObjects-Programming/dp/1590597591/ It’s a little dated but it’s hard to get good SDK/programming information about SAP BusinessObjects. I hope that helps.

  7. Hi David,

    I was wondering if you can open a post that wills dill with the security mechanism
    Limitations, I have a few in minds but before I post them I thought it might be appropriate to open a separate post, maybe: “security wish list”?

    Thanks

    Yoav

  8. Hi David,

    I have one issue where in one of my user cannot see data in one table in the webi report. She can see the other tables with data without any issues.. She does not have any restrictions on the universe. I was also looking for the filter that you have mentioned in this post but there was none. So I am really confused and cant find any rigts/restrictions for her.

    Any input from your side will be highly appreciated.

    Thanks,
    Antara

Comments are closed.