How to limit usability of Access objects per user? (2 Viewers)

[amorosik]

How to implement a generic functionality limitation system for some users of the Access procedure?
Let me explain, let's suppose that the access to the Access procedure is granted to users user1, user2, user3
Suppose you want to allow the user user1 to do everything, the user2 to do everything but only read in the frm123 form, and the user3 to enter all the forms and reports but only read, without being able to modify anything
How to create a system that allows you to define with good granularity the functions (read/write/execute) available on each object (form/report/module) available in the procedure?

 

[The_Doc_Man]

The correct topic to search in this forum is "role-based security." However, in order to implement this, you also need to research "securing a database" because if the DB isn't tied down tightly, role-based security won't work.

 

[mike60smart]

How to implement a generic functionality limitation system for some users of the Access procedure?
Let me explain, let's suppose that the access to the Access procedure is granted to users user1, user2, user3
Suppose you want to allow the user user1 to do everything, the user2 to do everything but only read in the frm123 form, and the user3 to enter all the forms and reports but only read, without being able to modify anything
How to create a system that allows you to define with good granularity the functions (read/write/execute) available on each object (form/report/module) available in the procedure?

Hi

This Link has a complete example of how to do this

 

[amorosik]

Hi

This Link has a complete example of how to do this

I would like to publicly thank Vlad Cucinschi who kindly offered to send me a working copy of his demo program for user management
The one available on the website didn't seem to be working properly
I wrote to him and he sent me a working copy of the demo

 

[GaP42]

I downloaded this too and as found it required 64bit conversion.

I would like to publicly thank Vlad Cucinschi who kindly offered to send me a working copy of his demo program for user management
The one available on the website didn't seem to be working properly
I wrote to him and he sent me a working copy of the demo

Changed some declarations to 64bit to get it working - was that the problem?

 

[bastanu]

Yes, the version I have on my site is an older 32 bit one, there are a couple modules that need to be updated to be compatible with 64 bit or have conditional compiling to make them work with both.
Cheers,
Vlad

 

[GaP42]

I hope then you will make your 64bit update available @bastanu Thanks.

 

[gemma-the-husky]

@amorosik

I think the real problem relatives to the atomic-ness of allowing users to manipulate forms.

If you want to allow/disallow users opening forms, then you can have a user system with permission levels, and a forms table declaring the required permission level. Then in every form's open event test whether that user can open the form.

Sort of

If not(CanIOpenThisForm(me.name) then
Msgbox "Sorry, you cannot use this form")
Cancel=true
Exit sub
End if

So all you need is to change each form's open event to include a standard function to test the users access level.

However if you want to limit a user's interaction within a form, then every little interaction needs to be programmed carefully which makes it much harder to achieve.

 

[bastanu]

Here is an updated version that should work with both 32 and 64 bit versions of MS Office (it allows to control access to individual control level).

Cheers,

 

[amorosik]

@amorosik

I think the real problem relatives to the atomic-ness of allowing users to manipulate forms.

If you want to allow/disallow users opening forms, then you can have a user system with permission levels, and a forms table declaring the required permission level. Then in every form's open event test whether that user can open the form.

Yes, this is what I'm trying to accomplish
Of course, modifying the content of some events on many forms/reports is a very expensive operation
If there was the possibility of acting "from the outside" it would be a great thing
But I imagine it's not feasible

 

[gemma-the-husky]

I slightly simplified my explanation before. As well as setting access levels required to open particular forms, you can also include user groups. So you add your user to user groups, and the access to forms is then controlled by the user group membership together with the individual users access level.

I imagine the example provided by @Pat Hartman includes user groups.

You can also actually write code to iterate your code modules to check whether the code blocks you want are present in each forms open event, and insert the code if it isn't there. It needs a bit of thinking about to get it to insert correctly, and an appropriate understanding of the process, but it's a real time saver for a big database.

 

[bastanu]

My sample uses table-based "access rules" at the form and control level so no need to hard-code individual control or form events. Everything happens in the Open form event and it is just one line:

Call vcSetAccessLevels("Form", Me.Name)

Cheers,

 

[stefanrobin]

How to implement a generic functionality limitation system for some users of the Access procedure?
Let me explain, let's suppose that the access to the Access procedure is granted to users user1, user2, user3
Suppose you want to allow the user user1 to do everything, the user2 to do everything but only read in the frm123 form, and the user3 to enter all the forms and reports but only read, without being able to modify anything
How to create a system that allows you to define with good granularity the functions (read/write/execute) available on each object (form/report/module) available in the procedure?

To implement this, create a permissions table linking users to objects (forms, reports, modules) with flags for actions like read, write, and execute. Before a user accesses an object, check this table in your code and enable or disable functionality accordingly. For example, lock controls on a form if the user has read-only permission. This way, you can give user1 full access, user2 read-only on specific forms, and user3 read-only on all objects. Centralizing permission checks in a helper function ensures granular, maintainable control across the Access application.

 

