Learn about Microsoft Access Security

access| Security | Safety profile
In earlier versions of Microsoft®access (prior to Microsoft Access 2000), knowledge about security was sometimes considered impossible for anyone to master and apply. You need to perform a number of steps sequentially, which can have disastrous consequences if you omit a step or reverse the sequence. With the advent of the security Wizard of Microsoft Access for Microsoft Windows®95, and the continuous improvement of security Wizard in Microsoft Access 2000, the ACCE Implementation of security in SS has become very simple. However, even with these help, you must be aware of your own security options and the actions to protect data and objects in your database. Otherwise, the light will bring data security risks, the heavy will lock you in your own database.

There are many ways to protect your Access database and the data it contains. In this article, we discuss ways to protect the objects that make up a database (including data), objects that contain elements such as forms and reports, and code, which may be the most valuable part of the database. The security techniques discussed in this article apply only to Microsoft Access database (. mdb) files.
Knowledge about protecting sensitive data that you need to know
You should know that there are many tools and third-party utilities that can be used to detect passwords for any type of database, as well as user names and passwords from any workgroup information file (the workgroup information file is described in detail later in this article). If you need to protect sensitive data from unauthorized access, the best security measure is to use file-level security and file-sharing security provided by your computer's operating system. File-level security involves setting permissions on the data file. File share security involves restricting access to data file storage locations. An example of file share security is to set user permissions on the folder where the data files are stored (on the local computer or on a network server). To do this, you can split the data into multiple files, set user permissions on those files, and then place the files in a protected file space. You can then link to these files from an Access database that has security settings.
Access Security Overview
The following sections describe several ways to protect an Access database.
Encrypt or decrypt a database
The simplest (and least secure) protection method is to encrypt the database. Encrypting a database is compressing the database files so that some utilities (such as word processors) cannot interpret them. Encrypting a database that does not have a security setting does not guarantee the security of the database because anyone can open the database and fully access all objects in the database. For more information about security settings for a database, see Using the security Wizard to set up secure Access databases later in this article.

Encryption prevents other users from accidentally accessing information in the database when the database is transmitted electronically or stored on a floppy disk, tape, or disc. However, the encryption method used by Jet (the database engine used by Access) is so weak that it must not be used to protect sensitive data. The Encrypt/Decrypt Database command is located in the Security submenu of the Tools menu. Decrypting a database is a inverse of the encryption process.
Using a custom interface
Another relatively simple protection scheme is to use a custom interface instead of the Access standard interface. As with encryption, it does not protect the security of objects and sensitive data in the database. By selecting the startup option on the Tools menu, you can specify a custom startup form, menu, or even a custom title and icon. You can also choose to cancel the Database window to hide these objects from application users who lack the appropriate technology. The features of the Startup dialog box can also be programmatically implemented. For more information about how to set startup options from the Startup dialog box, see "About startup Options" in Access help. For more information about how to set startup options programmatically, see Access help Microsoft Visual basic® Editor's options for setting startup options and encoding.
Set the database password
You can set a password on the database, which requires users to enter a password when accessing data and database objects.
Note: Using a password to secure a database or its objects is also known as shared-level security.
You cannot use this option to assign permissions to users or groups, so anyone with a password has unrestricted access to all Access data and database objects. The Set Database Password command is located in the Security submenu of the Tools menu.
User-level security
In addition to shared-level security, you can use user-level security, which provides the strictest access restrictions that allow you to maximize control over the database and the objects it contains. This is part of our recommended database protection (when used in conjunction with file-level and shared-level security provided by the operating system), so we'll cover user-level security in detail later in this article.

