Solved

How do i secure Access databases across versions?

Posted on 2011-02-24
9
325 Views
Last Modified: 2012-05-11
I have 2 new MS Access databases that I created using Access 2010.

1.  I need to make 1 of them available to users who may be using versions anywhere from 2000 to 2010.  They need to be able to view the information and manipulate it, as in searches and filters and sorting, but not be able to add, delete. or modify any information.  I have to be able to add, edit and modify.

2.  The 2nd database needs the same restrictions as the first but should only be viewable by a select group of people.

Any ideas on how I could secure these databases?  Do I need to limit the Access versions that people will be able to use?

Thanks in advance!
0
Comment
Question by:fabi2004
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 9

Accepted Solution

by:
meko72 earned 84 total points
ID: 34972137
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 166 total points
ID: 34972368
If you must support users running Access 2000 forward, you must be VERY careful to NOT use any new features that have been implemented since Access 2000 - assuming, that is, that you wish to maintain only 1 "version" of the application. If you plan on issuing different databases for each version of Access, then of course you can provide specific enhancments to each of those databases based on the new features that were introduced in each version.

Needless to say, this would be incredibly difficult to maintain, since ANY enhancements you made would have to be applied to all databases. That would be a maintenance nightmare.

IMO, you would be far better served if you required your users to run a specific version of Access (perhaps 2007 or 2010). This would significantly decrease the maintenance issues you'll face. That said, you may not be able to do this, and if so then you've got to plan carefully and take great care to NOT use any of those new features (like Attachment datatypes, multivalued fields, the Printer object, builting PDF printing, etc etc).

As to security - you could deploy that to a specific folder that ONLY that group can access. Using Windows permissions, you could tightly control who could even get to the file. Of course, a member of THAT group could always grab a copy and plonk it down somewhere else, where others could get to it. But that's an employee training issue, not a software one.
0
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 250 total points
ID: 34972379
I hope that the new databases you have created are mdb files and not Accdb files.
If they are Accdb files no-one except for A2010 users will be able to use them.

This is a complex issue so I think before going any further we should just get your confirmation on the above point.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:fabi2004
ID: 34972482
I created accdb files.  It sounds like I need to make sure my users are running Access 2007 minimum because I did use some of those new features.

The reason I don't think Windows/AD security will work for me is because I have to limit what the people who access the database can do with it (i.e. not change any data), so folder permissions aren't going to do the trick.
0
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 250 total points
ID: 34977810
There is a fundamental principle in development which is that you must develop the application using the lowest (oldest) version of the program that your users will be using.
This is because, generally speaking, versions are forward compatible, but not backwards compatible.
I would take your current Access app and try to run it on any A2007 machine and see if it even opens on that machine.

As for the security issue, this ia now a big problem for developers, because basically the features that remain in Access will only keep out honest people.   If data security is important and not just a nice-to-have then Access is not really equipped any more to offer this and you should really be using a server database as the backend.

If you want to identify application users by a username that is not their Windows/network name then you will have to build your own login mechanism.  And whichever way you identify the user, if you want different users to see/do  different things you will have to build this into your application yourself.  You do need to consider Activedirectory settings to limit who can get to the folders containing the database files.

Access 2010 gives you the ability to hide many things, such as the navigation pane and standard  menus/ribbons but anything which is user dependent you will have to build.
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 166 total points
ID: 34978623
Yes, it would seem that you need 2007 at a minimum. Users running 2003 or prior would not be able to use your app.

I agree 100% with Peter regarding the security aspects of 2007. Earlier versions had User Level Security, and while it wasn't perfect it was a lot beter than nothing (which is what we have now).

Peter's Software (no affiliation with our own peter57r) has a product that might help: http://www.peterssoftware.com/las.htm (Light Application Security). It is, basically, a login system that allows you to direct users to items they can use. It is certainly no replacement for true security, but might suit your needs. Of course you can build your own as well.

Note too that SQL Server Express has a very robust security mechanism, and SSE works very well with Access. However, SSE is "data only", so you'd still need to manage your user-type security on your own (i.e. if UserA can open Form1 but NOT Form2, you'd have to build that logic into the system).
0
 
LVL 1

Author Comment

by:fabi2004
ID: 34980390
hmmm, I didn't think it would be this complicated.  I thought that I could limit user's access to the database via AD/folder permissions.  But I need to make the data "read only" so they can look at it and manipulate it but not change it.  For some reason, I thought Access had features that allowed that.  I was thinking along the lines of form permissions/settings?  Am I completely off base?

I have MS SQL Server 2000 that I could store the data in, but I'd still need a front-end for it.  
0
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 250 total points
ID: 34981556
Access mdb files have user level security. It is not available in accdb files.
0
 
LVL 1

Author Comment

by:fabi2004
ID: 34981608
Alright, I'm going to close this question and open an new one leaving out the "versions" issue.

Thank you all.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question