Can I read an active directory attribute from a custom database profile script?

Unknown · May 2018

Overview

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.

Original Approach

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.

Question

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.

Other Thoughts

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)

Ideal

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?

Starting Answer

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 video E-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.