Similarly, we will discuss various ways to protect the Visual Basic for Applications (VBA) code contained in the database.
Warning: User-level security (when used alone) is primarily used to protect code and objects in the database, so that users do not accidentally modify or change them. If you do not want users to illegally access code in a form, report, or module, you must convert the. mdb file to an MDE file (described in more detail later in this article). The only way to prevent users from modifying queries, macros, or data access pages in a database is to place the database files in a protected file-sharing area. In addition, it is not possible in Access to both allow users to modify the data in a table, while preventing it from modifying the design of a table or deleting a table. To provide such a feature, you need to use a server-based database product, such as Microsoft SQL Server™. Set Module password
Use a password to protect all standard modules and class modules, such as those contained in forms and reports, lest users accidentally modify or view the VBA code. After you set a password, you only need to enter the password once per session to view or modify the code in the Visual Basic editor. In addition to viewing and editing, you need a password when you cut, copy, paste, export, or delete any module. It should be clear, however, that protecting your code with this method does not prevent you or other users from running the code, nor does it prevent other users from using Third-party utilities, such as the 16-in editor, to view the code. To fully protect your code, you must convert the. mdb file to an MDE file.

To set a password for a module in a project: Select Properties from the Tools menu in the Visual Basic editor for this project. In the Project Properties dialog box, click the Protection tab. Select the Lock project when viewing check box and type the password. In the Confirm Password box, retype the password, and then click OK. Using MDE files
By converting the database file to an MDE file, you can completely protect the code in access from illegal access. When you convert an. mdb file to an MDE file, Access compiles all modules, deletes all editable source code, and then compresses the target database. The original. mdb file will not be affected. The VBA code in the new database can still be run, but cannot be viewed or edited. The database will continue to work correctly, and you can still upgrade the data and run the report. In particular, saving an Access database as an MDE file prevents the following actions: View, modify, or create a form, report, or module in Design view. Add, delete, or change a reference to an object library or database. Use the properties or methods of the Access or VBA object model to change code-an MDE file does not contain editable code. Import or export a form, report, or module. tables, queries, data access pages, and macros can be imported from or exported to a non-MDE database.
To convert an. mdb file to an MDE file: Close the database. Click Database Utilities on the Tools menu. Click Generate MDE file. In the Save Database as MDE dialog box, locate the. mdb file, and then click Generate MDE. Note: The database created in Access 2002 uses the default Access 2000 file format. A database in Access 2000 file format can be converted to an MDE format only in Access 2000. To convert an. mdb file created in Access 2002 with the default Access 2000 format to an MDE file, you must first convert the file to an Access 2002 file format. To do this, you can first open the database exclusively (to open the database exclusively), see the manual setting up the Database Password section later in this article. Next, point to Database Utilities on the Tools menu, point to Convert Database, and then click Change to Access 2002 file format. The database can be converted to an MDE file.
For more requirements on converting. mdb files to MDE files, see "Securing Visual Basic for Applications code in an Access database" in Access Help.

Now let's take a look at user-level security.
About Access user-level security
Access uses the Microsoft Jet database engine to store and retrieve objects in the database. The Jet database engine uses a workgroup based security model, also known as user-level security, to determine who can open the database and secure the objects that the database contains. User-level security is always turned on for all Access databases, regardless of whether the security of the database is explicitly set. You can change the default security level in Access by manipulating the permissions and memberships of user and group accounts. This is described in the following.

Whenever you start the Access,jet database engine, you will find the workgroup information file (the default name is System.mdw, or you can use the extension. MDW to name it arbitrarily). The workgroup information file contains group and user information, including passwords, that determines who can open the database and their permissions on objects in the database. Permissions on individual objects are stored in the database. This way, for example, you can give a group of users (rather than other users) the right to use a particular table, while giving another group permission to view the report, but not the design of the report.

