Microsoft Access vs. SQLite: Security Comparison

MS Access (in its database role) is identical to SQLite when it comes to security.  

Both are file-based database systems, which means that access to their data is all or nothing.  Here are the user permission options for SQLite and MS Access database files:

• Read/Write ALL DATA
• Read ALL DATA
• Read/Write NOTHING

You can't grant users table-level security access in either system.

You can't use stored procedures to restrict users to only pre-approved operations.

It is simply not possible to meaningfully secure a file-based database, such as Access or SQLite.

That Can't Be True! SQLite is Used in Web Apps

When SQLite is used as a web application back-end, there is only a single "user" accessing the file: the web application process.

The actual users–the humans that are logging in to the website–are querying and updating the database via the web application.  They DO NOT have direct access to the SQLite database file itself.  Additionally, the server-side code is completely unavailable to the web users.

This is the key difference between Access and SQLite.

Why Can't We Secure Access as a Backend?

When Access is used as a backend database, the users accessing it tend to be on the same network as the data file.

In other words, most users DO have direct access to the MS Access database file itself.  And access to that file provides them with the proverbial keys to the kingdom.  Remember, if they can read or write to ANY table in the back-end database, then they are also allowed to read and write to EVERY table in the back-end database.  

The only way to achieve security with a file-based database is to prevent users from having direct access to the database file.  Unfortunately, this is not practical for an Access application.

The Secure Solution: SQL Server

The best way to improve the security situation for an Access application is to migrate its backend data into SQL Server.

SQL Server allows extremely robust security features, including:

• Role-Based Access Control
• Table-Level Permissions
• SELECT vs. UPDATE vs. INSERT vs. DELETE Permissions
• Truly custom permissions via Stored Procedures
• Integration with Windows Authentication
• And much more

For this and many other reasons (performance, scalability, etc.), we have exclusively used SQL Server as our backend data storage solution for the past several years.

You May Only Need Guardrails

Microsoft Access is an ideal solution for small businesses and departments within larger businesses.

If you can count on one or two hands the number of people who need ANY access to the backend database file, then you may not NEED the level of security that you can achieve with other solutions (e.g., a web application or SQL Server backend).

Instead, it may be enough to build guardrails in your Access front-end to help guide benevolent users and prevent them from hurting themselves accidentally.

Conclusion

SQLite and Microsoft Access are identical when it comes to security.

Both are file-based databases that rely on the underlying operating system to provide security.  Security is all or nothing.  The key to securing both is to prevent bad actors from gaining direct access to the database file itself.  

With SQLite, this is typically done via a web application.

With MS Access, this is typically done by avoiding insider threats.  

If insider threats are a concern, migrate your backend data to SQL Server.

Cover image created with Microsoft Designer