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

• Anonymous
  March 02, 2007
  The comment has been removed
• Anonymous
  February 16, 2008
  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.Thanks
• Anonymous
  March 12, 2009
  The comment has been removed