Restrict certain RECORDS by password

[rpadams]

I have a large database with many tables filled with student demographics etc. Each student is tied to one of 7 supervisors Field->SupID. I now filter these students for reports, input etc. by having the supervisors input their SupID number. What I need is someway to restrict them to ONLY their own students and not be able to see data on students who are not theirs.

I have given the Supervisors a password. Some are adept enough to go in and look at the VBA code. I made a mod arithmetic alogrithm that slows them down. For example, if their password is 35031, they would have to input 56213 (12345678 mod 56213) but they can still (with time) work out other Supervisors passwards where I filter the records. Does anyone know of a truly good encryption alogrithm that would foil someone who can read the code?

Splitting the database into 7 separate databases is NOT an option because some reports (billing, transport, overall enrollments) need to be done for all the students at once. Also we have some district personel who need access to ALL of the records regardless of Supervisor.

Any ideas or places I can look for help? I don't need super security (these people are honorable, just a little too curious.)

 

[mkranz]

What MS Access version are you using and what is the method your supervisors use to "logon"? Also, what is the extension of your application; is it an mdb or mde?

 

[rpadams]

I am using Access97. Users are on a network that controls access to the directory where the application resides. Their network password lets them use my program. When they start my program, I ask for another password I've given each one (actually a 5 digit number). I then run this number in VBA code thru a mod alogrithm and generate another number which is in a lookup table tied to their names. If these match, I then use their names as filters in queries etc. to select only records for their own students. Slows some of the users down, but if they understand the alogrithm or filter from the VBA code, it doesn't stop them. They work "backwards" to find each others passwords.

Users with universal access to the records have a different password and sets the filter to "null".

I am running app as .mdb because it is not completely designed yet and continual development is taking place on the front end. I think .mde would be awkward at this stage.

 

[RichMorrison]

I have done this kind of thing with an internal "User" table:
each user belongs to a group or team represented by a text "Team Name",
each user has an authority "Level", a numeric value of 1, 2, or 3
each user is identified by their Access User ID, another text field.

At startup, the application looks up authority Level and stores it is a global variable. The application also stores Team in a global. For level 3, supervisor, the value "*" is stored in the Team global.

Forms to view/change data use code to determine if a person can see:
their personal data for level 1,
all their teams data for level 2,
all data for level 3.

That's a rough outline.

RichM

 

[pono1]

Random Thoughts

R,

Have you put a password on your code? (Can do this in A2k but not sure about Access 97...)

Also, assuming you have taken away the system window and disabled the shift key, users will still be able to easily import tables from your DB into another Access DB and see student info.

Another relatively random thought: people will, out of convenience, often share their secrets with co-workers: "Jim, I don't want to come down there today. Can you help me out and go into 'Insert DB name here' and tell me Lance McWorth's phone number? My password is 2929210." Changing passwords on a regular basis mitigates this behavior but adds to your admininstrative duties (unless you can figure out how to automate a forced password change).

Security is not fun.

Regards,
Tim

 

[The_Doc_Man]

Normally I wouldn't suggest this, but...

If you really wanted to secure this, there are ways.

Like, make a startup form that actively hides the database window if you aren't in the Admins group. Make it a switchboard. Then don't let folks see anything.

Now, make Access security (workgroups) help you. Create a new group that allows folks access rights. But it won't be group Users. Take away ALL access rights from Users. Create a new account that will be your adminstrator account. Make admin (no 's' on the end is the account) a member of users but not of admins ('s' on the end is the group).

Search this forum for ways to bypass the SHIFT method that blocks execution of startup forms. Some articles have appeared that I won't reproduce here.

What these two things do is prevent a user from creating his own workgroup to become Admin and then connecting to your DB as Admin. It won't do any good because the DB gives no access rights to Admin.

OK, now, these queries that everyone uses but you want them to be restricted... I'm going to assume that you can add a code (or have a code) in the underlying tables to identify the records that a person uses as a "marker" to say "this record is for me." Under the code button in your switchboard, make a Case statement that adds a filter to any report, form, or query. (Or, you could make the case statement occur on Form_Load to define a global variable that is invariant per session, and just make the report, form, or query buttons automatically apply the filter.)

This is the amount of work you need to do what you said you wanted to do. I can't sugar-coat it. Security is no laughing matter when you really worry about who has rights to see what. But don't worry, it could be worse. You could add fiduciary responsibility to the mix by handling money. THEN you would really have to crank up security a notch or two.