How to give each person their own permissions in a small database

Two very different kinds of protection

Permissions in a database server are enforced by the server: whatever program a person uses, the server refuses what their account may not do. Permissions in a database file are enforced by the program that opens it, and only while that program is the one opening it. Both are useful, but only the first holds against someone who wants in.

Microsoft Access

Older Access databases (.mdb) had user-level security, with users and groups kept in a separate workgroup file. The .accdb format that Access has used since 2007 dropped it. What remains:

• Encrypt with Password, which encrypts the whole file under one password. Everyone who has the password sees everything.
• A split database: the tables in one file on a shared folder, the forms and reports in a copy on each computer. Windows folder permissions decide who can open the tables file at all, but not what they see inside it.
• Forms that hide things, which keep honest people on the right path and stop nobody else.

For real per-person rights, Microsoft’s own advice is to move the tables to a server, such as SQL Server, and link to them.

In a database server

PostgreSQL and MySQL both give each person an account and grant rights table by table:

• In PostgreSQL, make a role for each kind of person and grant it what it needs, such as GRANT SELECT ON products TO counter_staff;, then make each person a member of their role. Row-level security goes further: a policy such as “only rows this person added” is checked by the server on every query.
• In MySQL and MariaDB, CREATE USER and GRANT do the same per table, and per column too. There is no row-level security built in; a view showing only the right rows, granted instead of the table, does the job.

A plan that fits a small business

• An administrator, one or two people, who may change anything, including the design.
• A manager who sees and changes everything but the design and the staff records.
• Staff who add sales and change their own, and read the products.
• Read only for an accountant or an auditor.

Start from nothing and grant what each role needs, rather than starting from everything and taking things away. Before relying on a role, sign in as it, or view the database as it, and try what it should not be able to do.

Keep a record of who changed what

Permissions stop mistakes before they happen; a record of who added, changed and deleted each record finds the ones that happen anyway. A server can keep one with triggers or its logs. Whatever you use, make sure the record cannot be edited by the people it records.