How to set up an association between Projects and their Active Directory group memberships

Overview

Most companies accommodate Windows Active Directory groups to construct and administer security groups, which help organise users and govern resource access. For efficient management, assigning permissions for accessing specific projects based on user roles is beneficial, including constraints toward read-only or full-write capabilities.

Solution

The solution involves creating a few database objects in SQL Server and a Data View within Geobank.

Prerequisites

• Access to modify the SQL Server database.
• Admin Permission to create a new Data View in Geobank.

Steps

1. Create Mapping Tables (SQL Server):
   • GB_SYS_MAP_Win_Groups_to_Projects: Stores project-to-group mappings. The Geobank Data View will populate this.
   • GB_SYS_MAP_Win_Users_to_Win_Groups: Stores user-to-group mappings. The stored procedure “uspGB_Active_directory_users” will populate this.
     
     
     
     
2. Create Stored Procedure (SQL Server):
   • uspGB_Active_directory_users: This procedure directly reads user and group memberships from Active Directory and updates the GB_SYS_MAP_Win_Users_to_Win_Groups table.
     
     
3. Create New Geobank Data View:
   • Name: Admin: Map Win Groups to Projects
   • Purpose:
     • Populates the GB_SYS_MAP_Win_Groups_to_Projects table.
     • Triggers an update of the GB_SYS_MAP_Win_Users_to_Win_Groups table by executing the uspGB_Active_directory_users stored procedure (without parameters) via its on Exit Event.
       
       
       
       
4. Create Function (SQL Server):
   • ufnGB_Sys_AllowedProjectsListByUser:
     • Returns a list of projects a user has access to.
     • Optional Parameters:
       • User (defaults to the current user if not provided)
       • Write Access (Boolean, if ‘1’ filters for read/write access, defaults to Null for no filtering)
         
         
5. Modify Project Visibility in Geobank:
   • Locate areas in Geobank where project visibility restrictions are needed.
   • Replace existing “GB_Project” selections (e.g., Select * From GB_Project) with the function called “dbo.ufnGB_Sys_AllowedProjectsListByUser(SUSER_NAME(), null)”.

Note

• The GB_PROJECT table may have a different field name for project name storage in your database. Also, the length of this field could be different, so pay attention when altering the provided SQL code to your database schema.
• The xp_logininfo extended stored procedure used in the solution is an undocumented and unsupported feature in SQL Server. While it generally works, Microsoft does not guarantee its continued functioning or availability in future versions of SQL Server. Alternatively, you may simplify the setup by creating a single, manually loaded and managed table with project, user name, and access columns from a simple Data View. Then, modify the ufnGB_Sys_AllowedProjectsListByUser stored procedure to look into these projects, user names, and access records instead.

Conclusion

Setting up an efficient association between projects and AD group membership through SQL Server and Geobank configurations ensures a well-organized system for managing access rights based on user roles. This setup enhances security and offers flexible control over project visibility and interactions.

Want to learn more?

Online Help Manuals - Click here for the latest version

Learning Management System - Click here to login or here to request access