User-Level Security via ADOX

User -Level Security via ADOX

As you have seen, user-level security enables you to define a workgroup composed of user accounts. You can also create group accounts. A group account can have zero or more user accounts associated with it. This section demonstrates how to create user and group accounts as well as assign permissions to those accounts programmatically. The ADOX model supports this functionality through its Catalog object as well as its Users and Groups collection objects. Figure 10-6 shows the hierarchy: groups can belong to users, and users can belong to groups; users and groups both belong to the Catalog object.

Figure 10.6: The relationship of the User and Group objects to the Catalog object and the Users and Groups collections.

You can assign permissions to users, groups, or both. It is generally most efficient to assign permissions to groups when a workgroup has a large number of users with frequent additions and deletions of user accounts. You clear all default permissions from existing individual user accounts as well as the Users group account, and then assign users to all appropriate groups. With this type of user-level design, you can administer permissions by assigning users to groups and assigning permissions to groups. Recall that users inherit all permissions from the groups to which they belong. By restricting permission assignments to groups, this design provides a single focal point for managing permissions.

The upcoming samples show how to code typical user-level administration tasks . Because they are meant to highlight basic security management procedures, only a couple of the samples demonstrate error-trapping logic. The samples assume you have a working knowledge of Access security concepts, such as those reviewed in the "Overview of User-Level Security" section in this chapter. To learn more about the basics of Access user-level security, search Access Help for the topics "Manage user and group accounts" and "Types of permissions."

Connecting to a Secure Database

The following procedure connects to a secured Jet database named UserLevel. I created the database with the Chap10Admin user while I was joined to the SystemDemo.mdw workgroup created earlier in this chapter. The database has a single table name WebBasedList . This is the same table used in the "Producing Form Letters" section of Chapter 9. I stripped the Users group of all table/view permissions in the database so that no user had any permissions for the WebBasedList table except the Chap10Admin user. The UserLevel.mdb and the SystemDemo.mdw files for the workgroup are available in the book's companion materials.

The string for connecting to a database protected with user-level security requires four phrases. The first phrase designates the Provider property for the procedure's Connection object. It specifies the Jet 4.0 OLE DB provider. The second phrase assigns the system database property for the connection. This is the name and path to the workgroup information file. This file is SystemDemo.mdw in the Access11Files folder (or wherever you saved it). The third phrase specifies the data source, which in this case is the secured database file, UserLevel.mdb. The fourth phrase denotes user ID and password values for logging into the secure database. In this sample, the procedure logs on as the Chap10Admin user with a Null password. No other user in the workgroup has any permissions for the WebBasedList table.

Note  

Before attempting to run the following sample or any others in this section, you must copy the Systemdemo.mdw file from the book's companion content to your hard drive and join the workgroup it defines. As with any file copied from the companion content, clear the read-only property setting. To join the workgroup, choose the Tools, Security, Workgroup Administrator menu command, then click the Join button and select the SystemDemo.mdw file in the Access11Files folder (or wherever you saved it). In addition, turn on the Access logon feature; see the "Three-Step Process for Instituting User-Level Security" section in this chapter for instructions on accomplishing this. By the way, you can restore the Access default workgroup by joining the System.mdw. Perform a search for this file because it resides in different folders depending on your Windows user profile and Windows operating system.

 SubOpenUserLevel() Dimcnn1AsNewADODB.Connection Dimrst1AsNewADODB.Recordset     'Openconnectiontotargetuser-levelsecureddata 'source;specifypathforworkgroupinformation 'file;designatelogonIDandpassword(ifappropriate) cnn1.Provider= "Microsoft.Jet.OLEDB.4.0" cnn1.Properties("JetOLEDB:Systemdatabase")=_  "C:\Access11Files\SystemDemo.mdw" cnn1.Open "DataSource=C:\Access11Files\UserLevel.mdb;" &_  "UserID=Chap10Admin;Password=;"     'Printfirstfieldfromfirstrecordtoconfirmconnection rst1.Open "WebBasedList",cnn1,,,adCmdTable Debug.Printrst1.Fields(0)     'Cleanupobjects cnn1.Close Setcnn1=Nothing     EndSub 

