Why this matters
As a Data Analyst, you often need access to read data, create views, or share dashboards safely. Knowing how to GRANT and REVOKE permissions and use roles means you can request, verify, and even script least-privilege access for yourself and your team without risking production data.
- Control who can read sensitive tables (e.g., customers, transactions).
- Create shared roles (e.g., read-only, analyst, power-user) and assign them to users consistently.
- Confidently test what a user can and cannot do before a dashboard or model goes live.
Concept explained simply
Think of the database as a building:
- Users and roles are people and groups.
- Objects are rooms (databases, schemas, tables, views, sequences).
- Privileges are keys (SELECT, INSERT, UPDATE, DELETE, USAGE, EXECUTE, etc.).
Best practice: do not hand keys directly to each person. Instead, give keys to a role (a group), then add people to that role. When a person leaves or changes teams, remove them from the role instead of changing many object permissions.
Mental model
- Subjects: users and roles (roles can include other roles).
- Objects: database-level, schema-level, table/view-level, function-level.
- Privileges flow through role membership. REVOKE on a role affects all members.
- Default privileges control future objects, so new tables are automatically covered.
Cross-database notes (PostgreSQL vs MySQL)
- PostgreSQL: common privileges include USAGE on schemas, SELECT on tables, USAGE/SELECT on sequences; roles are first-class; default privileges can be set with ALTER DEFAULT PRIVILEGES.
- MySQL 8+: supports roles; you typically need to SET DEFAULT ROLE so the role is active for a user; SHOW GRANTS displays effective permissions.
Exact syntax varies by version. The patterns here are widely supported.
Worked examples
Example 1 (PostgreSQL): Read-only role on one table
-- As an admin or a privileged user
CREATE SCHEMA IF NOT EXISTS scratch;
CREATE TABLE IF NOT EXISTS scratch.customers_small (
id int PRIMARY KEY,
name text
);
INSERT INTO scratch.customers_small (id, name)
VALUES (1,'Ada'),(2,'Grace')
ON CONFLICT DO NOTHING;
-- Create a read-only role and a test user
CREATE ROLE r_ro_scratch NOLOGIN;
CREATE ROLE analyst1 LOGIN PASSWORD 'StrongPass1!';
-- Grant schema and table privileges to the role
GRANT USAGE ON SCHEMA scratch TO r_ro_scratch;
GRANT SELECT ON scratch.customers_small TO r_ro_scratch;
-- Give the role to the user
GRANT r_ro_scratch TO analyst1;
-- Test: connect as analyst1 and run
-- SELECT count(*) FROM scratch.customers_small; -- expect 2
-- INSERT INTO scratch.customers_small VALUES (3,'Lin'); -- expect permission errorExample 2 (PostgreSQL): Make future tables readable automatically
-- For an existing schema 'scratch':
GRANT USAGE ON SCHEMA scratch TO r_ro_scratch;
GRANT SELECT ON ALL TABLES IN SCHEMA scratch TO r_ro_scratch;
-- Ensure future tables in this schema are also covered
ALTER DEFAULT PRIVILEGES IN SCHEMA scratch
GRANT SELECT ON TABLES TO r_ro_scratch;
-- If you create a new table later, the role can read it without extra GRANT
CREATE TABLE scratch.new_items (id int, item text);
-- SELECT works for the role immediately.Example 3 (MySQL 8+): Role-based access
-- As an admin
CREATE DATABASE IF NOT EXISTS demo;
USE demo;
CREATE TABLE IF NOT EXISTS small_customers (
id INT PRIMARY KEY,
name VARCHAR(100)
);
INSERT IGNORE INTO small_customers VALUES (1,'Ada'),(2,'Grace');
CREATE ROLE IF NOT EXISTS r_ro_demo;
GRANT SELECT ON demo.small_customers TO r_ro_demo;
CREATE USER IF NOT EXISTS 'analyst2'@'localhost' IDENTIFIED BY 'StrongPass2!';
GRANT r_ro_demo TO 'analyst2'@'localhost';
SET DEFAULT ROLE r_ro_demo TO 'analyst2'@'localhost';
-- Test as analyst2:
-- SELECT COUNT(*) FROM demo.small_customers; -- expect 2
-- INSERT INTO demo.small_customers VALUES (3,'Lin'); -- expect permission errorExample 4: Revoking rights safely
-- Postgres illustration
REVOKE SELECT ON scratch.customers_small FROM r_ro_scratch;
-- All users who only had SELECT via r_ro_scratch will now lose read access.
-- MySQL illustration
REVOKE SELECT ON demo.small_customers FROM r_ro_demo;
-- Users relying on r_ro_demo now cannot read small_customers.Tip: Test with a non-admin account before and after REVOKE to confirm the effect.
Step-by-step playbook (safe and practical)
1) Plan least-privilege
List who needs what: read-only vs. write, which schemas/tables, and whether future tables should be included.
2) Create roles by use-case
Examples: r_ro_sales, r_ro_marketing, r_power_user. Avoid granting directly to individuals.
3) Grant at the right level
- Schema access (USAGE in Postgres).
- Table-level SELECT/INSERT/UPDATE/DELETE as needed.
- Default privileges for future tables if required.
4) Assign users to roles
Postgres: GRANT role TO user. MySQL: GRANT role TO user + SET DEFAULT ROLE.
5) Test with a non-admin account
Attempt the intended query (should succeed) and an unauthorized action (should fail).
6) Audit and document
Record which roles exist, what they grant, and who is a member. Keep it simple and review quarterly.
Exercises
Do these in a safe sandbox. Mirror of the exercises below. The Quick Test at the bottom is available to everyone; only logged-in users get saved progress.
Exercise 1 — PostgreSQL: Create a read-only role and test it
- Create schema scratch and table customers_small with 2 rows.
- Create role r_ro_scratch and user analyst1.
- Grant USAGE on schema and SELECT on table to the role. Grant the role to analyst1.
- Connect as analyst1, run SELECT COUNT(*). Then try an INSERT (expect failure).
Self-check checklist:
- [ ] SELECT COUNT(*) returns 2 for analyst1.
- [ ] INSERT fails with a permission error for analyst1.
- [ ] As admin, REVOKE SELECT from role causes analyst1 SELECT to fail afterwards.
Exercise 2 — MySQL 8+: Role activation and revoke
- Create database demo and table small_customers with 2 rows.
- Create role r_ro_demo and grant SELECT on the table.
- Create user analyst2; GRANT role and SET DEFAULT ROLE to activate.
- Verify analyst2 can SELECT but cannot INSERT. Then REVOKE SELECT from the role; confirm SELECT fails.
Self-check checklist:
- [ ] SELECT COUNT(*) returns 2 for analyst2.
- [ ] INSERT fails with a permission error for analyst2.
- [ ] After REVOKE, SELECT fails for analyst2 with a permission error.
Common mistakes and how to self-check
- Granting directly to users instead of roles. Fix: centralize in roles; assign users to roles.
- Forgetting schema-level USAGE (Postgres). Fix: grant USAGE on schema and SELECT on tables.
- New tables not covered. Fix: use ALTER DEFAULT PRIVILEGES (Postgres) or processes to apply grants after DDL.
- MySQL role not active. Fix: SET DEFAULT ROLE.
- Overbroad grants (ALL PRIVILEGES). Fix: grant only what is needed; test both allowed and disallowed actions.
- Not testing with a real non-admin account. Fix: always validate effective permissions.
Quick self-audit procedure
- List membership: who has which roles.
- List object grants: what each role can do.
- Try one allowed query and one forbidden action per role.
- Check future coverage: default privileges or automation exists.
Practical projects
- Role catalog: design r_ro_{schema} roles for each schema you use; script GRANTs and member assignments.
- Access hardening: pick a schema, remove direct user grants, migrate to role-based access, and verify with tests.
- Default-privileges rollout (Postgres): ensure future tables in analytics schemas are readable by your read-only roles.
Learning path
- Before this: SELECT basics, schemas/tables, user vs role concepts.
- Now: GRANT, REVOKE, roles, default privileges.
- Next: views and security considerations, auditing permissions, row-level security (if supported).
Who this is for
Data Analysts who need safe, reliable read access, reproducible sharing via views, and minimal friction when collaborating with engineers and BI.
Prerequisites
- Basic SQL (SELECT, INSERT), understanding of schemas and tables.
- Ability to connect with an admin account in a sandbox environment.
Next steps
- Convert one of your projects to role-based access today.
- Document your team’s role matrix and review quarterly.
- Take the Quick Test below to lock in your understanding.
Mini challenge
Design statements to: (1) create a read-only role for schema analytics covering current and future tables; (2) create a user data_viewer and grant the role; (3) verify the user cannot INSERT into analytics.fact_sales. Write the SQL you would run and the test queries you would use.