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.

Note  

You will need a UserLog table, as described earlier in the chapter.

 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 :

Note  

See the frmJetUserRoster form at the beginning of the chapter.

 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.




Real World Microsoft Access Database Protection and Security
Real World Microsoft Access Database Protection and Security
ISBN: 1590591267
EAN: 2147483647
Year: 2003
Pages: 176

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net