The two lines following the comment open a recordset based on the connection and print the first field's value from the first record. This simply confirms the operation of the sample. The table, WebBasedList , is the same one used in the earlier multiuser sample.

Adding and Deleting Users

When you develop and manage a custom application with user-level security, you are likely to add and delete users. Before you can add users, you must log on as a member of the Admins group, such as Chap10Admin. You can use the Append method of the Users collection to add users to a catalog or group. You must specify a name for the new user, and you can designate a password. ADO lets you assign a password later using the ChangePassword method for the User object. Unfortunately, you cannot assign a personal ID (PID) with this approach. Instead, ADO picks one randomly .

Note  

In the section "User-Level Security via Jet SQL and ADO," I'll show you another approach based on Jet SQL and ADO that enables you to set the PID. The discussion of that approach mentions why the ability to set the PID is important (as does the Access Help file).

The following two procedures show one approach to invoking the Append method to add a new user to an application. The CallMakeUser procedure launches the MakeUser procedure as it passes along two arguments. The first argument designates a new user's name. The second argument sends a password. In the sample, the string " password " is the value of the password argument.

 'MakesureNewUseraccountdoesnotexistpriortorunning 'thisprocedure;forexample,runcallDeleteUserfirst     SubCallMakeUser()  MakeUser "NewUser", "password" EndSub     SubMakeUser(usrNameAsString,secureWordAsString) Dimcat1AsADOX.Catalog Dimusr1AsADOX.User     'Instantiatecatalog Setcat1=NewADOX.Catalog     'SetActiveConnectionpropertyforcatalogfor 'useinaddinganewuser cat1.ActiveConnection=_  "Provider=Microsoft.Jet.OLEDB.4.0;" &_  "DataSource=C:\Access11Files\UserLevel.mdb;" &_  "JetOLEDB:Systemdatabase=C:\Access11Files\SystemDemo.mdw;" &_  "UserId=Chap10Admin;Password=;"     'Appenduserpassedfromcallingroutine 'andassignpassword Setusr1=NewADOX.User usr1.Name=usrName cat1.Users.Appendusr1 cat1.Users(usr1.Name).ChangePassword "",secureWord     'Cleanupobjects Setusr1=Nothing Setcat1=Nothing     EndSub 

The MakeUser procedure specifies a target for the new group using the Catalog object's ActiveConnection setting. Note that it designates a user ID with the authority to make a new user, and it points to a workgroup information file. The Append method in MakeUser adds a new member to the Catalog object. Therefore, this new user is not yet a member of any groups. The user created by MakeUser does not have any permissions for database objects. You can also add a member to a Group object so that the user has immediate membership in that group. One of the samples to follow uses this technique.

Even without any permissions for database objects in a database, the NewUser user can connect programmatically to any database in the SystemDemo.mdw workgroup. The user has a name and a password, and the LogonNewUser procedure demonstrates the syntax for connecting NewUser securely to the UserLevel database in the SystemDemo.mdw workgroup. Later in the chapter, after a demonstration of how to assign database object permissions and group memberships, you'll see another procedure demonstrate more advanced functionality from a programmatically created user.

 SubLogonNewUser() Dimcnn1AsNewADODB.Connection Dimrst1AsNewADODB.Recordset Dimstr1AsString     'Openconnectiontotargetuser-levelsecureddata 'source;specifypathforworkgroupinformation 'file;designatelogonIDandpassword Withcnn1 .Provider= "Microsoft.Jet.OLEDB.4.0" .Mode=adModeShareDenyNone .Properties("JetOLEDB:Systemdatabase")=_  "C:\Access11Files\SystemDemo.mdw" .Open "DataSource=C:\Access11Files\UserLevel.mdb;" &_  "UserID=NewUser;Password=password;" EndWith     'Cleanupobjects cnn1.Close Setcnn1=Nothing     EndSub 

