Access Security... is this a joke?

[rubbernilly]

Perhaps I am not seeing something here.

I have gone through an Access tutorial (specifically Access 2000), but the security seems only as good as people deciding to go along with the parameters and behaving.

I created a new system.mdw file.
I defined users and groups, and setup permissions.
I removed the Admin user from the Admin group.
I set the Admin password to be other-than-null.
I changed ownership of database objects with the Sec. Wizard.

Everything works great... on that one PC. What I was hoping for was a finished-product, secure-database that could be ported to another PC and work out of the box.

Do I have to tell every PC of every user that will use the database that they need to play by these rules (ie, this workgroup file) before that will work?

I don't want to touch every PC to configure them this way, nor am I comfortable that a user need only go back to creating a wide-open system.mdw workgroup file that allows them to have full access to the database in order to get into areas they should not.

What is preventing a user from either (1) changing the local settings back to the installation defaults (wide-open access), or (2) going to a new PC, one unconfigured with the system.mdw file, in order to get that same wide-open access?

 

[BNPMike]

Can you explain what you're doing? If you just run Access on one PC then why would you bother about security? Surely you need to put it on a server if you want to share it?

 

[rubbernilly]

Thanks for the response, Mike...

Yes, the database is on a network drive. The system.mdw is in the same directory as the DB.

From one PC, I went in to set up the security. I went to the other PC to test it.

From the first PC, PC-1, I created users and groups. I managed permissions so that the groups could only do the sorts of things that I wanted them to do. I changed ownership of all database objects to my username. I set a password for the Admin account and I removed the Admin account from the Admins group. I also removed all permissions for the Admin user. All these changes are saved in the MDW file on the server.

When I go to the second PC, PC-2, I open access and open the database. Because this instance of Access is not connected to that MDW workgroup file, the Admin user has no password and is a member of the Admins group, and I can get into any database object I want to.

So, after searching on this board, I have yet to find a suitable answer, but I did find other situations that would concern me:

If I have to touch every PC to supply the proper connection information for this database (through supplying shortcut with the MDW file defined)...

1) what is to stop someone from just opening the database from the root drive without the shortcut (and thus without the connection MDW file information?

2) what is to stop them from creating their own MDW file that is open with the security?

3) if the whole pc is setup as permanently connected to the mdw file (and not just for the purposes of opening this particular database) then what is to stop someone from going to another PC that is no setup with that restriction in order to get to the database without restriction?

4) if the whole pc is setup as permanently connected ot the mdw file (as in 3, above), what happens when the IT group changes the PC and I am unaware that the user now has unlimited access to the database?


I guess I am just not getting how the MDW file secures the database... it seems like using the MDW file is voluntary.

 

[JoeAtWork]

I don't blame you for being perplexed by Access security. Every time I have to set it up I find myself rereading the Security chapter in my Developer's Handbook to get back up to speed. I'll try my best to address your questions as best I can.

1) what is to stop someone from just opening the database from the root drive without the shortcut (and thus without the connection MDW file information?

If you removed all the default permissions from both the built-in Admin account, and the Users group, then somebody just opening the database directly shouldn't be able to get into the objects (in fact, if you removed permissions to the database itself they shouldn't even be able to open it).
When somebody opens a database directly, they are still using System.mdw file, but it is the default one installed on their machine, and they will automatically be logged in as Admin. Also, the Admin account is part of the Users group, so just taking the Admin out of the Admins group is not enough, because the Users group has a lot of default permissions.

Another thing you might want to do is before you create your database, you could log in to Access with your custom MDW, and not as Admin (let's say you use an account called SuperAdmin). Then when you create the database, the owner of the database will be SuperAdmin, not the generic Admin that is available in the default System.mdw.


2) what is to stop them from creating their own MDW file that is open with the security?
If you remove all permissions to the Admin account, remove Admin from the Admins group, and remove all permissions for the Users group - then somebody creating their own System.mdw shouldn't be a work around.
When you create new accounts, you have to give them a unique PID (identity number). So if someone happens to create a System.mdw with another SuperAdmin account, that wouldn't be sufficient if they did not know what PID you gave to your SuperAdmin.


