473,326 Members | 2,168 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Logging security in an acces db

9
Hi all,

I'm working on an access db where users need to log on to protect the data. It all works quite well, I only would like to record every log on attempt and place it in a table to audit every log on. If the user fails to log on, it should record in a table "failed to log on", if a user is locked after 3 failed logons, it should record in a table "user is locked". I made the log on screen using DAO, when a user fails to log on 3 times, a field 'lock_user' is activated and the user is locked out the database. If you would like to see the code behind this all, don't hesitate to ask.
Is there somebody who can help me built a ADO recordset to audit every log on? Does it need to be activated on the 'update event' of the form?

Thanks a lot for your help.

Regards,
Tom
Mar 20 '08 #1
2 1267
PianoMan64
374 Expert 256MB
Tom, it would be activated on the on_click event when someone logs in. That way you don't have any issues of when it's going to fire.

As for the ADO part, I need more information as to what you're looking to do and what fields you already have available.

Email me on here.

Hope that helps,

Joe P.
Mar 20 '08 #2
Tomino
9
Tom, it would be activated on the on_click event when someone logs in. That way you don't have any issues of when it's going to fire.

As for the ADO part, I need more information as to what you're looking to do and what fields you already have available.

Email me on here.

Hope that helps,

Joe P.
Hi Joe,

Thank you very much for your reply.

The login ID and password is checked when the user press 'log in' on the main form after filling in the user ID and psswrd.

I think its best that I make my code to audit the security in this part of the program because the code is already going to control the logID and psswrd in this part of the program, so I don't have to make all the user ID and password check again in the 'before update' event.

Here is the code to control access to the database, I use DAO recordset to control the access but I would like to use ADO to write every log on attempt with comments (login failed, log in ok, user locked,...) to the table tbl_security

The fields I would like to use in tbl_security are:

Username
CompName
Date&Time
Comment on log on: failed, succesfull, user locked

Thanks in advance!!

Regards,
Tom


