Access 2007 security with MDB file

[sparky961]

Hi all...

I've been doing some research on securing various parts of a new database application, but have yet to come to any conclusions. Another post on this site referred to this link:

http://www.geocities.com/jacksonmacd/AJMAccessSecurity.pdf

... which seems to be well written and informative (not to mention a bit of dry humour), but I'm a bit wary of taking the information at face value for Access 2007 since the author "only recently started using 2002, and have never even seen Access 2003".

Now, the database I'm working with is in the MDB format rather than the ACCDB format (I'm assuming that the file's extension is a good indicator of the format here). At this point, I can't use the new version because the data is exported from a program that doesn't yet support the new format.

Can anyone let me know if this document is still relevant to what I'm working with? Any thoughts on what I'm going to have to deal with once the data export program can deal with the ACCDB format, thus prompting a conversion of the database?

Thanks much!
-Sparky

 

[sparky961]

I've been doing a lot of reading today on this, and it looks like Micro$oft has really screwed this one up.

Correct me if I'm wrong, but it looks like my best option at this point is to stay with the MDB file format and user level permissions.

As a side note to anyone looking for similar information, you can still access the workgroup manager from Access 2007 by using the "immediate" window in the VBA code editor.

See this article from M$...
http://support.microsoft.com/kb/888734

-Sparky

 

[sparky961]

It seems to take a rather long time to go through and create users, groups, select the permissions, import to another database.... etc.

For starters, is all of this really necessary in order to secure a database?

Now that I've stuffed all of the security holes with duct tape and chewing gum, I've found that accessBooksRT, the program I'm using to export data from QuickBooks into Access, doesn't seem to like the new security that I've applied to the database. It doesn't have an option for a workgroup file and/or username/password.

I've posted a question in the accessBooksRT forum to look at the problem from that angle, but until then can anyone give me an easy way to revert a database back to being insecure while still giving the option to use the secure version in the future?

Thanks,
-Sparky

 

[Insane_ai]

I've posted a question in the accessBooksRT forum to look at the problem from that angle, but until then can anyone give me an easy way to revert a database back to being insecure while still giving the option to use the secure version in the future?

Thanks,
-Sparky

If you intend on upgrading the database to Access 2007, don't bother setting up the internal security. The User level security feature is no longer available in Access 2007 accdb format even though Access 2007 will allow you to edit the settings of a database in earlier formats. I suggest you use NTFS permissions on the file level or Sharepoint to secure the database.


If my suggestion above is not acceptable to you:
Copy the objects from your original databse to a new database without user and group security configured or search Access Help for "Work with User and Group Security" for links that provide the instructions you need.



My Source:
Microsoft Access 2007 Inside Out, Microsoft Press, Viescas, Conrad
ISBN 978-0-7356-2325-5 Page 45. ¶ 2
RE: The Database Tools Tab:

“Administer. Access Displays this group on the Database Tools Tab only when you open an Access database file created in Access 2000, 2002, or 2003 (.mdb). The Users and Permissions command lets you edit and define users and objects permissions in the legacy security system no longer supported in Access 2007 format database files.”

 

[sparky961]

Thanks for your feedback on this.

Until I get things sorted out, I've basically done what you suggest - to create a new insecure database and copy everything into it.

In my own "new to Access" way, I'm glad that they've dropped support for the JET user level security. It's extremely convoluted to get things set up properly... if you can call it that.

I'll consider using NTFS permissions. That might be a good way to go without making things overly complicated. One thing I'm also looking into is to use a MySQL back-end with the Access front-end.

Any other thoughts or suggestions would be appreciated.

-Sparky

 

[thatlemhome]

I agree. MS opted out of user level security in 2007 instead relying on server level security. I have some experience of what can go wrong in converting 2003 to 2007, so let me pass that on....

You should be able to run 2003 in Access 2007 without converting it, once the security is switched. 2007 has a mode to allow for it. However, if you want to take full advantage of 2007, you can use the conversion function to convert the 2003 to 2007.

In short, because of the issue above, you need to convert your user level security back to Administrative Security. So you'll have to strip out everyone's security, groups, etc. I'd play with a copy first. Once you get it back the basic form, and it works ok, then try to convert from 2003 to 2007 using the conversion tool. If things go hinky, chances are the user security is the issue.

I had several db lock up due to this issue. Fortunately, I had a system with 2003 still on it, so I could make the changes prior to the conversion. In every case of problems between switching from 2003 to 2007, it was user level security that was the issue.

Now I just opt out for system passwords. It's low brow but not nearly the headaches.

Good luck!!