Results 1 to 7 of 7
  1. #1
    Forbes's Avatar
    Forbes is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    133

    Better authentication than passwords.

    So I've notice a lot of people using login forms and passwords for access to the program or database, a lot of questions are going around about what the best way to do this is. There are good methods and I've used a few interchangeably, but when it comes down to it a password for msaccess will never be secure considering it has to be saved in a table or physical source.

    I'm playing with using Windows usernames to verify access authentication. So essentially using this Module:
    Code:
    Private Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
    
    Function UserNameWindows() As String
       
        Dim lngLen As Long
        Dim strBuffer As String
       
        Const dhcMaxUserName = 255
       
        strBuffer = Space(dhcMaxUserName)
        lngLen = dhcMaxUserName
        If CBool(GetUserName(strBuffer, lngLen)) Then
            UserNameWindows = Left$(strBuffer, lngLen - 1)
        Else
            UserNameWindows = ""
        End If
    End Function
    I can call the windows username in a text box "=UserNameWindows()" from here at first I used a password associated but at this point since I'm verifying the local account of the endpoint user, I realized that passwords are even more useless at this point.

    I have the program open a small loading form that welcomes the user followed by the username and a loading GiF, I want this form to run code to compare the username, see if it is within the users table, compare the username if it exists to the department that employee works in and loads the form that said employee needs to work, this also helps keep users in their respective sections of the DB. It's not a full-proof method yet as you can still bypass and add your username to the table, but I'm working on that as well and will update through-out.



    The part I need some advice on is the code I need to run, the username table is as follows:

    (tblUsers)
    fldUsername, fldDept

    The Loading Form's text box that contains the Username is called:

    (frmLoading)
    'txtUsername'


    ------

    My abilities aren't amazing yet so I want to make sure I do this correctly, let's pretend there are 3 departments "shipping and receiving and management"

    I can figure out how to compare the username values to the table however I need to know how to direct the next form to open as it relates to the employees department.

    Sorry If I was to vague in any areas, will be happy to clarify.

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    1.check if user is in the tUser table
    2.check with windows authentication. If they pass both, show the menu, else quit.




    Code:
       'form code
    '-------------
    Private Sub btnLogin_Click()
    '-------------
    Dim sUser As String, sPass As String, sDom As String
    dim vID, vDbID
    
    
    sUser = txtUser
    sPass = txtPass
    sDom = txtDom
    
    
    vID= Environ("Username")
    vDbID = Dlookup("[userId]","tUsers","[UserID]='" & vID & "'"
    
    
    If WindowsLogin(sUser, sPass, sDom) and vID = vDbID Then
       mbSafe = True
       DoCmd.OpenForm "frmMainMenu"
       DoCmd.OpenForm "frmLogin"
       DoCmd.Close
    Else
       MsgBox "LOGIN INCORRECT", vbCritical, "Bad userid or password"
    End If
    End Sub
    
    
    
    
    '-------------
    Public Function WindowsLogin(ByVal strUserName As String, ByVal strpassword As String, ByVal strDomain As String) As Boolean
    '-------------
            'Authenticates user and password entered with Active Directory.
    
    
            On Error GoTo IncorrectPassword
            
            Dim oADsObject, oADsNamespace As Object
            Dim strADsPath As String
            
            strADsPath = "WinNT://" & strDomain
            Set oADsObject = GetObject(strADsPath)
            Set oADsNamespace = GetObject("WinNT:")
            Set oADsObject = oADsNamespace.OpenDSObject(strADsPath, strDomain & "\" & strUserName, strpassword, 0)
            
            WindowsLogin = True    'ACCESS GRANTED
            
    ExitSub:
            Exit Function
            
    IncorrectPassword:
            WindowsLogin = False   'ACCESS DENIED
            Resume ExitSub
    End Function

  3. #3
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    I use AD similar to what ranman256 posted. This was you don't keep any passwords in the Access database and it only lets those into the specific database that have access (ID in the tblUsers table).

  4. #4
    Forbes's Avatar
    Forbes is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    133
    Thanks for the information, I have decided to run the login like such
    Code:
    Private Sub Form_Load()'// process ui style
        Call UISetRoundRect(Me, 16, False)
        
    
    
    
    
    Dim rs As Recordset
    Dim strSQL As String
    Dim strDept As String
    Dim strUserName As String
    
    
    On Error Resume Next
    
    
    strUserName = Username.Value
    
    
    
        Set db = CurrentDb
        strSQL = "SELECT fldDept FROM tblUsers WHERE fldUsername='" & strUserName & "'"
        Set rs = db.OpenRecordset(strSQL)
        
        If rs.RecordCount > 0 Then
        
            If rs.Fields(0) = "Admin" Then
                Me.Visible = False
                DoCmd.OpenForm "frmAhome", acNormal
                End If
             
        Else
            
        If rs.RecordCount > 0 Then
        
            If rs.Fields(0) = "Teaching" Then
                Me.Visible = False
                DoCmd.OpenForm "frmThome", acNormal
                End If
        Else
              
        If rs.RecordCount > 0 Then
        
            If rs.Fields(0) = "Compliance" Then
                Me.Visible = False
                DoCmd.OpenForm "frmComhome", acNormal
                End If
                
        Else
            
        If rs.RecordCount > 0 Then
        
            If rs.Fields(0) = "Mgmt" Then
                Me.Visible = False
                DoCmd.OpenForm "frmMhome", acNormal
                End If
             
        Else
            
        If rs.RecordCount > 0 Then
        
            If rs.Fields(0) = "Accounting" Then
                Me.Visible = False
                DoCmd.OpenForm "frmAcchome", acNormal
                End If
                
        Else
            
        If rs.RecordCount > 0 Then
        
            If rs.Fields(0) = "Student" Then
                Me.Visible = False
                DoCmd.OpenForm "frmShome", acNormal
                End If
             
        Else
            
        If rs.RecordCount > 0 Then
        
            If rs.Fields(0) = "Faculty" Then
                Me.Visible = False
                DoCmd.OpenForm "frmFhome", acNormal
                End If
        Else
        
        If rs.RecordCount = 0 Then
            MsgBox "You do not have access, see Admin.", vbOKCancel, "Warning!"
            DoCmd.CloseDatabase
            End If
        
        End Sub
    I've ended all my Ifs with End If but I still get the "Block If without End If" any suggestions as to where I'm missing it?

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You are missing 7 "End If" lines. For every ELSE, you need an "End If":
    Code:
    <snip>
        If rs.RecordCount > 0 Then
            If rs.Fields(0) = "Admin" Then
                Me.Visible = False
                DoCmd.OpenForm "frmAhome", acNormal
            End If
        Else
            If rs.RecordCount > 0 Then
                If rs.Fields(0) = "Teaching" Then
                    Me.Visible = False
                    DoCmd.OpenForm "frmThome", acNormal
                End If
            Else
                If rs.RecordCount > 0 Then
                    If rs.Fields(0) = "Compliance" Then
                        Me.Visible = False
                        DoCmd.OpenForm "frmComhome", acNormal
                    End If
                Else
                    If rs.RecordCount > 0 Then
                        If rs.Fields(0) = "Mgmt" Then
                            Me.Visible = False
                            DoCmd.OpenForm "frmMhome", acNormal
                        End If
                    Else
                        If rs.RecordCount > 0 Then
                            If rs.Fields(0) = "Accounting" Then
                                Me.Visible = False
                                DoCmd.OpenForm "frmAcchome", acNormal
                            End If
                        Else
                            If rs.RecordCount > 0 Then
                                If rs.Fields(0) = "Student" Then
                                    Me.Visible = False
                                    DoCmd.OpenForm "frmShome", acNormal
                                End If
                            Else
                                If rs.RecordCount > 0 Then
                                    If rs.Fields(0) = "Faculty" Then
                                        Me.Visible = False
                                        DoCmd.OpenForm "frmFhome", acNormal
                                    End If
                                Else
                                    If rs.RecordCount = 0 Then
                                        MsgBox "You do not have access, see Admin.", vbOKCancel, "Warning!"
                                        DoCmd.CloseDatabase
                                    End If
                                End If
                            End If
                        End If
                    End If
                End If
            End If
        End If
    End Sub
    Add the BLUE lines...


    However, you don't need a lot of the code as written. You only need to check if "rs.RecordCount > 0" one time.

    I would try this code:
    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub Form_Load()    '// process ui style
        Call UISetRoundRect(Me, 16, False)
    
        Dim db As DAO.Database  'added
        Dim rs As DAO.Recordset
        Dim strSQL As String
        Dim strDept As String
        Dim strUserName As String
    
        On Error Resume Next
    
        strUserName = UserName.Value
    
        Set db = CurrentDb
        strSQL = "SELECT fldDept FROM tblUsers WHERE fldUsername='" & strUserName & "'"
        Set rs = db.OpenRecordset(strSQL)
    
        If rs.RecordCount > 0 Then
            strDept =  NZ(rs.Fields(0)  ,"")  'in case of Nulls
            Me.Visible = False    'common to ALL IFs
            Select Case strDept
                Case "Admin"
                    DoCmd.OpenForm "frmAhome"'   ' acNormal is the default view
                Case "Teaching"
                    DoCmd.OpenForm "frmThome"  
                Case "Compliance"
                    DoCmd.OpenForm "frmComhome"
                Case "Mgmt"
                    DoCmd.OpenForm "frmMhome"
                Case "Accounting"
                    DoCmd.OpenForm "frmAcchome"
                Case "Student"
                    DoCmd.OpenForm "frmShome"
                Case "Faculty"
                    DoCmd.OpenForm "frmFhome"
                Case Else
                    MsgBox "You do not have access, see Admin.", vbOKCancel, "Warning!"
                    '                DoCmd.CloseDatabase
    
                    'clean up
                    rs.Close
                    Set rs = Nothing
                    Set db = Nothing
    
                    DoCmd.Quit
            End Select
        End If
    
        'clean up
        rs.Close
        Set rs = Nothing
        Set db = Nothing
    End Sub

  6. #6
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    IMHO, you can simplify this by getting any profile info from tblUser if the Windows login name in that table first passes a DLookup test. If it returns nothing, then they don't get in. I use the commonly found function fOsUserName while others seem to prefer the Environ method. I've never hear/read where the function failed, which I cannot say for Environ.

    If you want to get real slick, learn how to create a custom object (dbUser) and add any property you want to its collection. That might be FName, LName, EmplID, Dept, emlAddress, etc. etc., including any security or user level values you might think you need. Then when deciding if you want to allow a form opening (or even show the command button that would launch it) all you need to do is something like If dbUser.Level = "Admin" Then 'show the admin function at hand.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    Forbes's Avatar
    Forbes is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    133
    Steve, much appreciated this worked great! Both lines

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Service Account Authentication
    By SIMMS7400 in forum Access
    Replies: 4
    Last Post: 03-31-2016, 03:19 PM
  2. DSN-Less authentication and security
    By osupratt in forum Programming
    Replies: 13
    Last Post: 07-15-2015, 03:06 PM
  3. Replies: 7
    Last Post: 04-17-2013, 10:02 AM
  4. Access User Authentication
    By hi.its.anu in forum Access
    Replies: 1
    Last Post: 08-23-2012, 04:56 PM
  5. Password Authentication
    By jmjbear in forum Programming
    Replies: 8
    Last Post: 11-27-2011, 02:12 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums