Hlp - DB security mystery

[dee]

Hi,

I have successfully secured a number of databases - practised on many in
order to gain a good understanding of security and then enforced security on
my database.

I followed Joan Wild's instructions and successfully secured the db - when I
finished, I set the system.mdw back, went to Windows Explorer and tried to
open the db and got the message saying I didn't have access. Used the
shortcut on the desktop and was prompted for user name and password. All was
working very well.

The db will have data entered by a few users, so went to one of their
computers, logged on as a data entry user, and tested opening from Windows
Explorer - no problem. I triple checked the mdw and the Users group has NO
access, the Admin user only has access to the Users group and my users are
part of a DatabaseEntry group which has non-admin rights (I am the only user
with Admin permissions) but access to forms, queries, etc. in order to input
data.

I then logged on as administrator, unsecured the db by granting User group
all permission, making sure to join the system.mdw and then creating a new db
and importing my tables, forms, queries... Then followed all of the steps
to secure, including creating a brand new mdw. At the end of running the
user level security wizard, it says the db is secure, the snapshot indicates
it is secure, everything looks fine. But, again - I can just double-click
the file and open it in Windows Explorer.

The database in on a network share to which only my group and I have access.

Is it possible that it's something that the network people did when setting
up the share?

If anyone has encountered this problem, I would really appreciate any advice
or ideas. I am totally stumped.

Thanks!

 

[Keith Wilby]

Is it possible that it's something that the network people did when
setting
up the share?

No, you've missed a step. At a guess I'd say that Admin is probably still
the owner of the database object, but either way, I'd recommend you download
and study the FAQ on ULS to gain a full understanding. There's a link to it
on my web site and there's also a security example which might help.

The problem with using the wizard is that is does things without telling you
what it's done.

Regards,
Keith.
www.keithwilby.com

 

[dee]

Hi Keith,

No, I haven't missed a step. I have done this over and over many, many
times in order to understand it and was successful over and over (in test
databases).

The odd thing is that it tests as secure from one computer - but not others.
In other words, when I try to open from Windows Explorer, I don't have
sufficient access rights, as I'm not launching using the mdw that contains my
user information. This is the "test" that is recommended and I achieve with
no problem. It's when I go to a brand new computer on the network that I can
get in with no problem.

It just doesn make sense to me.

How can I secure it manually? I would be happy to do that no matter how
long it itakes.

Thanks!

 

[Keith Wilby]

Hi Keith,

No, I haven't missed a step. I have done this over and over many, many
times in order to understand it and was successful over and over (in test
databases).

With respect I'd say you have, there's no other explanation.

The odd thing is that it tests as secure from one computer - but not
others.
In other words, when I try to open from Windows Explorer, I don't have
sufficient access rights, as I'm not launching using the mdw that contains
my
user information. This is the "test" that is recommended and I achieve
with
no problem. It's when I go to a brand new computer on the network that I
can
get in with no problem.

It just doesn make sense to me.

Likely you have (unwittingly) modified the default "system.mdw" workgroup on
*your* computer.

How can I secure it manually? I would be happy to do that no matter how
long it itakes.

Use the FAQ and the example I sited in my first response. Practice on dummy
files until you're confident that you know what you're doing in case you
lock yourself out (easily done).

Keith.

 

[Joan Wild]

What version of Access on your computer? On their computer? Has there ever been any other versions of Access on either computer?

As Keith says, you can secure it manually; read the security FAQ and you can follow the manual steps outlined on the 97/2000 page at my website. If you've studied the FAQ/whitepaper/etc, then the steps should make sense to you.

 

[dee]

Hi again,

I read your steps and clearly understand them and had followed them. I also
re-read the Microsoft FAQs Security Paper.

I have done everything properly. The only thing that pops out at me is that
Access 2000 had a bug in which the db appeared secure, but was not as the
Users group still had permissions that did not show.

I am using Access 2003, but not that long ago, we realized that the default
in Tools Options was Access 2000. We had created a new db by following the
steps in order to do so, but perhaps there is still something there that is
causing this problem.

Perhaps I will test this when I get to the office - make sure I'm logged on
as administrator of my.mdw, create a new DB and import all of my objects,
then re-check all permissions, groups, users. If one does this after
securing the original db from which one imports via the security wizard, does
one need to re-run the wizard. I would think this would be redundant. Would
I, however, need to encode?

Thanks!

 

[Keith Wilby]

Hi again,

I read your steps and clearly understand them and had followed them. I
also
re-read the Microsoft FAQs Security Paper.

I have done everything properly. The only thing that pops out at me is
that
Access 2000 had a bug in which the db appeared secure, but was not as the
Users group still had permissions that did not show.

I've never user A2k so I can't comment.

I am using Access 2003, but not that long ago, we realized that the
default
in Tools Options was Access 2000. We had created a new db by following
the
steps in order to do so, but perhaps there is still something there that
is
causing this problem.

Perhaps I will test this when I get to the office - make sure I'm logged
on
as administrator of my.mdw, create a new DB and import all of my objects,
then re-check all permissions, groups, users. If one does this after
securing the original db from which one imports via the security wizard,
does
one need to re-run the wizard.

If you've secured manually and you understand it then the wizard is as much
use as a chocolate teapot.

I would think this would be redundant. Would
I, however, need to encode?

