Skip to content
Tutorials Center
My learning

Topics

Lesson 98 of 125 ยท Database Systems

Users, Roles and Permissions (DCL)

Learn how databases control access with users, roles, GRANT and REVOKE, how to apply least privilege, and how row-level security works in PostgreSQL and SQL Server.

A database server is shared by many people and programs: the web app, background workers, a reporting tool, developers, maybe an outside analytics service. They shouldn't all have the same powers. The web app needs to insert orders but should never drop tables; an analyst needs to read sales but shouldn't see password hashes. Data Control Language (DCL) is the part of SQL that decides who may do what: GRANT gives permissions and REVOKE takes them away.

SQLite has no users at all (it's a library that reads a file, so access is controlled by who can open that file), which means the server examples in this lesson are read-only. You'll still run several SQLite demos that show the underlying ideas: column- and row-level restrictions with views, a permission model you can query, and a connection that refuses to write.

Authentication and authorization

Two separate questions are involved every time someone connects:

  • Authentication: who are you? The server checks a password, a certificate, a cloud identity token or an operating-system account.
  • Authorization: what may you do? Once the server knows who you are, it checks your privileges on each object (database, schema, table, column, function) before every statement.

A privilege is a pair of an action and an object, such as SELECT on orders or EXECUTE on transfer. The common table privileges are SELECT, INSERT, UPDATE, DELETE, plus TRUNCATE, REFERENCES (create foreign keys pointing at the table) and TRIGGER in some systems. Schema-level privileges control whether you can see objects in a schema (USAGE in PostgreSQL) and create new ones (CREATE).

Users and roles

Granting privileges to each person one by one quickly becomes unmanageable. Instead, you group privileges into roles (reporting, app_writer, support) and give roles to users. When someone changes jobs, you change their role memberships, not dozens of individual grants.

In PostgreSQL, users and roles are the same kind of object: a "user" is just a role that is allowed to log in. Here's a typical setup for the shop database:

SQL
-- PostgreSQL
-- Group roles: they hold privileges but can't log in
CREATE ROLE shop_reader NOLOGIN;
CREATE ROLE shop_writer NOLOGIN;

GRANT CONNECT ON DATABASE shop TO shop_reader;
GRANT USAGE ON SCHEMA public TO shop_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO shop_reader;

-- Writers can do everything readers can, plus change orders
GRANT shop_reader TO shop_writer;
GRANT INSERT, UPDATE ON orders, order_items TO shop_writer;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO shop_writer;

-- Login roles for real users and services
CREATE ROLE web_app LOGIN PASSWORD 'use-a-long-random-secret';
GRANT shop_writer TO web_app;

CREATE ROLE dana LOGIN PASSWORD 'use-a-long-random-secret';
GRANT shop_reader TO dana;

GRANT ... ON ALL TABLES only covers tables that exist right now. For tables created later, set default privileges (they apply to objects created by the role that runs the command, or by the role named in FOR ROLE):

SQL
-- PostgreSQL: future tables in "public" are readable by shop_reader
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO shop_reader;

PostgreSQL also has useful predefined roles: pg_read_all_data and pg_write_all_data (PostgreSQL 14+) for broad access, pg_monitor for monitoring tools, and pg_maintain (PostgreSQL 17+) for running VACUUM, ANALYZE and REINDEX without owning tables. And since PostgreSQL 15, ordinary users can no longer create tables in the public schema by default, a change made specifically to tighten security.

GRANT and REVOKE

The general shape is the same in every database:

SQL
GRANT privilege [, ...] ON object TO role [WITH GRANT OPTION];
REVOKE privilege [, ...] ON object FROM role;

WITH GRANT OPTION lets the receiver pass the privilege on to others; use it rarely. Some practical examples:

SQL
-- PostgreSQL
REVOKE UPDATE ON orders FROM shop_writer;   -- writers can no longer update orders
GRANT EXECUTE ON PROCEDURE transfer(integer, integer, numeric) TO web_app;

-- Column privileges: support staff see contact details but not emails
GRANT SELECT (id, first_name, last_name, city, country) ON customers TO support;

-- Who has what?
SELECT grantee, table_name, privilege_type
FROM information_schema.table_privileges
WHERE table_schema = 'public'
ORDER BY grantee, table_name;

In psql, the shortcuts \du (list roles) and \dp orders (show a table's privileges) are faster. Every system also has a special pseudo-role, PUBLIC, meaning "everyone". Granting to PUBLIC is almost always a mistake.

Here is the core idea modeled as data in SQLite. A user's effective privileges are the union of the grants on every role they belong to:

SQL Effective privileges through roles

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

Dana can only SELECT from orders and products, while web_app can also INSERT and UPDATE orders. Change a row in role_members and watch the effective privileges change, exactly as they would after GRANT shop_writer TO dana;.

MySQL accounts and roles

MySQL identifies an account by user and host together: 'web_app'@'10.0.%' and 'web_app'@'localhost' are two different accounts, possibly with different passwords and privileges. Roles exist since MySQL 8.0:

SQL
-- MySQL 8.4 / 9.x
CREATE USER 'web_app'@'10.0.%' IDENTIFIED BY 'use-a-long-random-secret';

CREATE ROLE 'shop_reader', 'shop_writer';
GRANT SELECT ON shop.* TO 'shop_reader';
GRANT INSERT, UPDATE ON shop.orders TO 'shop_writer';
GRANT INSERT, UPDATE ON shop.order_items TO 'shop_writer';

GRANT 'shop_reader', 'shop_writer' TO 'web_app'@'10.0.%';
SET DEFAULT ROLE ALL TO 'web_app'@'10.0.%';   -- roles are inactive until set

SHOW GRANTS FOR 'web_app'@'10.0.%';
REVOKE UPDATE ON shop.orders FROM 'shop_writer';

Two MySQL details cause most surprises. Granted roles are not active in a session until they are set as default roles (or activated with SET ROLE), so a freshly granted role can look like it "doesn't work". And shop.* means every table in the shop database, while *.* means everything on the server, which an application should never have. The default authentication plugin is caching_sha2_password; the old mysql_native_password is disabled by default in 8.4 and removed in 9.0.

SQL Server logins, users and DENY

SQL Server separates the two questions physically. A login lives at the server level and handles authentication; a user lives inside one database and holds permissions there:

SQL
-- SQL Server
CREATE LOGIN web_app WITH PASSWORD = 'Use-a-long-random-secret-1';

USE shop;
CREATE USER web_app FOR LOGIN web_app;

CREATE ROLE shop_writer;
GRANT SELECT ON SCHEMA::dbo TO shop_writer;
GRANT INSERT, UPDATE ON dbo.orders TO shop_writer;
ALTER ROLE shop_writer ADD MEMBER web_app;

-- DENY beats GRANT, even one that comes from another role
DENY SELECT ON dbo.customers (email) TO shop_writer;

SQL Server has a third statement, DENY, which explicitly forbids an action and overrides any GRANT the user gets through other roles. Built-in database roles such as db_datareader and db_datawriter cover common cases, and db_owner can do anything in the database, so keep it away from applications. Oracle works similarly with CREATE USER, GRANT CREATE SESSION (permission to log in at all) and object grants; Oracle 23ai adds schema-level privileges like GRANT SELECT ANY TABLE ON SCHEMA hr TO analyst;.

Column-level restrictions with views

Column privileges are precise but tedious. A common alternative is a view that exposes only safe columns, with privileges granted on the view instead of the table. Users of the view never see the hidden columns:

SQL A view that hides columns ยท database: shop

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

The result shows Tara Fischer from Osaka, without her email address. Views are covered fully in Views.

Row-level security

Sometimes the question isn't which columns but which rows: a customer may see only their own orders, a sales rep only their region, a tenant in a SaaS app only their company's data. Row-level security (RLS) attaches a filter to the table itself, so every query is filtered automatically, even a careless SELECT * FROM orders.

SQL
-- PostgreSQL
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY customer_sees_own_orders ON orders
  FOR SELECT
  TO web_app
  USING (customer_id = current_setting('app.customer_id')::integer);

-- The app sets the current customer at the start of each transaction
BEGIN;
SELECT set_config('app.customer_id', '5', true);  -- true = local to this transaction
SELECT id, status FROM orders;                    -- only customer 5's orders
COMMIT;

Once RLS is enabled, a role with no matching policy sees no rows. Superusers, roles with BYPASSRLS and (unless you add FORCE ROW LEVEL SECURITY) the table's owner skip the policies, which is one more reason the app shouldn't connect as the owner. SQL Server offers the same idea through a security policy with a predicate function, and Oracle through Virtual Private Database (VPD). MySQL has no built-in RLS; filtered views are the usual workaround.

In SQLite you can imitate the effect with a view that filters on a "current user" value stored in a table:

SQL Row-level filtering with a view ยท database: shop

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

Only customer 5's seven orders appear. Change the value in app_session to 19 and run again: customer 19's orders appear instead. The real feature is stronger, of course, because a view only protects you if users can't reach the table behind it.

Least privilege in practice

The principle of least privilege says every account should have exactly the permissions it needs, and no more. If the web app is ever compromised, through SQL injection or a leaked password, the damage is limited to what that account can do. A solid setup for most applications:

Account Used by Privileges
Owner / migrations Deploy pipeline only Owns the schema; CREATE, ALTER, DROP
App runtime The web app and workers SELECT, INSERT, UPDATE (and DELETE only where needed) on its tables
Read-only reporting BI tools, analysts SELECT, ideally on views or a replica
Personal admin accounts Database administrators Broad rights, used rarely, audited

A few habits go with it:

  • Never let applications connect as a superuser (postgres, root, sa). An injection bug as superuser can read every database, and sometimes run operating-system commands.
  • One account per service and per person, so audit logs say who did what, and you can revoke one without breaking the others.
  • Keep secrets out of code. Store passwords in environment variables or a secrets manager, rotate them, and prefer short-lived cloud credentials where your platform supports them.
  • Limit the network. Bind the server to a private network, restrict allowed hosts (pg_hba.conf in PostgreSQL, the host part of MySQL accounts) and require TLS.
  • Review grants regularly, and remove accounts when people leave.

SQLite: the file is the permission

SQLite has no GRANT, because there is no server to enforce it: whoever can read the database file can read every row. Access control happens at other layers:

  • File-system permissions decide who can open the file at all.
  • Read-only connections: the command-line shell's sqlite3 -readonly shop.db, or the URI file:shop.db?mode=ro in drivers that accept URIs.
  • Authorizer callbacks: the C API sqlite3_set_authorizer() (Python: conn.set_authorizer(...)) lets the app approve or deny each table and column access when statements are prepared.
  • PRAGMA query_only makes the current connection refuse all writes:
SQL A connection that refuses writes ยท database: shop

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

The count (26) appears, and then the run stops with Error: attempt to write a readonly database. That's handy for reporting connections and admin consoles where a stray UPDATE would be a disaster.

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 authenticates the connection (password, certificate, token), then authorizes each statement against the user's privileges.
  2. 2. In the role demo, dana belongs to shop_reader. Which statement would let her insert orders too?

    Making dana a member of shop_writer gives her that role's INSERT and UPDATE privileges on orders.
  3. 3. In MySQL, you granted a role to a user, but the user still gets "access denied". What is the likely cause?

    MySQL roles are inactive in a session until they're set as default roles or activated with SET ROLE.
  4. 4. Why shouldn't a web application connect as the database superuser?

    Least privilege limits the damage of a compromise. The app account should only have the DML privileges it needs.
  5. 5. After PRAGMA query_only = ON; in SQLite, what happens to INSERT INTO categories (name) VALUES ('Garden');?

    query_only makes the connection reject every statement that would change the database.

Summary

  • DCL controls access: GRANT gives privileges, REVOKE removes them; SQL Server adds DENY, which overrides grants.
  • Authentication proves identity; authorization checks privileges on objects for every statement.
  • Grant privileges to roles, then grant roles to users. In PostgreSQL a user is a role with LOGIN; in MySQL an account is user + host, and roles must be activated.
  • Use ALTER DEFAULT PRIVILEGES (PostgreSQL) for tables created later, and column privileges or views to hide sensitive columns.
  • Row-level security filters rows per user automatically (PostgreSQL policies, SQL Server security policies, Oracle VPD).
  • Follow least privilege: separate owner, app, reporting and admin accounts; never run apps as superuser.
  • SQLite has no users: protect the file, open read-only connections, use authorizers or PRAGMA query_only.

Next: SQL Injection and Secure Queries, where you'll see how attackers abuse string-built queries and how parameters stop them.