Role-based access MS Access2016 (1 Viewer)

[aamyra]

hello,
when a user opening access, it should be able to access the form according to the roles
The table name is tblUser and the users are General User, HR, and Admin.
Currently, In the table I have the roles assigned number with GeneralUser (read-write) = 1, HR(Read-only) = 2, Admin(Full access) = 3.
table structure: Name, ID, Roles ID, Roles Desc

How to do that?
For e.g user Amyra Full access to view, read-write in the form
JohnSmith read-only

 

[Galaxiom]

For this kind of security you would be better off using an SQL Server backend where permissions are intrinsic.

 

[aamyra]

For this kind of security you would be better off using an SQL Server backend where permissions are intrinsic.

i have only ms access to do this.

 

[Gasman]

Store the user level when they log on into a Tempvar/global variable.
Set form access levels on form load.

 

[aamyra]

Store the user level when they log on into a Tempvar/global variable.
Set form access levels on form load.

do u have a sample of how to do it plz?

 

[Gasman]

I actually used a switchboard form and the user level dictated what options were available, so I did not have to add code to all the forms, just the switchboard table.
In my login form, I also supplied a combo with allowed users.
I know that makes cracking the password easier, but we were in a simple environment with trustworthy employees, plus it was for managers only, plus myself.

    TempVars("EmployeeID").Value = Me.cboEmployeeID.Column(0)
    TempVars("Employee").Value = Me.cboEmployeeID.Column(1)
    TempVars("UserLevel").Value = DLookup("DataOrder", "tblLookup", "LookupID = " & Me.cboEmployeeID.Column(3))
    DoCmd.OpenForm "Switchboard"
    DoCmd.Close acForm, strFormName

 

[jdraw]

Watch this youtube video by Steve bishop for ideas.

AWF Member Isladogs has a series of articles on improving database security here.

You can also research/google "Role Based Access Control" for concepts and approaches.

 

[isladogs]

I also have an example password login app with different user levels:

Password Login

This utility has been provided to manage user passwords and also keep a user session log

www.isladogs.co.uk

 

[theDBguy]

Hi @aamyra

Welcome to AWF!

 

[bastanu]

Please feel free to try my free utility:
http://forestbyte.com/ms-access-util...access-levels/
Cheers,

 

[The_Doc_Man]

In general, using "pure" Access as opposed to a foreign back-end, you are obliged to "roll your own" security. Search this forum for MANY articles on these topics:

Role-based security
Securing a database
User roles

Unfortunately, back in 2003, the problem with User-Level security in Access had grown to be such a problem that they deprecated the ability. At the time, the U.S. Dept. of Defense listed MCAS (the Microsoft Computer Access Security module that implemented User Level security) as one of the top 10 security threats to any computer-based organization. It was buggier than a spring termite swarm. Now, even if you had everything else you needed, modern versions of Access won't acknowledge the settings of an MDW file even if you have the system setup to run MDB files.

 

[Pat Hartman]

Here's another sample of simple security. It uses four security options - View, Edit, Add, Delete with values ranging from 0-9
Then each form is assigned four security options for View, Edit, Add, Delete. In each form, the user's security options are matched to those of the form. For example, code in the form's BeforeInsert event compares the "Add" values for the user to the form. The BeforeUpdate event compares the "Edit" values, the OpenEvent compares the "View" options and the OnDeleteConfirm event compares the "Delete" values. Any subforms or popups must default to the same security as the form that spawned them unless you want to make a more complicated setup.

Switchboard with simple security

This is a custom switchboard. It expands on the Switchboard Items table to add four additional columns to define view/edit/add/delete security levels for switchboard items and forms/reports referenced by them. It includes tables to manage users and also to assign them security levels. The user...

www.access-programmers.co.uk

 

[aamyra]

I actually used a switchboard form and the user level dictated what options were available, so I did not have to add code to all the forms, just the switchboard table.
In my login form, I also supplied a combo with allowed users.
I know that makes cracking the password easier, but we were in a simple environment with trustworthy employees, plus it was for managers only, plus myself.

    TempVars("EmployeeID").Value = Me.cboEmployeeID.Column(0)
    TempVars("Employee").Value = Me.cboEmployeeID.Column(1)
    TempVars("UserLevel").Value = DLookup("DataOrder", "tblLookup", "LookupID = " & Me.cboEmployeeID.Column(3))
    DoCmd.OpenForm "Switchboard"
    DoCmd.Close acForm, strFormName

u have a sample project for reference?

 

[Gasman]

No, sorry, not without extracting what is required. I also put in a tweak to make it a little more difficult to work out who could do what, and that might confuse you.
I'd go with Pat Hartman's model. It will definitely be more robust than mine.

 

[KitaYama]

I also put in a tweak to make it a little more difficult

I'd really like to see how it works.
No need for details. Just a simple explanation (even in a separate thread) is appreciated.

 

[aamyra]

am still struggling with that login and make the users have the read-only or able to change the field values....
is there a more simple way to do that?
when user click on the access to open it get authenticate and the form open based on the security level.

 

[Pat Hartman]

You've had several suggestions. Which one are you trying to implement?

Post YOUR version of the code and tell us what is or is not happening.