Ms Access - Global user groups.

[Darragh]

Hi all!

Appreciate any ideas on the following dilemma.

A client I have wishes to implement user management in Access. The
database in question wasn't originally designed for multiple user
groups and I'm wondering whats the best way on limiting what data each
particular user group is able to see.

An example scenario is as follows: Group 1 can only see data relative
to group 1, group 2 - only data relevant to group two, and so forth.

I know I might be able to use the security features of access, but I'm
unsure if it will solve the problem. I was thinking of implementing a
global filter based on a login form using global variables, but I
wonder if there is a better way. I'd prefer not having to redoing all
the queries, tables, forms etc, or developing separate interfaces for
the specific groups.

Any hints?

Regards
- Darragh

 

[Ken Sheridan]

Darragh:

I think what you have in mind is 'row level security', i.e. where each group
has access to only particular rows in the tables. A few years back I posted
a demo which simulates this at:


http://community.netscape.com/n/pfx...libraryMessages&webtag=ws-msdevapps&tid=24069


The demo is only a simulation designed to illustrate the methodology; it
doesn't actually implement security as, in order to demonstrate its
functionality, anyone can log in as any user. It was intended that in real
life the groups would be defined using Access's user and group security, but
Access 2007 no longer supports this of course so a different approach would
have to be adopted if using that version. If its only 'user management'
rather than 'security' you are looking for then it would probably suffice
with a little modification so that some of the features are not exposed to
the user.

Each group is identified by an integer UserGroupFlag and in the main table
an integer UserGroupFlag column acts as a bitmap, so a row accessible to
groups 1 and 2 only would have a value of 3 in this column, whereas one
available to all 6 groups would have a value of 3F (in hexadecimal notation,
63 in decimal).

Each row in the query underlying the demo's main form calls the following
function:

Public Function IsPermittedUser(i As Integer) As Boolean

Dim intUserGroups As Integer

intUserGroups = Forms("frmLogin").txtUserFlags

IsPermittedUser = (intUserGroups And i)

End Function

passing the UserGroupFlag value into the function as its argument. This is
then compared with the currently logged in user's flags by means of a Boolean
AND operation; the flags are obtained from the log-in form, which remains
open at all times. Each users flags are simply the sum of all the
UserGroupFlag values for the groups a user is assigned to.

Ken Sheridan
Stafford, England

 

[Darragh]

Thanks Ken, thats exactly what I'm after. I'll check it out and see if
it fits my needs exactly.

Thanks for the helpful comment.

 

[Mark Andrews]

I added a user/group security system to a little product we sell.
You can check it out at http://www.rptsoftware.com

If you aren't interested in the product you can probably get some good ideas
just by looking at the demo for a few minutes.
Shoot me an email if you only need the security related code. I'll strip it
out and send it to you.

It's more of the typical (users are in groups and groups can access certain
screens and controls) where you put a little code in each
form to control buttons users can click on, read/write access, filtering,
combo box choices that are available or what sections are visible etc....

If you really need to have a seperate field to mark each record to control
record level access you can do that, but I would think about that one.
I don't know how big or complicated your db is so it's difficult to comment
on the best approach. Stay as simple as possible would be my only advice.

HTH,
Mark
RPT Software
http://www.rptsoftware.com