Securing Your Data

Mike Wolfe -

You'll often read about how SQL Server offers better security than Access for storing your backend data.  But what does that actually mean?  How do you make an application more secure?

In the article below, I'll address four ways to secure your application's data.  If you're pressed for time, here's a list in order from least to most secure:

  • Application level (user interface) security
  • ACE (.accdb) back-end database security
  • SQL Server back-end database security
  • Client-server architecture (e.g., web apps)

Application Level Security

I define this as security logic built into the application's user interface.  

One common example is showing or hiding user interface elements based on the user that is using the application.  A public user might not see certain sensitive form fields, while an authenticated user might see read-only versions of those fields (Locked = True) and only an authenticated administrator has the ability to edit those fields.

Here's the thing about application level "security."  It's not actually secure.  Whether or not a user can modify data has nothing at all to do with any "security" that you build into the front-end of an Access application.

That's not to say you shouldn't have application level security.  You just need to treat it for what it is and what it is not.

Guard rails are the best analogy for application level security.  The purpose is not to keep attackers out of your data.  The proper role of application level security is to prevent users with good intentions from accidentally breaking things.

Database Level Security

If you want to keep attackers out of your data, the security must be applied at the data itself.

ACE back-end data security

If you are storing data in the .accdb file format, your security options are limited because it is file-based data storage.  You are stuck choosing user access at the file-level.  An individual user may have:

  • No access to any of the data in the .accdb
  • Read-only access to all of the data in the .accdb
  • Read-write access to all of the data in the .accdb

SQL Server back-end data security

With SQL Server, the security situation is much improved.  You can assign permissions at the table level.  In fact, with stored procedures, you can effectively set permissions at the individual field level.  

For the sake of your sanity and ongoing maintenance, you will want to assign such granular security to SQL Server roles.  You can then assign Windows security groups to one or more SQL Server roles.

If at all possible, I recommend you use Windows Authentication rather than SQL Server authentication.  Managing a second set of users and passwords is inefficient and prone to bad security practices (like storing username/password combinations directly in application source code).

Client Server Applications

If security is paramount, you need to prevent the user from having any direct access to the database itself.  Since Access provides the most development benefit when it can link directly to database tables, it's a poor choice for this sort of application architecture.  

In situations like these, a web application is usually a better choice.  In a web app, the user is interacting with the web browser only.  The web browser (client) is then communicating with a back-end application (server).  The back-end application (server) is the only part of the system communicating with the database.

Image by Cornell Frühauf from Pixabay