The next two procedures remove a user from the catalog for a database. The Delete method for the Users collection has the same syntax as the Delete method for the Tables , Procedures , and Views collection objects. The first procedure, CallDeleteUser , passes a single argument ”the user name ”to the second procedure, DeleteUser . The second procedure removes the user from the catalog and concurrently removes the user from any groups as well.

 'MakesureNewUseraccountexistspriortorunningthis 'procedure;forexample,runcallMakeUser     SubCallDeleteUser()  DeleteUser "NewUser" EndSub     SubDeleteUser(usrNameAsString) Dimcat1AsADOX.Catalog     'Instantiatecatalog Setcat1=NewADOX.Catalog     'SetActiveConnectionpropertyforcatalogfor 'useindroppingauser cat1.ActiveConnection=_  "Provider=Microsoft.Jet.OLEDB.4.0;" &_  "DataSource=C:\Access11Files\UserLevel.mdb;" &_  "JetOLEDB:Systemdatabase=C:\Access11Files\SystemDemo.mdw;" &_  "UserId=Chap10Admin;Password=;" 'Removeuserfromworkgroup cat1.Users.DeleteusrName     'Cleanupobjects Setcat1=Nothing     EndSub 

You must log on to a database as a member of the Admins group to delete a user. The Delete method does not require a password. All that the second procedure needs is a string argument naming the user to delete.

Assigning Groups to Users

One common technique for administering permissions is to assign groups to users and manage permissions for groups. Users can derive all their permissions implicitly through their group memberships. The samples in this discussion add and remove group memberships from a user account. Both samples use the built-in Users group, but the same techniques work for custom groups.

The following two procedures add a group to a user account called NewUser. Make sure the user account exists before running the procedure (for example, run the CallMakeUser procedure if the user account does not exist). The first procedure, CallAddGroupToUser , passes a user name and a group name to the second procedure, AddGroupToUser , which uses the Append method to add the Group object to the Groups collection for the user. The sample passes arguments to the second procedure that tell it to add the new group to the Groups collection for a particular User object.

 SubCallAddGroupToUser()  AddGroupToUser "NewUser", "Users" 'AddGroupToUser "Admin", "MySecretGroup1" EndSub     SubAddGroupToUser(usrNameAsString,grpNameAsString)  OnErrorGoToAddTrap Dimcat1AsNewADOX.Catalog ConstacctNameAlreadyExist=-2147467259     'Instantiatecatalog Setcat1=NewADOX.Catalog     'SetActiveConnectionpropertyforcatalogfor 'useinaddinganewgrouptoanexistinguser cat1.ActiveConnection=_  "Provider=Microsoft.Jet.OLEDB.4.0;" &_  "DataSource=C:\Access11Files\UserLevel.mdb;" &_  "JetOLEDB:Systemdatabase=C:\Access11Files\SystemDemo.mdw;" &_  "UserId=Chap10Admin;Password=;"     'AppendnewgrouptoGroupscollectionforthe 'workgroupandthenaddgrouptotheGroups 'collectionforauseraccountpassedinasanargument cat1.Groups.AppendgrpName cat1.Users(usrName).Groups.AppendgrpName     AddExit: Setcat1=Nothing ExitSub     AddTrap: IfErr.Number=acctNameAlreadyExistThen 'Ifaccountalreadyexists,ignoretherun-timeerror ResumeNext Else Debug.PrintErr.Number;Err.Description EndIf     EndSub 

The second procedure invokes the Append method in an attempt to create a group with the name of the second argument passed to it. This procedure works for groups whether or not they already exist. Because Users is a built-in group account, it will always exist. If a group with the name of the second argument does not already exist, the Append method succeeds; otherwise , the procedure falls into an error trap with error number -2147467259 and moves on to the next statement. Then, the procedure appends the group to the Groups collection for the NewUser object. Again, if the group is already in the Groups collection for the user, the procedure progresses to the next statement.

