I'm developping an application within our small company environment. This is our setup in a nutshell:
I want to be able to authenticate users in the application without the need to entering their AD login, just based on the fact that they are logged into their PC. Then I want to be able to grant users permissions in the application as I see fit.
This is what I came up with:
tblEmployees:
| EmployeeID | EmployeeName | ADLogin |
|---|---|---|
| 1 | John Doe | COMPANY\john.doe |
| 2 | Peter Wilson | COMPANY\peter.wilson |
When any user starts the MS Access application, it will do something like this on start-up:
Private Sub Form_Load()
Dim rsUser As Recordset
Dim intEmployeeID As Integer
Dim strEmployeeName As String
Set rsUser = CurrentDb.OpenRecordset("SELECT * FROM tblEmployees", dbOpenSnapshot, dbReadOnly)
rsUser.FindFirst "ADLogin='" & Environ("USERDOMAIN") & "\" & Environ("USERNAME") & "'"
If rsUser.NoMatch Then
' User does not have access to the application
rsUser.Close
Set rsUser = Nothing
Application.Quit
Else
' User with this AD Login has been found and that means that he does have access
intEmployeeID = rsUser("EmployeeID")
strEmployeeName = rsUser("EmployeeName")
TempVars("EmployeeID") = intEmployeeID
TempVars("EmployeeName") = strEmployeeName
DoCmd.OpenForm "frm_MainMenu"
Forms!frm_MainMenu.Requery
DoCmd.Close acForm, Me.Name, acSaveYes
Forms!frm_MainMenu!txtLoggedUser = TempVars("EmployeeName")
End If
rsUser.Close
Set rsUser = Nothing
End Sub
Then I will use TempVars("EmployeeID") throughout the application to make forms and buttons available and so on.
My question is: Is this a good practice to do? Is it secure? Is there perhaps a better way to do this? Thanks for any tips.
First of all let me start by saying Access is not secure, but it depends on your colleagues knowledge and how far are they willing to go.
In terms of your solution, I believe there's no need to load the entire table to the recordset and then try to find the desired record when you can filter it directly at source. In addition, string concatenation is (most of the times) bad practice, instead create a query (since its usage will be frequent) and pass the inputs as parameters.
See an example:
The query (your fields might be different but you get the idea)
PARAMETERS [Domain] Text (50), [Username] Text (50);
SELECT *
FROM T
WHERE T.Domain=[Domain] AND T.Username=[Username];
You can also create a temporary query from code.
Const SQL As String = "The SQL command above"
'No query name means it's temporary and will be deleted once the method runs.
Dim q As DAO.QueryDef
Set q = CurrentDb().CreateQueryDef("", SQL)
To call it and check the logged user:
Dim q As DAO.QueryDef
Dim r As DAO.Recordset
Set q = CurrentDb().QueryDefs("YourQueryName")
'Using the Environ() method.
q.Parameters("[Domain]").Value = Environ("USERDOMAIN")
q.Parameters("[Username]").Value = Environ("USERNAME")
'OR alternative method to get the username/domain from @Andre in comments
'This is more secure than the Environ() method.
With CreateObject("WScript.Network")
q.Parameters("[Domain]").Value = .UserDomain
q.Parameters("[Username]").Value = .UserName
End With
'read-only, no need for changes.
Set r = q.OpenRecordset(dbOpenSnapshot)
'Not found
If r.EOF Then
DoCmd.Quit acQuitPrompt
Exit Sub
End If
'Found
'The recordset now contains whatever fields the query selected.
'Do what needs to be done.
'...
'Clean up
If Not r Is Nothing Then r.Close
If Not q Is Nothing Then q.Close
Lastly, I would change the intEmployeeID variable to be of type Long and add error handling to the method.