Restrict access to a table when open button is clicked.

[SteHawk85]

Hi

I have a database that requires a log in to gain access. Some people are 'Admin' others aren’t. I have a hidden button when double clicked opens an Employees table. This table is used to check people’s username and password when they log in, it also has information like Access Level and network username. what I need to happen is when this button is double clicked it runs a check against the employees table to see if the users access level is 'Admin', if it is they get access to said table and can update it so new users can be added allowing them to log in, is this possible? The code I am currently using to open the table is below.

Private Sub Image67_DblClick(Cancel As Integer)
 
DoCmd.OpenTable "tblEmployees", acViewNormal
 
End Sub
 

Thanks in advance

Ste

[NeoPa]

Check out Function to Return UserName (NT Login) of Current User. With this you generally have no need for the user to log in separately.

Your table contains what level of access each user has, so look up in the table for the current user and determine their [AccessLevel] from their. Once you have the [AccessLevel] you can simply and easily run certain code only if the [AccessLevel] matches what you require for that code.

[SteHawk85]

Hi

I think I am going to rip my hair out. I have had a go at using the Dlookup function on my piece of code however I'm in new water and don't really know if I am even barking up the right tree. I know where I am going wrong in the code I just don’t know how to right it. The 'If' function is the problem I think but again I could be totally wrong and all of it may be wrong.

Private Sub Image67_DblClick(Cancel As Integer)
Dim strAdminAccess As String
If Me.Image67.OnDblClick = True Then
 
strAdminAccess = DLookup("strAccess", "tblEmployees", "[strAccess]= Admin")
 
DoCmd.OpenTable "tblEmployees", acViewNormal
 
End Sub

i know the '= True' is wrong but i dont know what else to put here.

Ste

[NeoPa]

Sorry to burst any bubbles Ste, but I see no signs here of the logic I suggested. Perhaps if you explained the data structure you're working with we could help some more. The first job is to get the user logon ID though. Without that the rest is going nowhere. You can't finish a race if you never go anywhere near the start-line ;-)

