Part 2 of 5 · 1 min read
GRANT, REVOKE and roles
Controlling access through named roles, instead of one permission per person.
Think of a company issuing keycards by job title. That beats cutting a brand-new, individually configured key for every single employee who joins.
In plain words: GRANT gives a role or user permission to perform an action on a database object. REVOKE removes it. A role is a named set of permissions that can be assigned to many users at once, so a permission change for "everyone on the reporting team" only needs to happen in one place, the role itself.
A worked example. Giving a reporting role read-only access to orders, then assigning that role to a new analyst.
The key lines.
CREATE ROLE reporting_role;
GRANT SELECT ON orders TO reporting_role;
GRANT reporting_role TO new_analyst;
REVOKE SELECT ON orders FROM reporting_role;
Role, role-based access control
A role is a named set of permissions assignable to many users at once. Role-based access control manages permissions by assigning and revoking roles, rather than configuring each user's permissions individually.
Example: adding a new analyst to reporting_role immediately gives them exactly the permissions every other member of that role already has, with no separate configuration needed.