Securing Users and Groups

The Users collection contains all users defined in the current workgroup information file. Similarly, the Groups collection contains all groups. By manipulating these collections, you define and control the security accounts that Microsoft Jet uses. The Groups and Users collections are interesting in that they are self-referencing. You can see from the DAO hierarchy that the Users collection contains a Groups collection, and the Groups collection contains a Users collection. Using this structure, you can easily find which users belong to which groups, and which groups contain which users.

There are a few things to keep in mind when you’re working with users and groups:

• When you add a new user or group, you create the user or group by using the CreateUser or CreateGroup method of a Workspace object. After you’ve appended the User or Group object to its respective collection under the Workspace object, you must also create and append the object to define the new user’s membership in groups, or to specify which users are in a new group. If you’ve created a new user, you add the new user to a group by using the CreateUser method of the group you want the user to belong to, then appending the User object to that group. Alternately, if you’ve created a new group, you can add users to that group by using the CreateGroup method of the User object and appending the Group object to the Groups collection for that user.
  
  
• If you add a user programmatically in Microsoft Access, you must add the user to the Users group. That is, you must append the new User object to the Users collection of the Users group, or append the Users group to the Groups collection of the new User object.
  
  
• You may need to refresh the User and Group collections after you’ve updated them.
  
  
• If you’re using DAO to manipulate users and groups in an application other than Microsoft Access, you must set the SystemDB property to the path and file name of the workgroup information file. Otherwise, Microsoft Jet will not know which workgroup information file you want to update. In Microsoft Access, you can set this property, but it will be ignored, as Microsoft Access uses whichever workgroup information file is currently active.

The following example illustrates these points. The procedure creates a new user, appends it to the Users group, then appends it to the Admins group.

Sub AddUserToAdmins(strWorkgroup As String, strUser As String, _
		strPID As String, strPwd As String)
	Dim wrk As Workspace
	Dim usr As User, grp As Group

	' Specify which workgroup information file to use.
	DBEngine.SystemDB = strWorkgroup

	Set wrk = DBEngine(0)
	' Create new user in Users collection of default Workspace object.
	Set usr = wrk.CreateUser(strUser, strPID, strPwd)
	wrk.Users.Append usr
	
	' Create user in Users collection of Users group.
	Set grp = wrk.Groups("Users")
	Set usr = wrk.CreateUser(strUser)
	grp.Users.Append usr
	
	' Create user in Users collection of Admins group.
	Set grp = wrk.Groups("Admins")
	Set usr = grp.CreateUser(strUser)
	grp.Users.Append usr
	grp.Users.Refresh
End Sub

Keep the following additional points in mind when working with User and Group objects:

• If a user or group is deleted from the workgroup information file, any object that has specific permissions for that user or group retains those permissions. If that user or group is re-created using the same user name and PID, those permissions will become active again. This behavior is evidenced by the fact that the Owner property of objects from which the owner has been deleted returns “<Unknown>.”
  
  
• To determine all the users and groups that have permissions on an object, you should iterate through each user and group name, set the UserName property, and check the Permissions property. You can also use the AllPermissions property. Unlike the Permissions property, which returns only the permissions for the given user or group, the AllPermissions property returns all the permissions a user has for an object, including permissions implied through group membership.
  
  
• Not everyone has permissions to view user and group information. Generally, it’s best to be logged on as an administrator account — that is, a user who is a member of the Admins group — before attempting to retrieve this information. This is also true if you want to modify security settings: You must be logged on as user with sufficient permissions.
  
  
• If you’re using a workgroup information file that was created with Microsoft Access 1.x using Microsoft Jet 1.x, you cannot use the Groups collection of a User object. Instead, write a short program that looks at each of the groups and determines if the user is in that group. Of course, the best way to avoid this problem is to upgrade your workgroup information file to Microsoft Jet 2.0 format.