User-Level Security via Jet SQL and ADO

User -Level Security via Jet SQL and ADO

Jet SQL is the language of the Jet database engine. Selected Jet 4.0 language features target user-level security issues. This section commences with an overview of Jet SQL statements and keywords for managing security and then moves on to present two samples. The first sample illustrates how to add and drop users. The second sample shows how to manage permissions for a user.

Jet SQL Statements and Keywords for User-Level Security

Four Jet SQL statements enable the management of user and group accounts: CREATE , ADD , ALTER , and DROP . Use these statements with the USER and GROUP keywords to create and drop user and group accounts. You can assign both a password and a PID when you initially create either type of user-level security account. Invoke ALTER statements to modify initial settings for user and group accounts. You add and remove users from groups with ADD and DROP statements. DROP statements also permit you to remove individual group accounts and user accounts from the currently active workgroup.

You can execute these statements from a VBA procedure via ADO Connection or Command objects. The USER and GROUP keywords fail in the Access SQL View window. Additionally, Data Access Objects (DAO) does not enable the use of the USER and GROUP keywords.

Use the GRANT and REVOKE statements to manage assigning and removing permissions for objects to user and group accounts. Typical objects include tables and stored queries (either views or procedures). However, you also have the capability to assign permissions to a whole database file; to its forms, reports , and macros; and to its containers, which behave similarly to AccessObject objects. The range of permissions that you can grant and revoke includes those available through the Access security permissions interface (by choosing Tools, Security, User And Group Permissions). The complete list of permissions available for Jet 4.0, which ships with Access 2003, is in the Help JETSQL40.chm file under the Grant statement topic. This file can be found in a different location from one computer to the next ; use a Windows Search to locate it on your computer.

Adding a New User

One advantage of adding a new user with Jet SQL and the ADO Connection object (rather than using ADOX) is that you can set the PID. When you create a new user with ADOX, Access randomly assigns the PID. Because Jet uses the PID and user name to compute a security identifier (SID) for tracking users, the ability to specify a PID is critical to recovering user accounts when the workgroup information file is destroyed , corrupted, or otherwise unavailable. Without the ability to recover user SIDs, you will have to re-create all the security settings for each user and group account.

The following pair of procedures adds and drops a new user, NewUser2, to the UserLevel.mdb database file. The first procedure, AddNewUser2 , adds and drops the new user and calls the second procedure. The second procedure, CountAndListUsers , generates a user report. The procedures generate three reports that give the count of users and their names. The first report documents the baseline number of users and their names . The second report confirms the addition of the new user. The last report confirms the return to the baseline number of users.

Notice the syntax for creating a new user. The PID appears after the password. Like a database password, the password for a user appears in square brackets. However, unlike a database password, the password for a user doesn't contain blank spaces or other special characters . All SQL strings execute from the cnn1 Connection object. Let's take a look at the syntax now:

 'Makesurethattheworkgroupinformationfiledoes 'notcontainausernamedNewUser2     SubAddNewUser2()  Dimcnn1AsADODB.Connection Dimstr1AsString     'Makesecureconnectiontotargetdatabase Setcnn1=NewADODB.Connection Withcnn1 .Provider= "Microsoft.Jet.OLEDB.4.0" .Properties("JetOLEDB:Systemdatabase")=_  "C:\Access11Files\SystemDemo.mdw" .Open "DataSource=C:\Access11Files\UserLevel.mdb;" &_  "UserID=Chap10Admin;Password=;"     'Countandenumerateusersbeforeaddingauser CountAndListUserscnn1     'SetSQLstringtoaddanewuserandexecuteit. 'NoticethespecificationofaPIDafterthepassword. str1= "CREATEUSERNewUser2[password]pidcode" .Execute(str1)     'Countandenumerateusersagaintoconfirmaddition 'ofthenewuser CountAndListUserscnn1     'SetSQLstringtodropauserandexecuteit str1= "DROPUSERNewUser2" .Execute(str1) EndWith 'Countandenumerateusersagaintoconfirmdropping 'ofpreviouslyaddednewuser CountAndListUserscnn1     'Cleanupobjects cnn1.Close Setcnn1=Nothing     EndSub     SubCountAndListUsers(cnn1AsADODB.Connection) Dimcat1AsADOX.Catalog Dimusr1AsADOX.User     'InstantiatecatalogandsetitsActiveConnectionproperty 'tothepassedConnectionobject Setcat1=NewADOX.Catalog cat1.ActiveConnection=cnn1     'Printcountofusers(lessCreatorandEngine) 'andenumeratetheirnames Debug.Print "Totalnumberofusersis= " &_ cat1.Users.Count-2 ForEachusr1Incat1.Users Ifusr1.Name<> "Creator" Andusr1.Name<> "Engine" Then_ Debug.Printusr1.Name Nextusr1     'Cleanupobjects Setcat1=Nothing     EndSub 