Encode?

 

[Joan Wild]

Don't use the wizard at all - it doesn't do anything that you can't do manually. The default in Options won't matter. I was asking whether your machine ever had Access 2000 installed on it (perhaps you upgraded to 2003)?

Since you are using 2003, I would revert to your unsecured backup of the mdb (be you sure it is completely unsecured); ensure you are joined by default to the standard system.mdw. Then quit Access; search for all *.mdw files on your computer and rename them e.g. - system.mdw to systemold.mdw, etc.

Now open Access, and proceed to secure manually.

 

[dee]

Hi Joan,

I will look at the manual steps and follow them.

In answer to your question, I have indeed had previous versions of Access on
this computer. One of the other computers has as well, but the others are
brand new.

I have found that joining the MyWrkgroup.mdw, logging on as MyAdmin and then
creating a brand new db and importing all objects, then setting the
permissions works. I'm just not sure why.

The in point steps I followed based on your instructions (I hopefully have
done so!):
1. Create NewDatabase with system.mdw and save
2. Close Access
3. Open Access but *not* NewDatabase
4. Create a new workgroup file, MyWorkgroup.mdw, and join it
5. Tools, Security, User and Group Accounts
6. Add new MyAdmin user
7. Add MyAdmin to the Admins group
8. Remove the Admins group from the Admin user
9. Add a password for the Admin user

10. Close Access
11. Reopen Access
12. Open the NewDatabase
13. When prompted for user name, make sure it’s the MyAdmin user – there is
no password set yet
14. Tools, Security, User and Group Accounts – add a password for MyAdmin

15. Close and reopen Access and NewDatabase, logging on as MyAdmin with
password
16. Tools, Security, User Level Security Wizard
17. Modify the current workgroup, Next
18. Leave all objects selected to make sure all will be secured, Next
19. Leave default groups unchecked – we will not use them, Next
20. Make sure Users group has no permissions on anything, Next
21. Add new users to avoid having to log in individually later to set their
passwords then Next
22. Click Next again, as we will create groups and assign permissions later
23. Click Browse to choose a location for the unsecured bak file of your db
24. Finish
25. Print and save snapshot report
26. Access closes, encodes and reopens the db, plus places a shortcut to it
using the MyWorkgroup.mdw

27. Close the database
28. Rejoin the system.mdw workgroup

29. Test the desktop shortcut to open the db with your user name and password
30. Set permissions by adding groups and assigning users to the groups

31. Open Windows Explorer and make sure that you can’t double-click the db
to open it.

Am I missing something?

Thanks so much!

--
Thanks!

Dee

What version of Access on your computer? On their computer? Has there ever been any other versions of Access on either computer?

As Keith says, you can secure it manually; read the security FAQ and you can follow the manual steps outlined on the 97/2000 page at my website. If you've studied the FAQ/whitepaper/etc, then the steps should make sense to you.

 

[Joan Wild]

I recall some issue about previous versions causing a problem (the details escape me at the moment and I don't have time to research).

More in line...

--
Joan Wild
Microsoft Access MVP

In answer to your question, I have indeed had previous versions of Access on
this computer. One of the other computers has as well, but the others are
brand new.

I have found that joining the MyWrkgroup.mdw, logging on as MyAdmin and then
creating a brand new db and importing all objects, then setting the
permissions works. I'm just not sure why.

So you're saying it's working now?

The in point steps I followed based on your instructions (I hopefully have
done so!):
1. Create NewDatabase with system.mdw and save
2. Close Access

Don't need to do 1 and 2.

3. Open Access but *not* NewDatabase
4. Create a new workgroup file, MyWorkgroup.mdw, and join it

I would create a new mdb and use Ctrl-G to verify that MyWorkgroup.mdw is actually being used at this point - it may still be using system.mdw
?dbEngine.systemdb

16. Tools, Security, User Level Security Wizard

I thought you weren't going to use the wizard?

 

[dee]

Hi Joan,

Sorry if I'm confusing the matter.

When I said :

1. Create NewDatabase with system.mdw and save

I simply meant that if a db hadn't been created, create one. (I am creating
test databases to try security)

Yes, I have it working - but only by following the steps indicating after
running the security wizard.

In answer to your question about not using the wizard, what I meant in this
post was to show you how I was initally trying to secure - before I tried the
method above to finally succeed. I guess I'm wondering if I should ever use
the Wizard at all if it seems hit and miss.

I will retry using Ctrl G to see if really joined the correct workgroup.

Thanks Joan.

 

[dee]

Hi again,

Joan, I followed your instructions to secure manually (using the Tools,
Security, Workgroup Administrator to create and join a new workgroup and also
Using Ctrl+G to double-check this). It worked like a charm.

The only drawback is that the Wizard allows me to include user names, PIDs
and passwords without having to log and as them to set the password.

Do you see anything wrong with running the wizard, then making sure I'm
joined to my workgroup and logged in as admin, then making a new db,
importing the objects and creating groups, assigning users to the groups and
making sure User group has no rights?

Thanks again!

 

[Joan Wild]

There is nothing wrong with running the wizard as you suggest, if that's what you want to do. Just test it thoroughly.

If it's just the setting of passwords that bothering you, you can leave them blank and then force the user to set one on initial opening.

 

[dee]

Thanks Joan, for all of your help.