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.