Security and menu questions

[blindrhino]

A rather amateur question I’m sure, but I’m teaching myself here and I’m stumped. I’ve been looking through all the help files I can find but haven’t found the answer, so… I have setup the built-in Access 2003 security on my database, but it seems that with all the default menus people could still get into design mode and open any table they want, no matter their security privileges. So, I could just turn off all the menus and F11, but then how would I get back into design mode if I need to make changes? Am I doing something fundamentally wrong design wise here?

Really appreciate any input, thanks in advance!

 

[Bat17]

once you set up security then you can control who can open tables etc. If your users can still open tables directly then you need to go to Tools>Security>User and Group permissions..... and set the permissions for your users.

It is best to set permissions for groups and just add users to groups.

This faq is hard work but will give you what you need to know.
http://support.microsoft.com/default.aspx?scid=/support/access/content/secfaq.asp
Look at section 10 to see how to keep users out of you tables.

Peter

 

[Number Six]

Security?

Im with Blindrhino here - it seems to me that all you have to do is delete the "secured.mdw" file and you're into the database with full administrative priviledges. Or you can open Access, go to "File/Open", select the required database file and you're in again! After hours of setting users and groups and testing it seems they can all get in through the back door.

How do you make a database thoroughly secure?

 

[Bat17]

set up the security properly and you will not get in without using the right mdw file!
Having said that you will never make Access truley secure as users have read/write permissions for the drive it is in, so they can steal the whole DB and send it off to a professional to desecure for them if they want to spend the money

To be secure the Back End needs to be run by the server, something like MSSQL server or MySql.

HTh

Peter

 

[Number Six]

Thanks Peter, that's what I thought. I've created a simple database on a network drive, as you say you have to give the users sufficient permission to get into the database. It's quite frustrating after spending hours on the damn thing to be told that the security can be easily bypassed.

Regarding the mdw file its in the same folder on the network.

I was really annoyed after I'd set up a database on my C drive only to find all Access databases somehow "inherited" the same security - obviously the same mdw file. Very annoying to say the least.

 

[The_Doc_Man]

I was really annoyed after I'd set up a database on my C drive only to find all Access databases somehow "inherited" the same security - obviously the same mdw file.

C-drive has nothing to do with it. The currently active workgroup for Access is a registry setting. It applies to ALL Access databases until you join a new workgroup. Unless ....

There are threads here about command-line options to select the workgroup from the short-cut icon. Basically, you build a shortcut to your DB. Then you look at the icon's properties via right-click and select Properties.

Then in the "target" line you will see that Access is being launched. If you add a /wkgrp switch you can make the icon's target override the registry entry. Something like

".... {launch access } {database path} ..."

and between launching Access and the database path, you insert

/wkgrp "drive-letter:/torturous-path/umphty-oomph.mdw"

This is basically a spot-override of the currently active workgroup recorded in your registry. Look up "Command-line" in the Help files to see the complete range of tricks you can play by setting up the icon ahead of time to include some options.

it seems to me that all you have to do is delete the "secured.mdw" file and you're into the database with full administrative priviledges.

Number six is wrong to say this. The TRUTH is if you delete the secured.mdw, NOBODY gets into the DB again if it was correctly set up with workgroup security. You see, the DB is ENCRYPTED when you do that. A poorly secured DB uses the "default" MDW file that everyone has a copy of, so it isn't that the DB is public, it is that the DECRYPTION KEY is public. This is a fine - but significant - difference.

Having said that you will never make Access truley secure as users have read/write permissions for the drive it is in, so they can steal the whole DB and send it off to a professional to desecure for them if they want to spend the money

Bat17 is correct, but if you are in THAT kind of environment, you have some choices:

1. Involve your security managers in setting up object auditing on the MDB to see who does what to it. There IS a way to determine if someone COPIED the DB if object auditing and session auditing are both turned on. To do this, you need to have a security manager who knows about auditing and System Access Control Lists and who is willing to cooperate.

2. Convert the DB to an external server (SQL Server, ORACLE are two good choices but there are others) and use ODBC. That seriously "raises the ante" for someone who wants to steal your DB.

