Security is a critical hitting point in MS Access. If you want to implement login function, I assume your business model contains somehow sensitive information. Such business model usually expects/requires more than average software designing skills.
At first you need to define/design your business requirements starting from
- If MS Access is something for you to start.
- Then what version of MS Access to use
- Single user, multi user, local access or internet access
- if the security level provided by MS Access sufficient for you.
- scalability
- etc...
MS Access allows to see the table contents which means, When you save your user details any user has access to the table will see the user details and able to use/bypass them.
You get some sort of security when you compile your Access database into a MDE or ACCDE which will prevent user accessing VBA functions or going into design mode. This will potentially help you in many ways to increase the security. Including releasing updates of your front-end application.
To answer your question regarding user access level. I would personally split the database before anything else to maintain centralized data throughout the application. if your application is fairly small, you can follwo this tutorial for a simple login form function.
http://www.databasedev.co.uk/login.html
To distinguish user levels you need to create a user role table and assign roles for each user. Create a new function which checks if a user has/is assigned to the requested roles if yes allow to perform the action. If not warn the user. something like this:
tables like:
- tbl_user: {user_id, user_name ....}
- tbl_roles: {role_id, role_name, ...}
- tbl_user_roles: {role_id,user_id, assigned_date,...}
a simple function to check the user level:
Public Function FN_IS_USER_IN_ROLE(iUSER_ID As Long, iREQ_ROLE As String) As Boolean
Dim MyDB As Database
Dim MyRS As Recordset
On Error GoTo FN_IS_USER_IN_ROLE_Error
FN_IS_USER_IN_ROLE = False
Dim SQL_GET As String
SQL_GET = "SELECT tbl_user.user_name, tbl_roles.role_name " & _
"FROM (tbl_user_roles INNER JOIN tbl_user ON tbl_user_roles.user_Id = tbl_user.user_id) INNER JOIN tbl_roles ON tbl_user_roles.role_id = tbl_roles.role_id " & _
"WHERE (((tbl_user.user_id)=" & iUSER_ID & ") AND ((tbl_roles.role_name)='" & iREQ_ROLE & "')); "
Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset(SQL_GET)
Dim mRc As Long
mRc = Nz(MyRS.RecordCount, 0)
If mRc > 0 Then
FN_IS_USER_IN_ROLE = True
End If
On Error GoTo 0
Set MyRS = Nothing
Set MyDB = Nothing
Exit Function
FN_IS_USER_IN_ROLE_Error:
FN_IS_USER_IN_ROLE = False
Dialog.Box "Error " & Err.Number & " (" & Err.description & ") in procedure FN_IS_USER_IN_ROLE", vbExclamation
End Function
and check if the user has enough privilege simply by:
Dim PR As String
PR = "Admin"
If (FN_IS_USER_IN_ROLE(PR)) Then
'Do something
Else
'Me.Undo
'Cancel = True
MsgBox "You do not have sufficient permissions to perform this task!" & vbNewLine & "Required access level: " & PR, vbCritical, "Access denied.."
Exit Sub
End If
Try this and let us know if you succeed.
