Checking for Users Who Skip the Startup Sequence

Because Access offers a variety of ways to open or connect to a database, you cannot ensure that your users will open the database by using the startup form or AutoExec macro that you set up for them. To alleviate this problem, you can turn off accelerator key sequences such as the Allow Bypass key, as explained in Chapter 2 on startup properties.

Wouldn't it be great if you could find out whether people were sneaking into your database by using ways that you haven't protected against? There is a way, and it involves comparing the logged record of all computers whose users opened your database by using your startup sequence (see the section "Logging Windows Users, Computers, and Access Accounts" earlier in this chapter) against the computer names returned from the JetUserRoster schema.

Another way to consider this possible security breach is that if a computer has a connection to a database and it is not in your custom UserLogs table, then that person is not opening the database as planned.

In the Access 2000 demonstration database, you will find a form called frmFindNoStartups that you can use in your database with the other user logging samples for this special monitoring purpose. The VBA code to find these users involves opening a recordset by using a query that retrieves a unique list of all computers that have your database open. To define "currently," I assume that a person has opened the database today and has not closed it.

strUserList = ""
Set adoCn = CurrentProject.Connection

sqlStr = "SELECT ComputerName" & _
         " FROM UserLogs" & _
         " WHERE (((Format([loginTime],'yyyy-mm-dd'))=" & _
         " Format(Date(),'yyyy-mm-dd'))" & _
         " AND ((UserLogs.logOffTime) Is Null))" & _
         " GROUP BY UserLogs.ComputerName;"

Now you will establish a second ADO recordset that retrieves the JetUserRoster form of all the computers that are connected to the current database, as follows:

Set usersRS = New ADODB.Recordset
usersRS.Open sqlStr, adoCn ' , adOpenStatic, adLockReadOnly

Set adoRS = adoCn.OpenSchema(adSchemaProviderSpecific, , _
"{947bb102-5d43-11d1-bdbf-00c04fb92675}")

  'Setup the column headers for the list box
strUserList = adoRS.Fields(0).Name & ";" & adoRS.Fields(1).Name & _
            ";" & adoRS.Fields(2).Name & ";" & adoRS.Fields(3).Name & ";"

Now you need to move through the JetUserRoster recordset one record at a time and compare the computer name against the unique list of computer names in your own UserLogs table. If your computer name is legitimate, you can jump to the next computer name in the roster.

While Not adoRS.EOF

' First test whether the computer name is in the the UserLogs table.

  usersRS.MoveFirst
  While Not usersRS.EOF
    If TrimToNull(adoRS.Fields(0)) = usersRS!ComputerName Then
' Computer has been legitimately logged.
      GoTo userLegitimate
    End If
    usersRS.MoveNext
  Wend

If a computer name is not found, then this connection has not opened the startup user logging sequence correctly and needs investigating. You may at this stage want to send an email or open a message box to warn the administrator of a possible security breach.

' Computer has not been logged. User has entered the database the wrong way.
  strUserList = strUserList & TrimToNull(adoRS.Fields(0)) & ";" & _
                 TrimToNull(adoRS.Fields(1)) & ";" & _
                 TrimToNull(adoRS.Fields(2)) & ";" & _
                 TrimToNull(adoRS.Fields(3)) & ";"

userLegitimate:

  adoRS.MoveNext

Wend

DisplayJetRoster.RowSource = strUserList

updateList_Exit:
  Set adoRS = Nothing
  Set usersRS = Nothing
  Exit Sub

To enhance this form, you can add a timer event to check security breaches on a regular basis.