The workgroup information file includes built-in groups (Admins and users) and a common user account (Admin) that has permission to manage the database and the objects it contains (unrestricted). You can also add new groups and users by using the menu commands (the Security submenu on the Tools menu) or through VBA code.
Note: When you install Access, the Setup program automatically creates the workgroup information file and names it with the name and organization information you specify. Because this information is often easily judged, unauthorized users are likely to create another version of the workgroup information file, thereby setting themselves an irrevocable Administrator account (member of the Admins group) permission in the workgroup defined by the workgroup information file. To prevent this from happening, create a new workgroup information file and specify a unique workgroup ID (WID). This way, only users who know WID can create a copy of the workgroup information file. Later in this article, you will discuss creating a new workgroup information file using the User-level security Wizard.
The Admins group cannot be deleted and its members have administrative privileges that cannot be undone. You can remove permissions from the Admins Group by menu or code, but any member of the Admins group can add permissions again. In addition, the Admins group must always have at least one member of the management database. For databases that do not have security settings, the Admins group always contains the default Admin user account, which is also the account that all users log on by default.

All users must belong to the default Users group, regardless of whether they belong to another group or not. You can create user accounts in VBA without adding new user accounts to the Admins group. However, if you do not add the user account to the Admins group at the same time, the user will not be able to start Access successfully because many of the tables used within access that are used to manage the database are mapped to the Users group's permissions.

Use the following procedure to make security settings for an Access database: Add a new user account to the Admins group. The user has administrative permissions on the objects in the database. Remove the default Admin user account from the Admins group. Remove permissions from the Admin user and the Users group. Assign permissions to any custom groups that you create.
Setting a password on the default Admin user account activates the logon dialog box, prompting the user to enter a username and password each time you start Access. If you do not set a password on the admin account, the user will automatically log on as the Admin user without a password and the logon dialog box will not appear.

Permissions on objects in the database can be explicit (assigned directly to user accounts) or implicitly (inherited from the group to which the user belongs), or they can be combined. Access uses the "least restrictive" rule on permission issues, that is, the user's permissions include the sum of their explicit and implicit permissions. For example, if User A's account has restricted permissions, and user A belongs to a group with restricted permissions and another group with administrative (all) permissions, user A will have administrative privileges. For this reason, it is usually best not to assign explicit permissions to user accounts. Instead, you should create groups with different permissions, and then assign users to groups with the appropriate permissions, which can reduce database management headaches.

In the following sections, we show how to protect the database and the objects it contains through a user interface and programmatic means. First, let's look at several different ways to set the database password.
Manually set the database password
You can set a password for the database, which requires the user to enter a password when accessing the database. However, once a user logs on, they can access data and objects in the database without restrictions.
Note: Before setting the database password, it is recommended that you back up the database and store it in a secure location.
Before you set the database password, you need to open the database in an exclusive manner. To open the database exclusively: If the database is open, close the database. Click Open on the File menu to reopen the database. In the Open dialog box, locate the database, click the arrow next to the Open button, and then click Open Exclusive.
To manually set the database password: On the Tools menu, point to Security, and then click Set Database Password. In the Password box, type the password. Note: Passwords are case sensitive. In the Verify box, retype the password to confirm, and then click OK.
The database password is now set. The next time you or another user opens the database, a dialog box appears asking for a password. Let's look at how to set the database password programmatically.
Set the database password programmatically
Use the keyword ALTER database in the following syntax, which allows you to programmatically set, modify, or delete a database password.

ALTER DATABASE PASSWORD newpassword OldPassword

In this statement, the password is represented by a String value delimited by square brackets ([]), except as follows.

The first time a database password is set, the NULL keyword is used as the OldPassword parameter in the ALTER database statement. To remove a database password, use the NULL keyword as the newpassword parameter for the ALTER database statement. In these cases, the keyword NULL should not appear in the box.

Before you use the following procedure, you may need to set a reference to the Microsoft ADO EXT 2.5 for DDL and security library, if it is not already set: in the Visual Basic Editor, point to References on the Tools menu. The Reference dialog box appears. Select the Microsoft ADO Ext 2.5 for DDL and security check boxes.
See the following code for the first time you set your password:

