Solved

How do i secure Access databases across versions?

Posted on 2011-02-24
9
321 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
  • 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
 
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now