How to create a ‘company’ security filter for a hosted application

Several customers have asked how to restrict data visibility in a report model for a hosted application, where every table has a “CompanyID” column, and every user that accesses the system is associated (via some other table) with exactly one company. The straightforward solution is to create a security filter on each corresponding entity; the filter should check whether any rows exist in the user table entity where Users.CompanyID=This.CompanyID and UserTable.UserID=GETUSERID(). This will work, but the SQL that is generated for this filter may not be optimal.

An alternative is to modify the DSV for the report model such that each table containing a CompanyID is really a named query of the form:

SELECT t.*, u.UserID FROM MyTable t, UserTable u WHERE t.CompanyID = u.CompanyID

Note that this derived table contains n rows for each row in MyTable, where n is the number of users associated with that row’s company. To effectively fool the model, you will need to lie in the DSV and claim that the primary key of your derived table is composed of only the key columns for MyTable.

You must then create a security filter on the corresponding model entity for MyTable, which simply states UserID = GETUSERID(). If you create any other security filters for that entity, they MUST contain the same filter condition, in addition to any other conditions. No user should be given access to all the rows of that entity.

This approach, while a bit of a hack, should result in much better SQL at runtime.

Credits to my illustrious colleague Chris Hays for coming up with this.

UPDATE: The alternative proposed above does not work correctly in all cases because of an optimization we do in the SQL translation layer. Best practice is to stick with the “straightforward solution” described at the beginning of the post.

Comments (3)

  1. burton says:

    Thanks for providing this info.  We were beating our heads trying to figure out how to limits the Areas, Iterations and Users that are listed in "In a List" filters using the TFSWarehouse database.  using this info, we created a new model and replaced each of the tables in the view with Named Queries and applied the filters that we wanted.

    Thanks Again!

  2. hegdeananth says:

    We have a similar setup except instead of adding the UserId to each table, we have a SQL in the DSV for each entity which joins the table with a view to generate the userId field.

    Everything was working as expected initially. However as we started puttting in more data into the tables the performance deteriorated to the extant that evean a simple report with data from two tables isnt working anymore.

    I chekced the SQL geenrated by the builder and noticed that it runs the subqueries first and applied the userId at the end on the out most query. This causes SQL to run the subquery  which is no of rows in table  times no of users joined with another table which again is no of rows in the table times no of users and the filter table1.userid=getuserId() and table2.userid = getUserId() get applied at the outer query.

    How can we avoid this from happening?

    Any help would be greatly appreciated.


  3. Manishpathak says:

    A million thanks to you Chris Hays !!!

    I spent 3 days banging my head to achieve the filtering on multiple tables and your post gave me the right inspiration on how can I do this. My requirement was different than this but I just did following the path.

    In my case the structure was bit more complicated as the tables are tied with ids and third table contains parent child structure. Besides one user can have multiple groups (companies in above case). But as one says principles are always same I achieved it by creating a third

    parallel table with a tweaked in query like this.

    Select U.UserName, MG.*, CHARINDEX(cast(UG.GroupID as varchar),MG.Lineage,0) as location  

    from Users U, UserGroups UG, MeterGroups MG

    Where U.UserID = UG.UserID

    and CHARINDEX(cast(UG.GroupID as varchar),MG.Lineage,0)>0

    then I put the same filter on this table for username i.e. getUserID()=UserName. And this is rocking now !!!

    Internet has very very little on this.