Advance Security Issue Part II

[Guest]

Okay, I've got the dual-mdw thing all straight, now I have a permissions
question.

In my master mdw, DEV.MDW, I have the SuperUser who owns all objects and has
all permissions. SuperUser has created a table called SYSTABLE. He has also
created a Snapshot query to read that table. The DBUSERS group has no
permission on the table, but READ permission on the query (which is set to
use the owner's access).

When I create the user DataUser in DEV.MDW, and add him to the DBUSERS
group, all is fine. He can't touch the table, or view the design of the
query, but can run the query and view the data.

Now when I create the user SiteUser in the second mdw - USER.MDW, and add
SiteUser to the DBUSERS group (which has been created with the same PID as
DBUSERS in DEV.MDW), he has no access to the query at all.

Is this because SuperUser doesn't exist in USER.MDW (thereby nullifying the
effects of the owner access option)? Or have I just missed something?

I was thinking of creating another user in both workgroup files that has
read permissions on the table and using him to create the query (that way
he'll own the query and can pass permissions on through the owner access
option). That creates a potential security hole though (direct access to the
tables, which I'm trying to avoid), since somebody could crack that password.

Any suggestions?

 

[Joan Wild]

Okay, I've got the dual-mdw thing all straight, now I have a permissions
question.

In my master mdw, DEV.MDW, I have the SuperUser who owns all objects and
has
all permissions. SuperUser has created a table called SYSTABLE. He has
also
created a Snapshot query to read that table. The DBUSERS group has no
permission on the table, but READ permission on the query (which is set to
use the owner's access).

When I create the user DataUser in DEV.MDW, and add him to the DBUSERS
group, all is fine. He can't touch the table, or view the design of the
query, but can run the query and view the data.

Now when I create the user SiteUser in the second mdw - USER.MDW, and add
SiteUser to the DBUSERS group (which has been created with the same PID as
DBUSERS in DEV.MDW), he has no access to the query at all.

Is this because SuperUser doesn't exist in USER.MDW (thereby nullifying
the
effects of the owner access option)? Or have I just missed something?

No, SuperUser doesn't need to exist in User.mdw. Are you certain that
DBUSERS group is really the same in both mdw files?

That would be my guess. This has worked countless times for me.

 

[TC]

Okay, I've got the dual-mdw thing all straight, now I have a permissions
question.

Permission granted!

In my master mdw, DEV.MDW, I have the SuperUser who owns all objects and has
all permissions. SuperUser has created a table called SYSTABLE. He has also
created a Snapshot query to read that table. The DBUSERS group has no
permission on the table, but READ permission on the query (which is set to
use the owner's access).

Looks good so far.

When I create the user DataUser in DEV.MDW, and add him to the DBUSERS
group, all is fine. He can't touch the table, or view the design of the
query, but can run the query and view the data.

Still looks good - but I have this strange feeling that something is
about to go wrong!

Now when I create the user SiteUser in the second mdw - USER.MDW, and add
SiteUser to the DBUSERS group (which has been created with the same PID as
DBUSERS in DEV.MDW), he has no access to the query at all.

I'm not surprised! (Um, actually, I was: until I read your next
paragraph!)

Is this because SuperUser doesn't exist in USER.MDW (thereby nullifying the
effects of the owner access option)? Or have I just missed something?

No, you got it spot-on. That is the cause of the problem. But it's not
because the effects of the WITH OWNER ACCESS (WOA, aka RWOP) option are
being "nullified". It's quite the opposite: the effects of that option
are exactly what they should be in that circumstance.

You've told Jet to execute the query with the permissions of a user who
does not exist in the current workgroup file. So, Jet does not know
/what/ permssions to use, so it can not afford to run the query at all.

I was thinking of creating another user in both workgroup files that has
read permissions on the table and using him to create the query (that way
he'll own the query and can pass permissions on through the owner access
option).

Yes, I believe that is exactly what you'll have to do. If anyone else
can think of a better way, chime in here!

