Security within a table of records 2

[Quotidius]

I don't see much mention of it in any of my key word searches - I am trying to prevent users from being able to see other people’s employee reviews within the same table of a database of mine.

I have successfully established user groups but the security is kind of weak for my purposes. Users view their manager's review and comments about them via a form/query. The query is based on their user-id and password so that it filters to only their records (note this too is a bit unwieldy because the user-id and password comes right after and is distinct from their workgroup user-id sign-on and password when opening the database).

The form opens showing only their records but obviously the user could close the form and open the table containing all employee reviews. This is the rub with Access Workgroup security, to enable a group to view a form you also have to grant permissions to view the entire table/qry on which the form is based on. Though the form produces the data specific to each user, the user group that the user belongs to must have the abiity to read the entire table!

My ultimate objective: A user sees only their pertinent record in the employee review table and not all others. Any suggestions?

 

[PHV]

No user should have access to the database windows.
Have a look at the startup options in the Tools menu.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jsteph]

You are talking about Row Level Security. It is not natively supported in Access but can be done with sql.

The table-joins and sql can get very complex here; one method I've seen is to use an IN clause on, say the where clause of an Employee table, and that IN clause references a user/security table with a record for each employee number which that user can see.
--Jim

 

[jsteph]

...to elaborate, the system I'm referring to (which I must admit deserves several years worth of ThDailyWTF.com entries) uses such a Where clause as I described in my above post on the base employee table of this system.

It's a payroll system, and basically every report/query in the entire system joins somehow to this main employee table. So every report in the system is joined on that employee table, which must be fully scanned, checking the IN clause on the security table. That sql on the Emp table is wrapped in a view, and that view is what's actually joined in all the canned and user-defined querys, so you can imagine that the performance is gut-wrenchingly horrible--forget any index usage.

Bottom line...it can be done but it's kludgy at best--but if you need it it is doable.

Obviously, as phv said, you need to restrict raw-table viewing, and you should make an Owner Access query like the view in my example, and secure that query from modification, and use that in your other queries (to centralize the kludgey sql), and just don't allow modifications to these queries--and use parameters for any other criteria.
--Jim

 

[Quotidius]

Jsteph, thank you for well defining the complexity of what this thread is after. I think I'll opt for the simplicity of PHV's recommendation.

PHV, on that topic, I also selected hide toolbars. It seems to work too well, how do I get it, the tool bar, to again show? Again thank you both.

 

[PHV]

Open your database with the shift key depressed.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Prattaratt]

I once devised a system that hid all the tables through code, and then based on your access level, allowed the user to see certain tables. It's been a long time, and I've since lost the code, but the basic premise was that you had a table with usernames and access levels, and another assigning access levels to different DB Objects. I then had a startup code on the load of the "Main" form execute a "reveal" operation based on the comparison of the access privilege of the user vs the access level of the object. A default level could also be implemented as well.

The simplest solution is the best!