The next two procedures remove a group from a user's Groups collection. The first procedure, CallRemoveUserFromGroup , passes user and group name parameters to the second procedure, RemoveUserFromGroup , which does the work. There is no error checking in this sample, so make sure the group belongs to the user. You can do this by running the preceding sample.

 'Makesurethegroupaccountexistsfortheuser 'priortorunningthisprocedure 'Forexample,runCallAddGroupToUser     SubCallRemoveUserFromGroup()  RemoveUserFromGroup "NewUser", "Users" EndSub     SubRemoveUserFromGroup(usrNameAsString,grpNameAsString)  Dimcat1AsADOX.Catalog     'Instantiatecatalog Setcat1=NewADOX.Catalog     'SetActiveConnectionpropertyforcatalogfor 'useindroppingagroupfromtheGroupscollectionfor 'anexistinguser cat1.ActiveConnection=_  "Provider=Microsoft.Jet.OLEDB.4.0;" &_  "DataSource=C:\Access11Files\UserLevel.mdb;" &_  "JetOLEDB:Systemdatabase=C:\Access11Files\SystemDemo.mdw;" &_  "UserId=Chap10Admin;Password=;"     'DropthegroupfromtheGroupscollectionforauser cat1.Users(usrName).Groups.DeletegrpName     'Cleanupobjects Setcat1=Nothing     EndSub 

You invoke the Delete method to remove a group from the Groups collection for a User object. Notice the hierarchical specification for an individual user. After identifying a user, the syntax requires the designation of the Groups collection and, finally, the Delete method. The syntax designates the group name as a parameter for the Delete method.

Creating, Deleting, and Tracking Groups in a Catalog

When you develop custom user-level solutions, you'll probably want to create custom groups with names that are meaningful to your clients and whose permissions fit the special requirements of your custom application. The four upcoming samples do the following:

• Create a custom group

• Delete a custom group

• Prepare a report itemizing all the groups in a catalog and the groups associated with each user account

• Toggle the membership of a user in the Admins group

• The next two procedures add a group named MySecretGroup1. After referencing a database file with a user ID sufficient to make the addition, the procedure invokes the Append method of the Groups collection. You must specify a container for the Groups collection. When you add a new group to the project's Users collection, the container is a Catalog object. When you assign a group to the Groups collection of a User object, you must specify the user as the root object for the Groups collection.

   'MakesureMySecretGroup1doesnotexistbeforerunning 'thisprocedure;forexample,runcallDeleteGroup     SubCallMakeGroup()  MakeGroup "MySecretGroup1" EndSub     SubMakeGroup(grpNameAsString) Dimcat1AsADOX.Catalog     'Instantiatecatalog Setcat1=NewADOX.Catalog     'SetActiveConnectionpropertyforcatalogfor 'creatingacustomgroup cat1.ActiveConnection=_  "Provider=Microsoft.Jet.OLEDB.4.0;" &_  "DataSource=C:\Access11Files\UserLevel.mdb;" &_  "JetOLEDB:Systemdatabase=C:\Access11Files\SystemDemo.mdw;" &_  "UserId=Chap10Admin;Password=;"     'Addcustomgrouptothecatalog'sGroupscollection cat1.Groups.AppendgrpName     'Cleanupobjects Setcat1=Nothing     EndSub 

The following two procedures remove a group from a catalog. You must make sure that the group already exists in the catalog before running the procedures. You can do this by running the preceding sample. In fact, the next sample removes the group added in the preceding one.

 'MakesureMySecretGroup1existspriortorunningthis 'procedure;forexample,runcallMakeGroup     SubCallDeleteGroup()  DeleteGroup "MySecretGroup1" EndSub     SubDeleteGroup(grpNameAsString) Dimcat1AsADOX.Catalog     'Instantiatecatalog Setcat1=NewADOX.Catalog     'SetActiveConnectionpropertyforcatalogfor 'droppingacustomgroup cat1.ActiveConnection=_  "Provider=Microsoft.Jet.OLEDB.4.0;" &_  "DataSource=C:\Access11Files\UserLevel.mdb;" &_  "JetOLEDB:Systemdatabase=C:\Access11Files\SystemDemo.mdw;" &_  "UserId=Chap10Admin;Password=;"     'Dropagroupfromthecatalog'sGroupscollection cat1.Groups.DeletegrpName     'Cleanupobjects Setcat1=Nothing     EndSub 

The syntax for deleting a group closely parallels that for adding a group. The code invokes the Delete method of the catalog's Groups collection. You pass the method a single parameter ”the name of the group to delete.