That creates a potential security hole though (direct access to the
tables, which I'm trying to avoid), since somebody could crack that password.

Yes. The ability to reverse-engineer the passwords from the workgroup
file, is very unfortunate. It is /not/, as many people mistakenly say,
due to the fact that the workgroup file is freely available for people
to play with. It is instead due to a simple, "schoolboy howler" mistake
in how the passwords are encrypted. I've submitted a suggestion on how
to eliminate this, with what I believe would be a 2 line fix to the Jet
sourcecode! Whether that will ever be done is, of course, a matter for
conjecture by us mere mortals.

Here's what I'd do. Create the new user, and give him (via VBA code) a
password like "s3crrrxx3t", where the two x's represent the hex
character for BACKSPACE - 0x08, from memory. Then, when the user runs a
cracking utility to display the passwords, the utility - unless it is
/very/ smart - will send the following character sequence to the
screen: s 3 c r r r BACKSPACE BACKSPACE 3 t. So the user will
see: "s3cr3t". That sure looks like a secret password!! But it will not
work, from the login box, or via VBA, because it actually isn't the
password, at all!

I think that would deter all but the most determined & technically
sophisticted users. You could throw some ENTERs (0x0D) and LINEFEEDs
(0x0A) into the mix, as well, if you wanted. You could easily see how
your choice, would look on the screen:

debug.print "s3crrr"; chr$(8); chr$(8); "3t"

Remember: 1% of effort will deter 99% of hacking users. But *no amount*
of extra effort, will deter the remaining 1%.

HTH,
TC

 

[Guest]

Doh! Thwarted by Microsoft again! Thanks for you very complete answer
though... I'll have to test out your suggestions. Although even with that
security problem, I think I'll end up with a pretty secure database. Or at
least as secure as it can be without having an armed guard standing by...

 

[Guest]

Just thinking this through... I have the overview of how it all works now,
but some of the inner details elude me.

The mdw contains Users and Groups, generates SIDs for each of those, and
contains the User/Group relationships. It also has the user passwords, of
course.

The mdb contains the db objects, the object/SID relationships, and the SID
of the Admins group that created the mdb (I think). I would also guess that
it contains the SID of the owner of each object.

But I'm not sure where the permissions themselves reside (for example, the
Read/Insert/Update/Delete permissions on a table or query). I would think
those would reside in the mdb. Therefore, the mdb (in the MSysACEs table),
would contain Object/SID/Permissions.

If that's the case, I can't see a use for the mdw once you've logged in.
After the mdw verifies your username/login, does it pass your user SID and
group SID's to the mdb? Or does the mdb go back to the mdw every time you
attempt to open an object?

Now if I'm logged in as DBAdmin from DEV.MDW, with READ permission on a RWOP
query, what happens? I assume from your answer that the mdb goes back to the
mdw, but for what? If I log in as SiteAdmin from USER.MDW, with the
SuperUser information missing, then the mdb must be checking the mdw for
something that isn't there (SuperUser information of some kind).

Is it because the mdb knows the SID of the owner, but doesn't know what
permissions the owner has until it checks with the mdw?

