"Admin" gives permissions to self on one table

[Paul]

I am trying to use the Access security (and before anyone says it, no I
didn't follow the instructions in the FAQ to the letter, and yes I did use
the wizard- I was following the Access help).

What bugs me is that having "secured" my databse, I was able to switch back
to System.mdw and for *only one* table have Admin give himself the
Administer privilege. The Users and Admins groups had had the permissions
to the tables removed and Admin had been removed from the Admins group.

What really bothers me is that this only occured on one of the tables and
not the rest - I can't see any difference in the settings and no amout of
fiddling the ownership of the table seemed to affect it... what's going on
?

Thanks

Paul

 

[Joan Wild]

I am trying to use the Access security (and before anyone says it, no I
didn't follow the instructions in the FAQ to the letter, and yes I did use
the wizard- I was following the Access help).


What bugs me is that having "secured" my databse, I was able to switch back
to System.mdw and for *only one* table have Admin give himself the
Administer privilege. The Users and Admins groups had had the permissions
to the tables removed and Admin had been removed from the Admins group.

I don't follow. If you can even *open* the db using the system.mdw, then
that is a further problem. Open it using your secure mdw and double check
that the Users Group and the Admin User do not have Open permission on the
Database object (often missed, it's at the top of the dropdown list).

You shouldn't be able to open your secure db using system.mdw. As for the
table, check to see what user owns the table. I'm not sure about this, but
the wizard may not have removed permissions for the Users group if that
table was hidden at the time. Anyway you should be able to change the owner
to your all powerfull user.

 

[Paul]

<g> Depending on version, the wizard can do an OK job. What version
are you using?

Access 2000

I don't follow. If you can even *open* the db using the system.mdw,
then that is a further problem. Open it using your secure mdw and
double check that the Users Group and the Admin User do not have Open
permission on the Database object (often missed, it's at the top of
the dropdown list).

I had actually wanted people who used System.mdw to be able to open it for
read-only Access. However this is starting to seem unworkable.

You shouldn't be able to open your secure db using system.mdw. As for
the table, check to see what user owns the table. I'm not sure about
this, but the wizard may not have removed permissions for the Users
group if that table was hidden at the time. Anyway you should be able
to change the owner to your all powerfull user.

Certainly not the owner, I am pretty sure I removed all the Table
permissions from all the groups and other users except my superuser but
Admin was still able to waltz in and assign Administer to himself.

I just feel unnerved that there seems to be something I can't see and can't
change which seems to render all my effort pointless. I guess I'll go back
and try try again.

Come to think of it the ownership of the tables was varied after running
the wizard and some of them did start off hidden, and I think the problem
table was one of the hidden ones. I guess it might not be just that one
table, it might be all the ones that started the process hidden. I also had
problems getting my new administrator accound to take ownership of the
tables... something is not right here.

I think I'm going to go back and do it again but not use the wizard this
time.

One last thing that concerns me is the Admins group. The wizard report says
the PID was previously created... does this mean it is the same as in
System.mdw ? in which case can't anyone who can open the database wander
along with it and be in the Admins group ? I'd like to think you needed a
matching WID as well but I'm just not sure.

Thanks again,

Paul

 

[Joan Wild]

Hi Paul,

Access 2000

Unfortunately, that wizard isn't so good.

I had actually wanted people who used System.mdw to be able to open it for
read-only Access. However this is starting to seem unworkable.

Sorry to have misled you. It is quite workable (I emphasized about opening
with the default mdw because most often that is the setup that people really
need/want). You can certainly setup your database so that it is secure and
still allow the standard system.mdw to be used by some/all users.

Certainly not the owner, I am pretty sure I removed all the Table
permissions from all the groups and other users except my superuser but
Admin was still able to waltz in and assign Administer to himself.

That suggests that Admin owned the table - the owner can always administer
regardless of the permissions.

I just feel unnerved that there seems to be something I can't see and can't
change which seems to render all my effort pointless. I guess I'll go back
and try try again.

I understand your frustration. In the end you will find some minute detail
that you overlooked.

Come to think of it the ownership of the tables was varied after running
the wizard and some of them did start off hidden, and I think the problem
table was one of the hidden ones. I guess it might not be just that one
table, it might be all the ones that started the process hidden. I also had
problems getting my new administrator accound to take ownership of the
tables... something is not right here.

I think I'm going to go back and do it again but not use the wizard this
time.

That sounds like a good idea. Unhide all your objects, don't even
touch/display any of the Msys objects.

In 2000, you shouldn't use the wizard, as I understand it left a gaping
hole. The wizard doesn't do anything that you can't do yourself.

Follow each step (each phrase) in the FAQ, but don't run the wizard.

One last thing that concerns me is the Admins group. The wizard report says
the PID was previously created... does this mean it is the same as in
System.mdw ?

No that suggests to me that you started by creating a new mdw to be your
secure workgroup, and you made it the default one to use. Then you ran the
wizard and chose to modify the existing mdw (thus it was previously
created).

in which case can't anyone who can open the database wander
along with it and be in the Admins group ?

The Admins group is the one entity that is *not* the same in any other mdw.
But Users Group and Admin user are the same in every one.

 

[Paul]

Unfortunately, that wizard isn't so good.

I've had another go today, sans wizard and got on much better.
I'd go as far as saying the wizard in Access 2000 is worse than useless,
since it doesn't do the job right, but the user is mislead by it.

I'd write in large letters "do not use the security wizard in Access
2000".

Sorry to have misled you. It is quite workable (I emphasized about
opening with the default mdw because most often that is the setup that
people really need/want). You can certainly setup your database so
that it is secure and still allow the standard system.mdw to be used
by some/all users.

I've got on much better than this since dispensing with the wizard,
suddenly it all makes sense and behaves the way I expect.

That suggests that Admin owned the table - the owner can always
administer regardless of the permissions.

I wrote some DAO to dump out all the user groups and table permissions to
a file. Admin was left with rights to a whole heap of hidden system
objects and tables. Which seems to coincide quite neatly with the new
Admins not seeming to have full control (e.g. not being able to take
ownership of all objects).

I understand your frustration. In the end you will find some minute
detail that you overlooked.

I suspect it was the rather large detail of using the wizard. Importing
the tables into a clean DB is clearly the way to go. Though I suspect the
FAQ doesn't mention encrypting it first, which I think is the only other
(intended) function on the wizard.

That sounds like a good idea. Unhide all your objects, don't even
touch/display any of the Msys objects.

I checked up on this, it was all the hidden tables. I dread to think what
the wizard is doing that makes it go wrong for hidden tables.

In 2000, you shouldn't use the wizard, as I understand it left a
gaping hole. The wizard doesn't do anything that you can't do
yourself.

Gaping hardly seems the word. Somone using System.mdw often seemed to
have more access than members of the Admins in the secured mdw, because
not all the ownerships had been transferred.

Follow each step (each phrase) in the FAQ, but don't run the wizard.

I'm glad that FAQ's there. Shame about all the misleading documentation
in the Access help files, it would be better off just pointing you there.

No that suggests to me that you started by creating a new mdw to be
your secure workgroup, and you made it the default one to use. Then
you ran the wizard and chose to modify the existing mdw (thus it was
previously created).

Not sure, not sure it matters. I am curious about the built-in nature of
the Users and Admins groups though.

The Admins group is the one entity that is *not* the same in any other
mdw. But Users Group and Admin user are the same in every one.

What is it that makes it different? The WID ? or do Admins groups in
different MDWs have different PIDs?
Since you can get back in my knowing the WID I guess the Admins group PID
is WID-mangled and the Users isn't ?

Paul

 

[Joan Wild]

I'd write in large letters "do not use the security wizard in Access
2000".

I draw your attention to step 10 in the FAQ

<q>
The Access 2000 Security Wizard removes permissions to the point where they
are not visible on the security menus, but testing has revealed that in
Access 2000 it is possible to open a database by using the default workgroup
information file regardless of themenu settings. The cure for both versions
of Access is to create a new, empty database while logged on as a member of
the Admins group and import all of the objects from the secured database.
You should take this step before spending too much time securing objects
because Access considers imported objects to be 'new' and loses the
permission information that was stored in the source database.
<q>

Perhaps, not big bold letters, but as is often said "Follow every step" (and
yes that includes step 10)

snip

What is it that makes it different? The WID ? or do Admins groups in
different MDWs have different PIDs?
Since you can get back in my knowing the WID I guess the Admins group PID
is WID-mangled and the Users isn't ?

It's actually the SID that is different, while the SID for Users and Admin
is the same. If you want to delve further into this I'd recommend reading
the security White Paper as it explains how these are created.
http://support.microsoft.com/?id=122036
http://support.microsoft.com/?id=148555

 

[Paul]

I draw your attention to step 10 in the FAQ

I think my point is, that even though the information is all in
there it
is still a little perplexing for someone approaching this. I'm
not
knocking the article - I think it is very good, I'm just saying I
was a
little confused as to what parts of steps 7 and 10 were
applicable to me
(as an Access 2000 user) and what order I should do them in (and
I'm
sure I can't be the only person). If you're new to the subject
and
already confused it is hard to see what the implications are of
what the
article is telling you.

As it says at the top "This FAQ was written originally to cover
Microsoft Access versions 2.0 through 97". And the instructions
for Acc.
2000 in that part read as something for an afterthought.

Actually I fail to see why step 10 says the import should be done
from
the security enhanced database. As it says on the following line
"Access
considers imported objects to be "new" ". So surely whether they
came
from a secured db or not is irrelevant?

Perhaps, not big bold letters, but as is often said "Follow every
step" (and yes that includes step 10)

Well apart from Step 7, which I didn't follow and the step of
manually encrypting the fresh database file I imported to which
is sort
of there by implication in step 7.

It's actually the SID that is different, while the SID for Users and
Admin is the same. If you want to delve further into this I'd
recommend reading the security White Paper as it explains how these
are created.
http://support.microsoft.com/?id=122036
http://support.microsoft.com/?id=148555

I'm sure I remember reading about SIDs in the dim and distant
past. I'll
have a look.

Cheers,

Paul

 

[Joan Wild]

I think my point is, that even though the information is all in
there it
is still a little perplexing for someone approaching this.

I wrote that in response to

<q> I'd write in large letters "do not use the security wizard in Access
2000".<q>

My point was that you saw a specific point that should have been in large
letters. If they put in large letters these critical points, then all ten
steps would look like that. You implied that the FAQ didn't mention the
flaws in the 2000 wizard, but it is there in step 10.

I understand your frustration. Perhaps the next version will create separate
set of steps for each version.

 

[Paul]

My point was that you saw a specific point that should have been in
large letters. If they put in large letters these critical points,
then all ten steps would look like that.

True. True.

You implied that the FAQ
didn't mention the flaws in the 2000 wizard, but it is there in step
10.

What I meant was it is one of those cases where the instructions make
perfect sense if you already understand what is going on, but the meaning
seems somewhat obscured for a novice (actually I'm still puzzled about
needing to import the tables from the secured file).

I understand your frustration. Perhaps the next version will create
separate set of steps for each version.

I'm not frustrated, I'm happy because it all seems to be working for me
now. I'm just trying to give some feedback (as someone who has recent
memories of being utterly befuddled by this) to help the next poor confused
soul who tries to do this.

Paul