My approach to security in Access is the following:
Encrypt the main database using a complex password.
Use a second database to log in to this encrypted database. The second database stores usernames, user salts, and an encrypted version of the main database password with the user password. This way, no user needs to have the main password.
Then, compile the second database, so it’s harder to modify the database and print the main encryption key.
Weaknesses:
- Revoking access to a certain user only works if he doesn’t have a
backup of the login database (or you have to change the encryption key on
the main database, forcing you to recreate accounts for every user). - The encryption I’m using is RC4 (implementation in VB by wqw found as an answer here), which isn’t that strong
- A tech savvy user that has a valid password could decompile the database and use it to acquire the main database password
- If you’re not signing your databases and enforcing all databases to be signed, someone might modify or replace it to weaken security
To go through all the details is too much, so here is my implementation.
File metadata: size: 672 KB, SHA1 hash: 19A6C756B8D5B0CDCEBE505B289062A1BBD94DEC
Quick manual: on first run it prompts main database password, location, first user name and password. After that, you can just use the forms to do anything you want.
Note that it’s earlier work, and I haven’t deeply thought about SQL injection (I use doublequote escaping and am ashamed for it). However, this isn’t a security risk for the main database, it only opens up a possibility for a destructive hack (and someone with write access to the database file can probably destroy it anyway).
It’s not compiled and menus aren’t hidden, so you can easily inspect and modify it. When implementing it, hide all menus and compile it.
The database, as all SO content, is licensed CC BY SA 3.0