If a query has WITH OWNER ACCESS, and the mdb knows who the owner is (or at
least the owner's SID), AND my SiteAdmin user from USER.MDW is a member of a
group that has READ access on that query, what's missing? I would have
thought that would be enough. If my assumption that the mdb contains the
permission information is correct, then I'm not sure why the missing
SuperUser information would really matter.

And if you can answer this, where the heck did you learn all this in the
first place? There isn't a whole lot of documentation on this, and what I've
learned about Access security has taken an awful long time (and there's still
a ways to go!).

I attempted to go back through what I'd just written to try to simplify it,
but I think my brain just short-circuited...

Thanks muchly,
Phillip Theriault

 

[Joan Wild]

You've told Jet to execute the query with the permissions of a user who
does not exist in the current workgroup file. So, Jet does not know
/what/ permssions to use, so it can not afford to run the query at all.

That is not correct. I have used this technique all the time; never put the
Owner/SuperUser in the production mdw and have not had any problems. See my
other post.

By the way, why do you think that owner shows as unknown while using a
production mdw? But it *does* work.

 

[Guest]

Hmmm... interesting. Looks like I may have goofed on something (not sure
what just yet though). I wanted to test a few things to see what happened,
and I found that when I gave my DBUSERS group full permissions on the tables
and queries, I could open the table and manipulate it any way I wanted (so my
DBUSERS group is set up correctly), I go could into the design view of the
query and see the design, but I can't modify the query or run it. If you
have any idea what I may have missed I'd appreciate the hint, but thanks for
letting me know this should work

 

[Guest]

Hey! Ever felt like a complete dufus? I just realized that I had taken
permissions off the SuperUser so that permissions were only on the groups
(why? I don't really know... just an obsessive control freak thing I guess).
As soon as I put permissions back onto the SuperUser, voila!!

Ah... time to go back into hiding.

 

[TC]

Just thinking this through... I have the overview of how it all works now,
but some of the inner details elude me.

Bring it on!

The mdw contains Users and Groups, generates SIDs for each of those, and
contains the User/Group relationships. It also has the user passwords, of
course.
Yes.


The mdb contains the db objects, the object/SID relationships, and the SID
of the Admins group that created the mdb (I think).

Yes to all those.

I would also guess that it contains the SID of the owner of each object.
Yes.


But I'm not sure where the permissions themselves reside (for example, the
Read/Insert/Update/Delete permissions on a table or query). I would think
those would reside in the mdb. Therefore, the mdb (in the MSysACEs table),
would contain Object/SID/Permissions.
Yes.



If that's the case, I can't see a use for the mdw once you've logged in.
After the mdw verifies your username/login, does it pass your user SID and
group SID's to the mdb? Or does the mdb go back to the mdw every time you
attempt to open an object?

As I understand it, Access (or most probably, Jet) builds an in-memory
structure containing the SID of the logged-in user /and/ the SIDs of
each of the groups to which that user belongs. Then, when Jet wants to
know, for example, whether the current user can read table 'x', it just
checks to see if the MSysACEs table in the mdb, has an entry with
{object='x', SID=any SID in the memory structure noted above, and
access-mask has the Read Data bit set}.

As for the workgroup file remaining open, remember that you can add or
delete users & groups (and modify their relationships) through code, &
those changes must be committed to the workgroup file ASAP. So it might
as well stay open, for that purpose, just in case.

Now if I'm logged in as DBAdmin from DEV.MDW, with READ permission on a RWOP
query, what happens? I assume from your answer that the mdb goes back to the
mdw, but for what? If I log in as SiteAdmin from USER.MDW, with the
SuperUser information missing, then the mdb must be checking the mdw for
something that isn't there (SuperUser information of some kind).

The current user's SID (and those of all his groups) is known, as
described above. The owner of the query is stored in the MSysObjects
table entry (in the mdb) for that query. And the owner's SID (and those
of all his groups) can be ascertained by walking the workgroup file.
That is all the information you need, to work the RWOP query.

Is it because the mdb knows the SID of the owner, but doesn't know what
permissions the owner has until it checks with the mdw?

Yes. The only way that this could /not/ be true, ie. that Jet & Access
/could/ disconnect the workgroup file as soon as someone had logged-in,
would be, if all the info from the workgroup file was cached, before it
was closed.

If a query has WITH OWNER ACCESS, and the mdb knows who the owner is (or at
least the owner's SID), AND my SiteAdmin user from USER.MDW is a member of a
group that has READ access on that query, what's missing? I would have
thought that would be enough. If my assumption that the mdb contains the
permission information is correct, then I'm not sure why the missing
SuperUser information would really matter.

Again I don't have the other info to hand. But from memory, you were
running an RWOP quewry which was owned by a user who wasn't defined in
the current workgroup file. Jet would say "ok, the owner of this query
is Blah. I need to get Blah's permissions (& those of all the groups to
which he belongs). I'll just walk the workgroup file to get that info."
Oops: Blah does not exist in that workgroup file, so effectively, he
has /no/ permissions to /anything/.

And if you can answer this, where the heck did you learn all this in the
first place? There isn't a whole lot of documentation on this, and what I've
learned about Access security has taken an awful long time (and there's still
a ways to go!).

A combination of things, I guess. I've been a professional software
developer for >30 years, I've written probably over a million lines of
code in dozens of different languages, I've done lots of system
programming (where you need to understand the details "behind the
scenes"), and I've done a lot of thinking, and experimentation, about
how it all works. With enough effort, you can work it out by experiment
& logical thinking. I also have an amateur interest in cryptography,
and that helped me crack the encryption scheme.

I attempted to go back through what I'd just written to try to simplify it,
but I think my brain just short-circuited...

No, it sounds fine to me. From what you have written, there's not much
more for you to learn about it, afaics!

Cheers,
TC

 

[Guest]

Thanks again TC. I thought I was an Access security expert until I
discovered the dual mdw thing. I think I may have regained my status now

 

[TC]

I agree! There's not much you would not know about it, now.

Cheers,
TC