We help IT Professionals succeed at work.

Users, privilegies.

Tony asked
Users and privileges :

I have a database with this panel:

I need to give privileges to users.  For example:  some users can only access Customers and Products, but not Invoices.  Some users can only see Products.  Some users can access all.

How can I create a table with the users, store the privileges (yes/no) by user and also use this information in the Panel to be able to activate or not the different options according to the privileges?
Watch Question

This is a rather large question to be answered as one.
Firstly you need to have some form of login to know who the user is - so user table needs to know logins (how will you manage passwords) or if you're in a domain based network you could validate against their windows login).

Then you need to store what rights they have and then based on those , use code to display the options that they are entitled to - will depend how you run that panel as to how you do that.

Have you considered how you secure that data at the table level (local Access table, linked Access table or some 3rd party db?

Partha MandayamTechnical Director

You can have a user table
user_id, user_name
and a tables table
table_id, name
and a user_tables table which defines which user can access which table
user_id, table_id
So when user logs in, you will check and grant access to all the tables which they have access to in the user_table table
Software & Systems Engineer
Similary a user table that you store UserName/Password and a separate table that holds which user has "access" to where...the concept is focused on forms/reports
AA : 1 User : John Pass : 123
AA: 2 User: Tony , Pass :321
On the the Privileges table
UserID     FormName
 1                Orders
 1                Sales
 2                Sales
So user John has "access" to Orders & Sales and user Tony has "access" to Sales
So in the navigation scheme you use...just make a simple lookup and either hide or deny access to these forms/reports.
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

You might want to have a look at this:


  Would be quicker (and probably cheaper) than developing your own setup.    Security though as Kelvin said covers a lot of ground;

1. Record level
2. Field by field level
3. Ability to carry out certain processes (i.e. like close a G/L period).

And where/what you store the data in can have a big impact.   ACE/JET (default db engine with Access) has no real security, where are SQL server does.

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

This technique does not address the issue of db security, just how I deal with this in the FE interface.

I don't usually do this at the user level, I create roles (tbl_Roles) and user-roles (tbl_UserRoles).

Then, when I load a form (Form_Load event), I generally enable/disable the button based upon whether the user has belongs to a certain role.

me.cmd_Customers.Enabled = fnUserRole(fosusername, "Customers")

I use fOSUserName to get the windows login of the current user; I also use this vale in Users and User_Roles tables in field [UserID].
Public Function fnUserRole(UserID as string, RoleName) as boolean
    Dim strCriteria as string
    strCriteria = "([UserID] = '" & UserID & "') AND ([Role] = '" & Rolename & "')"
    fnUserRole = NZ(Dlookup("RoleID", "tbl_UserRoles", strCriteria),0) > 0
End Function

Open in new window