Skip to content
Tutorials Center
My learning

Topics

Lesson 31 of 36 ยท Administration and Security

Access Security and Trusted Locations

Secure a Microsoft Access database. Learn the Trust Center and trusted locations, database encryption with a password, ACCDE files, hiding objects, SQL injection and user-level design.

Access security is different from server database security, and understanding the difference prevents false confidence. An Access file is a file: whoever can read the file can in principle read the data. The strong protections are therefore who can reach the file (folder and share permissions), encryption of the file, and keeping real security on a server when the data is sensitive. Everything inside Access (hidden objects, a login form) is a convenience layer, not a lock. This lesson covers each layer honestly.

The Trust Center and trusted locations

When you open a database that contains macros or VBA, Access shows a yellow Security Warning bar with Enable Content. This is the Trust Center protecting you from malicious code: macros and VBA do nothing until you trust the file. Settings live in File > Options > Trust Center > Trust Center Settings.

  • Macro Settings: the default, Disable all macros with notification, is the right choice for most users. The more permissive options are for developers only.
  • Trusted Locations: folders you declare safe. A database opened from one runs its code without the warning bar. Add the folder where your application lives (local drive or a UNC path to a server share) and use Allow Trusted Locations on my network only if the share is well controlled. A trusted folder anyone can write to is an open door.
  • Trusted Publishers: files signed with a digital certificate you trust.
  • Message Bar and Add-ins settings control what else loads.

Best practice for a business application: put the front end in a trusted location deployed by IT policy, or sign the project (VBE: Tools > Digital Signature) with a certificate so users trust the publisher. Users should never be told to lower macro security globally, because that exposes them to every other file they open. Also note that macros in sandbox mode only run a safe subset of actions, while VBA, RunSQL with some constructs and automation need trust.

Database password and encryption

File > Info > Encrypt with Password (open the database in Exclusive mode first: File > Open > Open Exclusive) encrypts the whole file with the Access database engine's encryption, using the password you choose. Without the password the file cannot be opened or read in a text editor. This is the right protection for a database on a laptop or a USB drive. Points to remember:

  • Use a long passphrase. A forgotten password cannot be recovered; keep it in a password manager and keep an unencrypted backup in a safe place.
  • Encryption protects the file at rest. Once a user has the password, they can do anything.
  • To remove it, open exclusively and choose Decrypt Database.
  • For a split database the back end holds the data, so encrypt the back end. The front end then connects with the password stored in its linked tables' connection string, which means someone who gets the front end can find it. Hence the folder permissions matter too.

ACCDE: protect your code

Create an ACCDE file (File > Save As > Make ACCDE) from your front end. It is compiled: forms, reports and modules can be run but not opened in Design view, and VBA source is removed. Users cannot change your forms or read your code, and the file is smaller and slightly faster. Always keep the original .accdb as your source, because you cannot convert an ACCDE back. Distribute the ACCDE; edit and rebuild from the ACCDB.

Hiding things and start-up options

Under Current Database options you can set a start-up form, hide the Navigation Pane, disable the ribbon and shortcut menus, and turn off Allow Full Menus and Allow Default Shortcut Menus. Hide the Shift bypass with the AllowBypassKey property (set it from VBA with CurrentDb.Properties). Locking the user interface keeps honest people from breaking things, but it is not security: a knowledgeable person can still open the file with a different tool or create a new database that links to the back end. If data must be truly restricted, it needs a server.

User-level access: what replaced it

Older .mdb files had user-level security with workgroup files. It is gone in the .accdb format. Modern options:

  1. Application-level roles: a tblUsers table with a role column (Admin, Clerk, ReadOnly) and code that reads the Windows login (Environ("USERNAME")) and shows or hides forms and buttons. Convenient but bypassable for a determined user.
  2. Folder permissions: give read-only rights to the folder holding reports, and read-write to the data folder only for those who edit.
  3. SQL Server back end with Windows authentication and per-table permissions: the real answer when roles matter (see upsizing). Users can open the front end, but the server refuses what their login may not do.

A role table in SQL form, with the query a start-up routine would run to decide what a user may do:

SQL Stand-in: application roles in a table

Press Run (or Ctrl + Enter) to see the result here.

SQL injection and safe VBA

If your code builds SQL by pasting user text, a user can change the meaning of the statement. Example: "SELECT * FROM Users WHERE Name = '" & txtName & "'" with ' OR '1'='1 typed in returns every user. Defend with parameter queries and validated input (see events and DoCmd). The danger is visible even in a toy query:

SQL Why pasted text is dangerous

Press Run (or Ctrl + Enter) to see the result here.

A security checklist

  • Folder and share permissions are the first and strongest layer.
  • Encrypt back ends that hold sensitive data; protect the password.
  • Deploy the front end as an ACCDE in a trusted location or signed.
  • Never store passwords in tables; if you must authenticate yourself, store salted hashes, or better, use Windows login.
  • Use parameter queries, validate input and handle errors without leaking details.
  • For regulated or high-risk data, move the data to a server.
  • Keep Office updated; many "Access vulnerabilities" are macro-based malware in files from outside.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does a trusted location do?

    Files opened from a trusted location are trusted, so code runs without Enable Content.
  2. 2. What does an ACCDE file protect?

    An ACCDE is compiled and design-locked; encryption is a separate feature.
  3. 3. Why is hiding the Navigation Pane not real security?

    Interface restrictions are convenience. Real protection comes from permissions, encryption or a server.
  4. 4. The best defense against SQL injection in VBA is:

    Parameters keep user input as data rather than SQL text.

Summary

  • Access security is file security: control access to the file first, encrypt it with a password, and use a server for sensitive or role-based data.
  • The Trust Center, trusted locations and signed code manage macro risk; never lower security globally.
  • Distribute an ACCDE; keep the ACCDB source safely.
  • UI restrictions and role tables help honest users but are not locks; prevent SQL injection with parameters.

Next: Split databases and multi-user Access