3. Revive corporal punishment for security violations. I understand the barbed whip works wonders as an attitude adjustment tool. But if you happen to work in a country where that kind of punishment is considered passe', maybe this wouldn't work. (In case you weren't sure, I kind of like the character "Argus Finch" in the Harry Potter series... even though he's only a squib, but I actually know a couple of spells.)

4. Screen your employees. Know your users. Know their usage patterns. Yeah, I know, sounds like .... WORK. It is. But anything worth doing (like securing a database) is worth doing well.

 

[ghudson]

I was really annoyed after I'd set up a database on my C drive only to find all Access databases somehow "inherited" the same security - obviously the same mdw file. Very annoying to say the least.

You have made the mistake of "joining" your computer to your .mdw file and/or worse you have altered your default System.mdw file. Rename [do not delete since you have modified it] your System.mdw then open up access. Your Computer will recreate the default System.mdw file. Never "join" a computer to a customized workgroup file. Use custom shortcuts as described above to open a secured db with the correct workgroup file.

 

[Number Six]

I'm always happy to stand corrected. Thanks.

 

[Number Six]

More question?

Here's another stupid question of mine - assuming a database has been secured (and I clearly have a lot to learn here) isn't it possible to simply create another database and import all the tables, queries and so on from the original. I'm sorry to be a pain in the arse but security on MS Access is something I do need to know more about.

Cheers.

 

[ghudson]

If it is properly secured then they will not be able to import the db objects from a secured db. They will get an error message stating that they do not have permission to that object. A properly secured is very difficult to crack but not impossible but they would have to pay somebody for a program to do it.

 

[The_Doc_Man]

Number six - "assuming a database has been secured (and I clearly have a lot to learn here) isn't it possible to simply create another database and import all the tables, queries and so on from the original."

Yes - if you had access to the original .MDB in the first place.

In order to open the original MDB, you would have to join its workgroup. Since there is one and ONLY ONE "current workgroup" setting in the registry, if the OTHER DB (the one receiving the data) used a different workgroup, you might have a hard time doing the transfer without going through an exported .CSV, comma-delimited or other delimited file as an intermediary. There might be a way to do this directly but I'll have to defer to someone who has actually tried this.

This is NOT a stupid question. It requires you to understand some really ugly points having to do with Access working behind the scenes with the Microsoft registry, which is NOT usually an area of expertise among Access DB folks.

If you need to know more about security, search this forum for "Security" as a topic.

You will find two or three levels of security. One of them is "Workgroup Security" but another (for shared environments) is "Permissions." BOTH of them apply - at different operational levels, of course - at the same time and BOTH are incredibly important. Do not feel bad about not understanding all of the ins and outs. Heck, I teach a security course for the U.S. Navy Reserve and I still am not always sure of all of the possible requirements.

An overview:

First security you touch: Your system's login process including machine and domain permissions, accounts, and defined group memberships.
Second: Shared-device security (for shared .MDB on a server, which requires rights to use the network in certain ways)
Third: Folder's security for the folder holding the .MDB, folders higher in the folder tree that contains the target folder - and for the .MDB itself. Not to mention the .MDW and .LDB files.
Fourth: Workgroup security for the .MDB file
Fifth: Any home-grown, extra security checks built into the DB by its author

Therefore, if you find yourself confused, don't worry. You're in good company.

 

[Number Six]

Thank you very much indeed. Being a complete novice on Access (it was banned at the last company I worked for as it was deemed to be insecure), I have much to learn.

What I am trying to do is create a simple database of customer complaints on a shared folder on the network, but I want to use Access security mainly to prevent users deleting data and modifying forms, queries and so on. I simply put my database in a shared folder, used the security wizard which created the "secured.mdw" file in the same folder as the database. It created a shortcut on my desktop exactly as The_Doc_Man described, users couldnt delete data on forms but I could.

You could imagine how deflated I felt when one person simply opened access, went to File/Open, trawled the network selected the database.mdb file and opened it completely bypassing the security. Records could be deleted I was not a happy person.

