Understanding access security

Introduction

In earlier versions of Microsoft Access (before Microsoft Access 2000), security 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 the appearance of Microsoft Access for Microsoft Windows 95 Security wizard and the continuous improvement of Microsoft Access 2000 Security wizard, implementing security in access has become very simple. 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 programmatically, see "set 'start' options and encoding options" in the access help Microsoft Visual Basic Editor ".

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 code. If you do not want to illegally access the code in a form, report, or moduleRequiredConvert the. MDB file to an 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.

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,RequiredConvert the. MDB file to the MDE file.

To set a password for a module in the project:

1. Select the "attribute" command from the "Tools" menu of the Visual Basic Editor for this project.
2. In the "Project Properties" dialog box, click the "protection" tab.
3. Select the "Lock project at view" check box and enter the password.
4. 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:

1. Shut down the database.
2. Click database utility in the Tools menu ".
3. Click Generate MDE file ".
4. 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. For databases without security settings, the admins group always contains the default admin user account, which is also the default Logon account for all users.

All users must belong to the default Users group, whether or not they belong to another group. You can create a user account in VBA instead of adding the new user account to the admins group. However, if the user account is not added to the admins group at the same time, the user cannot start access, because access is used internally to manage the database, many tables are mapped to the users group permissions.

Follow these steps to set the security of the Access database:

1. Add the new user account to the admins group. This user has the management permission on the objects in the database.
2. Delete the default admin user account from the admins group.
3. Delete permissions from admin users and users groups.
4. Assign permissions to any created custom group.

