Administer users via code

[A C]

Hello

Can someone point me to some code snippits or similar which detail how to
manage users via code. We dont want users to have to go through the normal
workgroup interface simply to manage users. The desired scenario is:

-create an "admin" user who is allowed to manage the workgroup, noone else
is. I suspect this probably happens automatically when creating the mdw
file? (ie only users in the admin group will be permitted to
add/remove/manage users...?). This may not necessarily be the dbase admin
user
-create other groups with various permissions on the dbase.
-if requested by a user, create a list of users and their groups. This will
be displayed in a nice format such as a form or datasheet, if I can cycle
through the users in code then it will be trivial to get this info into the
format I want.

-have some ability to "ADD" a new user and assign them to an existing group
-have some ability to "DELETE" an existing user from a group (all groups),
or even totally
-have some ability to "ADD" existing users to existing groups
All the above via nice interface.
These last 3 would be indicated somehow via forms or check boxes in tables
or whatever (zillions of possible ways to indicate what we want done), but
then I need code to actually do the management.


As well as how to do above, what files change wrt permissions??? ie What
files will i need to redistribute? I have not quite got my head around just
where and what is stored in the mdw vs the dbase. Does the above change the
mdw or the dbase (or both?). So if a change is made to the users what files
need to be resent out?

When adding new users do they appear (are they controlled) in the mdw or in
the dbase?

As you can probably tell I am a newbie to all this, I think once i get my
head around whats happening and where it will all make a bit more sense, so
excuse what may appear to be dumb questions.

My current thought on what happens in general is that I use a developer mdw
to create a dbase admin user and regular user groups (call these dbUserGrp)
with various permissions on the dbase. Then I can create a separate mdw
which contains a (subset) of these groups (each dbUserGrp created using same
PID etc as when I made the dbase), the main pt of difference is that it will
be missing the dbase admin group/user and have its own mdw admin user. Then
the mdw admin user is free to add users to the dbUserGrp and if they do they
will get access to my dbase and inherit all the regular dbUserGrp
permissions. The mdw admin user cannot adjust dbase pemissions for the
dbUserGrps, and if they invent new groups the developer didnt know about
they can do it but will be useless. Is this how it works? If so then I
suppose the adding/deleting etc of users happens in the mdw?

Thanks for all help
AC

 

[TC]

Hello

Can someone point me to some code snippits or similar which detail how to
manage users via code.

The Access Security FAQ at
http://support.microsoft.com/support/access/content/secfaq.asp has an
example of how to create a new user and add them to the Users group,
through code.

We dont want users to have to go through the normal
workgroup interface simply to manage users.

The standard interface is terrible, IMHO.

The desired scenario is: -create an "admin" user who is allowed
to manage the workgroup, noone else is. I suspect this probably
happens automatically when creating the mdw file?

One such set of users is, all members of the Admins group of the
workgroup file which was in effect when the database was first created.

As well as how to do above, what files change wrt permissions??? ie What
files will i need to redistribute? I have not quite got my head around just
where and what is stored in the mdw vs the dbase. Does the above change the
mdw or the dbase (or both?). So if a change is made to the users what files
need to be resent out?

User names & permissions, group names, and the relationships btw users
& groups (ie. group memberships) are stored in the workgroup file - not
the database. Conversely, the permissions that some particular group or
user has to some particular database object, is stored in the database
- not the workgroup file. So, if you created a new user, and assigned
permisions to that user /specifically/, you would have to deploy a new
workgroup file /and/ a new database file. But if you created a new
user, and added them to an existing group, and did not assign any
permissions to that user specifically, you would only have to deploy
the new workgroup file; the new user would get his permissions from the
(existing) group(s) to which you assigned him.

HTH,
TC

 

[TC]

Oops:

"User names & **PASSWORDS**, group names, and the relationships btw
users
& groups (ie. group memberships) are stored in the workgroup file -
...."

TC

 

[AzzA]

Here is one set of code to add a new user to an existing group. Do not
remember where exactly I got it from, so sorry for no mention of
original author.
--------------------------------------------------------------------------------------------------------------------
Add a User to a Group with CreateUser Method

SUMMARY
To add a user to an existing group with code, you must use the
CreateUser method twice: once to create the new user for that Microsoft
Access session, and again to create the user for the group.

Microsoft provides programming examples for illustration only, without
warranty either expressed or implied. This includes, but is not limited
to, the implied warranties of merchantability or fitness for a
particular purpose. This article assumes that you are familiar with the
programming language that is being demonstrated and with the tools that
are used to create and to debug procedures. Microsoft support engineers
can help explain the functionality of a particular procedure, but they
will not modify these examples to provide added functionality or
construct procedures to meet your specific requirements.

MORE INFORMATION

The following sample function called CUser() demonstrates how to create
a user named MyName for the Users group:

1. Create a module and type the following procedure:
Function CUser (uname As String, upin As String, upwd As String, _
gname As String)

