Restricting access to informatino

[mariaw]

Hi

I have made up an Access database form for our Sickness Administrators to fill in when someone is off ill. I want the form to only show (on the drop down list of names) the people that that particular SA deals with - for example, Jill would enter sickness records for the Democratic, Legal & Personnel section. I am not sure how to go about this:

a) if I have one table with all of the employees names on it, is there a way of restricting Jill from seeing any names other than the ones that she deals with?

b) if I have separate tables for each section, would that be easier? and if so, do I just make a "switchboard" for them to click on the appropriate list?

Thanks!

Maria

 

[statsman]

You would generally go about this by using Access security.
I would recommend that you have a separate form for each department or combination of departments. The forms would be run from specific queries which limit the records to be displayed, entered or edited to that specific department.
You would then set the permissions to allow access to the form only to the person responsible for that department.

Example: Jill would be given permission for Democratic Legal and Personnel form
Tom would be given permission for Sales form
Laura would be given permission for Admin form
etc.

For an excellent overview of Access security, go to:
http://www.geocities.com/jacksonmacd
download the first file – Security Paper by Jack Macdonald. It’s a little long but it tells you everything you need to know about Access security if you're not familiar with the subject.

 

[neileg]

I have made up an Access database form for our Sickness Administrators to fill in when someone is off ill. I want the form to only show (on the drop down list of names) the people that that particular SA deals with - for example, Jill would enter sickness records for the Democratic, Legal & Personnel section. I am not sure how to go about this:

a) if I have one table with all of the employees names on it, is there a way of restricting Jill from seeing any names other than the ones that she deals with?

Yes. It depends how secure you want this to be, though. Statsman is quite right that you can use Access security to achieve this. However you could more easily base your form on a query that retieves only the records that relate to that Administrator. You would need a table that holds the Administrator names and the sections they deal with.

b) if I have separate tables for each section, would that be easier? and if so, do I just make a "switchboard" for them to click on the appropriate list?

No, you need to keep all the records in one table and use queries to select the records you want.

 

[mariaw]

Thank you Neil & Statsman

Neil: If I create a table which states which sections Jill deals with, Tom deals with etc...how do I create a query so that the form only shows the sections they have to look after?

 

[gemma-the-husky]

Your application will start to get quite complex, I think. Access is fundamentally designed to expose whole tables to users. The access security model is designed to limit users ability to open or process certain forms or reports, and not to limit access to data from within those forms.

Consequently all your security will have to be included via program code.

Even then, it still might not be sufficient if your (determined) users are able to get to the database window, or able to import or export whole tables to another database.

I don't know if there are any general posts on this issue in this forum, but I suspect this will be an enormous undertaking for anything other than a very small system.

 

[neileg]

I think gemma is a bit pessemistic! As I said, it depends whether you want a fully secure system, or one that is designed to help the users by only presenting the data they are interested in.

The table structure might look something like this

tblAdmins
AdminID PK autonumber
AdminName
etc

tblDepartments
DepartmentID PK autonumber
DepartmentName
AdminID FK link to tblAdmins

tblEmployee
EmployeeID PK autonumber
EmployeeName
DepartmentID FK link to tblDepartment
etc

So a query that joins tblAdmin to tblDepartment to tblEmployee will link the Administrators to the employees. Adding a parameter to the query for the administrator will give you just the employees for that administrator.

 

[Len Boorman]

Technique that I use is to detect the windows logon ID which in our company is the same as network logon and use this to control what people can see and do.

Basically you store the logon id's in a table for teh authority level.

So tbl_admin has logon id's for people that I want to have full rights

So you build a few tables.
First form to open detects Logon ID and PC Name actually as well

Then the main Opening Form opens over the top, the on load event has an If statement that basically checks if the logon id appears in a "control table" and sets visible property of command buttons for various functions

If logon id does not appear in any table then application immediately closes

You could also use the logon id detected within a query to control who can see what records

Need also to disable shift key and F11

Sounds complicated but is actually easier to administer that full security,

Len

 

[The_Doc_Man]

For normalization purposes, you need to keep ALL personnel in one table and then use queries to restrict what a given person would see. The hardest part is to productively link a person's login name to the data you want them to see. This is where you will probably be doing a JOIN of the personnel table to an "allowed viewers" table.

In the most "general" case, you have persons in a department and some number of persons allowed to act on behalf of that department. I would take the approach of doing this:

Employee table.... shows current department code, changes only when person is reassigned, and it is ASSUMED you keep a separate history of this. But given that folks don't change departments daily, storing current department assignment is not a normalization violation.

Department table... shows information about departments

DepReps table... shows login names of persons who are allowed to see data about given departments. Looks like this...

DepRep: Login ID, Dept Code

Prime key is login ID and Dept Code as a COMPOUND KEY. In the case where user Joe.Blow sees departments 10, 11, and 12, you have three entries in this table for Joe Blow. Where Jane.Doe sees departments 12, 13, and 14, you have three more entries for Jane Doe.

Now, to presume that you don't want folks to see entire tables, you must have a permanent switchboard form that never goes away and it must never allow a user to see the Access object panes directly. When this form loads, you have a couple of options that might apply. Some VBA code in the Form_Load event is where you would do whichever thing you need to do.

If you have a FE/BE split database, you can put one table in the FE that contains ONLY the name of the current user. Since each FE is unique, you can assure uniqueness. Then JOIN the FE table that has the currently logged in user to your list of who can see what department, inner JOIN across the login name fields. This should be a canned query in the FE (like all queries in the FE should be...) Now you can join the employee table (which has department codes) to the QUERY that contains the department codes the currently logged in user is allowed to see.

This concept DOES NOT WORK if the table showing the logged in user is in the Back End of the DB. You MUST join it uniquely, which cannot be done if the table is in the BE.

If you don't have a split DB, you will have to make the query a parameter query and that is going to be a lot harder to do. Search the forum for more on the subject of user-specific queries or role-specific queries.

 

[gemma-the-husky]

thanks Doc Man, I told you it was hard!