Private Function Createdbpassword (ByVal Password As String, _ ByVal Path as String) as Boolean Dim objconn as ADODB. Connection Dim Stralterpassword as String on Error GoTo createdbpassword_err ' creates a SQL string to initialize a database password. Stralterpassword = "ALTER DATABASE PASSWORD [PASSWORD] NULL;" ' Open a database that does not have security settings. Set objconn = New ADODB. Connection with objconn. Mode = admodeshareexclusive. Open "Provider=Microsoft.Jet.OLEDB.4.0;Data" & _ "Source=path;" ' Execute the SQL statement to securely set up the database ... Execute (Stralterpassword) End With ' clears the object. Objconn.close Set objconn = Nothing ' If successful, returns TRUE. Createdbpassword = Truecreatedbpassword_err:msgbox Err.Number & ":" & err.description Createdbpassword = False En D Function

This procedure accepts the user's password and the path to the. mdb file. First, we declare a variable that represents a connection to a database that does not have a security setting, while declaring a String variable to contain the SQL statement we used to change the password. Next, we set Stralterpassword to a Jet SQL statement that uses the ALTER DATABASE PASSWORD keyword. Note that because we are not replacing the password, the second parameter is set to NULL. Next, we open a connection to the database. To set a password, you must open the database exclusively, so set the Mode property. Then execute the SQL statement. You can usually run this procedure from a database to set a password in a separate database that does not have security settings. If everything works, the function returns TRUE.

If you want to change the password for a database that has security settings, you first need to log in to the database with your old password, and then change your password. The following procedure shows this technique.

Before you use the following procedure, you may need to set a reference to the Microsoft ADO EXT 2.5 for DDL and security library, if it is not already set: in the Visual Basic Editor, point to References on the Tools menu. The Reference dialog box appears. Select the Microsoft ADO Ext 2.5 for DDL and security check boxes.
Take a look at the following procedure:

Private Function Changedbpassword (ByVal OldPassword As String, _ ByVal NewPassword As String, ByVal Path as String) as Boo Lean Dim objconn as ADODB. Connection Dim Stralterpassword as String on Error GoTo changedbpassword_err ' creates a SQL string to change the database password. Stralterpassword = "ALTER DATABASE PASSWORD [NewPassword] [OldPassword];" ' Open the database with security settings. Set objconn = New ADODB. Connection with objconn. Mode = admodeshareexclusive. Provider = "Microsoft.Jet.OLEDB.4.0". Properties ("Jet oledb:database Password") = "OldPassword". Open "Data Source=path;" ' Execute the SQL statement to change the password ... Execute (Stralterpassword) End With ' clears the object. Objconn.close Set objconn = Nothing Changedbpassword = Truechangedbpassword_err:msgbox Err.Number & ":" & Err.Des Cription Changedbpassword = False End Function

This procedure is similar to the previous subroutine, except that when you log on to a database with security settings, you need to use the old password before the change. To do this, we set the database Password property of the Connection object for the databases to be changed. This is one of the extended properties of the Connection object, so we use the special syntax shown above. You can also use this procedure to remove a password from a database that has security settings by simply replacing the first parameter of the ALTER DATABASE statement with the NULL keyword.

Let's look at how to implement a more powerful protection than setting up passwords in a database.
To set security for an Access database by using Wizard
You can use the security Wizard to set up your database. To do this, follow these steps: Open the database for which you want to set security. On the Tools menu, point to Security, and then click the Set Security Wizard. The Set Security Wizard dialog box appears. Because we want to create a new workgroup information file, make sure that this option is selected, and then click Next. In the second screen of the Set Security Wizard dialog box (see Figure 1), type a new name for the workgroup information file, or accept the default name. Type a workgroup ID (WID). WID a Admins group that uniquely identifies this workgroup file. Next, type your name and organization in the appropriate text box. Although these inputs are optional, it is best to fill them in, because only those who know the information can recreate the workgroup information file.

Figure 1: The second screen of the Set Security wizard dialog box
You can also choose whether to use this workgroup information file as the default file for all databases, or just for this particular database. Click the Create shortcut, open the security-set database option, and then click Next. In the next screen (see Figure 2), you can select an object that you want the wizard to set its security mechanism. By default, the wizard sets security for all existing objects and all newly created objects. Click "Next".