Dim u As User, w As WorkSpace, g As Group
Set w = dbengine.workspaces(0) 'The default workspace.
Set u = w.CreateUser(uname, upin, upwd) 'Create a user for the
'Access session.
w.users.Append u 'Add user to workspace.
w.users.Refresh
Set g = w.groups(gname)
Set u = g.CreateUser(uname) 'Create a user for a
group.
g.users.Append u 'Add new user to group.
g.users.Refresh
End Function

2.To test this function, type the following line in the Immediate
window, and then press ENTER:
? CUser ("MyName","1234","MyName","Users")
3.Save the module and close the Visual Basic Editor.4.On the Tools
menu, click Security, and then click User And Group Permissions.
Note that the new user appears in the Users/Group Name box.

 

[AzzA]

Can someone point me to some code snippits or similar which detail how to
manage users via code. We dont want users to have to go through the normal
workgroup interface simply to manage users.

Here are two more code snippets:
And once again sorry for no references to original authors....
----------------------------------------------------------------------
Change User Password in MDW from Form
-----------------------------------------------------------------------
Private Sub OK_Click()
On Error GoTo Err_OK_Click

Dim strUser As String
Dim strOldPW1 As String
Dim strNewPW1 As String
Dim strNewPW2 As String
Dim strMsg As String
Dim strTitle As String
Dim intType As Integer
Dim wrk As Object
Dim usrLocal As Object

strUser = CurrentUser()
If strUser = "Admin" Then
strMsg = "Password for Admin can't be changed. Please contact
system administrator."
strTitle = "Password"
intType = 16
MsgBox strMsg, intType, strTitle
Exit Sub
End If

If IsNull(Forms![yourform]![youroldpasswordfield]) Then
' strMsg = "No Old Password entered"
' strTitle = "Password"
' intType = 16
' MsgBox strMsg, intType, strTitle
' Exit Sub
strOldPW1 = ""
Else
strOldPW1 = Forms![yourfrom]![youroldpasswordfield]
End If

If IsNull(Forms![yourfrom]![yournewpasswordfield]) Then
strMsg = "No New Password entered"
strTitle = "Password"
intType = 16
MsgBox strMsg, intType, strTitle
Exit Sub
End If

If IsNull(Forms![yourform]![yournewpasswordconfirmfield]) Then
strMsg = "No verify entered"
strTitle = "Password"
intType = 16
MsgBox strMsg, intType, strTitle
Exit Sub
End If

strNewPW1 = Forms![yourfrom]![yournewpasswordfield]
strNewPW2 = Forms![yourfrom]![yournewpasswordfieldconfirm]
Set wrk = DBEngine.Workspaces(0)
Set usrLocal = wrk.Users(strUser)

'* Check if New password and verify are the same
If strNewPW1 = strNewPW2 Then
'* Check Length of new password
If Len(strNewPW1) <= 14 Then
usrLocal.NewPassword strOldPW1, strNewPW1
Else
strMsg = "Password can have a length of maximum 14
characters."
strTitle = "Password"
intType = 16
MsgBox strMsg, intType, strTitle
Exit Sub
End If
Else
strMsg = "Please verify the new password by entering it in the"
strMsg = strMsg + Chr$(13) & Chr$(10)
strMsg = strMsg + "verify box."
strTitle = "Password"
intType = 16
MsgBox strMsg, intType, strTitle
Exit Sub
End If

DoCmd.Close

Exit_OK_Click:
Exit Sub

Err_OK_Click:
If Err = 3033 Then
strMsg = "Old Password not correct for this user profile."
strTitle = "Password"
intType = 16
MsgBox strMsg, intType, strTitle
Exit Sub
Else
MsgBox Error$

Resume Exit_OK_Click
End If

End Sub


--------------------------------------------------------------
CreateUser Method and Password and PID Properties Example
--------------------------------------------------------------
This example uses the CreateUser method and Password and PID properties
to create a new User object; it then makes the new User object a member
of different Group objects and lists its properties and groups.

Sub CreateUserX(ByRef strPassword As String)

Dim wrkDefault As Workspace
Dim usrNew As User
Dim grpNew As Group
Dim usrTemp As User
Dim prpLoop As Property
Dim grpLoop As Group

Set wrkDefault = DBEngine.Workspaces(0)

With wrkDefault

' Create and append new User.
Set usrNew = .CreateUser("NewUser")
usrNew.PID = "AAA123456789"
usrNew.Password = strPassword
.Users.Append usrNew

' Create and append new Group.
Set grpNew = .CreateGroup("NewGroup", _
"AAA123456789")
.Groups.Append grpNew

' Make the user "NewUser" a member of the
' group "NewGroup" by creating and adding the
' appropriate User object to the group's Users
' collection.
Set usrTemp = _
.Groups("NewGroup").CreateUser("NewUser")
.Groups("NewGroup").Users.Append usrTemp

Debug.Print "Properties of " & usrNew.Name

