Abstract design question re: permissions

[JohnH]

Wondering if anyone with experience might be able to point me in the
right direction with this:

I'm designing a database that tracks customers and sales.
Salespeople, managers, admins, and guests will be using the database.
I want to implement a permissions system that will allow the different
groups different levels of access and information on the forms level.
I've created a 'Users' table and a 'UserTypes' table that will manage
users. I also have some tables in the database for Salespeople.
There is, -sometimes-, a relationship between Users and Salespeople.
This is what I don't know how to model.

So let's say John Paul is a salesperson who will be using the database
to enter and track his sales. He is both a salesperson and a user.
He will have permissions to view and edit certain information based on
his User Permissions, but the data he actually views is determined in
part by its relationship to Salesperson, i.e. John Paul the User
should not be able to see Sally May the Salesperson's data.

How would something like this be modeled? I haven't seen inheritance
really used in Access in a way that I intuitively imagine could solve
this problem.

Thank you.

 

[Larry Linson]

This is not a simple issue. If you are using .MDB or .MDE, you may want to
use User and Group level security, because it is really not possible to
implement "homegrown" methods that are secure.

The issue of what Users can see and update versus what Salespeople can see
and update would be a function of the Group to which you assign the person.
From your description, your needs are simple enough that Salespeople are
likely to have all the privileges of Users, and more. Unless you have needs
that aren't stated, there'd be no need to assign John Paul to the lower
level Users group if he's assigned to the Salespeople group.

The issue of allowing a Salesperson to see only their own information on
sensitive items is handled by giving them permission on information
displayed via a Query that includes the CurrentUser to obtain the person's
user id and apply it to selecting the information. You don't give them
permission on the Tables themselves, or a Query that returns everyone's
data.

This is a flying-at-40,000-feet-overview, and there are lots of details.
The ONLY satisfactory place to begin is with the Access Security FAQ, for
which look at
http://support.microsoft.com/default.aspx?scid=/support/Access/Content/secfaq.asp.

That Security FAQ is, IIRC, 39 pages, and there is no "filler," so read
carefully, study, and try it on a COPY of your database. Remember, missing
a step can make a database so insecure that anyone can get in, or so secure
that even you can't get in... so keep that un-secured master copy until you
have worked through the stumbles that all of us go through applying Access
security.

Larry Linson
Microsoft Access MVP

 

[JohnH]

This is not a simple issue. If you are using .MDB or .MDE, you may want to
use User and Group level security, because it is really not possible to
implement "homegrown" methods that are secure.

The issue of what Users can see and update versus what Salespeople can see
and update would be a function of the Group to which you assign the person.
From your description, your needs are simple enough that Salespeople are
likely to have all the privileges of Users, and more. Unless you have needs
that aren't stated, there'd be no need to assign John Paul to the lower
level Users group if he's assigned to the Salespeople group.

The issue of allowing a Salesperson to see only their own information on
sensitive items is handled by giving them permission on information
displayed via a Query that includes the CurrentUser to obtain the person's
user id and apply it to selecting the information. You don't give them
permission on the Tables themselves, or a Query that returns everyone's
data.

This is a flying-at-40,000-feet-overview, and there are lots of details.
The ONLY satisfactory place to begin is with the Access Security FAQ, for
which look athttp://support.microsoft.com/default.aspx?scid=%2Fsupport%2FAccess%2F....

That Security FAQ is, IIRC, 39 pages, and there is no "filler," so read
carefully, study, and try it on a COPY of your database. Remember, missing
a step can make a database so insecure that anyone can get in, or so secure
that even you can't get in... so keep that un-secured master copy until you
have worked through the stumbles that all of us go through applying Access
security.

Larry Linson
Microsoft Access MVP

Thank you for your help Larry. I could have wasted a lot of time.