User and Group Examples

Now that you understand the conceptual points of working with users and groups, here are some specific examples.

Adding a New User to an Existing Group

This example shows how to create a new user. The important step to note is adding the new user to the default Users group. Unlike creating a new user with the Microsoft Access user interface, this is not done automatically by Microsoft Jet: Your code must handle adding new users to the default Users group, along with any other group. The code works by first creating a new user with the CreateUser method of the Workspace object. It then appends this new user to the Users collection to make it a permanent part of security. Finally, it adds the default Users group to the collection of groups associated with this user.

Function AddUser(strWorkgroup As String, strUser As String, _
	strPID As String, strPwd As String) As Boolean
		
	Dim wrk As Workspace
	Dim usr As User, grp As Group
	
	On Error GoTo Err_AddUser
	DBEngine.SystemDB = strWorkgroup
	Set wrk = DBEngine(0)
	' Create user and append to default Workspace object.
	Set usr = wrk.CreateUser(strUser, strPID, strPwd)
	wrk.Users.Append usr
	
	' Add user to Users group.
	Set grp = usr.CreateGroup("Users")
	usr.Groups.Append grp
	usr.Groups.Refresh
	AddUser = True
		
Exit_AddUser:
	Exit Function
	
Err_AddUser:
	MsgBox "Error: " & Err.Number & vbCrLf & Err.Description
	AddUser = False
	Resume Exit_AddUser
End Function

Removing a User from a Group

The following example removes a user from a specified group. It works by first checking to see if the specified user is actually a member of the specified group. It does this by disabling error handling and assigning a temporary string to the group name you want to check. If the string is assigned, the user is a member of the group. In this case, the Delete method is used on the user’s Groups collection. If the string is not assigned, this indicates that the user is not a member of the specified group, and the code displays an error message.

Sub RemoveUserFromGroup(strWorkgroup As String, strUser As String, _
		strGroup As String)
	Dim usr As User
	Dim strTemp As String

	DBEngine.SystemDB = strWorkgroup
	Set usr = DBEngine.Workspaces(0).Users(strUser)

	On Error Resume Next
	' If user does not belong to specified group, then
	' strTemp will be an empty string.
	strTemp = usr.Groups(strGroup).Name
	If strTemp = strGroup Then
		usr.Groups.Delete strGroup
	Else
		Debug.Print "User " & strUser & _
			" is not a member of group " & strGroup
	End If
End Sub 

You can remove a user account altogether by removing the user from the Users collection of the default Workspace object.

Microsoft Jet maintains information about user and group accounts in the workgroup information file, but a user’s or group’s permissions for an object are stored in the database that contains that object. When you remove a user account by using DAO, it’s important to keep in mind that permissions for that user remain on the objects in the database. If someone knew the PID for the user account that you removed, he or she could re-create the user account and access the objects in your database with the same permissions that that user account had previously. To ensure that the database remains secure when you remove a user account, remove the user from any groups the user belongs to, revoke the user’s permissions for all objects in the database, and then remove the user account.

Determining If a User Has a Password

You can determine if a user has a password by attempting to log on to a new Workspace object as that user and supplying a blank password. If the user has no password, this will succeed. If the user does have a password, this will return a trappable error.

Function UserHasPassword(strWorkgroup As String, strUser As String) As Boolean
	Dim wrk As Workspace
	Const errBadPassword As Integer = 3029

	On Error GoTo Err_UserHasPassword
	DBEngine.SystemDB = strWorkgroup

	' Attempt to log on to a new Workspace object with a blank password.
	' If an error occurs error handler will set return value to True.
	Set wrk = DBEngine.CreateWorkspace("NewWorkspace", strUser, "")
	UserHasPassword = False

UserHasPasswordExit:
	Exit Function

Err_UserHasPassword:
	Select Case Err
		Case errBadPassword
			UserHasPassword = True
		Case Else
			' Unexpected error.
			MsgBox Err & ": " & Err.Description
	End Select
	Resume UserHasPasswordExit
End Function

Changing a User’s Password

To change another user’s password, you must be logged on as a member of the Admins group. To change a password, use the NewPassword method on the user’s User object. If the current password is unknown, you can use a zero-length string ("") for the current password. This enables you to easily manage situations where users have forgotten their passwords.

Sub ChangePwd(strWorkgroup As String, strUser As String, _
		strOld As String, strNew As String)
	Dim usr As User
	
	DBEngine.SystemDB = strWorkgroup
	Set usr = DBEngine.Workspaces(0).Users(strUser)
	usr.NewPassword strOld, strNew
End Sub