Security without signon

[jacksonmacd]

TC

I think Joan is right on this one -- although it contradicts what I
*thought* was correct. The attached A97 file has one secured table and
one RWOP query. I can open the query when Access is running under my
non-secured workgroup file, but I am denied permission to the
underlying table.

Contrary to the OP -- and what I would expect -- attempting to link to
the table from a different MDB file does not succeed.

Could you post a small example mdb & mdw where I can get at them?

TC

**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

 

[jacksonmacd]

TC

I think Joan is right on this one -- although it contradicts what I
*thought* was correct. Tried to attach a file to this message, but it
was rejected. Can you provide an email address or site for posting?

In the demo A97 file, I can open the query when Access is running
under my non-secured workgroup file, but I am denied permission to the
underlying table.

Contrary to the OP -- and what I would expect -- attempting to link to
the table from a different MDB file does not succeed.

Could you post a small example mdb & mdw where I can get at them?

TC

**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

 

[TC]

Jack, I can't see the attachment. I use google groups for most of my
usenet work, maybe they don't allow attachments?

Could you email it to me please? My address is aatcbbtccctc <you know
what comes here> Yahoo dot com.

Thanks,
TC

 

[TC]

Sorry, I just saw this. I think our posts got out of synch!

Please send the files to aatcbbtccctc (you know what comes here) Yahoo
dooooot com

TC

 

[Joan Wild]

If I felt better, I would take the time to do this. However I would think
with your knowledge of security, that you could just as easily test this
yourself using Northwind.

 

[Joan Wild]

You know, when you think about it, it makes sense that the owner doesn't
have to be in the mdw. Permissions are stored in the mdb (and the rwop
flag). The mdb just needs to know that it is RWOP, and what the owner
permissions are, to run the query.

 

[TC]

Yes, you are right.

The database file does not need the workgroup file, in order to know:
- that the query is an RWOP query;
- what SID owns the RWOP query, and
- what permissions that SID has, to other objects in that database.

So, if an RWOP query is executed: "with the permissions of the user who
owns that query", then, it would seem that the database file has all
the information that it needs to do that, regardless of whether a user
with that SID does or doesn't exist in the current workgroup file.

With one exception! (luckily for me!!)

When you are using a workgroup file in which the owner of the RWOP
query /does/ exist, the query would also have the permissions of all of
the groups to which that owner/user belonged. For example, the RWOP
query might obtain some extra permissions from its owner being a member
of the BlahBlah group. But in a workgroup file where the owner of the
RWOP query does /not/ exist, those group-based permissions would not be
added to the query.

So it is quite possible that the RWOP query would work 100% correctly
when its owner /does/ exist in the current workgroup, file, but not
when that user does not.

My error, as I see now, was in saying that an RWOP query will /never/
work when the owner of that query does not exist in the current
workgroup file. I should hav said, "an RWOP query will probably BUT NOT
NECESSARILY work corectly, when its owner is not a member of the
current workgroup file".

Gasp!
TC

 

[Joan Wild]

With one exception! (luckily for me!!)

When you are using a workgroup file in which the owner of the RWOP
query /does/ exist, the query would also have the permissions of all
of the groups to which that owner/user belonged. For example, the RWOP
query might obtain some extra permissions from its owner being a
member of the BlahBlah group.

I disagree. The query would only have permissions for the BlahBlah group,
if that group were given permissions on the query.

So it is quite possible that the RWOP query would work 100% correctly
when its owner /does/ exist in the current workgroup, file, but not
when that user does not.

Again I disagree. The RWOP query will work 100% whether the owner is in the
mdw or not. Please describe a scenario where it wouldn't.

 

[TC]

Ok, clearly we won't resolve this by further theory-based discussion.
I'll do some actual experiements & reply in due course. But that will
have to fit within the requirements of other work on my plate.

Cheers,
TC

 

[Zardoz]

Sorry to post so long after the fact, but I just came upon this very
interesting discussion while researching my own related problem with RWOP
queries.

