We are working on a business application with database on SQL Server 2000.
We are using BO-XI universe, and report users will use WebI to generate reports.
The database maintains users of the application in “User” table. The userid is same as the user’s network login (Active Directory Account name).
The “Project” table maintains the list of Projects in the system. (this list will grow with time).
The “Role” table maintains roles for the application. This is a static table with predefined roles such as Manager, Team Lead, Engineer, etc
Each user can have access to multiple projects. The User is assigned a role on that project. The roles assigned to Users are maintained in the “UserProjectRole” table which has the UserId, ProjectId, RoleId. (i.e. what role the user has on a project). A User who is “Manager” on a Project can be “Engineer” on another project, and may not have any role on a third Project.
There is set of 10 reports the application can generate. e.g. User assignment report, which lists all users for a project.
All reports are “Project specific”.
We would like to enforce security such that:
Only users assigned to the project can generate this report for the project
going further - only users who have the role of “Manager” on a project should be able to generate the report for that project.
We intend to use Active Directory authentication. Create groups on Active Directory, assign users to the Active Directory groups.
I can a restrict user to generate reports only for projects to which he is assigned to by adding ‘security preference’ -> row level access, and adding a condition “where ProjectID=34”. Then I would add my users to an Active Directory Group (say ReportUsersGroup) and apply this “security preference” to the AD group.
But this would mean i will have to do this whenever a new project is created.This is practically difficult to manage. (I do not want to hardcode ProjectID=34…35…36)
Is there a way the logged in User’s network ID can be passed to the Universe, and apply reporting restrictions based on the “UserProjectRole” mapping that we maintain in the database?
Is there a partial/complete to such a requirement?
True, but the presentation I referenced doesn’t use the “repository enforced” functionality for security. It uses Designer only to enforce joins and conditions on tables (including separate security tables, just like the original post described) within the database itself to enforce security. That technique should be essentially unchanged with Designer XI.
In the much bigger picture, it is true that the XI security model is much more flexible, so it may be that “someday” these Designer-driven security techniques can be replaced. Especially after the “unified semantic layer” arrives … combining Business Views and Universes … targeted for the next major release in 2006.
Thanks Dwayne, your solution works perfect.
The WHERE clause in my security preference reads:
Project.ProjectID IN(SELECT ProjectID FROM UserProjectRole WHERE IDSID=@Variable('BOUSER'))
This ensures that the logged in user is able to see only data for projects on which he/she has a role on.
But this necessitates that the report has the projectId on it (or some field from the Project table). If the person designing the report does not drag the project class, then this security preference will not be applied. Consequently, user will get to see data for all projects on the report.
Is there a way we can force this security restriction regardless of whether the ProjectId is on the report ?
Or is it reasonable to expect that the person should always drag the ProjectId field on the report ? (and resolve this by providing proper instructions during training)
There is one other thought that came up which was to redesign the universe, such that it would be based on database views (instead of db tables). Each view would expose the fields from the db table/s, additionally make the necessary joins to get the ProjectId from Project table. So each view will have ProjectId as a field. In this manner the security preference will be applied regardless of what fields the person drops on the report. Is this a recommended/standard way of handling the scenario?
What are the different ways we could work around this issue?
Indeed row level security is now under designer directly on the universes. I think it’s better ! but this new way of storing row level security is not good on that point : we couldn’t open Xi universe under previous release (it’s true from 5 and 6 release !)
Try placing the above as a “self join” on the Project table. That way any time the table is used (any object from that table) the restriction will be applied.
OK. But i would want the project level security to be enforced even when no object from the project table exists on the report. Besides the current way of applying ‘security preference’ works fine for me as i can enforce that preference to specific AD groups.
The question remains, how can i force the person designing reports to always drag the ProjectId/ProjectName field on the report (so that the project level security preference is enforced).
This makes no sense to me. If that table is not involved in the query whatsoever (no result objects, no conditions, nothing), then why would you need security enforced? I’m probably missing something.
How? Threaten to fire them if they don’t ! Seriously, there is no way systematically force a person to use a specific object. That’s why I suggested attaching it to the table (through the self-join).
(BOB member since 2002-09-19)
(BOB member since 2005-02-21)
! Seriously, there is no way systematically force a person to use a specific object. That’s why I suggested attaching it to the table (through the self-join).
(BOB member since 2002-06-20)