Row-Level security

[mabaker]

Is there anyone out there who can guide me in setting up some security on a database as follows:

Four levels of security each having different security permissions in the
database:

1. SuperUser full access
2. Report Users with access to own timesheet records and all reports
3. Approver Users who would be able to view all timesheets but no editing or deleting except for their own and unable to view reports
4. Regular User with access to own timesheet records, unable to view reports.

Any help would truly be appreciated. This is an Access 2000 application.

 

[The_Doc_Man]

Unfortunately, this is tricky because the only way to implement this depends on security of the individual records.

1. Full access - easy to do. Any member of the Admins group (note: Group has "s" on the end; user Admin does not...) has this level of access. Piece of cake.

2. Users who can access own records and all reports. The "all reports" part can be done with a user group that has read/open access to all reports PLUS the stuff I'll define for #4

3. Update-only access for approvals - can be done with a user group that can update any record (but not delete or edit) through a form that has that function pre-defined. Separate from the form implied by #4.

4. Users who see their own timesheets but nothing else...

The trick is that to implement THIS kind of security, which is based on a row rather than a whole table, a couple of things must be true.

a. The tables to which this applies MUST have a field to store the record's owner, which must be determined by the user's login name.

b. For accessing your own records, have a parameter query handy that takes one parameter - the userid. Drive your forms off that id.

c. NO ONE can be allowed to open anything but a form or report. (Except the admins group members.) ALL access must be through things that can run event code in the OnOpen or OnLoad or OnCurrent event. So you are talking about a switchboard form that NEVER allows non-Admins to see the real database window.

d. It probably wouldn't hurt to have some public module-base code handy that can test owner vs. user and group. Perhaps a function (just a guess) to return true/false for whether user has write access to the record. Then have your form change color for records that your current user cannot see or touch or whatever.

e. This is NOT a feature that is designed automatically into Access. You have some serious programming ahead. Access security granularity is normally at the object level - where individual records are not normally considered as objects in the same sense that queries and tables are.