3) if the whole pc is setup as permanently connected to the mdw file (and not just for the purposes of opening this particular database) then what is to stop someone from going to another PC that is no setup with that restriction in order to get to the database without restriction?
Again, if you removed the default permissions to Admin and the Users group, they shouldn't be able to open the protected objects directly. If you removed permissions from the database itself, they should get an "Insufficient permission" error when they try to open it.


4) if the whole pc is setup as permanently connected ot the mdw file (as in 3, above), what happens when the IT group changes the PC and I am unaware that the user now has unlimited access to the database?
As above, if permissions were set up correctly, your MDB file must be opened with the appropriate MDW. If IT didn't distribute the MDW with the MDB and set up the shortcuts correctly, your users should be yelling that they can't get into the database.

All that being said, it should be recognized that even a properly set up security file in Access is notoriously easy to hack. Here's an interesting little debate on it Access's security merits:

http://www.thescripts.com/forum/threadnav190971-1-10.html

I see Access security only useful for keeping people who don't have malicious intentions from accidentally doing damage.

 

[rubbernilly]

Thanks for the response, Joe... but I'm still not clear on this - mainly because what I am seeing in my test environment here and what you are describing don't seem to match up.

I took your suggestion and verified that the Users group has no permissions. I also verified that the Admin user is only in the User's group, and has no explicitly granted rights.

In fact, on PC-1, the PC where I configured this security after creating and joining the MDW file on the network, I try to open the database as Admin and I cannot do it, so I know that this part is working.

You are telling me that this should limit anyone at any PC who is using the default MDW file, but I do not see that. I go to my PC-2, which is still connected to the default MDW workgroup file, and try to open the database. I open as the default Admin user (since I have not configured a password in the default MDW file), and the database opens up with no problem.

What am I missing?

 

[AlexCuse]

I'm not sure how much security you need, but I have read something around about comparing the user's windows login ID to a list of users you store in your DB. I don't really need to use security in any of my access applications (and thank goodness!), but this idea intrigued me. Of course, I forgot to add it to my favorites.

If I am able to track it down I will let you know, and if anyone else has this handy I would love to see it again.

Good LUck!

Alex

Ignorance of certain subjects is a great part of wisdom

 

[JoeAtWork]

rubbernilly - Admin still must have some rights to that database, either directly or through a group it is in. Also, if you created the database while you were logged in as Admin, that would make the Admin the owner of the database. As the owner, I believe that trumps all other security settings.

Last time I did this (about a month ago) I was having similar problems. I kept thinking I had removed all permissions but kept finding I could still get in.

My final approach was to first create an MDW file, create a new AppAdmin account, add it to the Admins group, then take Admin out of the Admins group. Then I removed all rights to from the Users group (make sure to remove all rights to <New> objects as well, such as <New Forms>). Then I created my custom users and groups.

While still logged in as AppAdmin, I created my database so that AppAdmin would be the owner. I found this process gave me the results I wanted.

 

[AlexCuse]

Hey, here is the link to the thread where someone mentioned using windows security.

thread181-1334156

It was just a few below this thread, thats what I get for posting as I leave work on friday ;-)

Good Luck,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[KornGeek]

rubbernilly,

I have set up Access security several times, and the advice JoeAtWork is giving you is absolutely correct. If setup correctly, Access security will keep people from being able to open the database unless they are using your mdw.

Make sure you remove the open database permission from the admin user and from the users group. All users are members of the users group, so any permissions it has apply to everybody. The admin user is the default user. Any permissions it has can easily be accessed through a different mdw.

A final note: Access security is like locking a sliding glass door to your house. It will keep out people who might accidentally wander in, it will remind curious people of the boundaries, and it will block those who aren't highly motivated. However, anyone who is sufficiently motivated can easily break in. If you are going to be storing sensitive data, this might be a problem. There are tools freely available or for a low cost that can bypass any level of Access security.

 

