Question Database User Level Security

[swan]

Hi all,

Let me start by thanking you for reading my post, and for any assistance you may offer.

I posted this in the general forum as I wasn't sure which other section user level security would fit into.

I have a database whereby users already have to sign in with a username and password, using a form which them allows them to view a front page. The front page allows them to access data and add/amend it.

I now have to adjust this setup as we have directors who want to view the system, but we don't want them to amend it.

Is there anyway to change the current setup that I already have or do I have to scrap it and start again?

Thank you.

 

[MarkK]

I have a database whereby users already have to sign in with a username and password

Access used to ship with a built-in security mechanism called "User Level Security" that allowed one to set permission for database objects. Is this what you are talking about? Or do you have a custom login mechanism that you or some other developer wrote?

Microsoft is abandoning User Level Security in the new accdb file format, so it is not supported as of Access 2007, afaik.

Probably if you wrote your own security, you could modify it rather than scrap it. Maybe you want to scrap "User Level Security" if you are committed to using Access into the future.

HTH

 

[gemma-the-husky]

you will need to modify all the forms to open readonly, if you really want to do this.
eg set allowedits on open, based on who the user is.

alternatively, tell the directors they are seeing live data, so please do not amend it ....

 

[swan]

Thank you for your replies.

I am using 2010, and know that the user level security does effectively not now.

However, I wondered if there was something that I could add to my existing system.

The "Login" form requires the user to put in a matching username and password to a table called "User".

A macro attached to the "Ok" button actions this:

Private Sub Command1_Click()
If IsNull(Me.txtUsername) Then
MsgBox "Please enter Username", vbInformation, "Username Required"
Me.txtUsername.SetFocus
ElseIf IsNull(Me.txtPassword) Then
MsgBox "Please enter Password", vbInformation, "Password Required"
Me.txtPassword.SetFocus
Else
'process the job
If (IsNull(DLookup("UserLogin", "Users", "UserLogin = '" & Me.txtUsername.Value & "' And password = '" & Me.txtPassword.Value & "'"))) Then
MsgBox "Incorrect Username or Password"
Else
'open the front page
DoCmd.OpenForm "Front Page"
DoCmd.Close acForm, "Login", acSaveNo
End If
End If
End Sub

Can add anything into the "User" table to define whether they have read or read/write access? If so, how can this be linked to what they can then use?

Thank you.

 

[Roemer]

Hi,
I have done something similar. In my "user" table I was adding additional columns (Yes/No), like Administrator, Reviewer etc. Like this I can determine the security per user.
Based on this user table I am using dlookup in functions to gather the info about the user Access. I am calling these functions in the forms when opening them to determine whether they should get edit or read-only Access:

One of my functions:

Public Function SecRoleAdm(strImpUserId As String)
Dim intRespAdm As Integer
'*****Lookup what security role is assigned to a user*****
'if result =...
' 1 = Reconciler
' 2 = Reviewer 1 or 2
' 3 = Controller
' 4 = Administrator
' 5 = Display (e.g. for an auditor)
If IsNull(DLookup("FncAdm", "tbl_user", "UserID = '" & strImpUserId & "'")) Then
intRespAdm = 0
ElseIf (DLookup("FncAdm", "tbl_user", "UserID = '" & strImpUserId & "'") = True) Then
intRespAdm = 4
End If
SecRoleAdm = intRespAdm
End Function

Extract from my open form module:

intAdm = SecRoleAdm(strImpUserId)
If intAdm = 4 Then
'the form shall be opened in edit mode
DoCmd.OpenForm strFrmName, acNormal, , , acFormEdit, acWindowNormal
Else
DoCmd.OpenForm strFrmName, acNormal, , , acFormReadOnly, acWindowNormal
GoTo MessageNoAccess
End If

Not sure whether this is the best Approach, but it is working nicely.

 

[GinaWhipp]

Have a look here, there are additional links at the bottom of the page...
http://www.access-diva.com/f7.html

 

[The_Doc_Man]

In general, security is no longer an issue for the Access environment. It has been "migrated" to the developer's responsibility.

There are numerous ways to secure an Access database superficially, though the sad truth is that a "true" Access database, even with FE/BE split, is not impossible to crack with just a little effort. But start with this idea: If your users can see the object navigation pane that shows tables, queries, forms, etc. - you've already lost the battle.

From there, your setup of a login page plus some mechanism to show data is a good start as long as there is no way for anyone but a developer to bypass that form easily. Which leads to the concept of user roles, and the upper-level muckety-mucks just won't get that type of role.

You can build a dispatcher or switchboard that you can use to launch data-specific forms, and you can choose to open those forms in read-only mode for some roles. It is possible to take it farther than that, but not usually every easy if you have to retrofit security.

