This topic is locked

AD: How to allow users to manage group membership?

3/16/2017 11:03:37 AM
ASPRunner.NET General questions
Pete K author

Okay, so I'm sully on-board with using AD for authentication in my ASPR.net apps. I have deployed a couple so far and it's going great. Now all of a sudden it has hit me that I have to figure out a way to allow select users to add and remove members from the security groups that I have created for these apps. How is this normally handled? I don't think we want to put AD admin tools on the desktops of non-IT folks.
So far the best solution I have found involves setting up a shortcut to a Windows desktop tool (DSQuery widget) by means of 'rundll32 dsquery,OpenQueryWindow'. But this is still possibly a bit much for some of my users who will need to use it. I was hoping it may be possible to code something that could be integrated in with an ASPR application.
Anyone have any experience with this sort of thing, or know of an easy to use web-based tool that I might employ for this?

Pete K author 3/16/2017

I figured out a way to make the DS Query tool much more viable. In the tool itself, you can save a query to a .qds file which when double-clicked in Windows opens the query window and executes the search. So I can create custom queries for my admin users and set them up on their desktops. And if I'm careful about how I name my security groups, I can probably save a query that will pull up only the groups I want that particular user to see. Then they can double-click the group and adding/deleting members is pretty straightforward. Of course, you control admin access to the groups through AD permissions, so even if they grab groups / users they are not supposed to see, they still can't edit them. I just thought that eliminating as much extraneous information as possible would cut down on my calls for help from them. This might work out okay.

jadachDevClub member 3/16/2017

In my environment, we do not allow users to add people to AD security groups. All access is done via the IT Service Desk. This way we have an audit of who gets access and who requested the access.

Pete K author 3/17/2017



In my environment, we do not allow users to add people to AD security groups. All access is done via the IT Service Desk. This way we have an audit of who gets access and who requested the access.


That's probably standard in a lot of organizations. Here at FCPS, the AD admins set up an OU just for my web projects and any groups I create there are used exclusively for that, so there is no danger of changes there screwing up anything else in the enterprise. Some of my apps require managing hundreds of users in our schools. I don't want to have to add/remove those people myself so I'm working out a way to allow one or two select people be responsible for handing that per app. I think what I have outlined above will work out okay.

jadachDevClub member 3/17/2017

The one thing I really miss from my Iron Speed days, was the way I could use windows authentication combined with database roles. Best of both worlds and it really worked well for me.

Pete K author 3/20/2017

Did you ever try the code I posted in this thread: http://asprunner.com/forums/topic/23838-database-security-with-windows-authentication/
It was working for me, but there were a few minor glitches.

T
Tim 4/4/2017

Hey guys,
I too have struggled with this. Same issues: I like the AD integration of ASPR, but then I give up the flexibility of managing the groups in the DB. It seems the new security API in version 9.7 may be a solution to this. I've just started using it, and, admittedly, there is a bit up up-front work - and you have to remember to revisit when you add tables - but it does provide a solution.
The basic idea is in the "After successful login" event you query the DB to find out what groups the user is in (or whatever other data you want to use for criteria) and then set up table securities via "if" statements. This assumes you store the user's Windows username in your DB. I pull this info into my DB from AD.
Here is an example of setting up permissions:
string sid = "SELECT StaffID,HasReports FROM Staff WHERE dbo.Staff.WinLog = '" + XSession.Session["UserID"].ToString() + "'";

XVar idrs = tDAL.CustomQuery(sid);

XVar iddata = CommonFunctions.db_fetch_array(idrs);
if (iddata["HasReports"].ToString() == "1") {
Security.setPermissions("dbo.Staff","ESP");

}

else {

Security.setPermissions("dbo.Staff","S");

}
Thanks,

Tim

jadachDevClub member 4/4/2017



Hey guys,
I too have struggled with this. Same issues: I like the AD integration of ASPR, but then I give up the flexibility of managing the groups in the DB. It seems the new security API in version 9.7 may be a solution to this. I've just started using it, and, admittedly, there is a bit up up-front work - and you have to remember to revisit when you add tables - but it does provide a solution.
The basic idea is in the "After successful login" event you query the DB to find out what groups the user is in (or whatever other data you want to use for criteria) and then set up table securities via "if" statements. This assumes you store the user's Windows username in your DB. I pull this info into my DB from AD.
Here is an example of setting up permissions:
string sid = "SELECT StaffID,HasReports FROM Staff WHERE dbo.Staff.WinLog = '" + XSession.Session["UserID"].ToString() + "'";

XVar idrs = tDAL.CustomQuery(sid);

XVar iddata = CommonFunctions.db_fetch_array(idrs);
if (iddata["HasReports"].ToString() == "1") {
Security.setPermissions("dbo.Staff","ESP");

}

else {

Security.setPermissions("dbo.Staff","S");

}
Thanks,

Tim


I like your thinking. Thanks for sharing.