[rubbernilly]

Thank you all for your feed back. Let me address a couple of points.

Alex - I am being brought in on this project after-the-fact, and only for the purpose of solving a couple of problems. I do not have the developmental access to the database to fully implement windows-based security in this case. I appreciate the link and the information. I will keep that handy for the next time I have to secure a database I develop.

KornGeek - I understand that Access security is weak and quickly broken by determined hackers. The level of security that I am looking for has more to do with making sure the right people do the right things in the database than with making sure the wrong people stay out of the database.

Now... about the actual setup of this security. I am trying to understand something I am seeing in my test environment, and what people are telling me I should see, I do not see. My problem might be solved if someone can answer this question:

On my PC-1, I have configured security. When I log in as the Admin user, I CANNOT open the database (I get the message alerting me that I have insufficient rights). This would make me believe that I have thoroughly removed the rights for the Admin user... at least enough to prevent the Admin user from opening the database. Right? What else could I configure in the security that would make the Admin user MORE locked down? What is unconfigured so that on PC-2 I can still open the database?

I will check the database again in the morning and repost what I find as far as the security configuration, but this is what I have done so far:

Created a NewAdmins Group
Created a NewAdmin User, put in the NewAdmins Group
Set the password for the Admin user
Used the Security Wizard to change ownership of all database objects and of the database to the NewAdmin user
Removed the Admin user from the Admins group
Removed all rights of the Admins Group
Added those rights to the NewAdmins Group
Removed all rights from the Admin user for all objects, including new objects and including the database itself, too

 

[JoeAtWork]

I don't see on your list removing all permissions of the Users group. Admin is in the Users group.

You should set up your own custom AppUsers group for your regular users.

 

[rubbernilly]

Yes, I forgot to include that step in my rundown of things I had done, but I did remember to include that step when I secured the test database.

There are NO rights explicitly granted to the Users group.

I used the security tutorial I found here:

http://www.databasedev.co.uk/access_security.html

...modified so that I created my own groups and user names.

I have the test MDB and MDW if someone would like to look at them. Just a junk database with two tables, two queries, and two forms... enough to test the security. My test environment is 2 PCs... the same Novell user is logged into both. The MDB exists in the user's network home directory. On PC-1, Access is setup to use a MDW file that I created and configured the users; this file is also in the home directory of the Novell user. On PC-2, Access is still defaulted to the installed System.MDW that comes with Access.

 

[KornGeek]

Here are the basic steps:

1) Create a new security mdw.
2) Create a new MyProfile (or whatever) profile in this mdw.
3) Create a new MyUsers (or whatever) group in this mdw.
4) Create a new MyAdmins (or whatever group in this mdw.
5) Make MyProfile a part of MyUsers and MyAdmins.
6) Set a password on the admin user in this mdw.
7) Launch Access and login using MyProfile.
8) Create a new database and import everything from your old database. This will make MyProfile the owner for everything.
9) Remove all permissions from the admin user in the mdw (including the ability to open the database).
10) Remove all permissions from the users group in the mdw (including the ability to open the database).
11) Grant basic permissions to the MyUsers group.
12) Grand advanced permissions to the MyAdmins group.

Because the owner is no longer the admin user, the admin user has no rights, and the users group has no rights, users using different mdw's should no longer be able to open the database.

(Disclaimer: This was done from memory, and it's possible I skipped a step or two. To those who have implemented security before, please correct me if I left anything out.)

 

[rubbernilly]

OK, the only thing different from what you described that I did was to use the Security Wizard to change the ownerships.

If the Admin user cannot open the database AT ALL from the MDW file I have configured, why can he open it from the other?

If he can't open it from the MDW file I configured, doesn't that mean that the rights are not there? (And I have also visually confirmed that they are not there - none for him, none for the Users group).

He cannot open it from one MDW, why can he open it from the other?