The final question is that to implement security, you need a buy-in from management and they need to understand that there is cost/risk consideration. How much risk are they willing to absorb and how much (labor) cost are they willing to subsidize to reach some level of security? If you don't have an answer to this question, you are groping in the dark and there will be no light at the end of the tunnel.

 

[spikepl]

@Roemer

You should know that you've committed the usual sin of "doing Access by Excel", by burying information for your data in column names. You have to look in different columns to look up similar information - that is the Excel way.

Look up data normalization on google. The magic sentence is "store similar data in the same container". There is no normalization police though, just eternal PITA, since superfluous work is required in input and maintenance of redundant unnormalized data (Just think what you need to do if you add one more permission level to your list of privileges)

 

[ButtonMoon]

The final question is that to implement security, you need a buy-in from management and they need to understand that there is cost/risk consideration. How much risk are they willing to absorb and how much (labor) cost are they willing to subsidize to reach some level of security? If you don't have an answer to this question, you are groping in the dark and there will be no light at the end of the tunnel.

Cost vs security risk is not such a big deal because commercial grade security is extremely cheap and easy to implement in most database architectures. The real dilemma here is not one of cost but of using the right technology: you can have a level of security that satisfies industry and regulatory standards OR you can use Accdb, but you cannot do both.

 

[Roemer]

@Spikepl
Yes, you are right. I did not normalize the data in this case. Since my security setup is pretty simple it is sufficient. But I agree, in a more "sophisticated" tool where you don't know the final security setup normalization will be a must AND more criteria (like security objects) will be required.

 

[JLCantara]

Swan:
Ignore the bubbles popping out from the replies: useless...
Gina's (remember me?) links are interesting but at your level, I don't understand that you don't see the solution! Just add a yes/no field, in the user table, indicating Yes->Read/write, No->Read only. Upon entry, that can be saved in a utilities module, and when a form is selected then check the status of the user...

 

[GinaWhipp]

Yes, JLCantara I remember...

 

[swan]

Thank you all for your replies, and apologies for the late response.

@JLCantara, I have added the yes/no field into my "Users" table, but I'm not sure what to do next. What do you mean by a utilities module?

Thanks.

 

[JLCantara]

@Swan: a Utility module is kind of a fourre-tout, a module where you keep all kinds of unrelated procs. Add a public UserPermission as Boolean. So when the user logs in, fetch the yes/no field and store it in UserPermission. When he opens a form, in the open Event use it to set the form attributes (form data tab).

 

[swan]

Thanks for coming back to me.

On opening the database, the user is presented with a login form. It requests their username and password, then allows them to press an "ok" button to enter, or a "cancel" button to close the system.

The "ok" button has the following code behind it:

Private Sub Command1_Click()
If IsNull(Me.txtUsername) Then
MsgBox "Please enter Username", vbInformation, "Username Required"
Me.txtUsername.SetFocus
ElseIf IsNull(Me.txtPassword) Then
MsgBox "Please enter Password", vbInformation, "Password Required"
Me.txtPassword.SetFocus
Else
'process the job
If (IsNull(DLookup("UserLogin", "Users", "UserLogin = '" & Me.txtUsername.Value & "' And password = '" & Me.txtPassword.Value & "'"))) Then
MsgBox "Incorrect Username or Password"
Else
'open the front page
DoCmd.OpenForm "Front Page"
DoCmd.Close acForm, "Login", acSaveNo
End If
End If
End Sub


Do I alter this to add in the UserPermission as Boolean? Or do I alter/add it somewhere else?

Thank you.

 

[JLCantara]

Did you read #14? In the user table add a UserPerm field that YOU and only you can set set. As it is, I suspect your sub sub is bugged. I will have a look at it and will be back...

 

[JLCantara]

Here is MY version off your command button:

 Private Sub CmdLogin_Click()
    On Error GoTo Erreur
     If IsNull(Me.txtUsername) Then
        MsgBox "Please enter Username", vbInformation, "Username Required"
        Me.txtUsername.SetFocus
        GoTo ExitProc
    End If
    
    If IsNull(Me.txtPassword) Then
        MsgBox "Please enter Password", vbInformation, "Password Required"
        Me.txtPassword.SetFocus
        GoTo ExitProc
    End If
    
    'process the job
    Utility.UserPrm = DLookup("UserPrm", "Users", _
        "UserLogin = '" & Me.txtUsername.Value & "' And password = '" & Me.txtPassword.Value & "'")
    If IsNull(Utility.UserPrm) Then
        MsgBox "Incorrect Username or Password"
    Else
        'open the front page
        DoCmd.OpenForm "Front Page"
        DoCmd.Close acForm, "Login", acSaveNo
    End If
    
ExitProc:
    Exit Sub
    
Erreur:
    MsgBox Err.Description, vbCritical, "Error #" & Err.Number
    Resume ExitProc
 End Sub

Of course, as said before YOU HAVE TO CREATE the Utility module.
Next, for all your forms, create an OnOpen event that will control the behaviour of its data handling.