As you add and delete groups and users and reassign groups to users, you can easily create a custom report that tracks the group memberships for the Catalog and individual User objects. The following procedure itemizes the groups in a Catalog object that points at a specific database. Then it itemizes the Groups collection members for each user in the catalog's Users collection.

 SubListGroupsInCat() Dimcat1AsADOX.Catalog Dimgrp1AsNewADOX.Group Dimusr1AsADOX.User     'Instantiatecatalog Setcat1=NewADOX.Catalog Setusr1=NewUser     'SetActiveConnectionpropertyforcatalogfor 'loopingthroughgroupsandusers cat1.ActiveConnection=_  "Provider=Microsoft.Jet.OLEDB.4.0;" &_  "DataSource=C:\Access11Files\UserLevel.mdb;" &_  "JetOLEDB:Systemdatabase=C:\Access11Files\SystemDemo.mdw;" &_  "UserId=Chap10Admin;Password=;"     'Groupsinoverallcatalog Debug.Printcat1.Groups.Count& " groupsareinthecatalog" ForEachgrp1Incat1.Groups Debug.PrintString(3, " ")& "* " &grp1.Name Nextgrp1 Debug.Print     'Groupsineachuser 'CreatorandEnginearespecialusersthatappearinthe 'Userscollectionbutdonotneedtobetracked ForEachusr1Incat1.Users Ifusr1.Name<> "Creator" Andusr1.Name<> "Engine" Then Debug.PrintString(5, " ")&usr1.Groups.Count&_  " group(s)arein " &usr1.Name ForEachgrp1Incat1.Users(usr1.Name).Groups Debug.PrintString(8, " ")& "* " &grp1.Name Nextgrp1 Debug.Print EndIf Nextusr1     'Cleanupobjects Setusr1=Nothing Setcat1=Nothing     EndSub 

The report generated from this procedure appears in Figure 10-7. The report displays the total number of groups in the workgroup information file, and it lists the names of those groups. Then, the procedure reports the number of groups to which each user belongs. If a user does belong to any groups, the procedure prints their names. The Admin user belongs to the built-in Users groups, but the Chap10Admin user, which serves as a workgroup administrator, belongs to the built-in Users and Admins groups. The NewUser user does not belong to any group, but the ReadOnly and ReadUpdate users, which were created for the manual user-level security demonstrations , both belong to the Users groups. You can use the samples just discussed to create and delete users, groups, and user membership in groups.

Figure 10.7: A group membership report from the ListGroupsInCat procedure.

The following procedure shows one possible application of the ListGroupsInCat procedure shown a moment ago. The ToggleNewUserInAdminsGroup procedure does what its name implies. It toggles the membership of the NewUser object in the Admins group. It also documents the current status of the NewUser object in the Admins group by calling the ListGroupsInCat procedure.

 SubToggleNewUserInAdminsGroup()  OnErrorGoToToggleTrap Dimcat1AsADOX.Catalog ConstnotInAdmins=3265     'Instantiatecatalog Setcat1=NewADOX.Catalog     'SetActiveConnectionpropertyforcatalog cat1.ActiveConnection=_  "Provider=Microsoft.Jet.OLEDB.4.0;" &_  "DataSource=C:\Access11Files\UserLevel.mdb;" &_  "JetOLEDB:Systemdatabase=C:\Access11Files\SystemDemo.mdw;" &_  "UserId=Chap10Admin;Password=;"     'AttempttodeletemembershipofuserintheAdminsgroup cat1.Users("NewUser").Groups.Delete("Admins")     ToggleExit: Setcat1=Nothing ListGroupsInCat ExitSub     ToggleTrap: IfErr.Number=notInAdminsThen 'IfusernotinAdminsgroup,addittothegroup cat1.Users("NewUser").Groups.Append "Admins" Else Debug.PrintErr.Number;Err.Description EndIf ResumeNext     EndSub 

Notice that the toggling procedure relies on error trapping. After connecting to the target database and the workgroup information file through the cat1 object reference, the procedure attempts to delete Admins from the Groups collection of NewUser . If it is successful, the procedure closes by calling ListGroupsInCat and exiting. Otherwise, an error occurs. If the error occurs because the group is not in the user's Groups collection, the procedure adds Admins to the NewUser Groups collection. Then it closes by resuming as though no error had occurred.

Setting Permissions

