Share via

How to handle user level security?

Anonymous
2024-06-06T12:31:06+00:00

I realize that this question might possibly be a bit more complex than others, maybe not.

As we all know, with a lot of databases there needs to be user level security in regards to who has what permission to do what and what parts of the database they can access. In my last project, simply as a matter of lack of experience, my security checks (code) on everyone were long and repetitive all over the place!

So, I will ask what to me is an obvious question .......

What methods of code have you all used to check user level security that would apply either globally or if needed to check at the form leve that helps to keep the code to a minimum but at the same time is a thourough and complete check of someone's permissions ?

So, to state the obvious, if you have a Manager and a CSR (Customer Service Representative) and you have 3 options (buttons) on a 'Customer Menu:

-Add Customer

-Edit Customer

-DELETE Customer

Now, both positions, Manager and CSR, can Add and Edit customers, but only a Manager can delete them! What might the code look like to check the access level of who is logged in to be able to ENABLE or DISABLE the options based on the access rights of who is logged in at the time!

Then, when they log out, I want to have a routine that flushes any traces of them being there so that there is no place for them to get mistaken access by the access level of the previous user.

As I mentioned, I am programmer with a little bit of knowledge but am I x lea linuvel uom

Microsoft 365 and Office | Access | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

5 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2024-06-06T15:30:27+00:00

    Another aspect of database security which you might need to consider is 'row level security'.  This controls which rows in a table users have access to.  You'll find this illustrated in RowLevelSecurity.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    The zip archive includes a number of files which illustrate two ways of applying this type of security:

    1. Security is applied by making users members of one or more groups.  Access to each row in a table is then governed by means of a UserGroupFlag column in the table.  This is a number, usually expressed in hexadecimal notation, which uses bit-mapping to give access to  the row to one or more user groups.
    2. The second approach governs access to each row to individual users rather than to groups.  This is applied by means of a RowUsers table which models the many-to-many relationship type between a users table and the table to which access is governed.  You'll notice in one of the files that, in the form by which users are given access to specific rows, it includes a row to which Fiona has access, but she does not appear in the list of users in the same form.  This is because Fiona is given administrator status  in the opening form, and therefore has access to all rows.  In reality this would not be done in a form openly available to any user of course. It's done so here merely for the purposes of the demo.

    1 person found this answer helpful.
    0 comments
  2. ScottGem 68,810 Reputation points Volunteer Moderator
    2024-06-06T23:02:28+00:00

    You might also find my blog on logon Security with VBA to be useful. See below for blog address.

    0 comments
  3. Anonymous
    2024-06-06T16:23:32+00:00

    Thanks for this ....

    0 comments
  4. Tom van Stiphout 40,201 Reputation points MVP Volunteer Moderator
    2024-06-06T13:44:11+00:00

    In an app I am finishing up, we have a subform to set permissions:

    Of course that Employees form can only be opened by users with that permission :-)

    This is backed by a table with a list of permissions, and a junction table between Permissions and Employees to set the permissions.
    There is an enum with the ID values of the permissions.

    Then a Property Get of a static class clsUser which takes an enum value and returns the permission value.

    Then a few lines of code, typically in Form_Open, to set Cancel=True if no rights, Me.AllowEdits, and Me.AllowDeletions as appropriate.

    0 comments
  5. DBG 11,711 Reputation points Volunteer Moderator
    2024-06-06T12:52:09+00:00

    First question, is your database properly split?

    You can create a standard module for applying user permissions by passing the form object to the routine. Your login form can establish a TempVar to hold the user's role that the routine will use. You can then use the Controls' Tag property to designate which controls to secure.

    0 comments