Queries return no read perms on table? After RWOP?

[kiln]

I am forgetting something; I have a fully secured A2k db, which utilizes
the two mdw trick in the AccSecFAQ (item 33). I've made mods recently,
and now when logged in as a non-admim level user (member of SiteUser
group), I get a "Record(s) cannot read; no read permissions on
tblEntity" error message.

For the SiteUser group:
Backend data file has all table perms revoked.
Backend data file has Open/Run and Open/Exclusive perms allowed.
Front end table links have wide open perms on all tables (links really).
All queries are set to RWOP.

Yet every single query returns the error, . The user acct I'm trying to
log in with *is* a member of the SiteUser group. I must have missed as
step, can anyone spot it?

 

[Joan Wild]

I am forgetting something; I have a fully secured A2k db, which
utilizes the two mdw trick in the AccSecFAQ (item 33). I've made mods
recently, and now when logged in as a non-admim level user (member of
SiteUser group), I get a "Record(s) cannot read; no read permissions
on tblEntity" error message.

For the SiteUser group:
Backend data file has all table perms revoked.
Backend data file has Open/Run and Open/Exclusive perms allowed.
Front end table links have wide open perms on all tables (links
really). All queries are set to RWOP.

Yet every single query returns the error, . The user acct I'm trying
to log in with *is* a member of the SiteUser group. I must have
missed as step, can anyone spot it?

My initial reaction is 'what mods did you recently make'?

However, does the query owner have full(any) permissions on the backend
tables?
Are you modifying any query in code, or changing any recordsources in code?
Have you used a lookup in either tables or queries?

 

[kiln]

Hi Joan I was hoping you'd show up, I get stale on this sec stuff after
a while.

I created some new tables queries and forms. All while logged in to the
dev mdw; thus the dev admin remains as the owner of all (I just checked,
and it's so).

None, not one, of the queries will run unless the user is using the dev
mdw. Again, backend allows non-dev admin users to open run the db, but
nothing on tables. Front end has all perms ok on tables (links) and all
queries are rwop.

Another clue that I can't seem to place is that even running an install
from August that has older mde mdb and mdw stuff fails in the same
manner; they did work in Aug. What am I missing?

 

[kiln]

And, yes there are rowsource and form recordsource mods in code; but for
those few forms, I've allowed non-dev admin users to mod design. The
problem I'm having is global, I really don't get it.

 

[Joan Wild]

I created some new tables queries and forms. All while logged in to
the dev mdw; thus the dev admin remains as the owner of all (I just
checked, and it's so).

Just to be clear, are you saying the Admin user in the dev mdw (I hope not),
or do you mean a user called DevAdmin in the development mdw?

None, not one, of the queries will run unless the user is using the
dev mdw. Again, backend allows non-dev admin users to open run the
db, but nothing on tables. Front end has all perms ok on tables
(links) and all queries are rwop.

Have you assigned permissions to the appropriate group in the development
mdw? Does your production mdw have the *exact* same group name and group
PID in it?

Another clue that I can't seem to place is that even running an
install from August that has older mde mdb and mdw stuff fails in the
same manner; they did work in Aug. What am I missing?

I'm thinking that you've added a group to your development mdw, but the
production mdw doesn't have this group (or that your users aren't a member
of the proper group in the production mdw).

 

[Joan Wild]

And, yes there are rowsource and form recordsource mods in code; but
for those few forms, I've allowed non-dev admin users to mod design.
The problem I'm having is global, I really don't get it.

But are those recordsources or rowsources a sql statement that references a
table?
Are you modifying any sql properties of saved queries?
Again, are you using any lookups anywhere?

 

[kiln]

But are those recordsources or rowsources a sql statement that references a
table?
Are you modifying any sql properties of saved queries?
Again, are you using any lookups anywhere?

These are all saved queries, I think that is what you're after. No SQL
statements (strings) that directly ref tables. No mods to qdef.sql
values if that's what you also are asking. Lookups? Not sure what you
mean, yes there are some dlookup uses here and there but they aren't
relevant here. Not a single simple query will run without the perms
issue, where run as a rec source of a form or from the db window
interface directly.

 

[kiln]

Not the admin user; a genuine new DevAdmin user, the one and only member
of the admins group in the dev.mdw. There is an admin "SiteAdmin" in the
user.mdw too, but that is one that is only good for adding creating
users, as per item 33 in the secfaq.

Yes the same group names; yes identical pid; all of this worked in aug,
I've not really changed the two mdw files since then. Yes, the two
'user' level groups in the dev.mdw have perms as I've outlined:

backend: open/run and open exlusive on db
backend: no perms on tables except for a single table that I use to test
for links being ok

frontend: open/run/exlu on db
frontend: all perms on all tables (including new)
frontend: all perms on queries
frontend: open perms on forms (rare design perm for rs changing)

There aren't any new user groups, and no user memberships have changed.

I also user the .Connect property to link; but now it errors out on all
tables.

I'm really flummoxed at this point.

 

[kiln]

Well; maybe solved; but I can't see why. I repeated a fix that worked
for me a couple of months ago, didn't make sense then and doens't now.

Basically, I joined dev.mdw as DevAdmin (the only admins group user).
Then I created two new mdb files from scratch, imported the front and
back end objects into the dbs. Reset all perms on both files for the
various groups. Now it works as expected.

I can't understand why. The only thing that recreating the dbs should
"change" is the owner. But the owner of all objects is DevAdmin both in
the old and recreated. I have both files and both seem to be identical
as far as ownership goes...

This really bugs me. Access sec is so contorted already, I don't need a
part like this that seems to defy the pattern. It's kind of a big deal
to have to reacreate the dbs with all perms and activex objects. So if
anyone has a clue let me know. And thanks Joan.

 

[Joan Wild]

Well; maybe solved; but I can't see why. I repeated a fix that worked
for me a couple of months ago, didn't make sense then and doens't now.

Basically, I joined dev.mdw as DevAdmin (the only admins group user).
Then I created two new mdb files from scratch, imported the front and
back end objects into the dbs. Reset all perms on both files for the
various groups. Now it works as expected.

I can't understand why. The only thing that recreating the dbs should
"change" is the owner. But the owner of all objects is DevAdmin both
in the old and recreated. I have both files and both seem to be
identical as far as ownership goes...

This really bugs me. Access sec is so contorted already, I don't need
a part like this that seems to defy the pattern. It's kind of a big
deal to have to reacreate the dbs with all perms and activex objects.
So if anyone has a clue let me know. And thanks Joan.

Based on what you've told us, I can't see what was wrong. Just a hint for
if you need to do this again, you can use the utility at
http://www.daiglenet.com/MSAccess.htm to script out the permissions, and
them write them back in your new mdb. Look for DbUtilities.

 

[Joan Wild]

Lookups? Not sure what you
mean, yes there are some dlookup uses here and there but they aren't
relevant here.

I was referring to using the lookup wizard in a table (or using the lookup
tab in a query). These are often overlooked in security, and they often
reference tables.

 

[kiln]

Thanks Joan, I'll look into the utility, sounds handy.