Figure 2: The third screen of the Set Security wizard dialog box
In the next screen (see Figure 3), you can select an optional security group with specific permissions. We strongly recommend that you use groups to manage users ' access rights, rather than giving each user specific permissions. Select any other group account, and then click Next.

Figure 3: The fourth screen of the Set Security wizard dialog box
In this screen (see Figure 4), you can set specific permissions for the Users group. Because all users are automatically members of the Users group, any permissions that are selected here apply to all users, so it is a good idea to restrict the group's permissions or leave the default settings-no permissions. Click "Next".

Figure 4: The fifth screen of the Set Security wizard dialog box
In the next screen (see Figure 5), you can add users to the workgroup information file. You can also select a password and a personal ID (PID) for each user. Access uses the PID and account name to identify a unique user for the workgroup. Add the user and click Next. Note: The personal identifier (PID) is not a password. It is encrypted and creates a unique system identifier (SID) with the name to identify the user.

Figure 5: The sixth screen of the Set Security wizard dialog box
In this screen (see Figure 6), you can add users to the groups in the workgroup information file. When you have made your selections, click Next.

Figure 6: The seventh screen of the Set Security wizard dialog box
In the last screen, select a name for the backup of the database that does not have security settings. It is necessary to secure a backup of this original database in case of loss or damage to a database with security settings. Click Finish. The original database will be saved with a. bak extension. Warning: When you create a workgroup information file, security Wizard creates a report that contains all the information needed to re-create the workgroup information file. You must save this information to recreate the file when the original file is missing or corrupted. When you click Finish, security Wizard encrypts the database and then asks you to reopen the database that was secured with the new workgroup information file.
Let's look at several ways to use users and groups programmatically.
Adding and removing Users and groups programmatically
After you have set up security for your database, you may want to use users and groups. Some of these tips are shown in the following sections.

Before you use the procedure that is described in the following sections, you may need to set a reference to the Microsoft ADO EXT 2.5 for DDL and security library, if not already set: in the Visual Basic Editor, point to References on the Tools menu. The Reference dialog box appears. Select the Microsoft ADO Ext 2.5 for DDL and security check boxes. Adding and removing users
The following procedure creates a new user account and then appends it to the default Users group in the workgroup information file for the current database.
Note: To use the following example in Access, you need to log on as a member of the Admins group and open a database. In the following procedure, you want to ensure that the workgroup information file does not contain users whose names are specified in struser. For example, you can call the DeleteUser subroutine first to ensure this.
Please see the following code:

Private Function AddUser (ByVal struser As String, _ ByVal Strpid As String, _ Optional ByVal strpwd As String) as Boolean Dim CatDB as ADOX. Catalog on Error GoTo adduser_err ' Instantiate Catalog object. Set catdb = New ADOX. Catalog with CatDB ' uses a connection to the current database to open the Catalog object ... ActiveConnection = CurrentProject.Connection ' Create a new user account ... Users.append struser, strpwd, Strpid ' append a new user account to the default Users group ... Groups ("Users"). Users.append struser End With ' closes the Catalog object. Set CatDB = Nothing AddUser = Trueadduser_err:msgbox Err.Number & ":" & err.description AddUser = False End funct Ion

The procedure first declares a variable for the Catalog object, and then instantiates the object.
Note: The Catalog object is a container for all objects in an Access database file.
The procedure then opens the connection to the current database and appends the new user to the Users collection of the Catalog object, using the arguments from the calling procedure. The new user is then appended to the default Users group. The Users collection contains all the users of the database defined in the workgroup information file.

To delete an existing user, you can use the following procedure:

Private Function DeleteUser (ByVal struser as String) as Boolean Dim CatDB as ADOX. Catalog on Error GoTo deleteuser ' Instantiate Catalog object. Set catdb = New ADOX. Catalog with CatDB ' opens the Catalog object in the current database ... ActiveConnection = CurrentProject.Connection ' Delete struser ... Users.delete struser End With ' closes the Catalog object. Set CatDB = Nothing deleteuser = Truedeleteuser_err:msgbox Err.Number & ":" & err.description = DeleteUser nd Function