After seeing Joan state so unequivocally that RWOP queries will work even if
the owner is not in the production mdw, I decided to investigate further why
this was not my experience. Turns out that she is right, of course, but I'll
share with you why it wasn't working for me.

The owner of all of the objects in my front-end mdb, including the queries,
was an individual "super user" (i.e., not a group). When I secured the
back-end mdb, I gave table permissions to the administrator group (not
Admins) that this user was a member of and gave no table permissions
explicitly to the individual "super user", assuming that permissions would
flow through to the "super user" by virtue of membership in the administrator
group.

Well, with one important 'Gotcha', it appears as if the permissions do flow
through as you would normally expect, as the "Super User" was able to access
the tables as per usual. However, anyone using the default System mdw would
get error 3112 (no read permission) even though the default Admin user had
appropriate permissions on the queries in the secured front-end. When I
changed security in the back-end so that the "Super User" was explicitly
granted permissions on the tables, then everything worked for users with the
default System mdw just as Joan suggested it would.

So the 'Gotcha' is that, with RWOP queries, permissions do not flow-through
from a Group to a user. If an individual user is specified as the owner of
the query, then for that query to work as a RWOP query, that individual owner
must have explicit permissions on the back-end table, not permissions that
are inherited by virtue of group membership.

It will also work if the owner of the query is a group and the group has
permissions on the back-end table. You can change the ownership of the query
to a group if you want. The important point is that the names must
match--the owner of the query must also have explicit permissions on the
back-end table, whether that be a group or an individual user.

 

[Joan Wild]

Not my experience. I have never had to give the query owner explicit
permissions on the tables (shouldn't need to since the query owner is
the owner of the tables as well).

Your super user should not need to inherit any permissions from any
group, as the owner trumps all.

Your explanation makes sense to me, but only if Super User did not own
the tables.

Joan

 

[Zardoz]

Thanks for the response, Joan. You gave me more pause for thought, just when
I thought I had a handle on this.

I double- and triple-checked, and the superuser is the owner of the tables
in the backend database.

I have retested this scenario several times, and my original findings hold,
but I can add one more piece to the apparent puzzle.

Recall that the superuser is the member of a 'supergroup', and the
'supergroup' has explicit permissions on the tables in the backend database
but the superuser does not have any explicit permissions on the tables in the
backend database.

When I go in as the Admin user using the mdw that I used to secure the
database, everything works fine.

However, as noted above, when I go in as the Admin user using the default
System.mdw, error 3112 is generated (no permissions on the table). If I give
the superuser explicit permissions on the table, then this error goes away
and everything works fine.

Any suggestions on what else I can check?
Thanks.

 

[David W. Fenton]

I double- and triple-checked, and the superuser is the owner of
the tables in the backend database.

What about the table links in the front end? Permissions on those
are completely independent of the back-end table permissions.

 

[Zardoz]

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.

 

[Joan Wild]

The only thing I see different, is that I use the Admins Group in
securemdw, whereas you created a separate SuperGroup.

Joan Wild
MS Access MVP

 

[Zardoz]

Thanks Joan. Then presumably your SuperUser/Owner is a member of Admins and
Admins has permissions on the back-end tables?

The Admins group, as I understand it, isn't portable across mdw files. So
the Admins group in SecureMDW will not be recognized as the same as the
Admins group in SystemMDW (unless, perhaps, SystemMDW is copied to make
SecureMDW).

If this is true, then theoretically the Admin user coming in through
SystemMDW (who is a member of the Admins group in SystemMDW) in your scenario
should get the same 3112 error that the Admin user coming in through
SystemMDW in my scenario gets. This is because your Admin user will still
have no way of knowing that your SuperUser/Owner is a member of Admins (as
this membership is defined only in your SecureMDW), and will thus not be able
to trace the inherited permissions back from Admins through to your
SuperUser/Owner.

So there must be more to it.