Managing Access (JET) Security with DAO

Security in Access is based on the workgroup model, which is conceptually similar to the user-level security model employed by the Windows operating system. In contrast to database-level security models employed by other desktop database systems, Access workgroup information is stored in a file that can reside on a network share. Using this approach, the same security system can be shared by many databases, rather than having to create a separate security system for every instance of your database. It enables you to simplify security maintenance by adding or removing users and groups, or changing permissions in one centralized file.

Microsoft Access security is always present and always enabled; it is not something that can be disabled. You just don't notice it because of the default workgroup and several default users and groups.

Because DAO acts only as an interface to security in the Access database engine. For those who want to learn about Access database engine security in greater detail, there are several excellent books on the subject that you can read. This tutorial discusses only those aspects of the DAO object model that directly relate to security; specifically how to manage users, groups, and permissions in the Access database engine using code.

Beginning with Access 2007, user-level security is no longer supported for new file formats. This means that you cannot assign permissions to database objects such as tables and queries, or Access objects such as forms and reports in ACCDB files. User-level security is still supported for MDB files. However, the DAO object model related to users and groups has been hidden. Your code will continue to run, although DAO objects such as User and Group will not appear in the Object Browser unless you select Show Hidden Members from the browser's context menu.

DAO deals with Access security in two ways. First, the Workspace object maintains two security-related collections: Groups and Users. Each Group object maintains a Users collection that contains information about all the users who belong to that group. Similarly, each User object contains a Groups collection that lists the groups to which that user belongs. Second, Access and database engine objects (for example, tables, forms, and so on) each have a Permission object that stores information about the permissions a user has to that object.