Security confusion

N

Nick Balstone

I've thoroughly read the security FAQ and Jack MacDonald's alternative
guide to setting up security in Access, but I'm still not 100% clear
on a particular point, and would greatly appreciate clarification from
those in the know.

I've gone through the whole procedure of:

- creating a new secure mdw, using wrkgadm.exe
- opening my db using this mdw (via a new shortcut)
- set a password for the default user Admin
- closed Access, and restarted via the shortcut, and logged in as
Admin
- created a new 'superuser', and added them to the Admins group
- closed Access again, restarted and logged in as the 'superuser'
- created new groups: "newadmins" and "newusers"
- made the superuser the owner of the database (did this via the
Immediate window rather than by creating a new db and importing -- it
worked, as the User permissions dialog now shows the superuser as
being the owner of the db)
- made the superuser the owner of all objects in the db
- created a set of users, and put them into the appropriate group
(newusers or newadmins)
- set up group permissions: newadmins have all permissions, newusers
have read, insert and update permissions
- removed all permissions from the default groups Admins and Users
- removed all permissions from all users, including the default Admin
user

This works fine when I run my secured db via the secure mdw file,
everyone has the permissions that I was hoping for.

However, upon opening the same db via the default system.mdw, the
default user (i.e. Admin) appears to have full permissions to do
anything.

What I then did was to go into the User and Group permissions dialog
in the default version of Access and removed all permissions from the
Admin user there. That then did the trick -- the db can now only be
opened by using the secure mdw file and logging in with
username/password.

What's my problem, you may well be asking? It is that this final step
doesn't seem to be documented in the FAQs. And thinking about it,
security is set on the individual db, so once I removed all rights
from the default Admin user (using the secure mdw) shouldn't that have
rendered the database unusable via the default system.mdw? And when I
distribute the db to the rest of the team then I'm going to have to
remove all permissions to this db from the default Admin user on each
of their workstations to stop them opening it except via the secure
shortcut I give them. Surely that can't be right.

Can anyone tell what I've missed in my procedure above?

Thanks in advance,

Nick
 
J

Jeff Conrad

Hi Nick,

Comments inline.....
I've thoroughly read the security FAQ and Jack MacDonald's alternative
guide to setting up security in Access, but I'm still not 100% clear
on a particular point, and would greatly appreciate clarification from
those in the know.

Here's two more links you may want to look at:

Lynn Trapp's Ten Security Steps:
http://www.ltcomputerdesigns.com/Security.htm

Joan Wild's Tips:
http://www.jmwild.com/security02.htm
I've gone through the whole procedure of:

- creating a new secure mdw, using wrkgadm.exe
- opening my db using this mdw (via a new shortcut)
- set a password for the default user Admin
- closed Access, and restarted via the shortcut, and logged in as Admin
- created a new 'superuser', and added them to the Admins group
- closed Access again, restarted and logged in as the 'superuser'
- created new groups: "newadmins" and "newusers"
- made the superuser the owner of the database (did this via the
Immediate window rather than by creating a new db and importing -- it
worked, as the User permissions dialog now shows the superuser as
being the owner of the db)

Huh???
All the experts and documentation say to import all the database objects into a new container while
logged in as the SuperUser. This will make the SuperUser the owner of the database. Quite frankly
I've never even heard it was possible to do this via the Immediate Window. This would definitely be
one step I would not miss nor try to 'fudge' some other way. This could be part of the problem
why...:
However, upon opening the same db via the default system.mdw, the
default user (i.e. Admin) appears to have full permissions to do
anything.

Moving on.
- made the superuser the owner of all objects in the db
- created a set of users, and put them into the appropriate group
(newusers or newadmins)
- set up group permissions: newadmins have all permissions, newusers
have read, insert and update permissions
- removed all permissions from the default groups Admins and Users
- removed all permissions from all users, including the default Admin user