This procedure is similar to the previous procedure, except that the user specified in the struser String parameter was deleted by using the Delete method of the Catalog object.
Adding and removing groups
The process of adding a group is similar to the process of adding a user.

Private Function addgroup (ByVal Strgroup As String, _ ByVal Strpid as String) as Boolean Dim CatDB as ADOX. Catalog on Error GoTo addgroup_err Set catdb = New ADOX. Catalog with CatDB ' opens the Catalog object in the current database ... ActiveConnection = CurrentProject.Connection ' Create a new group ... Groups.append Strgroup, Strpid end With ' closes the Catalog object. Set CatDB = Nothing addgroup = Trueaddgroup_err:msgbox Err.Number & ":" & err.description = AddGroup FalseEnd Ction

This procedure first instantiates the Catalog object and then opens a connection to the current database. Next, the new group is appended to the Groups collection of the Catalog object by using the arguments from the calling procedure.

To delete an existing group, you can use the following procedure:

Private Function DeleteGroup (ByVal strgroup as String) as Boolean Dim CatDB as ADOX. Catalog on Error GoTo deletegroup_err Set catdb = New ADOX. Catalog with CatDB ' opens the Catalog object in the current database ... ActiveConnection = CurrentProject.Connection ' Delete Strgroup ... Groups.delete Strgroup End With ' closes the Catalog object. Set CatDB = Nothing DeleteGroup = Truedeletegroup_err:msgbox Err.Number & ":" & err.description = DeleteGroup Seend Function

This procedure is similar to the previous procedure, except that the group specified in the Strgroup String parameter was deleted by using the Delete method of the Catalog object.

Let's take a look at how to programmatically set permissions on database objects.
Setting Permissions Programmatically
To set permissions on various objects in the database, you can use the SetPermissions method of the Group or User object. In the following procedure, we first undo all the permissions for the group, and then give the group specific permissions. This ensures that the group has only the permissions we specify:

Private Function setgrouppermissions (ByVal Strgroup As String, _ ByVal strtable As String, ByVal strObjectType as String, _ ByVal Straction As String, _ ByVal Strrevokeenum as String) as Boolean Dim CatDB as ADOX. Catalog on Error GoTo setgrouppermissions_err Set catdb = New ADOX. Catalog with CatDB ' opens the Catalog object in the current database ... ActiveConnection = CurrentProject.Connection ' Revoke all permissions for the group ... Groups (Strgroup). SetPermissions tbltable, _ strObjectType, straction, Strrevokeenum ' assigns specific permissions to the group. Groups (Strgroup). SetPermissions tbltable, _ strObjectType, straction, _ Adrightread or Adrightinsert or adrightupdate end With ' Close Catalog Object. Set CatDB = Nothing setgrouppermissions = Truesetgrouppermissions_err:msgbox Err.Number & ":" & Err.Description S Etgrouppermissions = False End Function

After opening a Catalog object in the current database, we use the SetPermissions method of the Groups collection to undo all the permissions that the group has on the Employees table. The first parameter is the name of the table, and the second argument shows the type of the object, which is the table. The third parameter specifies the type of action to be performed when the permission is set, and the fourth parameter is a permission constant that specifies that the group does not have any permissions. We have revoked all of the group's permissions on the Employees table and can now give it the permissions it wants.

The first three parameters of the next statement are the same as in the previous statement. The fourth parameter is a value created by combining different permission constants by using the OR operator. Here, we give permission to read, insert, and update the table.

To set permissions on all new objects of the specified type, such as the table in the preceding example, change the first argument in the statement that is used to give permission to the NULL keyword. For example:

... catdb.groups (strgroup). SetPermissions NULL, adpermobjtable ...


Summary
In this article, we discussed various ways to implement different levels of protection for an Access database, introducing both shared and user-level security. It also describes how to use Access security Wizard and how you can programmatically implement secure settings.