You can use the SetPermissions method for Group and User objects to manage the permissions available to a security account. You invoke the GetPermissions method for these objects to return a Long value that specifies the types of permissions assigned to a group or to a user. Both methods offer a wide array of outcomes ; they can assign and report various permissions for a number of database object types. In addition, you can use the SetPermissions method to assign, revoke, and deny permissions as well as audit their use.

The two procedures that follow grant a group full permissions for any new table. Setting the permission for new tables has no impact for existing tables. Therefore, a group can have full permissions for all new tables and no permissions for existing tables.

 'MakesureMySecretGroup1existsbeforerunningprocedure SubCallSetAllTablePermissionsForGroup()  SetAllTablePermissionsForGroup "MySecretGroup1" EndSub     SubSetAllTablePermissionsForGroup(grpNameAsString)  Dimcat1AsADOX.Catalog     'Instantiatecatalog,group,anduser Setcat1=NewADOX.Catalog     'SetActiveConnectionpropertyforcatalog cat1.ActiveConnection=_  "Provider=Microsoft.Jet.OLEDB.4.0;" &_  "DataSource=C:\Access11Files\UserLevel.mdb;" &_  "JetOLEDB:Systemdatabase=C:\Access11Files\SystemDemo.mdw;" &_  "UserId=Chap10Admin;Password=;"     'AssignfullrightstonewtablestogrpNamegroup cat1.Groups(grpName).SetPermissionsNull,adPermObjTable,_ adAccessSet,adRightFull     'Cleanupobjects Setcat1=Nothing     EndSub 

The first procedure passes a group name, MySecretGroup1 , to the second procedure. The second procedure invokes the SetPermissions method for the group member with that name. Therefore, you must make sure that the group exists before you run the procedure or add error-trapping logic. The method's first parameter has an explicit Null value. This parameter normally specifies the name of a database object, such as a table. A Null value indicates that you should set permissions for any new database objects. The second parameter designates a Table object type. The third parameter serves as a verb; it indicates that the command will set a permission. Other constants indicate different actions that the method can launch, such as revoking permissions. The fourth parameter grants the user full rights. Other rights grant data manipulation privileges, such as insert, update, and delete rights, as well as data definition privileges, such as create and drop rights for objects such as tables. The method and its parameters grant MySecretGroup1 full rights for all new tables in the UserLevel.mdb database file with the SystemDemo.mdw workgroup information file.

This basic design is flexible and can serve in many different situations. For example, to revoke all rights for new tables, you change the third parameter for the SetPermissions method from adAccessSet to adAccessRevoke . To set rights for an existing database object, you replace the Null for the first parameter with the database object's name.

Note  

The book's companion content contains more VBA samples illustrating how to revoke permissions for new objects and how to set and revoke permissions for existing objects.

Putting It All Together

The following two procedures tap a cross-section of prior samples and show a new twist to the SetPermissions method. The first procedure calls the MakeGroup procedure to create a new group in the SystemDemo.mdw workgroup information file. Then, it invokes the second procedure and passes along the new group's name as well as the name of a database object for which it wants to assign permissions. The last two lines in the first procedure create a new user named NewUser2 and add MySecretGroup2 to its Groups collection. In this way, NewUser2 inherits the permissions assigned to MySecretGroup2 by the second procedure.

 SubCallSetRIDTablePermissionsForGroupTable()  'ThisproceduremakesagroupcalledMySecretGroup2 'AssignsRead/Insert/Deletepermissionsfor 'WebBasedListtabletoMySecretGroup2 'Next,itcreatesNewUser2andassigns 'MySecretGroup2toNewUser2     'Beforerunningthis,deleteMySecretGroup2and 'NewUser2fromUserLevel.mdbiftheyexist     MakeGroup "MySecretGroup2" SetRIDTablePermissionsForGroupTable "MySecretGroup2", "WebBasedList" MakeUser "NewUser2", "password" AddGroupToUser "NewUser2", "MySecretGroup2"     EndSub     SubSetRIDTablePermissionsForGroupTable(grpNameAsString,tblName)  Dimcat1AsNewADOX.Catalog Dimgrp1AsNewADOX.Group,usr1AsNewADOX.User     'Instantiatecatalog Setcat1=NewADOX.Catalog     'SetActiveConnectionpropertyforcatalog cat1.ActiveConnection=_  "Provider=Microsoft.Jet.OLEDB.4.0;" &_  "DataSource=C:\Access11Files\UserLevel.mdb;" &_  "JetOLEDB:Systemdatabase=C:\Access11Files\SystemDemo.mdw;" &_  "UserId=Chap10Admin;Password=;"     'Assignread,insert,anddeleterightstoanexistingtable 'forgrpNamegroup cat1.Groups(grpName).SetPermissionstblName,adPermObjTable,_ adAccessSet,adRightReadOradRightInsertOradRightDelete     'Cleanupobjects Setcat1=Nothing     EndSub 

