Query permissions

[RipperT]

Using Access 2002. I have 3 nested subforms that are each based on their own
query. I want all users to have read permissions to the data in these forms.
I want one user (we'll call him Hugo) to be able to read, update, modify and
delete data in these forms. I have removed all permissions on the base
tables and given Read permissions to my custom users group for each query.
I've given read, update, modify and delete permissions on each query to the
group called HugosGroup, and Hugo is the only member of that group (except
for my test user). Yet, Hugo cannot read, update, modify and delete any of
the data, he can only read it, as with the other users. I don't understand
why. If I use WITH OWNERACCESS OPTION in the queries, then everybody can
read, update, modify and delete the data. I've read the Access help file and
the SECFAQ and they seem to contradict each other. How do I allow only Hugo
to read, update, modify and delete?
Many thanks,
Ripper

 

[Chris O'C via AccessMonster.com]

Copy the sql from each query for the form and subforms and put the sql in the
corresponding form's record source property. Make sure you add the WITH
OWNERACCESS OPTION clause at the end of these queries. In the form's open
event, use the currentuser() function and see if this user is a member of
HugosGroup or the Admins groups. If they're not, use vba code to lock every
input control (so the user can't make any changes to the data) and add this
line:

Me.allowadditions = false

so they can't add any new records either. Give all groups except the Admins
group only open/run permission on the forms. The Admins group keeps
administrator permission. You can delete the original query that doesn't
have the WITH OWNERACCESS OPTION clause unless the query is used somewhere
else in the app.

Now Hugo and the Admins group can read, edit, add and delete records in the
form and subforms and the normal users can't.

Chris
Microsoft MVP

 

[RipperT]

Chris,
I appreciate your taking time to respond with this workaround. However, I am
trying to understand why I simply can't, according to SECFAQ "remove ALL
permissions from the underlying table(s) and use only queries to get at the
data you want your users to have. You then grant the appropriate level of
permissions to the groups or users only on the query itself, allowing them
only to view data or to be able to modify it through your query. "

Can you help me understand this. Many thanks.

Rip

 

[Joan Wild]

Have you ensured that the Users group does not have any permissions to
anything?
If I understand you,
1. Your custom users group has no permissions on the base tables.
2. Your queries are all saved RWOP queries.
3. The HugosGroup has no permissions on the base tables.
4. Your custom users group has read permissions on all the RWOP queries.
5. HugosGroup has read/update/modify/delete permission on the RWOP queries
(why modify? or do you mean insert).


Who is the 'owner' of the saved RWOP queries? What permissions does this
user have on the underlying tables?

 

[Keith Wilby]

If I use WITH OWNERACCESS OPTION in the queries, then everybody can read,
update, modify and delete the data.

If you use RWOP queries then *you* set the permissions for users on them as
if they were the tables. With RWOP queries other groups do not inherit the
db owner's permissions, on the contrary, by default they have *no*
permissions, you have to set them. If your users have full permissions to
your RWOP queries and you haven't set them then you may not have set up ULS
properly (and it is easy to miss a step).

Regards,
Keith.
www.keithwilby.com

 

[RipperT]

I only removed permissions from the Users group for the tables, but not the
queries, forms, etc. A silly oversight on my part.

I do thank all of you for helping me better understand RWOP queries.

Rip

 

[Keith Wilby]

I only removed permissions from the Users group for the tables, but not the
queries, forms, etc. A silly oversight on my part.

If "Users" had permissions to your queries by default then I suspect that
that group may also have permissions to the database object. Worth
checking.

Keith.