Users & Privileges (GRANT/REVOKE)

A database user is not the same thing as a user of your application. Your app might have millions of end users stored as rows in a users table, all connecting through one shared application account — but the database itself also has its own, much smaller set of accounts: the credentials your application server, your reporting tool, and your DBA actually log in with. These database-level accounts are what GRANT and REVOKE control.

Creating a database user

A new login for the reporting service

SQL

CREATE USER reporting_service WITH PASSWORD 'use-a-real-secret-here';

A freshly created user starts with essentially no privileges — it can connect, but it cannot read or write any table until you explicitly grant it permission to do so.

Common privilege types

Privilege	Allows
SELECT	Reading rows from a table or view.
INSERT	Adding new rows to a table.
UPDATE	Modifying existing rows in a table.
DELETE	Removing rows from a table.
CREATE	Creating new objects, such as tables, in a schema or database.
DROP	Deleting an existing object such as a table.
ALL PRIVILEGES	Every privilege available on the object — a broad grant, best reserved for admin accounts.

GRANT — giving privileges

Read-only access to two tables

SQL

GRANT SELECT ON orders TO reporting_service;
GRANT SELECT ON customers TO reporting_service;

Full read/write access for an application account

SQL

GRANT SELECT, INSERT, UPDATE, DELETE ON orders TO app_service;

REVOKE — taking privileges away

Removing a privilege that is no longer needed

SQL

REVOKE DELETE ON orders FROM app_service;

REVOKE is the exact mirror image of GRANT — it takes the same object and privilege names and simply removes the permission instead of adding it. It does not delete the user, only the specific capability.

Privileges can be granted on many kinds of objects

The examples above grant privileges on individual tables, but the same syntax works on views, sequences, schemas, and whole databases. Granting at the schema or database level applies more broadly and should be used sparingly — it is much easier to reason about access when it is scoped to exactly the tables a user needs.

The principle of least privilege

Every database account — human or application — should be given the smallest set of privileges it needs to do its job, and nothing more. A reporting tool that only ever runs SELECT queries should never also be able to DELETE rows; if its credentials ever leak, the damage it can do is limited to what it was actually granted. Reviewing and trimming unused privileges periodically is a normal part of database hygiene, not a one-time setup step.