The second procedure assigns read, insert, and delete permissions for the WebBasedList table in UserLevel.mdb to MySecretGroup2. Notice update permissions are missing from the list of rights. This procedure is similar to the earlier sample that applied rights for a specific database object, but this one illustrates how to concatenate three separate rights to get a combined set of permissions. Notice that the syntax uses an Or operator for concatenating rights.

The major substantive outcome of the preceding pair of procedures is that NewUser2 has select, insert, and delete permissions through its membership in the MySecretGroup2 group. The following SelectInsertDeleteAndUpdateWithNewUser2 procedure demonstrates how to connect NewUser2 to the UserLevel database and invoke its rights in the database for the WebBasedList table. The procedure starts by connecting to the database. Then it successively selects, inserts a record, and deletes the added record. Finally, it attempts to update the last record. This attempt fails since NewUser2 does not have an update right for the WebBasedList table. However, you can manually or programmatically add this right, and the attempt will succeed. A sample in the "User-Level Security via Jet SQL and ADO" section of this chapter demonstrates one approach to this task.

 SubSelectInsertDeleteAndUpdateWithNewUser2()  Dimcnn1AsNewADODB.Connection Dimrst1AsNewADODB.Recordset Dimstr1AsString     'Openconnectiontotargetuser-levelsecureddata 'source;specifypathforworkgroupinformation 'file;designatelogonIDandpassword cnn1.Provider= "Microsoft.Jet.OLEDB.4.0" cnn1.Mode=adModeShareDenyNone cnn1.Properties("JetOLEDB:Systemdatabase")=_  "C:\Access11Files\SystemDemo.mdw" cnn1.Open "DataSource=C:\Access11Files\UserLevel.mdb;" &_  "UserID=NewUser2;Password=password;" 'SelectandprintLastNamefieldfromlastrecord rst1.Open "WebBasedList",cnn1,adOpenKeyset,_ adLockOptimistic,adCmdTable rst1.MoveLast Debug.Print "LastNamebeforeupdate: " &rst1("LastName")     'Addanewrecord rst1.AddNew rst1.Fields(1)= "Rick" rst1.Fields(2)= "Doe114" rst1.Update     'PrintLastNamefieldfromlastrecord rst1.MoveLast Debug.Print "LastNamebeforeupdate: " &rst1("LastName")     'Deletepreviouslyaddedrecord rst1.MoveLast rst1.Find "LastName='Doe114'",,adSearchBackward rst1.Delete     'PrintLastNamefieldfromlastrecord rst1.MoveLast Debug.Print "LastNamebeforeupdate: " &rst1("LastName")     'ClearErrobjectandtrapforerrorwithattempttoupdate Err.Clear OnErrorResumeNext     'SaveFirstNamefieldbeforeattemptingtoupdateitsvalue str1=rst1("LastName") rst1("LastName")=str1& "updated"     'CheckresultsinErrobjectandrespondappropriately IfErr.Number=-2147217911Then 'Printnopermissionmessage Debug.Print "NoUPDATEpermissionforWebBasedListtable" ElseIfErr.Number<>0Then 'Printalternatemessageforanothererror Debug.PrintErr.Number,Err.Description ExitSub Else 'ForLastNamefieldupdatetable,printupdatedvalue 'andrestoreoriginalLastNamefieldvalue rst1.Update Debug.Print "LastNameafterupdate: " &_ rst1("LastName") rst1("LastName")=str1 rst1.Update EndIf     'Cleanupobjects cnn1.Close Setcnn1=Nothing     EndSub