Microsoft Access Security

Introduction
In earlier versions of Microsoft®In Access (before Microsoft Access 2000), security-related knowledge is sometimes considered impossible for anyone to master and apply. You need to perform many steps in order. If you omit or reverse a step, it will have disastrous consequences. With Microsoft Access for Microsoft Windows®The emergence of Security Wizard of 95 and the continuous improvement of Security Wizard of Microsoft Access 2000 have made it very easy to implement Security in Access. However, even with these help, you must be aware of your security options and master the operations to protect data and objects in the database. Otherwise, data security risks may occur to the light user, and the heavy user will lock you out of your own database.

There are many ways to protect your Access Database and the data contained in it. In this article, we will discuss the objects and code used to protect the various objects (including data) that make up the database, including elements such as forms and reports (which may be the most valuable part of the database). The security techniques discussed in this article only apply to Microsoft Access database (. mdb) files.

Knowledge about sensitive data protection
You should know that there are many tools and third-party utilities that can be used to detect passwords of any type of database, and the username and password from any workgroup information file (this article will detail the workgroup information file ). To protect sensitive data from unauthorized access, the best security measure is to use the file-level security and file sharing security provided by the computer operating system. File-level security involves setting permissions on data files. File Sharing Security restricts access to data file storage locations. An example of file sharing security is to set user permissions on the folder where data files are stored (on a local computer or network server. To this end, you can split data into multiple files, set user permissions on these files, and then place these files in protected file sharing spaces. You can then link to these files from the Access database with security settings.

Access Security Overview
The following sections describe several methods to protect the Access database.

Encrypt or decrypt a database
The simplest (and most secure) protection method is to encrypt the database. Encrypted databases compress database files so that some utilities (such as word processors) cannot interpret these files. Encrypting a database with no security settings does not guarantee database security, because anyone can open the database and access all objects in the database. For more information about database Security settings, see "Security Wizard" later in this article.

Encryption prevents other users from accidentally accessing the 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 very weak, so it cannot be used to protect sensitive data. The "encrypt/decrypt Database" command is located in the "Security" sub-menu of the "Tools" menu. Database decryption is a reverse operation of the encryption process.

Use the custom Interface
Another relatively simple protection solution is to use a custom interface instead of the Access standard interface. Like encryption, it cannot protect the security of objects and sensitive data in databases. By selecting the "Start" option in the "Tools" menu, you can specify a custom startup form, menu, or even custom title and icon. You can also choose to cancel the Database window to hide these objects for application users who lack the corresponding technology. The functions of the "Start" dialog box can also be programmed. For more information about how to set startup options from the "Start" dialog box, see "about startup options" in Access help ". For more information about how to set startup options through programming, see Access help Microsoft Visual Basic®In the editor, set the 'start' option and encoding options ".

Set Database Password
You can set a password on the database to require users to enter a password when accessing data and database objects.

Note: using passwords to protect the security of databases or objects is also known as shared-level security.
You cannot use this option to assign permissions to users or groups. Therefore, anyone with a password can Access all Access data and database objects without restriction. The "set Database Password" command is located in the "Security" sub-menu in the "Tools" menu.

User-level security
In addition to shared-level security, you can also use user-level security. It provides the strictest access restriction, allowing you to control the database and its objects to the maximum extent. This is part of our recommended database protection measures (when used in combination with the file-level and shared-level security provided by the operating system ), therefore, we will introduce user-level security in detail later in this article.

Similarly, we will discuss various methods to protect the Visual Basic for Applications (VBA) Code contained in the database.

Warning user-level security (when used separately) is mainly used to protect the code and objects in the database, so that users do not accidentally modify or change the data. If you do not want to illegally access the code in a form, report, or module, you must convert the. mdb file to the MDE file (this article will be detailed later ). To prevent users from modifying query, Macro, or data access pages in the database, the only way is to put the database files in a protected file sharing area. In addition, in Access, it is impossible for users to modify the data in the table and prohibit them from modifying the design or deletion of the table. To provide such a function, you need to use a Server-based database product, such as Microsoft SQL Server & #8482 ;.
Set module Password
Password can protect all standard modules and class modules (such as the code contained in forms and reports) so that you do not accidentally modify or view VBA code. After setting the password, you only need to enter the password once each session to view or modify the code in the Visual Basic Editor. In addition to viewing and editing, you also need a password to cut, copy, paste, export, or delete any module. However, it should be clear that using this method to protect the Code cannot prevent you or other users from running the code, nor prevent other users from using third-party utilities (such as a hexadecimal Editor) to view the code. To fully protect the code, you must convert the. mdb file to the MDE file.

To set a password for a module in the project:

Select the "attribute" command from the "Tools" menu of the Visual Basic Editor for this project.
In the "Project Properties" dialog box, click the "protection" tab.
Select the "Lock project at view" check box and enter the password.
In the "Confirm Password" box, retype the password and click "OK ".
Use MDE files
By converting database files to MDE files, you can completely protect the code in Access from unauthorized Access. When the. mdb file is converted to the MDE file, Access will compile all modules, delete all editable source code, and compress 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 continues to work normally, and you can still upgrade data and run reports. In particular, saving an Access database as an MDE file can prevent the following operations:

View, modify, or create a form, report, or module in the design view.
Add, delete, or modify references to an object library or database.
Use Access or VBA object model properties or methods to change the code-MDE file does not contain editable code.
Import or export forms, reports, or modules. Tables, queries, data access pages, and macros can be imported to or exported from non-MDE databases.
To convert a. mdb file to an MDE file:

Shut down the database.
Click database utility in the Tools menu ".
Click Generate MDE file ".
In the "Save database as MDE" dialog box, find the. mdb file and click "generate MDE ".
Note: The database created in Access 2002 uses the default Access 2000 file format. Databases in the Access 2000 file format can only be converted to the MDE format in Access 2000. To convert a. mdb File Created in the default Access 2002 format to an MDE file in Access 2000, you must first convert the file to the Access 2002 file format. To do this, you can first open the database in an exclusive mode (to open the database in an exclusive mode, refer to the manual setting of the Database Password section later in this article ). Next, point to "database utility" in the "Tools" menu, point to "Convert Database", and click "convert to Access 2002 File Format ". The database can be converted to the MDE file.
For more requirements on converting. mdb files to MDE files, see "protect the Visual Basic for Applications code in the Access Database" in Access help ".

Let's take a look at user-level security.

Access user-level security
Access uses the Microsoft Jet Database Engine to store and retrieve objects in a 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 protect the security of the objects contained in the database. Whether or not the security of the database is explicitly set, user-level security is always enabled for all Access databases. You can change the default security level in Access by manipulating the permissions and identities of users and group accounts. This topic is described below.

Whenever Access is started, the Jet database engine needs to find the workgroup information file (the default name is system. mdw, or any name can be used with the extension. mdw ). The workgroup information file contains group and user information (including passwords), which determines who can open the database and their permissions on objects in the database. Permissions on a single object are stored in the database. In this way, for example, users in A group (rather than other users) can be granted the permission to use specific tables, while users in another group can be granted the permission to view reports, but the report design cannot be modified.

The workgroup information file includes a built-in group (Admins and Users) and a general user account (Admin), which has the permission to manage databases and their objects (unrestricted ). You can also use menu commands ("security" sub-menu in the "Tools" menu) or use VBA code to add new groups and users.

Note: When Access is installed, the installer automatically creates the workgroup information file and uses the name and organization information you specified to name it. Because this information is often easily identified, unauthorized users are likely to create a working group information file of another version, which is defined in the working group defined in the Working Group information file, set an irrevocable Administrator Account (members of the Admins group) for yourself. To prevent this, create a new workgroup information file and specify a unique workgroup ID (WID ). In this way, only users who know the WID can create a copy of the workgroup information file. This article will discuss how to use User-level Security Wizard to create a new workgroup information file.
The Admins group cannot be deleted, and its members have unrecoverable management permissions. You can delete the permissions of the Admins group through the menu or code, but any member of the Admins group can re-add permissions. In addition, The Admins group must always have at least one member managing the database. Admins Group