[tvanstiphout]

If you are on a Domain network, this blog post of mine may be of interest to you.

 

[Ken Sheridan]

How to create a system that allows you to define with good granularity the functions (read/write/execute) available on each object (form/report/module) available in the procedure?

The attached little demo file illustrates how to assign permissions to objects in the database. Two versions are included in the zip archive, one for assigning permissions per user, the other per group. As regards modules you should distribute the front end as a .accde file. This prevents users accessing code, or objects other than tables or queries in design view.

 

[Richard Rost]

Like The Doc Man said, you’re basically describing role-based security, but in Access that almost always ends up being “UI-level security,” not true security like you’d get in something like SQL Server.

The pattern I’ve found works best is a table-driven approach like some folks mentioned, but combined with a central function that runs when each form opens. Store users (or groups), objects (forms/reports), and permissions (read/edit/add/delete) in tables, then have a single routine that applies those rules to the form.

For example, in the form’s Open event you call something like:

Call SetPermissions(Me)

That function can loop through controls and set properties like Locked, Enabled, AllowEdits, AllowAdditions, etc. based on the current user’s role. That way you’re not hard-coding logic all over the place.

If you try to control everything “from the outside” without touching forms at all, it gets tricky fast. At some point, Access needs code running inside the form to enforce behavior.

Also worth noting: this is usability control, not security. If someone has direct access to the tables, they can bypass all of this unless you lock things down properly (split DB, ACCDE, limited navigation, etc.).

If you want more background on the concepts, try searching for “Microsoft Access user-level security” or “securing your database.” There’s a lot of good material out there on different approaches.

 

[RonPaii]

I use a menu and user group system. All Forms, Reports and Actions as listed in a menu with a PC table linking Reports and Actions available on forms and another linking Forms, Reports and actions available to groups. Users can be members of multiple groups. All forms are opened using FormOpen function with the menu managed by a class filtering the menu for each form by Reports, Actions and Group.

'-------------------------
' Open form with given openArgs
' Verify current user has rights to the form
' Form will always receive the default openArgs value vbnullstring
'
' MenuIdToOpen      ' Menu ID of form to open (Pai1MenuKeyEnum)
' OpenArgsValue     ' Pass to form's OpenArgs
' WaitUntilClosed   ' If true don't return until form closes, ignord if acDialog
' FormView          ' How to open the form AcFormView
' FormDataMode      ' Read/write AcFormOpenDataMode
' ProvidedFormName  ' Overide form name from menu
' AllowReopen       ' Allow multiple instances of this form
'
' Defaults to Normal view, editable, in normal window
' Returns true if form was/is opened
'
Public Function FormOpen(ByVal MenuIdToOpen As Pai1MenuKeyEnum, _
                Optional ByVal OpenArgsValue As String = vbNullString, _
                Optional ByVal WaitUntilClosed As Boolean = False, _
                Optional ByVal FormView As AcFormView = AcFormView.acNormal, _
                Optional ByVal FormDataMode As AcFormOpenDataMode = AcFormOpenDataMode.acFormEdit, _
                Optional ByVal FormWindowMode As AcWindowMode = AcWindowMode.acWindowNormal, _
                Optional ByVal ProvidedFormName As String = vbNullString, _
                Optional ByVal AllowReopen As Boolean = True) As Boolean
               
....
        ' Verify user access
        If Form_pai.oClassMenu.VerifyMenuAccess(MenuIdToOpen, _
                                                Form_pai.oVar.UserSetupType(), _
                                                Form_pai.oVar.UserInformation( _
                                                    Form_pai.oVar.UserSetupID, _
                                                    SystemUserInfoEnum.AllowedTypes, _
                                                    SystemUserIdEnum.SystemID)) Then
                                                   
                                               
.....
' Simple call with only menu id
FormOpen Pai1MenuKeyEnum.PartsCountingForm

 

[The_Doc_Man]

The method I used began with me creating a template form for each of three generic groups of forms. For maintenance I had single-table forms that let you look at every field. Every table - even those used for lookups - had a single-table maintenance form. The template contained Form_Open, Form_Load, and Form_Current events that weren't yet specific to fields, but which "set the stage" for later parts of the process.

Form_Open verified the user's identity using domain API functions, and it checked whether user security "globals" (o.k., Public variables) had been loaded. This included loading the Public "ability" flags that allowed someone to perform specific actions dependent not only on the user's group, but on the specific item in table in question. E.g. a manager could add a new admin to the list of admins tasked to manage a specific machine, based on assigned workloads. An ordinary admin could not. But the supervisor of the machine's group could add that new admin but only for his assigned group.

Form_Load knew who the user was so loaded up username, general role name, and a couple of other things including their previous login date and time.

