Skip to content
Tutorials Center
My learning

Topics

Lesson 71 of 79 ยท Security and Administration

Authentication, Authorization and Access Control

Learn users, roles, GRANT and REVOKE, least privilege, row and column security with views, and auditing, plus what SQLite cannot do and how to compensate.

A database often holds the most valuable data a company owns, so it must answer two questions before it does anything for a client. First: who are you? That is authentication. Second: what are you allowed to do? That is authorization, and the rules that decide it are called access control. Every serious DBMS has both. Getting them right means a bug in one application, or one stolen password, does not expose everything.

This lesson covers users and roles, the GRANT and REVOKE statements, the principle of least privilege, how views give you row-level and column-level protection, and how auditing records what happened. A server database such as PostgreSQL or MySQL has a built-in user system. SQLite has none: it is a library that opens a file, and whoever can read the file can read everything in it. So the account-management statements below are shown read-only, while the view and audit techniques run live. The next lesson, Injection, Encryption and Data Protection, continues with attacks and encryption.

Three questions

Authentication Who are you?
Authorization What may you do?
Auditing What did you do?

Security needs all three, in this order.

1 / 2

Authentication: proving who you are

Authentication happens when a client connects. Common methods are a password (stored by the server as a salted hash, never as plain text), a client certificate, an operating-system or directory identity (Kerberos, LDAP, Active Directory), and cloud identity tokens. Good practice is to use strong, unique credentials for every application, rotate them, and prefer short-lived tokens or certificates over a long-lived shared password. Also limit where connections may come from: most servers can restrict each account to certain hosts, and the database port should never be open to the whole internet.

An important distinction: your web application's own users (people who log in to your site) are usually not database users. The application connects with one or a few database accounts and enforces its own user rules. This makes the database account of the application the most powerful credential in the system, so it must be treated as such.

Authorization: users, roles and privileges

Once the server knows the account, it checks privileges: permission to perform an action (SELECT, INSERT, UPDATE, DELETE, CREATE, REFERENCES, EXECUTE...) on an object (a database, table, column, view or routine). Privileges are collected into roles, so that you manage a handful of roles instead of hundreds of users. A user receives roles; a role can contain other roles.

SQL
-- Read-only: PostgreSQL syntax (SQLite has no users, roles or GRANT)
CREATE ROLE analyst NOLOGIN;
CREATE ROLE app_writer NOLOGIN;

GRANT SELECT ON orders, customers TO analyst;
GRANT SELECT, INSERT, UPDATE ON orders TO app_writer;
GRANT SELECT (id, name, city) ON customers TO app_writer;   -- column-level

CREATE ROLE dana LOGIN PASSWORD 'use-a-secret-manager';
GRANT analyst TO dana;                  -- dana inherits the analyst role

REVOKE UPDATE ON orders FROM app_writer;   -- take a privilege back

Some details matter in practice:

  • A deny by default policy applies: an account has no access to an object until a privilege is granted.
  • GRANT ... WITH GRANT OPTION lets the receiver pass the privilege on. Use it rarely; privileges that spread are hard to track and revoking one can leave the others behind or cascade.
  • REVOKE removes a privilege. In the standard, you can say RESTRICT (fail if others depend on it) or CASCADE (also remove what was granted onward).
  • The owner of an object usually has all privileges on it, and a superuser account bypasses checks entirely. Applications must never connect as the owner or a superuser.
  • Privileges can also be granted on routines (EXECUTE on a stored procedure), which lets an application do one controlled operation without access to the underlying tables.

Least privilege

The principle of least privilege says that every account gets the smallest set of privileges that lets it do its job, and no more. A reporting tool needs SELECT only. A web application needs SELECT, INSERT, UPDATE on its tables, not DROP or CREATE USER. A migration tool gets schema privileges, but only while the migration runs, and it uses a separate account from the application. The payoff is damage control: if the web application account is stolen or its code is exploited with SQL injection, the attacker is bounded by that account's privileges instead of owning the database.

Views: row-level and column-level protection