The "workgroup" concept is new to me as is the registry stuff, again thanks to eveyone who has contributed, I have had some well deserved stick at times - apologies if my frustrations spilled over into my postings but yes I'll do more reading.

What a fine bunch you are!!

 

[Bat17]

main thing to look at is removing permmission from the Admin user. when you use the std mdw file Access logs you in as Admin by default and gets any permissions you have left open to Admin.

Peter

 

[blindrhino]

Hey, as Number Six said, thanks a lot for all the help! And Number Six, thanks for asking all the right questions, I would have had to ask them had you not

Much appreciated!

G.

 

[Pat Hartman]

You could imagine how deflated I felt when one person simply opened access, went to File/Open, trawled the network selected the database.mdb file and opened it completely bypassing the security.

This issue is addressed in the Security FAQ. http://support.microsoft.com/default.aspx?scid=/support/access/content/secfaq.asp#_Toc493299666
I'm not sure why no one has directed you to it. The database has NOT been properly secured.

 

[Number Six]

Sorry to be a pain, is the workgroup setting in the registry H_KEY\CURRENT_USER\SOFTWARE\MICROSOFT\OFFICE\10.0\ACCESS\JET\4.0\ENGINES\SystemDB

This seems to show the path to the workgroup file, so does this explain The_Doc_Man's answer? If so I have another question - assuming a user has the right to access the PC's registry then if you delete this entry, open access and use the menu "File\Open", select the required "secured" database and you're in. Does this mean I have to stop clever users accessing the registry?

Or am I missing something else? This "workgroup" concept is giving me a headache.

Sorry but I'm working with people who will find their way round any sort of security so any help is very much appreciated.

 

[ghudson]

If your db is properly secured then the user will only be able to access [edit] the objects that you give them permissions to. You will have to disable the Shift key. If you have the developers edition then you could create a runtime package and that will also prevent them from getting inside the db. Search around for there are thousands of threads related to security. I would search for the keywords [security & system.mdw] and read and understand how to do it and how to avoid the common mistakes the beginners make.

 

[The_Doc_Man]

For your version, the path is, indeed,

HKEY_LOCAL_MACHINE\CURRENT_USER\...\OFFICE\...\JET\...\ENGINES\SystemDB

Some of the dotted lines have path elements that differ based on your individual install options and the Office versions installed on your machine.

I believe someone with some smarts could, indeed, replace the contents of this key using RegEdit or RegEdit32. However, that is too much work. They could do the same thing just by running the workgroup admin program and joining the new DB.

The catch is that when they attempt to open the DB, at that moment they STILL don't have an established SESSION (i.e. they haven't created an Access Workspace yet). And without a validated username and password, they won't be able to do so. Or if they do, it is empty and still has no permissions on anything. This is why you create your own groups and assign your VALID users to those groups. Let them get their permissions from YOUR groups, not the system default groups. Users group and the Admin user should have NO intrinsic permissions.

The selected .MDW file is a static, remembered selection. I.e. the registry is updated by a JOIN operation through WRKGADM or whatever that silly program is called. But the authenticated SESSION is a dynamic creation of Access itself. It does NOT exist in the registry (that I know of...) and is not automatically created just by joining a workgroup. So your concern

Does this mean I have to stop clever users accessing the registry?

is irrelevant to the original question. ANYONE can join a workgroup. Only someone with an account defined in the workgroup file can constructively USE the workgroup or the application protected by it.

On another subject, you really should stop your users from editing the registry anyway. But if you have to take action to accomplish that, you already are in a situation with less security than is recommended for a secured shop. I understand that for smaller shops, the staffing issue is significant. To be a full-time system administrator for several machines - and perhaps also be the domain admin - frequently overloads people. But then again, there ARE some policies that one can set within a domain's group policy objects that would put a quick stop to folks whacking their registry. If you don't know about GPOs but are the domain admin, it is time to do some research. A GPO can be your friend. (After you use one to limit your users, it might be your ONLY friend for a while until they get over it...)