It's not immediately clear, but did you remove the Admin USER from the Admins GROUP? This is a vital
step. The Admin user should only be a member of the Users Group. Period. The Users group should have
zero permissions which it sounds like you did. Not removing the Admin user from the Admins group
could also be part of the problem why...:
However, upon opening the same db via the default system.mdw, the
default user (i.e. Admin) appears to have full permissions to do
anything.

Moving on.
This works fine when I run my secured db via the secure mdw file,
everyone has the permissions that I was hoping for.
OK.

However, upon opening the same db via the default system.mdw, the
default user (i.e. Admin) appears to have full permissions to do anything.

Ding, Red Flag!
This is a sure sign that at least one step was missed in applying security. If done *properly* no
one should be able to open the database with the default system.mdw. Setting up ULS is a difficult
task to do the first couple of times and steps can easily be missed. My money is on the problems
discussed above.
What I then did was to go into the User and Group permissions dialog
in the default version of Access and removed all permissions from the
Admin user there. That then did the trick -- the db can now only be
opened by using the secure mdw file and logging in with
username/password.

This should not need to be done at all. See above.
What's my problem, you may well be asking? It is that this final step
doesn't seem to be documented in the FAQs.

Not documented because it is not needed. See above.
And thinking about it, security is set on the individual db, so once I removed all rights
from the default Admin user (using the secure mdw) shouldn't that have
rendered the database unusable via the default system.mdw?

If you also remove the Admin user from the Admins GROUP then the problem would not arise.
And when I distribute the db to the rest of the team then I'm going to have to
remove all permissions to this db from the default Admin user on each
of their workstations to stop them opening it except via the secure
shortcut I give them. Surely that can't be right.

Correct, that is not right. You should not need to do these extra steps.
Can anyone tell what I've missed in my procedure above?

Listed above.
Thanks in advance,

You're welcome, hope that helps.
 
N

Nick Balstone

Many thanks Jeff, for your prompt and helpful reply.

My comments are inserted inline too:
Here's two more links you may want to look at:

Excellent -- I will do so
Huh???
All the experts and documentation say to import all the database objects into a new container while
logged in as the SuperUser. This will make the SuperUser the owner of the database. Quite frankly
I've never even heard it was possible to do this via the Immediate Window. This would definitely be
one step I would not miss nor try to 'fudge' some other way. This could be part of the problem
why...:

I got this from this thread (I've split the url over 3 lines so you'll
need to concatenate it)
http://groups.google.co.uk/groups?hl=en&lr=&threadm=OfMHHH6vCHA.2380@TK2MSFTNGP11&rnum=1
&prev=/groups%3Fhl%3Den%26lr%3D%26q%3Downership%2Bimmediate%26btnG%3DSearch%26
meta%3Dgroup%253Dmicrosoft.public.access.security

which says one can use the following syntax from the Immediate Window:

CurrentDb.Containers("Databases").Documents("MSysDb").Owner= strUser

Of course it only works if the logged-in user has permission to do so.
Not really sure, in hindsight, why I did it this way instead of
importing -- I'm sure I had my reasons then!
It's not immediately clear, but did you remove the Admin USER from the Admins GROUP? This is a vital
step. The Admin user should only be a member of the Users Group. Period. The Users group should have
zero permissions which it sounds like you did. Not removing the Admin user from the Admins group
could also be part of the problem why...:

Alas, I did do this; Admin is only in group Users as it should be.
Ding, Red Flag!
This is a sure sign that at least one step was missed in applying security. If done *properly* no
one should be able to open the database with the default system.mdw. Setting up ULS is a difficult
task to do the first couple of times and steps can easily be missed. My money is on the problems
discussed above.

I thought so. I will recheck, but it appears from your comments that
the only non-standard thing I did was the change of db ownership using
the Immediate window. Luckily I backed up the original before setting
security as I knew it was never going to work first time! I'll follow
the instructions to the letter and see what happens.

Many thanks again, Jeff.

Nick
 
J

Jeff Conrad

Hi Nick,