Privileges alone are coarse: in the standard model you grant access to a table or, in some systems, to specific columns. A view (see Nested Queries, Views and Materialization) lets you publish exactly the slice you want and grant access only to the view. A view can:

  • hide columns, by simply not selecting them (salary, national id, password hash);
  • hide rows, with a WHERE clause (only the caller's department, only active records);
  • mask values, by showing derived or partial data (the last 4 digits of a card);
  • aggregate, so a role sees averages and counts but never individual records.

The following example builds an employee table and three views for three audiences. SQLite cannot restrict a user to a view, because it has no users, so the example demonstrates the query design. On a server database you would GRANT SELECT on the views and give nothing on the base table.

SQL Column, row and aggregate views

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

Each audience sees a different shape of the same data, and the last view hides individuals completely. Two warnings apply. First, a view limits access only if users cannot reach the base table, so the grants must match. Second, some clever queries can leak hidden data through side effects (error messages, cost, functions with side effects) on certain systems. For sensitive data, native row-level security policies are stronger.

SQL
-- Read-only: PostgreSQL row-level security (not available in SQLite)
ALTER TABLE employee ENABLE ROW LEVEL SECURITY;

CREATE POLICY own_department ON employee
  FOR SELECT
  USING (dept = current_setting('app.current_dept'));

A role model you can run

You can still model application-level authorization in any database with ordinary tables: users, roles, permissions and the links between them. This is the role-based access control (RBAC) pattern that many applications implement themselves (the database accounts stay few; the application's users live in tables). The check is then a query.

SQL RBAC tables and a permission check

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

Only fay (admin) may delete invoices, and dana, who holds two roles, gets the union of both roles' permissions. The application must run this check on the server side for every request. Hiding a button in the user interface is not authorization.

Auditing: recording what happened

Auditing records who did what and when, so that misuse can be detected and investigated, and so that compliance rules can be met. Server databases offer built-in audit features or extensions (for example pgaudit for PostgreSQL), and they log logins, failed attempts, privilege changes and data access. Within SQLite, you can still keep a change history with triggers. The log table should be append-only and, in a real system, writable by a different account than the one an attacker would control.

SQL An audit trail with triggers

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

The log contains three entries: two balance updates and a deletion, with the values before and after. Notice what is missing: who made the change. SQLite does not know the caller, so a real audit trail would add a user column filled from the server's session information.

What SQLite lacks, honestly

SQLite has no accounts, no GRANT, no roles, no row-level security and no network port. Its security model is the file system: whoever can open the database file has full access. For an embedded database that is reasonable, because the application and the data live together. You protect it with operating-system file permissions, an encrypted file (via extensions such as SQLCipher), and application-level checks like the RBAC tables above. If several programs or people need different rights over shared data, that is a signal to use a server database.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What is the difference between authentication and authorization?

    The server first checks identity (authentication), then checks privileges for each action (authorization).
  2. 2. What does the principle of least privilege recommend for a web application's database account?

    Limiting privileges limits the damage if the account or the application is compromised.
  3. 3. Which of these can a view do?

    A view can publish a filtered, projected or masked slice of a table. It protects only if users have rights on the view and not on the base table.
  4. 4. Which statement takes a previously granted privilege away?

    REVOKE removes a privilege from a user or role. GRANT gives it.
  5. 5. Why is hiding a delete button in the interface not enough to protect the data?

    Anyone can craft a request without your interface. Authorization has to be enforced on the server, in the application and the database.

Summary

  • Authentication proves identity; authorization decides what an identity may do; auditing records what it did.
  • Privileges (SELECT, INSERT, ...) on objects are collected into roles; GRANT gives and REVOKE takes away. Access is denied by default.
  • Least privilege: give each account the smallest set of rights it needs, and never let applications connect as owner or superuser.
  • Views provide column-level, row-level, masked and aggregate access; native row-level security is stronger on servers that have it.
  • RBAC can be modelled with ordinary tables and a permission query, enforced on the server side.
  • Audit triggers record changes; real audits also record the user. SQLite has no accounts, so protect the file and check access in the application.

Next: Injection, Encryption and Data Protection.