Introduction
In earlier versions of Microsoft® Access (previously in Microsoft Access 2000), knowledge about security is sometimes considered to be unable to master and apply anyone. You need to perform many steps in order, once you miss a step or reverse the order, it will bring catastrophic consequences. As Microsoft Access For Microsoft Windows® 95's Security Wizard appears, and the continuous improvement of Microsoft Access 2000 Security Wizard is very simple to implement in Access. However, even if you have these help, you must also know your own security option and master the operation of protecting data and objects in the database. Otherwise, the light will bring data security hidden dangers, and you will lock you outside your own database.
There are many ways to protect your Access database and the data contained therein. In this article, we will discuss each object (including data) for protecting the components such as forms, such as forms, such as forms and reports, and code (probably the most valuable part of the database). The security techniques discussed herein apply only to the Microsoft Access Database (.mdb) file.
What you need to know about protecting sensitive data
You should know that there are many tools and third-party utilities to detect the password of any type of database, as well as usernames and passwords from any working group information file (later describe the workgroup information files later). If you need to protect sensitive data from illegal access, the best security measures are the use of file-level security and file sharing security provided by the computer operating system. File level security involves setting permissions on a data file. Document sharing security involves access to data file storage locations. One example of file sharing security is set up user permissions on a folder (located on the local computer or web server) of the stored data file. To do this, you can split the data into multiple files, set the user permissions on these files, and then place these files into protected file sharing spaces. These files can then be connected to the Access database with secure settings.
Access security overview
The following sections describe several ways to protect the Access database.
Encryption or decrypt database
The easiest (minimum security) protection method is to encrypt the database. Encrypted databases are compressed database files, making certain utilities (such as word processors) cannot interpret these files. Encrypting a database that does not have a secure settings does not guarantee the secure database, as anyone can open the database and fully access all objects in the database. For more information on secure settings for the database, see the security of the Access database using Security Wizard later in this article.
Encryption can avoid information in the database in an electronically transmitting database or when it is stored on a floppy disk, a tape or disc. However, the encryption method used by Jet (database engine used by Access) is very weak, so it is absolutely to protect sensitive data. The Encryption / Decryption Database command is located in the "Security" submenu of the Tools menu. The decryption database is the counterputing of the encryption process.
Use custom interfaces
Another relatively simple protection scheme is to use a custom interface instead of the Access standard interface. Like encryption, it does not protect the security of objects and sensitive data in the database. By selecting the "Start" option in the Tools menu, you can specify a custom startup form, menu, and even custom headers and icons. 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 Launch dialog can also be implemented. For more information on how to set up the boot option from the Startup dialog, see "About Start Options" in Access Help. For more information on how to set up the options by programming, see the Access Help Microsoft Visual Basic® Editor's "Settings 'Start' Options and Options in Codes". Set the database password
You can set a password on the database to require users to enter a password when accessing data and database objects.
Note: Use password to protect the database or the security of the object therein, also known as shared level security.
You cannot use this option to assign permissions for users or groups, so anyone who has a password can access all Access data and database objects without restrictions. The "Setting the Database Password" command is located in the "Security" submenu of the Tools menu.
User-level security
In addition to the safety level, you can use user-level security, which provides the most stringent access restrictions that allow you to maximize the database and the objects included. This is part of our database protection measures (when used in combination with the file level and sharing level security provided by the operating system), we will introduce the user-level security after this article.
Similarly, we will also discuss various methods for protecting the Visual Basic for Applications (VBA) code included in the database.
WARNING: User-level security (when used alone) is mainly used to protect the code and objects in the database, so as not to modify or change the user accidentally. If you do not want the user to illegally access the code in the form, report, or module,
The .mdb file must be converted to the MDE file (described later in this article). To avoid users to modify queries, macro, or data access pages in the database, the only way is to place database files in a protected file sharing area. In addition, it is impossible to allow the user to modify the data in the table in Access, and it is forbidden to modify the design or delete table. To provide such a function, you need to use a server-based database product, such as Microsoft SQL ServerTM.
Set module password
Use your password to protect all standard modules and class modules (such as the code contained in the form and report) to prevent users from being modified or viewing VBA code. After setting your password, you only need to enter a password at each session to view or modify the code in the Visual Basic Editor. In addition to viewing and editing, you also need a password when cut, copy, paste, export or delete any modules. It should be clear that the use of this method protection code cannot prevent you or other users from running code, nor does other users from viewing the code using a third-party utility (such as a 16-binding editor). To fully protect the code, you must convert the .mdb file to the MDE file.
To set a password for the module in the project:
Select the Properties command from the Tools menu of the Visual Basic Editor. In the Project Properties dialog, click the Protection tab. Select the "View Time Lock Project" check box and type your password. In the Confirm Password box, retrore password, then click OK.
Use MDE file
By converting database files to MDE files, you can fully protected the code in Access from illegal access. When converting the .mdb file to the MDE file, Access will compile all modules to delete all editable source code and then compress the target database. The original .mdb file will not be affected. The VBA code in the new database is still running, but you cannot view or edit. The database will continue working properly and you can still upgrade data and run reports. In particular, save the Access database as the MDE file to prevent the following: View, modify, or create a form, report or module in the design view. Add, delete or change references to the object library or database. Change the code - MDE file does not contain editable code using the properties or method of the ACCESS or VBA object model. Import or export forms, reports, or modules. Table, query, data access page, and macros can import non-MDE databases, or export from it.
To convert the .mdb file to the MDE file:
Turn off the database. Click Database Utility in the Tools menu. Click "Generate MDE File". In the "Save Database as the MDE" dialog, find the .mdb file, and then click Generate MDE.
Note: The database created in Access 2002 uses the default Access 2000 file format. The database of Access 2000 file format can only be converted to the MDE format in Access 2000. To convert the .mdb files in the default Access 2000 format to the MDE file in Access 2002, you must first convert files to Access 2002 file format. To this end, you can first open the database in an exclusive way (to open the database in an exclusive way, see the following
Manually set the database password section). Next, point to "Database Utilities" in the Tools menu, point to "Convert Database", and then click "Go to Access 2002 File Format". The database can be converted to the MDE file.
For more requirements that convert .mdb files to MDE files, see "Protecting Visual Basic for Applications Code in Access Database" in Access Help.
Let's take a look at user-level security.
About Access user 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 protect the security of the objects included in the database. Regardless of whether the database is secured, user-level security is always open at all Access databases. You can change the default security level in Access by manipulating permissions and membership of the user and group account. This will be described below.
Whenever you start Access, the Jet database engine is looking for a workgroup information file (the default name is System.mdw, or you can use the extension .mdw any name). Workgroup information files contain groups and user information (including passwords), which determines who can open the database and their permissions to objects in the database. The permissions for a single object are stored in the database. Thus, for example, a user (rather than other users) can give the permissions of the specific table to view the permissions of the report, but cannot modify the design of the report.
Working group information files include a built-in group (ADMINS and USERS), and a universal user account (admin), which has permission to manage the database and the object therebet (unlimited). You can also add new groups and users to the "Security" submenu in the menu command ("Tools" menu.
Note: When installing Access, the installer automatically creates a workgroup information file and name it using the name and unit information you specify. Because this information is often easily judged, so unauthorized users are likely to create another version of the workgroup information file, so that they can set it with themselves in the working group defined by the working group information file. Undo Administrator Account (member of the Admin Group) permission. To prevent this, you should create a new workgroup information file and specify a unique working group ID (WID). In this way, only the WID users can create a copy of the workgroup information file. This article will discuss the use of the User-Level Security Wizard to create a new workgroup information file.
The Admin Group cannot be deleted, and its members have irreparable administrative privileges. You can delete permissions for the Admin Group by menu or code, but any member of the Admin Group can re-add permissions. In addition, there must always be a member of the administrative database in the Admin Group. For databases without security settings, the Admins group always contains the default admin user account, which is also an account that all users log in.
All users must belong to the default User group, whether they still belong to other groups. You can create user accounts in VBA and do not add new user accounts to the Admin Group. However, if the user account is added to the Admin Group at different times, the user will not successfully start Access because many tables used to manage the database inside the Access are mapped to the User Group permissions.
Use the following steps to make the security settings for Access databases:
Add new user accounts to the Admins group. This user has administrative privileges for objects in the database. Remove the default admin user account from the Admin group. Delete permissions from the Admin user and the UserS group. Assign permissions for any custom group created.
Setting the password on the default admin user account activates the login dialog, so that the user enters the username and password each time you start Access. If you do not set a password on the Admin account, the user will automatically log in as an Admin user, no password, nor does the login dialog box.
Permissions to objects in the database can be explicit (directly assigned to user accounts) or implicit (inheritance from the user belonging), or the combination of both. Access uses the "minimum restriction" rule on permission issues, that is, the user's permissions include the sum of its explicit and implicit privileges. For example, if the account of the user A has restricted rights, and the user A belongs to a group with restriction authority, and it is also a group with management (all) permissions, then user A will have administrative privileges. In view of this, it is usually not to assign explicit permissions for the user account. You should create a group with different permissions, and then assign users to groups with appropriate permissions, which reduces trouble in database management.
In the following sections, we will show how to protect the database and the objects therebetween through the user interface and programming means. First, let's take a few different ways to set up the database password.
Manually set the database password
You can set a password for the database, requiring the user to enter the password when accessing the database. However, once the user logs in, the data and objects in the database can be accessed unrestricted.
Note: Before setting the database password, it is recommended to back up the database and store it in a secure location.
Before setting the database password, you need to open the database in an exclusive way. To open the database with exclusive ways:
If the database is open, turn off the database. Click "Open" in the File menu to reopen the database. In the "Open" dialog box, find the database, click the arrow next to the "Open", and then click "Open in exclusive way". To manually set the database password:
In the Tools menu, point to Safe, then click Set Database Password. In the Password box, type a password.
Note: The password is case sensitive.
In the Verification box, retrore your password to confirm, and then click OK.
The database password is now set. The next time you or other users open the database, a dialog box requests to enter a password. Let's take a look at how to set up the database password by programming.
Set the database password by programming
Use the keyword ALTER DATABASE as follows, you can program, modify, or delete database passwords.
Alter Database Password NewPassword Oldpassword
In this statement, the password is represented by square brackets ([]) String value, but the following exceptions are described below.
Using the NULL keyword as the Oldpassword parameter in the ALTER DATABASE statement when setting up the database password. To delete 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 using the following procedure, you may need to set a reference to the Microsoft ADO Ext 2.5 for DDL And Security library (if not yet set):
In the Visual Basic Editor, point to "References" in the Tools menu. The Reference dialog will appear. Select the Microsoft ADO Ext 2.5 for DDL and Security check box.
Please see the following first time the code is set up:
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
'Creating a SQL string to initialize a database password.
StralTerpassword = "ALTER DATABASE Password" NULL; "
'Open the database that does not have a security setting.
Set objconn = new adoDb.connection
With objconn
.Mode = AdmodesharexClusive
.Open "provider = microsoft.jet.oledb.4.0; data" & _
"Source = path;"
'Executive SQL statement to securely set the database.
.Execute (StralTerpassword)
End with
'Clear the object.
Objconn.close
Set objconn = Nothing
'If successful, return to TRUE.
Createdbpassword = true
CREATEDBPASSWORD_ERR:
MsgBox Err.Number & ":" & Err.Description
Createdbpassword = false
END FUNCTION
This process 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 include the SQL statement we used to change the password. Next, we set StralTerPassword to the Jet SQL statement using the Alter Database Password keyword. Please note that because we are not to replace 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 in exclusive ways, so set the MODE attribute. 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 a secure setting. If everything is normal, the function returns True. If you want to change the password with a secure settings, first you need to use the old password to log in to the database, then change your password. The following procedure shows this technology.
Before using the following procedure, you may need to set a reference to the Microsoft ADO Ext 2.5 for DDL And Security library (if not yet set):
In the Visual Basic Editor, point to "References" in the Tools menu. The Reference dialog will appear. Select the Microsoft ADO Ext 2.5 for DDL and Security check box.
Please see the following procedure:
Private function changebpassword (byval oldpassword as string, _
Byval NewPassword As String, ByVal Path As String) AS Boolean
DIM Objconn as adodb.connection
DIM Stralterpassword As String
ON Error Goto ChangeDbpassword_err
'Creating a SQL string to change the database password.
StralTerpassword = "Alter Database Password [newpassword] [oldpassword];"
'Open the database with secure settings.
Set objconn = new adoDb.connection
With objconn
.Mode = AdmodesharexClusive
.Provider = "Microsoft.jet.OleDb.4.0"
.Properties ("Jet OLEDB: Database Password") = "Oldpassword"
.Open "Data Source = path;"
'Execute SQL statements to change your password.
.Execute (StralTerpassword)
End with
'Clear the object.
Objconn.close
Set objconn = Nothing
Changedbpassword = TRUE
ChangeDbpassword_err:
MsgBox Err.Number & ":" & Err.Description
Changedbpassword = false
END FUNCTION
This process is similar to the previous subroutine, just when logging in with a secure settings, you need to use the old password before the changes. To do this, for the database to be changed, we set the Database Password property of the Connection object. This is one of the extended properties of the Connection object, so we use the special syntax as shown above. To delete a password from a database with secure settings, you can use this procedure, just replace the first parameter of the ALTER DATABASE statement to Null keywords. Let's take a look at how to achieve a more powerful protection for setting passwords in the database.
Set security for Access database using Security Wizard
You can use Security Wizard to set the security of the database. To do this, do the following steps:
Open the database to set security. In the Tools menu, point to Safe, then click Set Security Mechanism Wizard. The Set Security Mechanism Wizard dialog will appear. Because we want to create a new workgroup information file, make sure this option is checked, and then click Next. In the second screen of the Set Security Mechanism Wizard dialog (see Figure 1), type a new name for the Working Group Information file or accept the default name. Type a working group ID (WID). The WID uniquely identifies the Admins group of this working group file. Next, type your name and unit in the corresponding text box. Although these inputs are optional, it is best to fill it, because people who know this can recreate the workgroup information file. Figure 1: Second screen of the Set Security Mechanism Wizard dialog All You can also choose whether to use this workgroup information file as the default file for all databases, or just for this specific database. Click "Creating a shortcut to open the database settings set up the security mechanism" option, and then click Next. In the next screen (see Figure 2), you can select the object you wish to set its security mechanism. By default, the wizard will set a security mechanism for all existing objects and all newly created objects. Click Next. Figure 2: The third screen of the "Setting the Security Mechanism 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 the group to manage user access rights, not give each user-specific permissions. Select any other group account and click Next. Figure 3: The fourth screen of the "Setting the Security Mechanism Wizard" dialog box (see Figure 4), you can set specific permissions for the users group. Because all users automatically become a member of the User Group, any permissions selected here will apply to all users, so it is best to limit the privileges of the group, or retain the default settings - no permissions. Click Next. Figure 4: The fifth screen of the Set Safety Mechanism 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 personal ID (PID) for each user. Access uses PID and account names as the workgroup identifies unique users. Add users and click Next.
Note: Personal Identifiers (PID) is not a password. It is encrypted and created with the name to identify the user.
Figure 5: Sixth screen of the "Setting Security Mechanism Wizard" dialog box (see Figure 6), you can add users to groups in the workgroup information file. Click "Next" after making your choice. Figure 6: Seventh screen of the "Setting the Security Mechanism Wizard" dialog box in the last screen, select a name for a backup of a database that does not have a secure setting. Protecting the backup of this raw database is necessary, in case loss or damaged databases with secure settings. Click Finish. The original database will be saved with the .bak extension. WARNING: After creating a working group information file, Security Wizard will create a report that contains all the information you need to recreate the workgroup information file. You must save this information in order to recreate the file when the original file is lost or corrupted.
After Click Finish, Security Wizard will encrypt the database and ask you to reopen the database using the new working group information file.
Let's take a look at how many ways to program users and groups.
Add and delete users and groups by programming
When you set security for the database, you may need to use users and groups. The following sections show some of these techniques.
Before using the following sections, you may need to set a reference to the Microsoft ADO Ext 2.5 for DDL and Security library (if not yet set):
In the Visual Basic Editor, point to "References" in the Tools menu. The Reference dialog will appear. Select the Microsoft ADO Ext 2.5 for DDL and Security check box.
Add and delete users
The following procedure will create a new user account and append it to the default UserS group in the workgroup information file for the current database.
Note: To use the following example in Access, you will need to log in as a member of the Admins group and open a database. In the following procedure, you have to ensure that the Working Group information file is not included
Users of their name are specified in the STRUSER. For example, you can call first
DELETEUSER subroutines 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 the Catalog object.
SET CATDB = New Adox.catalog
With catdb
'Use the connection to the current database to open
'Catalog object.
.ActiveConnection = CurrentProject.Connection
'Creating a new user account.
.Us.Append Struser, Strpwd, StrPid
'Additional new user accounts to the default USERS group.
.Groups ("Users"). Users.Append Struser
End with
'Close the Catalog object.
SET CATDB = Nothing
Adduser = TRUE
AddUser_err:
MsgBox Err.Number & ":" & Err.Description
Adduser = false
END FUNCTION
This process first declares a variable for the Catalog object and instantiates the object.
note:
The Catalog object is a container for all objects in the Access database file.
Then, the process opens to the current database and uses the parameters from the calling process to append the new user to the UserS collection of the Catalog object. The new user is then appended to the default USERS group. The UserS collection contains all 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 the Catalog object.
SET CATDB = New Adox.catalog
With catdb
'Open the Catalog object in the current database.
.ActiveConnection = CurrentProject.Connection
'Delete Struser.
.Uers.delete struser
End with
'Close the Catalog object.
SET CATDB = Nothing
Deleteuser = true
DELETEUSER_ERR:
MsgBox Err.Number & ":" & Err.Description
Deleteuser = false
END FUNCTION
This process is similar to the previous process, just using the DELETE method of the Catalog object to delete the user specified in the STRUSER STRING parameter.
Add and delete groups
The process of adding groups is similar to the process of adding users.
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
'Open the Catalog object in the current database.
.ActiveConnection = CurrentProject.Connection
'Creating a new group.
.Groups.Append strgroup, StrPid
End with
'Close the Catalog object.
SET CATDB = Nothing
Addgroup = true
AddGroup_err:
MsgBox Err.Number & ":" & Err.Description
Addgroup = false
END FUNCTION
This process first instantiates the Catalog object and opens a connection to the current database. Next, by using parameters from the calling process, add the new group to the Groups collection of the Catalog object.
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
'Open the Catalog object in the current database.
.ActiveConnection = CurrentProject.Connection
'Delete strgroup.
.Groups.Delete strgroup
End with
'Close the Catalog object.
SET CATDB = NothingDeleteGroup = true
Deletegroup_err:
MsgBox Err.Number & ":" & Err.Description
Deletegroup = false
END FUNCTION
This process is similar to the previous process, just use the DELETE method of the Catalog object to delete the group specified in the STROUP STRING parameter.
Let's take a look at how to set the permissions of the database object by programming.
Sign up
To set permissions to various objects in the database, you can use the setPermissions method for Group or User objects. In the following procedure, we first undo all the permissions of the group and then give a group-specific permissions. This ensures that the group only has the permissions we specified:
Private function setGrouppermissions (byval strgroup 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
'Open the Catalog object in the current database.
.ActiveConnection = CurrentProject.Connection
'Undo ownership.
.Groups (strunt) .SetPermissions TBLTABLE, _
StrobjectType, Straction, Strrevokeenum
'Give a specific permissions.
.Groups (strunt) .SetPermissions TBLTABLE, _
StrobjectType, Straction, _
Adrightread or adrightinsert or adrightupdate
End with
'Close the Catalog object.
SET CATDB = Nothing
SetGroupPermissions = TRUE
SetGroupPermissions_ERR:
MsgBox Err.Number & ":" & Err.Description
SetGroupPermissions = false
END FUNCTION
After opening a Catalog object in the current database, we used the setpermissions method for Groups collection to undo all the privileges for the Employees table. The first parameter is the name of the table, the second parameter shows the type of object, here is a table. The third parameter specifies the type of the operation to be executed when setting permissions, and the fourth parameter is a permissions constant, specifying that the group has no permissions. We have revoked all permissions for the Employees table, and now you can give it its desired permissions.
The top three parameters of the next statement are the same as those in the previous statement. The fourth parameter is a value created by a different permissions constant by using the OR operator. Here, we give read, insert, and update the permissions of the table.
To set permissions to all new objects of the specified type (such as the table in the above example), change the first parameters in the statement that give privileges to the null keyword. E.g:
...
CATDB.Groups (strgroup) .SetPermissions Null, AdpermoBjTable ...
summary
In this article, we discussed various methods for achieving different levels of Access database, introducing shared grade and user-level security. Also describes how to use Access Security Wizard and how to implement security settings.