Expand|Select|Wrap|Line Numbers
  1. ' Module   : mod_display_menu
  2. ' Function : display the switchboard after checking the access and password and password expiry date
  3.  
  4. Option Compare Database
  5. Option Explicit
  6. Dim rsUsers As ADODB.Recordset
  7. Dim fldAttempt As ADODB.Field
  8.  
  9.  
  10.  
  11. Sub display_menu()
  12. On Error GoTo err_display_menu
  13.  
  14.  
  15.  
  16. '*************************************************************
  17. '*          Check userId and access level                    *
  18. '*************************************************************
  19. Dim access_level As Integer
  20. Dim finish_Date As Date
  21. Dim port_syd As String
  22. Dim valid_user As Integer
  23. Dim check_user As Integer
  24. Dim password_period As Date
  25. Dim check_password As String
  26. Dim strmsg As String
  27. Dim bln_lock As Boolean
  28. Dim intLogonAttempts As Integer
  29. Dim user_name As String
  30.  
  31.  
  32. valid_user = 2
  33. user_name = Forms!frm_main!user_id
  34.  
  35.  
  36.  
  37. ' ***********************************************
  38. ' *             Validate user_id                *
  39. ' ***********************************************
  40. check_user = DCount("[user_id]", "tbl_users", "user_id=forms!frm_main!user_id")
  41. bln_lock = DLookup("[Control_lock]", "tbl_users", "user_id=forms!frm_main!user_id")
  42.     If check_user = 1 And bln_lock = False Then
  43.         If user_name = UserName() Then
  44.         valid_user = 2
  45.         Else:   MsgBox "U hebt met een verschillende acount ingelogd in LimsFreeze en het NT netwerk, dit is niet mogelijk.  Gelieve onder dezelfde acount in te loggen a.u.b.", vbCritical, "LimsFreeze Login"
  46.                 valid_user = 0
  47.                 Application.Quit
  48.         End If
  49.     Else
  50.         valid_user = 0
  51.    End If
  52.  
  53.  
  54.  
  55.  
  56. ' ********************************************************************************
  57. ' *         Validate password                                                    *
  58. ' *         REMARK: Changed UCase scenario to allow upper and lower case symbols *
  59. ' ********************************************************************************
  60. If valid_user = 2 Then
  61.    check_password = DLookup("[passwords]", "tbl_users", "user_id=forms!frm_main!user_id")
  62.    If check_password = Forms!frm_main!password Then
  63.         valid_user = 2
  64.    Else
  65.         valid_user = 1
  66.    End If
  67.  
  68. End If
  69.  
  70.  
  71.  
  72. ' **********************************************
  73. ' *         Validate access_level              *
  74. ' **********************************************
  75. If valid_user = 2 Then
  76.     access_level = DLookup("[access_level]", "tbl_users", "user_id=forms!frm_main!user_id")
  77.  
  78. End If
  79.  
  80. Select Case valid_user
  81.  
  82.     Case 0, 1
  83.             strmsg = " Access Denied" & _
  84.                         vbCrLf & " Contact your Administrator if the problem persists.   "
  85.             MsgBox strmsg, vbCritical, "INVALID USER ID or PASSWORD"
  86.  
  87.             'Get recordset
  88.             Set rsUsers = New ADODB.Recordset
  89.             user_name = Forms!frm_main!user_id
  90.  
  91.  
  92.             'open recordset
  93.             rsUsers.Open "tbl_users", CurrentProject.Connection, adOpenStatic, adLockOptimistic
  94.  
  95.             With rsUsers
  96.             'Check each record
  97.             While Not .EOF
  98.                 For Each fldAttempt In .Fields
  99.                 'check value
  100.                 If fldAttempt.Name = "user_id" Then
  101.                     If fldAttempt.Value = user_name Then
  102.                     .Fields("Attempts") = .Fields("Attempts") + 1
  103.                     intLogonAttempts = .Fields("Attempts")
  104.                         If intLogonAttempts > 3 Then
  105.                             MsgBox "You are locked out this database.  Please contact your System Administrator.", vbCritical, "Restricted Access!"
  106.                             .Fields("Control_lock") = True
  107.                             'Application.Quit
  108.                         End If
  109.  
  110.                     .Update
  111.                     End If
  112.                 End If
  113.                 Next
  114.                 .MoveNext
  115.             Wend
  116.             End With
  117.  
  118.  
  119.  
  120.             rsUsers.Close
  121.             Set rsUsers = Nothing
  122.  
  123.             'DoCmd.Quit
  124.  
  125.     Case 2
  126.             Select Case access_level
  127.  
  128.                Case 1   ' Level1 menu; System Administrator
  129.                         ' validate password expiry
  130.                       password_period = DLookup("[password_date]", "tbl_users", "user_id = forms!frm_main!user_id")
  131.                       If password_period < date - 30 Then
  132.                             strmsg = " Your password has expired. You must change your password"
  133.                             MsgBox strmsg, vbInformation, "Expired Password"
  134.                             DoCmd.OpenForm "frm_change_password", acNormal
  135.                         Else
  136.                             DoCmd.OpenForm "switchboard"
  137.                         End If
  138.  
  139.  
  140.                         'Get recordset
  141.                             Set rsUsers = New ADODB.Recordset
  142.                             user_name = Forms!frm_main!user_id
  143.  
  144.                         'open recordset
  145.                             rsUsers.Open "tbl_users", CurrentProject.Connection, adOpenStatic, adLockOptimistic
  146.  
  147.                             With rsUsers
  148.                         'Check each record
  149.                             While Not .EOF
  150.                             For Each fldAttempt In .Fields
  151.                         'check value
  152.                             If fldAttempt.Name = "user_id" Then
  153.                             If fldAttempt.Value = user_name Then
  154.                             .Fields("Attempts") = "0"
  155.                             intLogonAttempts = .Fields("Attempts")
  156.                             .Update
  157.                             End If
  158.                             End If
  159.                             Next
  160.                             .MoveNext
  161.                             Wend
  162.                             End With
  163.                             rsUsers.Close
  164.                             Set rsUsers = Nothing
  165.  
  166.                Case 2   ' Level2 menu; super visor acount
  167.                         ' validate password expiry
  168.                       password_period = DLookup("[password_date]", "tbl_users", "user_id = forms!frm_main!user_id")
  169.                       If password_period < date - 90 Then
  170.                             strmsg = " Your password has expired. You must change your password"
  171.                             MsgBox strmsg, vbInformation, "Expired Password"
  172.                             DoCmd.OpenForm "frm_change_password", acNormal
  173.                         Else
  174.                             DoCmd.OpenForm "switchboard"
  175.                         End If
  176.  
  177.  
  178.                         'Get recordset
  179.                             Set rsUsers = New ADODB.Recordset
  180.                             user_name = Forms!frm_main!user_id
  181.  
  182.                         'open recordset
  183.                             rsUsers.Open "tbl_users", CurrentProject.Connection, adOpenStatic, adLockOptimistic
  184.  
  185.                             With rsUsers
  186.                         'Check each record
  187.                             While Not .EOF
  188.                             For Each fldAttempt In .Fields
  189.                         'check value
  190.                             If fldAttempt.Name = "user_id" Then
  191.                             If fldAttempt.Value = user_name Then
  192.                             .Fields("Attempts") = "0"
  193.                             intLogonAttempts = .Fields("Attempts")
  194.                             .Update
  195.                             End If
  196.                             End If
  197.                             Next
  198.                             .MoveNext
  199.                             Wend
  200.                             End With
  201.                             rsUsers.Close
  202.                             Set rsUsers = Nothing
  203.  
  204.                Case 3   ' Level3 menu; user acount
  205.                         ' validate password expiry
  206.                       password_period = DLookup("[password_date]", "tbl_users", "user_id = forms!frm_main!user_id")
  207.                       If password_period < date - 90 Then
  208.                             strmsg = " Your password has expired. You must change your password"
  209.                             MsgBox strmsg, vbInformation, "Expired Password"
  210.                             DoCmd.OpenForm "frm_change_password", acNormal
  211.                         Else
  212.                             DoCmd.OpenForm "switchboard"
  213.                         End If
  214.                         intLogonAttempts = 0
  215.  
  216.  
  217.                         'Get recordset
  218.                             Set rsUsers = New ADODB.Recordset
  219.                             user_name = Forms!frm_main!user_id
  220.  
  221.                         'open recordset
  222.                             rsUsers.Open "tbl_users", CurrentProject.Connection, adOpenStatic, adLockOptimistic
  223.  
  224.                             With rsUsers
  225.                         'Check each record
  226.                             While Not .EOF
  227.                             For Each fldAttempt In .Fields
  228.                         'check value
  229.                             If fldAttempt.Name = "user_id" Then
  230.                             If fldAttempt.Value = user_name Then
  231.                             .Fields("Attempts") = "0"
  232.                             intLogonAttempts = .Fields("Attempts")
  233.                             .Update
  234.                             End If
  235.                             End If
  236.                             Next
  237.                             .MoveNext
  238.                             Wend
  239.                             End With
  240.                             rsUsers.Close
  241.                             Set rsUsers = Nothing
  242.  
  243.                 Case Else
  244.                         strmsg = " Access Denied" & _
  245.                                     vbCrLf & " Contact your Administrator if the problem persists.   "
  246.                         MsgBox strmsg, vbInformation, "INVALID USER ID or PASSWORD"
  247.             End Select
  248.  
  249. End Select
  250.  
  251. exit_display_menu:
  252.     Exit Sub
  253.  
  254. err_display_menu:
  255.     MsgBox Err.decsription
  256.     Resume exit_display_menu
  257.  
  258. End Sub
  259.  
  260.  
