Levels of User Access (1 Viewer)

[MrBee]

Good afternoon!

I am building a database at the moment that is based on 2 Forms: Adding a new client, editing an existing client. The main form on opening has two buttons, one for each of these client forms. I want the database to be set up to have 4 levels of User Access:

AUTHORISED USER: To be able to run queries and reports already set up; I am assuming I would need a third button to go to a new form displaying buttons for the various queries and reports.

APPROVED USER: To be able to do as the AUTHORISED USER, but also use the Edit Client form to amend data for existing clients.

SUPER USER: To be able to also add new clients using the New Client form.

SUPER ADMIN: Access to everything, including accessing tables; basically as if there were no security controls at all in place.

Any advice on how to start setting this up would be much appreciated.

Many thanks,

MrBee

 

[bob fitz]

I am building a database at the moment that is based on 2 Forms:

Before you get too involved with what forms you may or may not need, I would advise that you make sure your tables are properly designed and normalized. Too many people start of with forms when they should have been looking at their tables first.

Would you like to show us your tables?

 

[Steve R.]

As an additional consideration. How are you configuring your database system?
1. Will it be a split MS Access database system, with an MS Access Front-End and an MS Access Back-End?
2. Will it be split with an MS Access Front-End, but using MySQL (or other database) as the Back-End?

 

[arnelgp]

Would you like to show us your tables?

As an additional consideration. How are you configuring your database system?

dont you have any recommendation on the Actual request rather than diversion of topic?

 

[bastanu]

MrBee,
Have a look at my utility that allows you to set up custom access levels: http://forestbyte.com/ms-access-utilities/fba-custom-access-levels/.
Cheers,

 

[onur_can]

I agree with mr bob fitz. First of all, tables and relationships are very important, then you can spend time with forms.

 

[Steve R.]

dont you have any recommendation on the Actual request rather than diversion of topic?

That is not a diversion. Should @MrBee be using MySQL (or equivalent database) as a Back-End, access permissions can be set there.

 

[arnelgp]

access permissions can be set there.

what i understand is the OP need it on the FE.
if it is set on the BE (mssql/mysql), we still need to set it up
on the FE, to prevent popping up of unwanted msg from the server
when a form is opened.

see this real-world demo.
username: arnel
password: puzon

go to User Management on the Ribbon.
the access to the system (access to Button on the Ribbon).

try exiting an logging again as user "common" (password is common also).
you will see that some of the buttons on ribbon are disabled.

 

[Isaac]

@MrBee
If your users exist in the context of typical, corporate IT dept-managed network user identities, just make sure there is a table showing their network username as well as various access levels. That way you can look it up in your app at any time.

 

[The_Doc_Man]

The way I approached this was similar to what Isaac just suggested. In fact, because our domain was locked down pretty well, I requested and got permission to "trust" the domain, thus using system calls to identify my users. Some security folks call that a "single-sign-on" method.

An important concept is that if you are going to worry about unauthorized actions, you NEVER let people see a situation where they could perform an unauthorized action. I.e. turn off the navigation pane and ribbon for all users. (Which means you keep a non-secured version of the DB for yourself as a developer version.)

I had a few user levels but many projects, so sometimes people had USER access to everything. Other times, they had access only to specific projects. The way I handled that was that each form's FORM_OPEN event looked at the user ID and role (which for my system were constant for one session). I had global items to record the username, userrole, and a collection of userproj (projects).

In every FORM_OPEN, if your role allowed you to use the form, you could launch that form. Otherwise, FORM_OPEN allows a CANCEL, so if your role wasn't high enough, I popped up a message box explaining that you couldn't use the form - and why. Then I canceled the OPEN and the user got locked out. If you could use the form but were restricted by projects, I had a table of projects YOU could access and put up a filter on the forms so that they would only show items in your projects. You never saw anything you weren't supposed to see.

The key to this is that besides your project-related tables you also need some user-related tables. You need to think ahead of time about security issues and remember that Access will only do - or not do - as you tell it. If you don't tell it what to NOT do, you can guess what it WILL do.

 

[Pat Hartman]

I would recommend using a single form for both add and update. That will allow you to have all your validation in one place. If you use two different forms, you will need validation code in both and that means you will always have to update two code modules unless you start by consolidating the validation in a separate which you call from two places. Validation for add/update is generally the same but if it needs to deviate, you can tell if the record you are working on is new (add) or not(update) so you can do what you need to.

 

[gemma-the-husky]

The tricky thing is that you may need code inside every form to change full access to read access only.

for just opening a form/report - you can have a table with all the form names specifying the required user names/user groups/access level and test those before either a) opening the form or b) displaying the form on a switchboard. Alternatively within each form, you can test the user access. with a similar procedure.

The cleanest way to control access in general is by user group (IMO) - as then any new users can just be allocated to user groups to give them appropriate access.

 

[Pat Hartman]

User groups were killed in A2007. Access no longer contains any internal security. If you want to separate user groups, you have to create your own security model.

There are several simple security "systems" that can be found by searching. One is called LASSIE (I think) Another is found in a database I post that uses a custom switchboard. Let me know if you want it and I'll post it again.

 

[mike60smart]

Pat
I would be interested in looking at your custom switchboard.

 

[Gasman]

Pat
I would be interested in looking at your custom switchboard.

@mike60smart
You even responded to this thread?

https://www.access-programmers.co.uk/forums/threads/database-help.317629/#post-1764170

 

[gemma-the-husky]

User groups were killed in A2007. Access no longer contains any internal security. If you want to separate user groups, you have to create your own security model.

There are several simple security "systems" that can be found by searching. One is called LASSIE (I think) Another is found in a database I post that uses a custom switchboard. Let me know if you want it and I'll post it again.

Yes - I didn't mean using access user groups, which were quite hard to use. I meant roll your own.

I meant have a tblUsers, a tblGroups and a tblUserGroups - and assign permissions to forms based on a user's membership of a particular group.
You could assign permissions at a user level, but once you get more than a handful of users, it's much easier to allocate users to groups, than to assign permissions for each user.

Then as you add new forms and reports you can assume that all groups can access them - but if you want just limited access it's easer to include a given group to to assign permissions to a number of individual users, rather than to set all the users individually.

 

[The_Doc_Man]

According to the U.S. government regulations and guidelines on roles and permissions, any time you potentially have more than a very small handful of users, your preferred method is to assign privileges and access rights to groups and then add users as members of the group. Thus, users have NO rights - but their group membership confers the necessary rights. The standard explicitly allows the possibility of being a member of more than one group, though you would then have to decide whether the rights were additive or not. To Windows, rights and permissions via multiple group membership ARE additive.

 

[Pat Hartman]

I wasn't disputing the "group" concept. I agree. It just sounded the way it was written that it was referring to the .mdb security model