Form_Current would reset the form status and could call the control-list color/enable/visibility manager. The templates also had code for command buttons that appeared and vanished based on context.

The templates had maybe 60% of the security code, and 100% of the parts that that didn't touch fields. Then when I copied the template to a form that would be table-specific, I added fields from the underlying recordset and the remaining 40% of the code determined what fields the current user would see. When the form was fleshed out such that it's topic was defined, the Form_Open code could at that point determine if the user's role allowed all permissions, limited permissions, or no permission. The Form_Current code would enable or disable fields and would show or hide function buttons.

The BeforeUpdate code checked for consistency once the fields were defined, and we had a rule about not even showing the "SAVE" button unless all mandated fields were filled in. We color-coded the buttons so that erroneous or missing data was represented by brightly colored backgrounds. We also used LostFocus as a semi-final status check that would flip colors around on the control if it was good or bad. There was also code to intercept an attempt to close the form if it was dirty but hadn't been explicitly sanity-checked and saved. The "[X]" in the upper right corner wasn't there, and Alt-F4 was also intercepted. AfterUpdate did required logging and other behind-the-scenes functions.

I mentioned three form-groups. The first set was the single-table set described above. The second set of forms was based on relationship issues between any two of the main tables. A set of four forms involved the primary purpose of this particular magnum opus - the status of each computer with respect to each mandated security action. This one involved the requirement that the status update would be performed by a member of the appropriate management group, showing the date of status update, who performed the update, and whether the particular status was final (because sometimes the status was "pending downtime" or "pending vendor action"). This group also included maintaining the lists of admins who had rights to enter updates and creating the entries in a junction table to show that a certain action was pending for each machine.

The third group of forms was the set that didn't actually update anything, but rather were support forms. One "utility" was a topic-specific help form, essentially a "help tree." One was a "dispatcher" that allowed you to specify report selectivity. One allowed you to build an Excel spreadsheet showing a grid of boxes with rows for each computer and columns for each required action, and the cells show OK, W (waiting), NA (not applicable), etc, plus color coding of the background. One was the "trouble report" entry manager.

Sound like a lot of work? You're damned right it was. But using the templates that had security and other actions built-in before customization, it would only take a couple of days to completely fill in all the bells and whistles for a new form. It ran for several years before the Navy found a commercial off-the-shelf product that came close. They decided to implement that system because technically, my system - being home-grown - was not commercially maintained. And I have to admit I was telling them that I might retire soon.

Before anyone asks - the code was written under a Navy contract such that it was the property of the U.S. Navy, not me or the company I worked for at the time. So I couldn't take the code with me and don't have it to publish now.

 

[P J Bryant]

How to implement a generic functionality limitation system for some users of the Access procedure?
Let me explain, let's suppose that the access to the Access procedure is granted to users user1, user2, user3
Suppose you want to allow the user user1 to do everything, the user2 to do everything but only read in the frm123 form, and the user3 to enter all the forms and reports but only read, without being able to modify anything
How to create a system that allows you to define with good granularity the functions (read/write/execute) available on each object (form/report/module) available in the procedure?

If your client's network is an Active Directory managed network then you can use AD security groups to control anything in Access. A major advantage is that you can delegate the management of those groups to the IT staff. I've done it many times - on starting a db, identify the groups in AD of which the user is a member (to an internal table) and then have code in the database that controls access to functions, forms, controls, anything by naming the group to which they must belong as a requirement. A simple local table lookup checks their membership and permission flows from there. The real benefit is that membership of the groups is managed outside the database so the client and their IT support can make the changes to give staff changing permissions.

 

[KitaYama]

If your client's network is an Active Directory managed network then you can use AD security groups to control anything in Access.

This frequently comes up and unfortunately a lot of people don't realize the back draws on this method.
There are a lot of practical concerns worth mentioning.

First, relying on Active Directory ties your application user directly to the Windows login. In environments where PCs are used as shared terminals (e.g., clinics, hospitals, schools, universities, companies like ours with a lot of employees, ....), this becomes a limitation. Staff typically move between stations and only log in/out of the application, not Windows. Requiring a full Windows login/logout cycle for each user is slow and disrupts workflow, making AD-based control less suitable in these scenarios. Just imagine a user needs to do a search on Orders when he's not on his desk and is on the shop floor. How long does it take to log out of windows and login with his account just for a search. Or an edit on a record.

There are also some technical drawbacks:

• Nested groups may not resolve easily, depending on how membership is retrieved
• There can be performance hits at startup when querying AD for group membership
• It requires domain connectivity to function properly
• If AD is unavailable, cached credentials may not include full group information, leading to incorrect or incomplete permission assignment

So while AD groups are great for centralized management, they don’t always align well with real-world usage patterns, especially in shared workstation environments.

If someday I want to use active directory security for my database, instead of querying the server directly, I add Users and AD group memberships tables to my BE, then sync it peridically with AD. This way, my app can be used on its own.