Granting and Revoking Permissions

The next sample we'll look at contrasts permissions for a new user. Like the previous sample, this one uses two procedures. The AssignUpdatePermissionsToNewUser2 procedure creates a new user account and grants and revokes user permissions. This procedure calls the SelectInsertDeleteAndUpdateWithNewUser2 procedure discussed in the "Putting It All Together" section of this chapter. In particular, the AssignUpdatePermissionsToNewUser2 procedure grants SELECT , INSERT , DELETE , and UPDATE permissions. It subsequently revokes the UPDATE permission. After initially granting all permissions and then revoking just the UPDATE permission, the AssignUpdatePermissionsToNewUser2 procedure invokes the SelectInsertDeleteAndUpdateWithNewUser2 procedure. An error trap in SelectInsertDeleteAndUpdateWithNewUser2 responds when the user does not have permission to update a value from the WebBasedList table in the UserLevel.mdb database file.

The AssignUpdatePermissionsToNewUser2 procedure starts by making a connection to the UserLevel.mdb file. Then, it defines the SQL string for adding a new user and executes the string through the connection. Next, the procedure grants the new user Select permission for the MSysAccessObjects table. This step is not absolutely necessary in the sample application. However, if a user manually attempts to open the database with the new user account, the Database window won't be viewable without receiving Select permission for the table.

Note  

The ADOX technique for adding a user shown in the "Adding and Deleting Users" section of this chapter does not grant Select permission for the MSysAccessObjects system table. Therefore, you need to supplement the technique to grant permission if you want users to log on manually. Alternatively, this is a good technique for forcing users to log on through your interface instead of using the built-in Access interface.

The critical part of the AssignUpdatePermissionsToNewUser2 procedure is the execution of the next two SQL strings. The first of these SQL strings grants the new user SELECT , INSERT , DELETE , and UPDATE permissions to the WebBasedList table in the UserLevel.mdb file. After granting these two permissions, the procedure invokes the SelectInsertDeleteAndUpdateWithNewUser2 procedure. This procedure exercises all the permissions and prints results reflecting the outcome. Next, the AssignUpdatePermissionsToNewUser2 procedure revokes the UPDATE permission of NewUser2 to the WebBasedList table. The procedure follows this action with a call to the SelectAndUpdateWithNewUser2 procedure. This second call prints results for all valid permissions and traps the error resulting from the revoked permission. The error trap prints a message explaining the problem instead of printing the updated value. Figure 10-8 shows the results printed to the Immediate window.

Figure 10.8: Output to the Immediate window from the AssignUpdatePermissionsToNewUser2 procedure.

 'Makesurethattheworkgroupinformationfiledoes 'notcontainausernamedNewUser2     SubAssignUpdatePermissionsToNewUser2() Dimcnn1AsADODB.Connection Dimstr1AsString     'Makesecureconnectiontotargetdatabase Setcnn1=NewADODB.Connection Withcnn1 .Provider= "Microsoft.Jet.OLEDB.4.0" .Properties("JetOLEDB:Systemdatabase")=_  "C:\Access11Files\SystemDemo.mdw" .Open "DataSource=C:\Access11Files\UserLevel.mdb;" &_  "UserID=Chap10Admin;Password=;"     'SetSQLstringtoaddanewuserandexecuteit str1= "CREATEUSERNewUser2[password]pidcode" .Execute(str1) 'Neededforviewingwithmanualopeningofdatabase str1= "GRANTSELECTONTABLEMSysAccessObjectsTONewUser2" .Execute(str1)     'GrantSELECTandUPDATEpermissionsforWebBasedListtable 'andattempttoselectandupdatefromthetable str1= "GRANTSELECT,DELETE,INSERT,UPDATE " &_  "ONTABLEWebBasedListTONewUser2" .Execute(str1) Debug.Print "OutcomewithSELECTandUPATEPermissions" SelectInsertDeleteAndUpdateWithNewUser2 'RevokeUPDATEpermissionforWebBasedListtable 'andattempttoselectandupdatefromthetable str1= "REVOKEUPDATE " &_  "ONTABLEWebBasedListFROMNewUser2" .Execute(str1) Debug.PrintvbLf& "OutcomewithUPDATEPermissionrevoked" SelectInsertDeleteAndUpdateWithNewUser2     EndWith     EndSub