16.2. Shared-Level Security

Shared-level security involves setting a password on a database. Setting a database password is a simple solution to protect a database for a small group of people. This can be effective in situations where anyone who can access the database is permitted to insert, delete, or update the data and update any of the objects ( for example, forms, reports, and so on) in the database.

When shared-level security is applied to a database, the user is prompted for a password each time that database is opened. Shared-level security only protects the MDB file on which the password is set. When the correct password is entered, the user gains full access to that database including all data and all objects contained in that database.

Be aware that setting a database password only affects attempts to access the database using the Microsoft Jet Engine. See Encoding a Database for more discussion.

16.2.1. Shared-Level Secured Back-End Databases

Be careful while linking to tables in a shared-level protected database. When your front end links to tables in the protected back-end database, you will be required to specify its password. After the tables are linked, the link and the password are recorded. This is true whether you link from another Access database or create an ODC linking file [(through Open Database Connectivity (ODBC)] to access the protected database from Excel or Word. After the link has been established, anyone can then open the protected database without entering the password.Note: ODC is an Office Data Connection. These connections use HTML and XML to store the connection information. Users can view and edit the data in a database through the ODC using Word, Excel, and other text editors.

As mentioned in the Overview, if the link is from another Access database, the password is recorded in the front-end database without encoding in one of the system tables. A user with a moderate level of experience can determine where to find this password and could begin accessing the remote database without the benefit of the front end you have written for them.

Since user-level security does not expose its passwords in this way, user-level security is preferred for databases that utilize linked tables. At the very least, any front-end database that accesses a shared-level secured back-end database should also have shared-level security applied.

16.2.2. Setting Up Shared-Level Security Using the Access Interface

Shared-level security is added to a database from the Tools | Security menu. The option Set Database Password will be visible if the database does not have a shared-level password. The option Unset Database Password will be visible if the database has a shared-level password.

16.2.2.1. Adding a Database Password

To set a database password, the database must be opened in Exclusive mode. Figure 16-2 shows how to select Open Exclusive to open the file in Exclusive mode.

Select Tools | Security | Set Database Password...from the menu as shown in Figure 16-3.

This displays the Set Database Password dialog box as shown in Figure 16-4.

Enter a valid password in the Password text box and reenter the password in the Verify text box. Click OK to apply the password.

16.2.2.2. Removing a Database Password

After a password has been set on a database, the menu option changes to Tools | Security | Unset Database Password.

To unset a database password, the database must be opened in Exclusive mode.

Figure 16.2. Figure 16-2

Figure 16.3. Figure 16-3

Figure 16.4. Figure 16-4

Select Tools | Security | Unset Database Password...from the menu to display the Unset Database Password dialog box shown in Figure 16-5.

Enter the current password for the database and click OK to unset the password.

16.2.2.3. Using Jet and ADO to Set a Database Password

You can use Visual Basic code to set or change a database password. To change the password, open the database in Exclusive mode and use the SQL statement ALTER DATABASE PASSWORD newpassword oldpassword. The password is case sensitive and must be specified within square brackets ([]). When setting a password on a database that does not currently have a password, use NULL without the brackets as the old password. When removing a password from a database use, NULL without the brackets as the new password.

The procedure SetDatabasePassword provides functionality to add, update, or remove a database password. Notice that if either the new or the old password is not specified (IsMissing) when calling SetDatabasePassword, the word NULL is substituted for the password. If the password is specified, SetDatabasePassword encloses the value in square brackets ([]). Also notice that if the old password is specified, the ADODB Connection properties uses the password without square brackets.

Figure 16.5. Figure 16-5

Error traps are provided in the code to show errors that can occur. You will likely wish to move the error traps to the procedure that calls SetDatabasePassword and enhance the error messages to provide friendly feedback to the user.

Public Function SetDatabasePassword _
(pDBName As String, _
 Optional pNewPassword As Variant, _
 Optional pOldPassword As Variant) As String
On Error GoTo report_error
Const cProvider = "Microsoft.Jet.OLEDB.4.0"
Dim cnn As ADODB.Connection
Dim strNewPassword As String
Dim strOldPassword As String
Dim strCommand As String
Dim strResult As String
' if a password is not specified (IsMissing),
' the string is "NULL" WITHOUT the brackets
If IsMissing(pNewPassword) Then
   strNewPassword = "NULL"
Else

strNewPassword = "[" & pNewPassword & "]"
End If
If IsMissing(pOldPassword) Then
   strOldPassword = "NULL"
Else
   strOldPassword = "[" & pOldPassword & "]" End If
' define the string to change the password
strCommand = "ALTER DATABASE PASSWORD " _
& strNewPassword & " " & strOldPassword & ";"
' Open a connection to the database
Set cnn = New ADODB.Connection
With cnn
   .Mode = adModeShareExclusive
   .Provider = cProvider
   If Not IsMissing(pOldPassword) Then
       .Properties("Jet OLEDB:Database Password") = pOldPassword
   End If
   .Open "Data Source=" & pDBName & ";"
   .Execute strCommand
End With
strResult = "Password Set"
exit_SetDatabasePassword:
    On Error Resume Next
    cnn.Close
    Set cnn = Nothing
    SetDatabasePassword = strResult
    Exit Function
report_error:
    If Err.Number = −2147467259 Then
        strResult = "Specified an old password when one was not set"
    ElseIf Err.Number = −2147217843 Then
        strResult = "Invalid password for database"
    Else
        strResult = Err.Number & " " & Err.Description
    End If
    Resume exit_SetDatabasePassword

End Function

The procedure ShowSamplePasswordSetting shows how to use SetDatabasePassword to set a new password, change a password, and remove a password from the database. The resulting SQL ALTER DATABASE PASSWORD statement is documented for each call.

Public Sub ShowSamplePasswordSetting()
Const cDBName = "C:AccessSecurity SamplesDatabasePassword.mdb"
' give the database a password
' the statement becomes: ALTER DATABASE PASSWORD [newpass] NULL;
Debug.Print "New password result: " _
& SetDatabasePassword(cDBName, "newpass")
' change the password from "newpass" to "NEWPASS" (all caps)
' the statement becomes: ALTER DATABASE PASSWORD [NEWPASS] [newpass];
Debug.Print "Change password result: " _
& SetDatabasePassword(cDBName, "NEWPASS", "newpass")

' remove the password from the database
' the statement becomes: ALTER DATABASE PASSWORD NULL [NEWPASS];
Debug.Print "Remove password result: " _
& SetDatabasePassword(cDBName, , "NEWPASS")
End Sub