Mar 20 '08 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Xizor | last post by:
Ok, I'm new to PHP and MySQL. I've been going through tutorials, reading the documentation, and looking through web sites. PHP to me seems great! With MySQL it seems even better. However, I'm an...
1
by: Huzefa | last post by:
I am working on a amll project in Java that includes many classes. Each of the classes has a Logger object. I have associated a FileHandler with each of these Logger objects. The file is the same...
0
by: bart plessers | last post by:
Hello, I want to list my folders with ASP. I use following script to achieve this: Set oFSO = Server.CreateObject("Scripting.FileSystemObject") Set oRoot = oFSO.GetFolder(Path) Set oFolders =...
1
by: Ollie Riches | last post by:
I have written a custom logging sink for the enterprise library logging application block (June 2005). I have been able to configure it as expected from the UI config tool (EntLibConfig.exe). I...
3
by: Vic | last post by:
Dear All, I have been developing a small access database, but I am new to security concepts with access. This is a multiuser database, I have a table which will be written by various users...
16
by: Einar Høst | last post by:
Hi, I'm getting into the Trace-functionality in .NET, using it to provide some much-needed logging across dlls in the project we're working on. However, being a newbie, I'm wondering if some...
16
by: Marina | last post by:
Hi, I am trying to find the minimum security settings to allow a windows control embedded in IE have full trust. If I give the entire Intranet zone full trust, this works. However, this is...
8
by: A.M | last post by:
Hi, What would be the best approach to log different items (SQL, Exceptions, Security, ...) I have a simple Logging function that adds log string to the end of a text file. Is there any...
5
by: Rocky | last post by:
My Access 2007 file fails to open on a machine with the 2007 Access runtime version installed. It comes up with a Security alert message. Is there a security setting in the Access Database that...
17
by: Cramer | last post by:
I plan to implement an exception logging feature in an ASP.NET Web application that writes encountered exceptions to disk. The exception data will be stored as XML. I am planning on having each...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.