Access control.

[travisdh]

Hello.

I was wondering if anyone has implemented their own access control solutions in their access databases. I got a system working for login but want to now limit users access to both forms, and view, add, edit and delete access to each form or report and was wondering how people have managed access control and permissions in their databases.

 

[Galaxiom]

I have used a quick and dirty technique where the Tag Property string is consulted to limit access. Each user is allocated a security level. The Form load event comparares the Tag on control with the user security level and modifies the visibility or locking accordingly. The whole job can be done in a For Each loop.

The Tag can even hold a series of security level values if you want. However it does mean the security level of a form can only be modified by changing the Tag property.

Alternative the Tag can be allocated a string that then retrieves the security level from another table.

However by the time you do this you might as well directly consult the value from a table that holds the name and security information about objects. They are then easily edited using a form by a person with sufficient rights.

 

[Dairy Farmer]

In the table that contains the user accounts add a field for permissions.
i.e. 0= No access, 1=Read only, 2=Read + Add, 3=Read + Add +Edit.........

In the code for the button to open a form, use an if statement to allow opening.

If [UserPersission]>0 then
DoCmd.OpenForm "Form1"
Else
msgbox "No permission to open"
End if

Set the permissions for all forms to not allow edits, additions and deletions.
Have buttons on your form that add or delete records and use an If statment

If [UserPermission] >1 then
    Me.AllowAdditions = True
Else
    Me.AllowAdditions = False
End If

So basically all permissions or default to False
Only users with permission > 0 can open form
Only users with permission > 1 can add
Only users with permission > 2 can add or edit
Only users with permission > 3 can add, edit or delete

Get the idea?

 

[travisdh]

Thanks for your help, I was wondering how in the code i can define a global variable that applies to the open session of ms access (since there will be multiple users, i only want it to apply to the frontend program) that i can use to store the users permissions and logged in user, instead of requerying the database all the time.

I was hoping i could create variables for the currently logged in user, each of the major forms, and the permissions for each of the major forms, for example

Dim Username as String
dim LastAction as Time
dim StrSamples as string
dim StrCustomerservice as string
dim StrReport as string

and so on, and then take the appropiate permission, or is it better to just store the username, and the last action and requery the permissions database and how might i go about doing that so for example if the currentlyloggedinuser is jblogs and they tried to access samples_DS, it would query the employees table for employeeusername 'jbloggs' and return the permission field where formname = samples_DS (or whatever the current form name is)

 

[Galaxiom]

Retrieve the current username from Windows and store it as a Global Variable in VBA.
You could copy the permissions to a local table or as variables. However the load to retreive the permission value across the network is not huge.

Be aware in any of these scheme that they are only as good as the seurity on the table. If a user can write to the security table they just need to open it and change their level. Using linked tables makes it really vulnerable.

There are several ways around this.
Use Access security to prevent write permissons to the table.
Store the security in MS SQL Server (the Express version is free).
Keep the security in a text file with limited premissions and connect to it or read it directly.

Also not that your security will need to work with a domain login and prevent certain ways of working around limitations. If you system allows them to login locally they may be able to fake their identity.

The designer has to be very aware in any system. Security is only as good as its weakest vulnerablility.

 

[Simon_MT]

Although this might seem anarchical, I'm not a fan to securing down a database. If an instance arises that no-one is available with the right permissions then an process or event could be completely stalled. You have to cater for any eventually and being flexible and not to draconian with security is about balancing the need to be safe rather than sorry.

Simon

 

[Dairy Farmer]

This link may help:

Summary: Learn how to use Access 2007 to protect the information in your databases. Learn about encrypting files, administering passwords, converting databases to the new ACCDB and ACCDE formats, and using other security options such as SQL Server.

Security Considerations and Guidance for Access 2007

 

[arichins]

Retrieve the current username from Windows and store it as a Global Variable in VBA.
You could copy the permissions to a local table or as variables. However the load to retreive the permission value across the network is not huge.

Be aware in any of these scheme that they are only as good as the seurity on the table. If a user can write to the security table they just need to open it and change their level. Using linked tables makes it really vulnerable.

There are several ways around this.
Use Access security to prevent write permissons to the table.
Store the security in MS SQL Server (the Express version is free).
Keep the security in a text file with limited premissions and connect to it or read it directly.

Also not that your security will need to work with a domain login and prevent certain ways of working around limitations. If you system allows them to login locally they may be able to fake their identity.

The designer has to be very aware in any system. Security is only as good as its weakest vulnerablility.

We have our ADP project connect to SQL using windows authentication, then, in SQL Server, you add in the same windows user accounts, and put them into groups etc. Then for each table, view or SP you can define permissions directly to the SQL Server objects. But the windows accounts need to be on the same domain as the sql server, or it usually will not work.

This has a limit though, in it doesn't account for application level security. Like if you have two sets of users sharing the same table, and you want each of them to only be able to update stuff from their own building.

In that case, you can use the users identity itself to determine if certain controls should be locked or invisible. In a larger organization, you can make a user table and query what group they're in.