Thanks for the suggestion, David. I rechecked the ownership of the front-end
table objects (which as you state are just links to the back-end) and the
owner of these objects is my SuperUser, so I don't think that's it. But
please keep the ideas coming.
One thing I noticed is that, when a query is set as RWOP (in the
RunPermissions property of the query), I cannot change the owner of the
query, even when I am logged in as the owner of the query (i.e., as the
SuperUser). I would get the message "You don't have permission to change the
owner of <Name of query>. To change the owner of a database object, you must
have Administer permission for it...." My SuperUser does have Administer
permission for all queries. This had me wondering whether somehow my
SuperUser was not actually being recognized as the owner of the query. But
when I researched this, I found that this behaviour is actually by design, at
least in MS Access 2000 (see
http://support.microsoft.com/kb/208929) and
earlier versions (see
http://support.microsoft.com/kb/120885). I am using MS
Access 2003, and I could not find a similar article for my version,, but
presumably this behaviour continues to be by design.
So my situation continues to be that, using RWOP queries, I can achieve what
the title of this thread refers to as 'security without signon' only if the
owner of the RWOP query is given explicit permissions on the back-end tables
rather than inherited permissions by virtue of group membership. This is
fine for my purposes, but given Joan's statements above, it sounds as if this
should work with inherited permissions as well, in which case I would like to
understand why this isn't working for me in case there is an underlying
problem with my security implementation.
Actually, the more I think about this, the more I am convinced that my
findings make sense, which leads me to wonder whether my scenario is somehow
different from the one implied by Joan. It is the different experiences of
the default Admin user in my scenario that might be the key to understanding
what is going on here.
As we know, the default Admin user is the same across all mdw files. There
are two mdw files at play in my scenario: the one that I created to secure
my database (SecureMDW), and the default one that comes with Access
(SystemMDW). SystemMDW contains the default Admin user account plus the two
default group accounts (Admins, Users). SecureMDW contains these default
accounts plus the account for my SuperUser and the account for my SuperGroup,
and SuperUser is a member of SuperGroup.
If I log in as the Admin user through SecureMDW, inherited permissions
(through SuperUser's membership in SuperGroup) work; if I log in as the Admin
user through SystemMDW, inherited permissions do not work.
What I suspect is happening is that the Admin user (or any user) connecting
through SecureMDW can see that SuperUser is a member of SuperGroup because
these accounts and this membership is defined within SecureMDW. The Admin
user connecting through SystemMDW knows nothing about the existence of
accounts for SuperUser or SuperGroup (and certainly does not know that
SuperUser is a member of SuperGroup) because these accounts and membership do
not exist in SystemMDW.
Both Admin users will see that someone named SuperUser is the owner of the
objects in the database (such as queries, etc.) because object ownership is
part of the permissions that reside with the database file. When SuperUser's
back-end table permssions are inherited through SuperGroup, the SecureMDW
Admin user will recognize SuperGroup is (because SuperGroup is also a member
of SecureMDW) and will be able to trace this inheritance back to recognize
that the owner of the query also has permissions on the back-end table;
however, SystemMDW Admin user will have no idea who this SuperGroup is
(because SuperGroup is not defined in SystemMDW) and, since SuperGroup is
different from the name of the owner of the query (SuperUser), this Admin
user will conclude that the query owner (SuperUser) does not have any
permissions on the back-end table.
My only hesitation in thinking that this is reasonably correct is that Joan
has asserted above that inherited permissions should work in this context and
that her users use the production mdw (which I assume is SystemMDW) and she
has never had to give explicit back-end table permissions to the query owner.
So I am hoping that her scenario is somehow different from mine; otherwise,
I will still have some work to do to understand why inherited permissions are
not working for me.