Setting usernames/password driven permissions

[accessaspire219]

Hi,
I have a database which contains sensitive data. The objective of the security system for the database would be:
1. None of the users should be allowed to change data
2. None of the users should be allowerd to change code/modify database
3. A user should not be able to view other users data (is there a way of somehow tying the user name to a criteria in a query?)
4. No one without a valid username and password should be allowed to access data.

Basically, once the user logs in I want them to only see the form (and not all the tables and queries) and the output of the form (report) while tying their username to a criteria in a query so that they can only view their data.

Does anyone have any idea how this can be done?
Thanks.

 

[DJkarl]

The forum has many posts on this subject and many suggestions, but the bottom line of all those suggestions is that even with the best possible security in place MS Access is not a secure platform.

Ask yourself How sensitive is your data? If it fell into the wrong hands could there be legal action against you or your company? If the answer is yes I would highly suggest a different platform to host your data.

 

[Insane_ai]

The simple answer is maintain a table with users and passwords then distribute a .MDE (2003 and earlier) or .accde (2007) to eliminate access to the database window. You could then implement further NTFS permissions to permit read only access to the database with the exception of a few users who are designated to enter the data.


Limiting the users to only see their data is as easy as using a query to match the currently logged in user's credentials.

If you are housing sensitive data, I agree with DJKarl, Access is not the product to use.

 

[accessaspire219]

Thanks guys. The data is sensitive because it contains details about the pay of employees and the security is to prevent one from seeing the pay details of the other.
Insane - ai: How do I capture the user's credentials to use in a query? I am new to this stuff - havent done this before EVER!!
Appreciate your help!!

 

[Insane_ai]

Thanks guys. The data is sensitive because it contains details about the pay of employees and the security is to prevent one from seeing the pay details of the other.
Insane - ai: How do I capture the user's credentials to use in a query? I am new to this stuff - havent done this before EVER!!
Appreciate your help!!

OPtion 1:

1. Create a table with user account information.
2. Create a login form that opens with the database.
3. Store the loginID in a hidden field on a dashboard or by setting a global variable then reference this when applying the security.

Option 2:
Read the username of the person logged into the computer. Here is an example.

I created A form with an unbound text box named txtUserName to facilite this. I used Access 2000 for this example.

Private Sub Form_Load()
Dim strUser as String
Set WshNetwork = VBA.CreateObject("WScript.Network")
strUser = WshNetwork.UserName
[txtUserName] = strUser
End Sub


Once you capture the information, you can manipulate it or use it as criteria. If you use the hidden field method you can reference it directly in a query. If you use the script example (modified to work in Access) you would use the variable as criteria in a SQL statement in VBA.

Some considerations for security:

1. Use the Password character mask on the user accounts table for the password field.
2. Consider custom encryption of the password. Something simple like converting the characters to the ASCII values in a string will do.
3. Build your interface for usability first. Then decide what to secure and how, based on what you have built.
4. Build the forms that display sensitve data to rely on the query you build so the users will not be able to scroll through records. If the data source is limited to a singel record, there will be nothing to scroll through.

 

[accessaspire219]

Thanks insane-ai, I will try it out and let you know.

 

[accessaspire219]

Hey, I have this code for a button which will validate the username and password against stored values in a table. However when I run it I get error 2001 "You cancelled previous statement" The red colored text is highlighted in the code. Do you know what could be causing this?

Private Sub enter_Click()
If IsNull(Me.UserName) Or Me.UserName = "" Then
MsgBox "You must enter a username.", vbOKOnly, "Required Data"
Me.UserName.SetFocus
Exit Sub
End If
If IsNull(Me.password) Or Me.password = "" Then
MsgBox "You are required to enter a password.", vbOKOnly, "Required Data"
Me.password.SetFocus
Exit Sub
End If
[COLOR=red]If Me.password.Value = DLookup("Password", "UserInformation", "[UserID]=" & Me.UserName.Value) Then[/COLOR]
UserID = Me.UserName.Value
DoCmd.Close acForm, "Login_Form", acSaveNo
DoCmd.OpenForm "Performance_Reports", acNormal
Else
MsgBox "Password Invalid! Please try again.", vbOKOnly, "Login Failed"
Me.password.SetFocus
End If
intLogonAttempts = intLogonAttempts + 1
If intLogonAttempts > 3 Then
MsgBox "You do not have access to use this database. Please contact Rohit Vaidya/Dave Marcelletti if you need access.", vbCritical, "Restricted Access"
Application.Quit
End If
End Sub

 

[accessaspire219]

Oh and I wasn't sure where I am supposed to put the code you have written above and how to "call" it. It will be helpful to have the username automatically populated with the username of the person logged on to the computer.

Thanks!!