' Enumerate the Properties collection of NewUser. The
' PID property is not readable.
For Each prpLoop In usrNew.Properties
On Error Resume Next
If prpLoop <> "" Then Debug.Print " " & _
prpLoop.Name & " = " & prpLoop
On Error GoTo 0
Next prpLoop

Debug.Print "Groups collection of " & usrNew.Name

' Enumerate the Groups collection of NewUser.
For Each grpLoop In usrNew.Groups
Debug.Print " " & _
grpLoop.Name
Next grpLoop

' Delete the new User and Group objects because this
' is a demonstration.
.Users.Delete "NewUser"
.Groups.Delete "NewGroup"

End With

End Sub


------------------------------------------------------------------
And just dug up anothe piece:

Programmatically Managing Access Security
------------------------------------------------------------------
Access security can be programatically managed with just a little
up-front knowledge and a lot of caution.

The first thing required is to create an admin workspace. To do this we
use the poorly documented and little known PrivDBEngine object. This
function does this handily...

Code:

Public Function GetAdminWorkspace() As Workspace

On Error GoTo Proc_Err

Dim dbe As PrivDBEngine
Dim strPathToMDW As String
Dim strUser As String
Dim strPWD As String

Set dbe = New PrivDBEngine
strPathToMDW = "c:\SomePath\MyApp.mdw"
strUser = "AppAdmin"
strPWD = "gomer"

dbe.SystemDB = strPathToMDW
dbe.DefaultUser = strUser
dbe.DefaultPassword = strPWD

Set GetAdminWorkspace = dbe.Workspaces(0)

Proc_Exit:
Set dbe = Nothing
Exit Function

Proc_Err:
' Add Error Handling
Resume Proc_Exit

End Function


Notice that I've hard coded the path, user and password. I don't
typically do this or recommend it. You could pass those strings to the
function or have the function fetch them from a table.

Here's a function showing how to use this to change a user's password.
Notice that you don't need to know the old password.


Code:

Public Function ChangePWD(strUser As String, strNewPassword As String)
As Boolean

On Error GoTo Proc_Err

Dim usrChange As User
Dim strPassword As String

Set usrChange = GetAdminWorkspace().Users(strUser)
usrChange.NewPassword "", strNewPassword

ChangePWD = True

Proc_Exit:
GetAdminWorkspace().Users.Refresh
GetAdminWorkspace().Close
Exit Function

Proc_Err:
' Add Error handling
ChangePWD = False
Resume Proc_Exit

End Function


The Workspace object has several pretty handy methods. Take a look.

Note: Using code to manage security can be very tricky and unforgiving.
Test your code offline on a copy of your app and MDW.

Have fun!

Here is a function I have used to add a user:

Code:

Public Function sCreateUser(ByVal strUser As String, ByVal _
strPID As String, Optional varPwd As Variant) As Integer
'-----------------------------------------------------------
' Create a new user and add them to the Users group
' Returns True on success, False if user already exists
'================================================= ==========
Dim db As DATABASE
Dim ws As Workspace
Dim usr As User
Dim grpUsers As GROUP
Dim strSQL As String
' if the password isn't supplied, make sure you
' pass an empty string for the password argument
If IsMissing(varPwd) Then varPwd = ""
Set ws = DBEngine.Workspaces(0)
ws.Users.Refresh
On Error Resume Next
' check to see if user already exists by using inline
' error handling to trap any errors caused by setting
' a reference to a possibly non-existent user
strUser = ws.Users(strUser).Name
If Err.Number = 0 Then
MsgBox "The user you are trying to add already exists.", _
vbInformation, "Can't Add User"
sCreateUser = False
Else
' go ahead and create the user account
Set usr = ws.CreateUser(strUser, strPID, varPwd)
ws.Users.Append usr
ws.Users.Refresh
' now add the user to the Users group
Set grpUsers = ws.Groups("Users")
Set usr = grpUsers.CreateUser(strUser)
grpUsers.Users.Append usr
grpUsers.Users.Refresh
sCreateUser = True
End If
End Function


You may have to use an Admin Workspace for this to work, I have only
used this code as is while logged in as the owner of the DB.

Add the reference for DAO Library 3.6

If you want to clear the admin password then you would use this code:
ChangePWD "Admin", ""
The Call command is not really appropriate for Functions, only for
Subroutines.

-----------------------------------------------------------------------------
PS: All the code was found in various Access Forums while searching for
"programmatically manage secutiry" or similar search terms.
www.mvps.org/access/ comes highly recommended, as well as UtterAccess
Google Groups.
-----------------------------------------------------------------------------

 

[TC]

I don't have Access here to check, but there's no way that your code is
correct. It is creating two seperate users - not one. When you append a
newly created user to a group, you do not call createuser a second
time. And even if you /did/ have to do that, you'd have to use the same
Personal Identifier (PID) on each call.

OP, I'd stick to the FAQ code if I were you

HTH,
TC

 

[TC]

Gak! I stand corrected. That is from the FAQ.

I still don't believe it! Will check tonight & post back tomorrow
(doubtless to say I was wrong).

TC