How to organize security accounts
This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.
A Microsoft Access workgroup information file contains the following predefined accounts.
| Account | Function |
|---|---|
| Admin | The default user account. This account is exactly the same for every copy of Microsoft Access and other applications that can use the Microsoft Jet database engine, such as Microsoft Visual Basic for Applications and Microsoft Excel. |
| Admins | The administrator's group account. This account is unique to each workgroup information file. By default, the Admin user is in the Admins group. There must be at least one user in the Admins group at all times. |
| Users | The group account comprising all user accounts. Microsoft Access automatically adds user accounts to the Users group when a member of the Admins group creates them. This account is the same for any workgroup information file, but it contains only user accounts created by members of the Admins group of that workgroup. By default, this account has full permissions on all newly-created objects. The only way to remove a user account from the Users group is for a member of the Admins group to delete that user. |
In effect, security in Microsoft Access is always "on." Until you activate the logon procedure for a workgroup, Microsoft Access invisibly logs on all users at startup by using the default Admin user account with a blank password. Behind the scenes, Microsoft Access uses the Admin account as the administrator account for the workgroup, as well as the owner of any databases and tables, queries, forms, reports, and macros created.
Administrators and owners are important because they have permissions that can't be taken away:
Administrators (members of the Admins group) can always get full permissions for objects created in the workgroup.
An account that owns a table, query, form, report, or macro can always get full permissions for that object.
An account that owns a database can always open the database.
Because the Admin user account is exactly the same for every copy of Microsoft Access, the first steps in securing your database are to define administrator and owner user accounts (or use a single user account as both the administrator and owner account), and then to remove the Admin user account from the Admins group. Otherwise, anyone with a copy of Microsoft Access can log on to your workgroup by using the Admin account and have full permissions for the workgroup's tables, queries, forms, reports, and macros.
For example, to secure a database named Orders, you could create your own OrdersAdmin and OrdersOwner user accounts, and then add passwords to these accounts.
You can assign as many user accounts as you want to the Admins group, but only one user account can own the database itself the user account that is active when the database is created, or when ownership is transferred by creating a new database and importing all of a database's objects into it. However, group accounts can own tables, queries, forms, reports, and macros within a database.
Important
The accounts that you create for users of the database must be stored in the workgroup information file that those users will join when they use the database. If you're using a different file to create the database, change the file before creating the accounts.
Make sure to create a unique password for your administrator and user accounts. A user who can log on by using the administrator account can always get full permissions for any tables, queries, forms, reports, and macros created in the workgroup. A user who can log on using an owner account can always get full permissions for those objects owned by that user.
Organizing users in groups makes it easier to manage a secure database. With this strategy, rather than assign permissions to each user for each table, query, form, report, and macro in your database, you assign permissions to a few groups, and then add users to the appropriate group. When users log on to Microsoft Access, they inherit the permissions from any groups they belong to. Only user accounts can log on to Microsoft Access; you can't log on by using a group account.
For example, you could secure an Orders database by creating a Managers group for managers, a Sales Reps group for sales representatives, and a Staff group for staff employees. You can assign the least restrictive set of permissions to the Managers group, a more restrictive set of permissions to the Sales Reps group, and the most restrictive set of permissions to the Staff group. When you create a user account for a new employee, you add that account to the appropriate group. The employee then has the permissions associated with that group.
After you create user and group accounts, you can view the relationships between them by clicking User And Group Accounts on the Security submenu (Tools menu), and then clicking the Print Users And Groups button. Microsoft Access prints a report of the accounts in the workgroup, showing the groups to which each user belongs and the users that belong to each group.