Security: Form to manage users and permissions

[jdttek]

I am providing a manager with Administer permissions so that he can add and delete users to the the security set-up. I would like to avoid exposing the manager to the User Level Security Wizard or directly to User and Group Accounts and Permission dialogs.

Idealy, if magager is logged in, he would be able to open a simple dialog box that would allow add, edit, delete to User Names, PID and Group Assignments only. He would add a name, assign a PID and assign the new User to a Group (e.g., Full Data User, Read-Only User, etc (but NOT Full Permissions !!!)using Combo box selection. Changes would bew reflected in secutity

I have a dialog box that allows users to change password, and would like to provide something similar per above.

Any suggestions on how to get started on this (other than cmd button to open User and Group Accounts), or any suggested add-ins or examples wld be greatly appreciated.
Only reference I see in help is acCmdUserAndGroupAccounts.

ps: Access 2000

Thanks in advance
JDTTEK

 

[psemianonymous]

1. What you ask for is possible. It is possible to manipulate security settings via the Workgroup object and the Group/Groups objects/collections and the User/Users objects/collections.

So, for example, to add the "Admin" user to the "Admins" group, you would do:

dbengine(0).Groups("Admins").Users.Append dbengine(0).Users("Admin")

- note that this code probably will not work using DBEngine(0), instead open a new Workspace object...

But I don't think it's useful enough to actually implement what is already provided.


I suggest instead, that you write thorough documentation for each activity your 'host DBA' will have to perform:
1. Adding a new user from scratch
2. Adding security for a user, so this means you have to describe what each security group does as well
3. Deleting a user


This is my solution to the problem you have. Just write detailed, numbered-list step-by-steps for each of the three above, and you'll probably save yourself some time. And as a bonus, you get to keep the documentation for the next database...

 

[jdttek]

Foolio12
Thanks for your reply, and to a certain extent I agree. Why try to reinvent the wheel. Several reasons:
1. Don't want to expose the DBA (in this case the Dept manager who wants to control the users) to too much complexity or detail. Form could be designed to be much more user friendly than what exists with security now.
2. Want to control Groups and Permissions DBA can create or assign (by limiting selections in combo dropdowns)ie, without direct access to Security, would not be able to assign Admin, Full Permissions, etc).
3. To extent possible, limit DBA role to Security only, without making access to query and table design (even if read only) too obvious.
Figure, if I could figure a way to do this, I could also replicate to other projects, just as you suggest replicating procedures to other projects!!!

On # 3. Is there any way to give DBA (or a defined Group) the ability to manage Security ONLY without granting full Admin permissions that expose form, query and database design? Is MDE the oly way (even that exposes read design of query).

Appreciate ur thots

JDTTEK

 

[HomeALone]

Here are my answers for your questions in your second post.

1). You have to do this in a split database environment (FE/BE database). When you are ready to distribute your FE, make a copy of this FE, then disable the AllowByPassKeys function (see helps file for this function) to STOP everybody including yourself to open the FE database window. You can then distribute this copy of FE to the manager who can manipulate the user permission/account (provide you have a button or menu to open the user permissions/accounts). The KEY here is to keep the original FE copy (only to yourself) that you can still open the database window for further development.

2). You have to assign the Dept manager as an Admin, who in turn with direct access to security to assign full permissions to every thing in order to manipulate user permissions/accounts. What I do I would give the Dept manager the permission to do all that since he/she is pretty much the owner of the database. My two suggestions are: First, give him enough training to make sure he knows what he is doing when getting into the Security window. Second, backup a SYSTEM.MDW file (the one that you are an Admin member) just in case he locks all of you out. Then all you have to do is to replace his updated SYSTEM.MDW with your backup SYSTEM.MDW file, so you can get yourself in and restart all over again for the security assignment.

3). See # 1 above.

I hope this helps you think!