Don't rip out any hair, just take it one simple step at a time. If there's a step you can't manage then don't push on randomly (as you'll find yourself wandering in the desert), stop and report what you're stuck on.

First step - Provide information as to what data you're working with.
Second step - put Mary's code in from the linked article.

[SteHawk85]

Ha! Sorry about yesterday I had 101 things on the go at the same time and I knew I was barking up the wrong tree! And I was certainly wandering lost in the desert. I will read over the link again (this time slowly) and then have another go. I have a bit less on my plate today and after reading I will try and explain what I need.

Thanks for your patience

Ste

[SteHawk85]

Let me start again...

My database already has a 'Log in' system and that works great. It consists of a form which has a combo box containing a list of all users. After selecting the appropiate user they must then enter their password. After they enter the correct password they are given access to the database. As i say this works fine!

The problem I have is on my Switchboard (after they have gained access to the DB) I have a picture which when double clicked opens a table. This table (tblEmployees) is the one which the login form uses to lookup username and password. What I am trying to achieve is when a user double clicks this image in order to gain access, to add a new user to the log in system, I want a check to be run to ensure the user has 'Admin' rights (another piece of data that is held in the Employees table). This hasn’t been requested by the users i am just trying to add another level of security to the DB.

Hope this makes more sense then my rant yesterday

Ste

[NeoPa]

It makes sense, but doesn't take us forward at all. You still have not provided any information pertaining to what fields are in your record, and the reason I ask is that without something else in there it will not be possible. I'm assuming you have the requisite information stored if you're asking such a question, but I can't answer just on the assumption you might have something. I need details of exactly what is there.

Using a home-made system of logins rather than one already available to you and pre-security checked, is not going to change the fundamental concept much. You will simply use that ID instead of the one Windows would have returned. How you manage that within Your data structure though, we can only help with when we know something about it.

[SteHawk85]

Hi Neo

The tblEmployees has the fields - lngEmpID, this is an autonumber ID that is generated when a new user is added, strEmpName, this is a Username e.g. mine is Ste, strEmpPassword, this is there password strAccess, this is the users level either 'Admin' or 'User' and finally fOSUsername, this is our departments username for the network i.e. ben jones would be jonesb.

hope this helps thanks again!

Ste

[NeoPa]

All good, and what does your login system return to you for use in looking up the correct record from the table?

[SteHawk85]

Here's my code I have used for my login system:

Option Compare Database
Private intLogonAttempts As Integer
 
Private Sub cmdLogin_Enter()
'Check to see if data is entered into the UserName combo box
 
    If IsNull(Me.cboEmployee) Or Me.cboEmployee = "" Then
            MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
            Me.cboEmployee.SetFocus
        Exit Sub
    End If
 
'Check to see if data is entered into the password box
 
    If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
            MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
            Me.txtPassword.SetFocus
        Exit Sub
    End If
 
'Check value of password in tblEmployees to see if this matches value chosen in combo box
 
    If Me.txtPassword.Value = DLookup("strEmpPassword", "tblEmployees", "[lngEmpID]=" & Me.cboEmployee.Value) Then
 
        lngMyEmpID = Me.cboEmployee.Value
 
'Close logon form and open splash screen
 
        DoCmd.Close acForm, "frmLogon", acSaveNo
        DoCmd.OpenForm "F Start Up"
 
        Else
        MsgBox "Password Invalid.  Please Try Again", vbOKOnly, "Invalid Entry!"
        Me.txtPassword.SetFocus
    End If
 
'If User Enters incorrect password 3 times database will shutdown
 
    intLogonAttempts = intLogonAttempts + 1
    If intLogonAttempts > 3 Then
        MsgBox "You do not have access to this database.  Please contact your system administrator.", vbCritical, "Restricted Access!"
        Application.Quit
    End If
 
End Sub
 
Private Sub Form_Load()
On Error GoTo ErrorHandler
 
Me.Caption = SSH_APPLICATION_KMBC & " Log On"
 
CleanUpAndExit:
    Exit Sub
 
ErrorHandler:
    Call MsgBox("An error has been encountered" & vbCrLf & vbCrLf & "Error Description: " & Err.Description & vbCrLf & "Error Number: " & Err.Number, , "Error Deleting Record")
    Resume CleanUpAndExit
End Sub
 
Private Sub Form_Open(Cancel As Integer)
'On open set focus to combo box
Me.cboEmployee.SetFocus
 
Dim i As Integer
For i = 1 To CommandBars.Count
CommandBars(i).Enabled = False
Next i
 
End Sub
 
Private Sub cboEmployee_AfterUpdate()
'After selecting user name set focus to password field
Me.txtPassword.SetFocus
End Sub
 
Private Sub cmdLogin_Click()
 
'Check to see if data is entered into the UserName combo box
 
    If IsNull(Me.cboEmployee) Or Me.cboEmployee = "" Then
            MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
            Me.cboEmployee.SetFocus
        Exit Sub
    End If
 
'Check to see if data is entered into the password box
 
    If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
            MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
            Me.txtPassword.SetFocus
        Exit Sub
    End If
 
'Check value of password in tblEmployees to see if this matches value chosen in combo box
 
    If Me.txtPassword.Value = DLookup("strEmpPassword", "tblEmployees", "[lngEmpID]=" & Me.cboEmployee.Value) Then
 
        lngMyEmpID = Me.cboEmployee.Value
 
'Close logon form and open splash screen
 
        DoCmd.Close acForm, "frmLogon", acSaveNo
        DoCmd.OpenForm "F Start Up"
 
        Else
        MsgBox "Password Invalid.  Please Try Again", vbOKOnly, "Invalid Entry!"
        Me.txtPassword.SetFocus
    End If
 
'If User Enters incorrect password 3 times database will shutdown
 
    intLogonAttempts = intLogonAttempts + 1
    If intLogonAttempts > 3 Then
        MsgBox "You do not have access to this database.  Please contact your system administrator.", vbCritical, "Restricted Access!"
        Application.Quit
    End If
 
End Sub
 
 
 

When the correct Password is entered it takes you too the 'F Start Up' screen, which is a form showing Data Protection Policy and is timed for 30 secs then that closes and takes you to the CLASwitchboard.

The 'Admin' and 'User' statuses have exactly the same access, however this is why I want the tblEmployees to be protected from ~Users so only 'Admin' staff can add new staff members to the system.

ta

Ste

[NeoPa]

I have no idea why you might post this code. I can only imagine you have misunderstood what I thought was a simple question. Let me just point you to Before Posting (VBA or SQL) Code before leaving that with you and trying to rephrase the question so that we can continue sensibly.

What I need from you, as last details, is the name and type of the variable (or control) in which you store the value of the User ID that has already been security-checked (No matter how that checking is done), and which field in the table that this value should match?

I've understood the requirement for the question from the first post. The only thing holding back an apposite answer is the lack of relevant details so far available in the thread. You might want to make a mental note of how important it is to provide the relevant information for the question in the first place (as is instructed in those sticky threads posted to assist members to ask sensible questions). It would have saved you (not to mention others involved) a lot of time and effort on this occasion ;-)

[SteHawk85]

Hi Neo

i'm sorry i am annoying myself with this at the minute so god knows how you feel.

i'm still a little confused (think my brain must be fried), the field in the table that the log in system checks against are the strEmpName - the user must select their username from a combo box then the strEmpPassword - this is a textbox, must match to gain access. this line of code shows how.

'Check value of password in tblEmployees to see if this matches value chosen in combo box 
 
    If Me.txtPassword.Value = DLookup("strEmpPassword", "tblEmployees", "[lngEmpID]=" & Me.cboEmployee.Value) Then 
 
        lngMyEmpID = Me.cboEmployee.Value 
 

once again sorry and hope this is what your after in terms of information

[NeoPa]

Before I go past this let me just advise that storing passwords in a table in plain text ensures your security system is a long way short of secure. This is never advisable.

Your explanation is not too clear to be fair, and what it implies contradicts the code shown, so I'll go with the code and assume you have an integer value available in the variable [lngMyEmpID] which can be used to determine the user record that you're interested in.

The code to get the string value for [strAccess] would be (It's really very simple after all that) :

DLookup(Expr:="[strAccess]", Domain:="[tblEmployees]", Criteria:="[lngEmpID]=" & lngMyEmpID)

Clearly, this would return a string value, but you'd need a line of code that does something with it.

[SteHawk85]

Cheers mate!

Yes you are right the lngEmpID is an integer and the password's are stored as text however you can only see them as ********** in the text box. I didn’t actually set up the login system and it has just been 'borrowed' from another DB. Hence the confusion!

Thanks again for all your help! I will have a ponder and try the code

[NeoPa]

No worries on the advice. Pleased to help.

On the security point though, if you are under the impression that a 5-year-old couldn't view the data in the table without recourse to your form, then you might want to disabuse yourself of that particular impression. I appreciate that most office users don't have quite that level of intellect, and most won't even think about looking, but security is supposed to restrict access forcefully, rather than simply relying on the laziness / lack of awareness of any potential wrong-doer.

I also appreciate you're not looking for extra tasks to be lumbered with, but it's only right that I should bring this to your attention. At least then you understand what you're dealing with and can make informed choices on the matter.

[SteHawk85]

To be honest I totally agree with you! I was just told to use the same log in system as used on previous DB's. Do you think I would be better off using the code you originally posted frm MMcCarthy?

The data that is being held is very sensitive, and I feel that a more robust level of security is important. With MMc code am I am able to select certain users to have access? The DB is held on a secure server that only certain users have access to, but there will be a lot of people who can see the DB who don't need to.

I would rather have the extra task and feel comfotable with what I am handing over then worry about somebody gaining access who shouldn't have any. Plus, since I didnt write the code I dont really know it or feel 100% comfortable with it.

[NeoPa]

SteHawk85:
Do you think I would be better off using the code you originally posted frm MMcCarthy?

I like easy questions. Yes.

It means, assuming you're happy with NT Domain Security, that you don't even need to maintain a password at all in your table and still have access to all other user-level info that's required.

If you do end up using a bespoke security system in Access, then at least obscure the password data in the table (as well as on the form of course). This typically means to encrypt it, and there are articles here to help with that (NB. The recommended approach is still to use Mary's suggestion and then passwords don't need to be stored in the database) :

1. AES Encryption Algorithm for VBA and VBScript.
2. RC4 Encryption Algorithm for VBA and VBScript.
3. SHA2 Cryptographic Hash Algorithm for VBA and VBScript.

[SteHawk85]

Hi Neo

The passwords are obsecured in the Table as *************, however I would still prefer to use NT domain security. Can you provide any information on it please? For example how it works and an example of how best to use it. Do I need a .dll to run the function?

Ta

[NeoPa]

SteHawk85:
The passwords are obsecured in the Table as *************

That's good. Now only 10-year-olds and older could crack it with a simple command such as ?DLookup("[Password]","[SecurityTable]","[AccountName]='Bob'") from the Immediate pane of the VBA IDE ;-D

SteHawk85:
Can you provide any information on it please?

You should find what you need in the linked article. If that isn't clear enough for you then I'll need an explanation of how far you did get and where you got stuck. You don't need any special DLLs to run it. Windows libraries are available as standard and the how of using them is included within the linked article.

[SteHawk85]

Think this has taken me well outside my comfort zone and I don’t really know what to do with it. I have added the code as a function and saved it to the modules section of my DB. Now I am stuck as to what I am meant to do, I have tried creating a new form and asking for the username to be added and then when a button is pressed it calls the function. Nothing happens! Little bit stuck after reading the code and the comments there seems to be little in the way of advice as to what I am meant to do with this code after adding it as a function. Obviously there is a certain level of experience required to understand this and I am totally new to VBA.

[NeoPa]

OK. Let's start with the fact that the function will return a string value that reflects the logon ID that the user used to log on to the domain. My guess, from what you've posted already, is that this would probably match a field you referred to as [fOSUsername]. It would certainly be unique within the domain. The filter for such a record in your table would then be (assuming "JonesB" is the value returned from Mary's function) :

([fOSUsername]='JonesB')

Thus, your code might look similar to :

Dim strUser As String, strWhere As String
 
strUser = sys_OrigUserID()
strWhere = Replace("[fOSUsername]='%U'", "%U", strUser)
DLookup(Expr:="[strAccess]", Domain:="[tblEmployees]", Criteria:=strWhere)

Sorry this took so long. I had it mostly done earlier (and thought I'd posted it), then I got an interruption and forgot all about it until now.