?
Solved

How do i secure Access databases across versions?

Posted on 2011-02-24
9
Medium Priority
?
328 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 336 total points
ID: 34972137
0
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 664 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 1000 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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 1000 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 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 664 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 1000 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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
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…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

765 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