User Level Security Using ADO

In addition to DAO code, the Microsoft ACE Database Engine can employ ADO code to set up groups and users and to set up permissions for user-level security.

Groups and users are set up using the SQL statements CREATE, ALTER, and DROP, which, as their names suggest, create, alter (or modify) and drop (or delete) user groups and users. The GROUP and USER keywords indicate whether the action is for a user group or user. The ADD and DROP statements add users to a group or remove them from a group.

GRANT and REVOKE statements grant permissions to or remove permissions from a specific object for a group or individual user. The SELECT, INSERT, UPDATE, and DELETE keywords indicate which permissions are granted or revoked. The table near the end of this chapter defines the meaning of these keywords and additional keyword options.

These routines do not provide any error trapping, which is usually recommended for full implementations of subroutines. Handling basic problems to suit your specific needs enables you to present custom error messages that users can easily understand. For the purpose of this exercise, however, code is concise. The following declarations are used in the procedures that show how to use ADO to set up userlevel security. Also, you define a SetGlobals subroutine to quickly set these sample database settings for the VBA code examples.

Dim strDBPath As String Dim strMDWPath As String Dim strAdminUser As String Dim strAdminPass As String

Enum eObjectTypes Database = 1 Container = 2 Table = 3 Other = 4 End Enum



strDBPath = CurrentProject.Path & 1


strMDWPath = CurrentProject.Path &


strAdminUser = "Admin"

strAdminPass = ""



0 0

Post a comment