Setting the password on the default admin user account activates the logon dialog box, prompting the user to enter the user name and password each time access is started. If you do not set a password for 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 (directly assigned to the user account) or implicit (inherited from the user's group), or a combination of the two. Access uses the "minimum limit" rule for permission issues, that is, the user's permissions include the sum of explicit and implicit permissions. For example, if user a's account has restricted permissions, and user a belongs to a group with restricted permissions, it also belongs to another group with administrative (all) permissions, user A has the management permission. In view of this, it is generally better not to assign explicit permissions to user accounts. Instead, you should create groups with different permissions and assign users to groups with appropriate permissions, which reduces the trouble of database management.

In the following sections, we will show you how to protect the database and its objects through user interfaces and programming methods. First, let's take a look at several different methods to set the Database Password.

Manually set Database Password

You can set a password for the database to require the user to enter the password when accessing the database. However, once a user logs on, the data and objects in the database can be accessed without restriction.

Note:Before setting the database password, we recommend that you back up the database and store it in a safe location.

Before setting the database password, you must open the database exclusively. To open a database exclusively:

1. If the database is on, close the database.
2. Click Open in the File menu to reopen the database.
3. In the open dialog box, locate the database, click the arrow next to the Open button, and then click open exclusively ".

To manually set the database password:

1. In the Tools menu, point to security and click Set Database Password ".
2. In the "password" box, type the password.
   

   Note:The password is case sensitive.

3. In the "verify" box, retype the password to confirm, and then click "OK ".

Now the database password is set. The next time you or another user opens the database, a dialog box appears asking for a password. Next we will look at how to set the database password through programming.

Set the database password through programming

Use keywords according to the following syntaxALTER DATABASEYou can set, modify, or delete database passwords programmatically.

ALTER DATABASE PASSWORD NewPassword OldPassword

In this statement, the password is separated by square brackets ([]).StringValue, except in the following cases.

UseNullKeywordALTER DATABASEStatementOldPasswordParameters. To delete the Database Password, useNullKeywordALTER DATABASEStatementNewPasswordParameters. In these cases, the keywordNullIt should not appear in the box.

Before using the following procedure, you may needMicrosoft ADO ext2.5 for DDL and securityLibrary Reference (if not set ):

1. In the Visual Basic Editor, point to "Reference" in the "Tools" menu ". The "Reference" dialog box is displayed.
2. SelectedMicrosoft ADO ext2.5 for DDL and securityCheck box.

See the following code when setting the password for the first time:

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 create an SQL string to initialize a Database Password. Stralterpassword = "alter Database Password [Password] Null;" 'open a database without security settings. Set objconn = new ADODB. connection with objconn. mode = admodemo-exclusive. open "provider = Microsoft. jet. oledb.4.0; Data "& _" Source = path; "'perform SQL statements to set database security. . Execute (stralterpassword) end with 'to clear the object. Objconn. Close set objconn = nothing 'If the operation succeeds, true is returned. Createdbpassword = truecreatedbpassword_err: msgbox err. Number & ":" & err. Description createdbpassword = false End Function

This process accepts the user's password and path of the. MDB file. First, we declare a variable to indicate the connection to the database without security settings, and declareStringVariable to include the SQL statement that we use to change the password. Next, we willstrAlterPasswordSet to useALTER DATABASE PASSWORDKeyword of the jet SQL statement. Note that because we do not need to replace the password, the second parameter is setNull. Next, we open a connection to the database. To set a password, you must open the database exclusively. Therefore, you must setModeAttribute. Then execute the SQL statement. You can usually run this process from a database to set a password in a separate database that does not have security settings. If everything is normal, the function returnsTrue.

To change the password of a database with security settings, you must first use the old password to log on to the database and then change the password. The following process shows this technology.

Before using the following procedure, you may needMicrosoft ADO ext2.5 for DDL and securityLibrary Reference (if not set ):

1. In the Visual Basic Editor, point to "Reference" in the "Tools" menu ". The "Reference" dialog box is displayed.
2. SelectedMicrosoft ADO ext2.5 for DDL and securityCheck box.

See the following process:

Private function changedbpassword (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 create an SQL string to change the Database Password. Stralterpassword = "alter Database Password [newpassword] [oldpassword];" 'open a database with security settings. Set objconn = new ADODB. connection with objconn. mode = admodemo-exclusive. provider = "Microsoft. jet. oledb.4.0 ". properties ("jet oledb: Database Password") = "oldpassword ". open "Data Source = path;" 'Run the SQL statement to change the password. . Execute (stralterpassword) end with 'to clear the object. Objconn. Close set objconn = nothing changedbpassword = truechangedbpassword_err: msgbox err. Number & ":" & err. Description changedbpassword = false End Function

This process is similar to the previous subroutine, except that you need to change the old password when logging on to a database with security settings. For this reason, we have setConnectionObjectDatabase PasswordAttribute. This isConnectionOne of the extension attributes of the object, so we use the special syntax shown above. To delete a password from a database with security settings, you only needALTER DATABASEReplace the first parameter of the statementNullKeyword.

Next we will look at how to implement more powerful protection measures than setting passwords in the database.

Use Security Wizard to set the security of the Access Database

You can use Security Wizard to set database security. To do this, perform the following steps:

1. Open the database for which you want to set security.
2. In the "Tools" menu, point to "security" and click "Set security mechanism wizard ". The "Set security mechanism wizard" dialog box is displayed.
3. Because we want to create a new workgroup information file, make sure to select this option and then click "Next ".
4. In the second screen of the "Set security mechanism wizard" dialog box (seeFigure 1), Enter a new name for the workgroup information file, or accept the default name. Type a workgroup ID (WID ). WID uniquely identifies the admins group of this workgroup file. Next, type your name and unit in the text box. Although these inputs are optional, it is best to enter them because only those who know the information can recreate the workgroup information file.

   Figure 1: second screen of the "Set security mechanism wizard" dialog box

5. You can also choose whether to use this workgroup information file as the default file for all databases, or only for this specific database. Click "create shortcut, open the database with security mechanism set" option, and then click "Next ".
6. In the next screen (seeFigure 2), You can select the object for which you want the Wizard to set its security mechanism. By default, the wizard sets a security mechanism for all existing objects and all newly created objects. Click "Next ".

   Figure 2: Third screen of the "Set security mechanism wizard" dialog box

7. In the next screen (seeFigure 3), You can select an optional security group with specific permissions. We strongly recommend that you use groups to manage user access permissions instead of granting specific permissions to each user. Select any other group account and click "Next ".

   Figure 3: fourth screen of the "Set security mechanism wizard" dialog box

8. In this screen (seeFigure 4), You can set specific permissions for the Users Group. Because all users automatically become members of the users group, any permissions selected here will be applied to all users, so it is best to restrict the permissions of the group or retain the default settings-No permissions. Click "Next ".

   Figure 4: Fifth screen of the "Set security mechanism wizard" dialog box

9. In the next screen (seeFigure 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 as the unique user of the workgroup. Add a user and click "Next ".
   

   Note:The personal ID (PID) is not a password. It is encrypted and a unique system identifier (SID) is created with the name to identify the user.

   Figure 5: Sixth screen of the "Set security mechanism wizard" dialog box

10. In this screen (seeFigure 6You can add users to a group in the workgroup information file. Click "Next" after making your selection ".

    Figure 6: Seventh screen of the "Set security mechanism wizard" dialog box

11. On the last screen, select a name for the backup of a database that does not have security settings. It is necessary to protect the backup of the original database in case that the database with security settings is lost or damaged. Click Finish ". The original database will be saved with the. Bak extension.
    

    Warning:After the workgroup information file is created, security wizard creates a report that contains all the information required to recreate the workgroup information file. You must save this information so that you can recreate the original file when it is lost or damaged.

12. After you click "finish", security wizard encrypts the database and asks you to use the new workgroup information file to re-open the database with security settings.

Next let's take a look at several programming methods for using users and groups.

Add and delete users and groups through programming

After security is set for the database, you may need to use users and groups. The following sections show some tips.

Before using the procedures described in the following sections, you may needMicrosoft ADO ext2.5 for DDL and securityLibrary Reference (if not set ):

1. In the Visual Basic Editor, point to "Reference" in the "Tools" menu ". The "Reference" dialog box is displayed.
2. SelectedMicrosoft ADO ext2.5 for DDL and securityCheck box.

Add and delete users

The following process creates a new user account and 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 to and open a database as a member of the admins group. In the following process, make sure that the workgroup information file is not included instrUserUser whose name is specified in. For example, you can callDeleteUserTo ensure this.

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 the 'catalog object. . Activeconnection = currentproject. Connection 'create a new user account. . Users. append struser, strpwd, strpid 'to append a new user account to the default Users Group. . Groups ("users"). Users. append struser end with 'Close the catalog object. Set catdb = nothing adduser = trueadduser_err: msgbox err. Number & ":" & err. Description adduser = false End Function

The process isCatalogThe object declares a variable and then instantiates the object.

Note:CatalogThe object is the container of all objects in the ACCESS database file.

Then, this process opens the connection to the current database, and uses parameters from the call process to append new usersCatalogObjectUsersCollection. The new user is appended to the default Users Group.UsersThe 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 catox object in the current database. . Activeconnection = currentproject. Connection 'Delete struser. . Users. Delete struser end with 'Close the caters object. Set catdb = nothing deleteuser = truedeleteuser_err: msgbox err. Number & ":" & err. Description deleteuser = falseend Function

This process is similar to the previous process.CatalogObjectDeleteMethod deleted inStruser StringThe user specified in the parameter.

Add and delete groups

The process of adding a group is similar to that 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' open the catdb object in the current database. . Activeconnection = currentproject. Connection 'create a new group. . Groups. append strgroup, strpid end with 'Close the catalog object. Set catdb = nothing addgroup = trueaddgroup_err: msgbox err. Number & ":" & err. Description addgroup = falseend Function

This process is first instantiatedCatalogObject, and then open a connection to the current database. Next, append the new groupCatalogObjectGroupsSet.

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 = nothing deletegroup = truedeletegroup_err: msgbox err. Number & ":" & err. Description deletegroup = falseend Function

This process is similar to the previous process.CatalogObjectDeleteMethod deleted inStrgroup StringThe Group specified in the parameter.

Next we will look at how to set database object permissions through programming.

Set permissions through programming

To set permissions for various objects in the database, you can useGroupOrUserObjectSetpermissionsMethod. In the following process, we first revoke all permissions of the group and then grant the group specific permissions. This ensures that the Group has only the permissions we specified:

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' open the catdb object in the current database. . Activeconnection = currentproject. Connection 'undo all group permissions. . Groups (strgroup). setpermissions tbltable, _ strobjecttype, straction, strrevokeenum 'Grant group-specific permissions. . Groups (strgroup). setpermissions tbltable, _ strobjecttype, straction, _ adrightread or adrightinsert or adrightupdate end with 'Close the catalog object. Set catdb = nothing setgrouppermissions = truesetgrouppermissions_err: msgbox err. Number & ":" & err. Description setgrouppermissions = false End Function

OpenCatalogObject, we useGroupsSetSetpermissionsMethod.EmployeesAll table permissions. The first parameter is the table name, and the second parameter shows the object type. Here is the table. The third parameter specifies the type of the operation to be performed when the permission is set. The fourth parameter is a permission constant, specifying that the group has no permissions. We have removed this groupEmployeesAll permissions of the table, which can be granted to the expected permissions.

The first three parameters of the next statement are the same as those in the previous statement. The fourth parameter is to useOrOperator, which combines a value created by different permission constants. Here, we grant the permission to read, insert, and update the table.

To set permissions for all new objects of the specified type (for example, tables in the preceding example), change the first parameter in the statement used to grant permissionsNullKeyword. For example:

...catDB.Groups(strGroup).SetPermissions NULL, adPermObjTable...

 

Summary

In this article, we discuss various methods for implementing different protection levels of the Access database, and introduce the shared-level and user-level security. It also introduces how to use access security wizard and how to implement security settings through programming.