For our HR model, Drew Steans & I are trying to implement security such that each department manager can only see their own department, and nothing else.
We have 20 departments. Each department has a different manager. Each manager should have a Lite license to allow them to run reports. So far, I've created 20 security profiles so I have 1 security profile for each department. I've also created 20 database profiles, so I have 1 database profile for each department. Each database profile has a select applied limiting the Department entity to their individual department.
Although this works, is there a more elegant way to arrange these security lines? Ideally, I would like to have one security profile for Department Managers where I define which features they can use and what license should be used. Depending on the particular person, I'd like to be able to lookup which departments they can access.
- Mapping Cube - I thought about setting up an integer mapping cube to be used as a security cube, but this can be easily disabled. E-learning Crumb: Lock your Data Entry (CR28)
- Active Directory/LDAP - I thought about trying to read a user's department attribute out of active directory and I see that's possible, but I can't tie that to security without a datareader. How to query the User List from MS Active Directory
- Custom Script - Bingo! - E-learning Crumb: Security and @user (CR61)
My ideal scenario would be having an active directory group called Department Managers. In BOARD, the Department Managers windows group is set to use the Department Managers security group. The Department Managers security group identifies the features and licenses allowed. The database security is limited to the same department as the LDAP department attribute of the user. If I could implement this approach, maintenance would be near zero. Is this possible?
User=@User - By using a custom security script on my database profile, I can get down to using only one security profile and one database profile as outline in this videoE-learning Crumb: Security and @user (CR61)
To get closer to my ideal, is there a way to add some script to lookup their department in active directory? If so, that would remove the need for any datareaders for security.