Thanks for the link; interesting info.
I still believe though that you should import all objects into a new database container since
*something* is not working properly in the secured database. You should not need to mess with any
settings on other computers if secured properly. The only way anyone should be able to get in is
through a shortcut. From a quick read of your step this 'appears' to be the only step missed since
you have removed the Admin user from the Admins group. Try importing and see if that solves the
issue.
 
N

Nick Balstone

Jeff Conrad said:
Hi Nick,

Thanks for the link; interesting info.
I still believe though that you should import all objects into a new database container since
*something* is not working properly in the secured database. You should not need to mess with any
settings on other computers if secured properly. The only way anyone should be able to get in is
through a shortcut. From a quick read of your step this 'appears' to be the only step missed since
you have removed the Admin user from the Admins group. Try importing and see if that solves the
issue.

I've now set up security using the standard method of creating a new
database and importing objects. It has indeed worked perfectly --
no-one can get into the database without using the secure link.

Clearly setting database ownership from code is not the way to go. I
wonder why though? The end result *appears* to be the same when one
views permissions/ownership in the security dialog, and indeed, the
user one gives the ownership to seems to have all the permissions one
would expect of the owner. What it doesn't seem to do is remove
permissions from the original owner -- even though the dialog says
different!

Hmmm. Dubious undocumented back doors that don't quite do what they
appear to do -- hope I'm not being harsh, but Microsoft's reputation
with regard to security is not being enhanced in my eyes!

Nick
 
J

Jeff Conrad

Hi Nick, comments below...
I've now set up security using the standard method of creating a new
database and importing objects. It has indeed worked perfectly --
no-one can get into the database without using the secure link.

Excellent, that is good to hear!
Glad everything is working fine now.
Clearly setting database ownership from code is not the way to go. I
wonder why though? The end result *appears* to be the same when one
views permissions/ownership in the security dialog, and indeed, the
user one gives the ownership to seems to have all the permissions one
would expect of the owner. What it doesn't seem to do is remove
permissions from the original owner -- even though the dialog says
different!

My only guess on this matter would be that the System Tables are involved with this somehow.
Changing the 'owner' in code may simply not make all the necessary changes to the System Tables.
Just a guess.
Hmmm. Dubious undocumented back doors that don't quite do what they
appear to do -- hope I'm not being harsh, but Microsoft's reputation
with regard to security is not being enhanced in my eyes!

My only comment on this would be since ALL the documentation points to importing all objects into a
database while logged in as your 'Super User', then this is what Microsoft deems necessary to
properly secure the database. I've never seen any information about changing the database owner in
code, expect for the link you provided, so in my eyes this 'undocumented' feature is just that:
undocumented and unsupported, and apparently does not work.
:)
 
N

Nick Balstone

Hello again Jeff,

Jeff Conrad said:
My only guess on this matter would be that the System Tables are involved with this somehow.
Changing the 'owner' in code may simply not make all the necessary changes to the System Tables.
Just a guess.

Makes sense.
My only comment on this would be since ALL the documentation points to importing all objects into a
database while logged in as your 'Super User', then this is what Microsoft deems necessary to
properly secure the database. I've never seen any information about changing the database owner in
code, expect for the link you provided, so in my eyes this 'undocumented' feature is just that:
undocumented and unsupported, and apparently does not work.
:)

I remembered why I went down this route in the first place: I wanted
to have the database owned by a group, rather than an individual user,
which cannot be done using the correct method. *Why* I wanted this to
be so is another question that I don't have an answer to -- all I can
say is that it seemed like a good idea at the time!

I would still say that being able to change security -- even with
dubious results -- in this undocumented way is a trifle unusual. It
does smack somewhat of the technique of "security via obfuscation",
which MS have been heavily criticised for in the past. That said, it's
not a big deal -- I'm fully aware that for real security a desktop
database such as Access is not the product of choice.

Thanks again Jeff, it's been illuminating and enjoyable conversing
with you.

Nick
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top