Permissions & Roles Interview Questions & Answers

GRANT gives a database user or role permission to perform an operation. REVOKE removes a previously granted permission.

-- Grant SELECT on one table
GRANT SELECT ON orders TO analyst_user;

-- Grant multiple privileges at once
GRANT SELECT, INSERT, UPDATE ON products TO app_user;

-- Grant on all tables in a schema (Postgres)
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_role;

-- Grant to a role (not a user directly)
GRANT SELECT ON customers TO reporting_role;

-- Revoke a specific privilege
REVOKE INSERT ON products FROM app_user;

-- Revoke all privileges on a table
REVOKE ALL PRIVILEGES ON orders FROM analyst_user;

Rule of thumb: always grant to roles, not individual users. Assign users to roles. This makes permission management scalable — add a new employee by assigning them to the correct role, not by running a dozen GRANT statements.

Least privilege means giving each user or service account only the minimum database permissions needed to perform its function — no more.

-- Application database user: only needs to read/write its own tables
CREATE ROLE app_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON orders    TO app_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON customers TO app_role;
GRANT SELECT ON products TO app_role;  -- read-only on the product catalogue
-- NOT granted: DROP TABLE, CREATE TABLE, TRUNCATE, ALTER

-- Reporting user: read-only access
CREATE ROLE reporting_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting_role;

-- Migrations user: only runs during deploys
CREATE ROLE migration_role;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO migration_role;
GRANT CREATE ON SCHEMA public TO migration_role;

Rule of thumb: the application's runtime database user should never have DROP TABLE, TRUNCATE, or ALTER TABLE privileges. Use a separate migration user for schema changes, and revoke it after deploys.

A role is a named collection of privileges. A user is a role that can log in. In Postgres, users and roles are unified — CREATE USER is syntactic sugar for CREATE ROLE … LOGIN.

-- Postgres: create roles
CREATE ROLE readonly_role;
CREATE ROLE readwrite_role;
CREATE ROLE admin_role;

-- Grant privileges to roles
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO readwrite_role;

-- Create users (roles that can log in)
CREATE ROLE alice LOGIN PASSWORD 'secret';
CREATE ROLE bob   LOGIN PASSWORD 'secret';

-- Assign users to roles
GRANT readonly_role  TO alice;
GRANT readwrite_role TO bob;

-- Alice now inherits all privileges of readonly_role

SQL Server uses CREATE LOGIN (server-level) + CREATE USER (database-level)

• CREATE ROLE as separate concepts.

Rule of thumb: define a small set of roles that match your access patterns (read-only, read-write, admin, migration). Add users to roles rather than granting privileges to individual users — it is far easier to audit and maintain.

WITH GRANT OPTION allows the grantee to re-grant the same privilege to other users or roles.

-- Alice can SELECT on orders AND can grant that to others
GRANT SELECT ON orders TO alice WITH GRANT OPTION;

-- Alice can now do:
GRANT SELECT ON orders TO bob;  -- valid because alice has GRANT OPTION

-- Revoke cascades to anyone alice granted to
REVOKE SELECT ON orders FROM alice CASCADE;
-- Bob loses SELECT too, because it came from alice

WITH GRANT OPTION creates a chain of trust that is hard to audit — Bob's access depends on Alice's access, and revoking Alice's access removes Bob's.

Rule of thumb: avoid WITH GRANT OPTION in most cases — it makes permission chains hard to audit and revocations unpredictable. Only use it for schema owners or DBA roles that are explicitly responsible for managing access.

Row-Level Security (RLS) enforces access policies at the table level — the database automatically filters rows based on the current user, hiding rows that the policy says the user cannot see.

-- Enable RLS on the table
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- Policy: users can only see their own orders
CREATE POLICY own_orders ON orders
  FOR ALL
  TO app_role
  USING (customer_id = current_setting('app.current_user_id')::INT);

-- Application sets the config before every query
SET app.current_user_id = '42';
SELECT * FROM orders;  -- automatically filtered to customer_id = 42

-- Bypass RLS (table owner and superuser bypass by default)
ALTER TABLE orders FORCE ROW LEVEL SECURITY;  -- forces even for table owner

Rule of thumb: use RLS for multi-tenant applications where every table query must be tenant-scoped. It enforces the isolation at the database level — a bug in application code cannot accidentally expose another tenant's data.

In Postgres, a user must have USAGE on a schema before they can access any objects within it, even if they have SELECT on individual tables.

-- Step 1: grant schema access
GRANT USAGE ON SCHEMA reporting TO analyst_role;

-- Step 2: grant table access within the schema
GRANT SELECT ON ALL TABLES IN SCHEMA reporting TO analyst_role;

-- Ensure future tables are also covered (Postgres)
ALTER DEFAULT PRIVILEGES IN SCHEMA reporting
  GRANT SELECT ON TABLES TO analyst_role;

-- Deny schema access entirely
REVOKE USAGE ON SCHEMA private_data FROM analyst_role;
-- Now analyst_role cannot see any tables in private_data schema

Rule of thumb: use separate schemas (app, reporting, audit, staging) and grant schema USAGE per role. This lets you grant broad access to an entire schema with two GRANT statements instead of one per table.

Yes — SQL supports column-level SELECT, INSERT, and UPDATE grants. This lets you expose some columns of a sensitive table while hiding others (e.g., show names but not salaries).

-- Grant SELECT on specific columns only
GRANT SELECT (id, name, department) ON employees TO hr_report_role;
-- hr_report_role can NOT read salary or ssn columns

-- Grant UPDATE on specific columns
GRANT UPDATE (email, phone) ON users TO support_role;
-- support_role can update contact info but not password_hash

-- Revoke column-level grant
REVOKE SELECT (salary) ON employees FROM payroll_role;

Column-level grants work in Postgres, SQL Server, and MySQL but are complex to manage. An alternative is to create a view that exposes only the allowed columns and grant SELECT on the view instead.

Rule of thumb: prefer a view over column-level grants for hiding sensitive columns — views are easier to discover, test, and document. Use column-level grants when a view is not practical (e.g., you need write permissions on specific columns).

A superuser (Postgres) or sysadmin (SQL Server) bypasses all permission checks and can do anything in the database — create/drop databases, bypass RLS, read any table, impersonate other users.

-- Check if current user is a superuser (Postgres)
SELECT current_user, usesuper FROM pg_user WHERE usename = current_user;

-- Create a non-superuser admin for routine work
CREATE ROLE dba_role CREATEDB CREATEROLE;  -- can manage DBs and roles, not superuser

-- Application connection string should NEVER use a superuser
-- BAD:  postgresql://postgres:password@host/db
-- GOOD: postgresql://app_user:password@host/db (limited privileges)

Rule of thumb: the application's database connection string must never use a superuser account. Use superuser credentials only for database administration tasks, run from a secured bastion host or local machine — never from application servers.

Audit logging records which user performed which action and when. Common approaches:

1. Application-level: record the actor and action in an audit table from application code.
2. Trigger-based: a database trigger automatically writes to an audit table on every INSERT/UPDATE/DELETE.
3. Extension/feature: pgaudit (Postgres), SQL Server Audit, MySQL General Log.

-- Postgres: trigger-based audit log
CREATE TABLE audit_log (
  id         BIGSERIAL PRIMARY KEY,
  table_name TEXT NOT NULL,
  operation  TEXT NOT NULL,  -- INSERT / UPDATE / DELETE
  old_data   JSONB,
  new_data   JSONB,
  changed_by TEXT NOT NULL DEFAULT current_user,
  changed_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE OR REPLACE FUNCTION audit_trigger() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO audit_log (table_name, operation, old_data, new_data)
  VALUES (TG_TABLE_NAME, TG_OP,
          CASE WHEN TG_OP = 'DELETE' THEN row_to_json(OLD)::jsonb END,
          CASE WHEN TG_OP <> 'DELETE' THEN row_to_json(NEW)::jsonb END);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER orders_audit
  AFTER INSERT OR UPDATE OR DELETE ON orders
  FOR EACH ROW EXECUTE FUNCTION audit_trigger();

Rule of thumb: trigger-based auditing is comprehensive but adds write latency. For compliance-grade auditing, use a dedicated extension (pgaudit) or the database's native audit feature — they capture more events (reads, DDL) and cannot be bypassed by application code that circumvents triggers.

In Postgres, every user has CREATE and USAGE on the public schema by default (before Postgres 15). Any user can create tables there, potentially shadowing system functions or other users' objects via the search path.

-- Check who has what on the public schema
SELECT grantee, privilege_type
FROM   information_schema.role_schema_grants
WHERE  schema_name = 'public';

-- Revoke CREATE from all non-superusers (Postgres < 15)
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

-- Postgres 15+: CREATE is revoked from PUBLIC by default
-- but USAGE is still granted — revoke if needed
REVOKE USAGE ON SCHEMA public FROM PUBLIC;

-- Application code should use an explicit schema, not rely on search_path
SET search_path = app, public;
-- Or set it per user:
ALTER ROLE app_user SET search_path = app;

Rule of thumb: on a shared database, immediately revoke CREATE ON SCHEMA public FROM PUBLIC (Postgres < 15) and put application objects in a dedicated schema. Set search_path explicitly for each role to prevent search-path hijacking attacks.

-- Postgres: create a user with a password
CREATE ROLE app_user LOGIN PASSWORD 'str0ng-p@ssw0rd!';

-- Set password expiry (force rotation)
ALTER ROLE app_user VALID UNTIL '2026-12-31';

-- Use SCRAM-SHA-256 authentication (more secure than md5)
-- In pg_hba.conf: host all all 0.0.0.0/0 scram-sha-256
-- Then:
SET password_encryption = 'scram-sha-256';
ALTER ROLE app_user PASSWORD 'new-password';

-- MySQL: create user with strong auth plugin
CREATE USER 'app_user'@'%' IDENTIFIED WITH caching_sha2_password BY 'str0ng-p@ss';

-- SQL Server: enforce password policy (Windows policy integration)
CREATE LOGIN app_login WITH PASSWORD = 'str0ng-p@ss!',
  CHECK_POLICY = ON, CHECK_EXPIRATION = ON;

Rule of thumb: use randomly generated, long passwords (32+ characters) for service accounts and store them in a secrets manager (Vault, AWS Secrets Manager). Rotate passwords automatically. Never hardcode credentials in application source code.

Database-level host restrictions add a network layer of access control — even if credentials are compromised, connections from unauthorised IPs are rejected.

-- Postgres: pg_hba.conf (host-based authentication file)
-- Each line: TYPE  DATABASE  USER  ADDRESS  METHOD
-- Allow the app server IP only:
host   myapp    app_user   10.0.1.5/32    scram-sha-256
-- Reject everything else:
host   myapp    all        0.0.0.0/0      reject

-- MySQL: user accounts include the host
CREATE USER 'app_user'@'10.0.1.5' IDENTIFIED BY 'password';
-- This account can ONLY connect from 10.0.1.5
GRANT ALL ON myapp.* TO 'app_user'@'10.0.1.5';

-- SQL Server: use firewall rules (Azure Portal / Windows Firewall)
-- plus Windows authentication or IP restrictions in network config

Rule of thumb: never expose the database port to the public internet. Allow connections only from application servers and VPN/bastion hosts, using IP allowlists at both the database (pg_hba.conf) and network (firewall) levels.

By granting access to a view instead of the base table, you restrict what data a user can see — without row-level security policies or column grants.

-- Base table: all employees including salary and SSN
-- View: only expose name, department, and hire date
CREATE VIEW employee_directory AS
  SELECT id, full_name, department, hire_date
  FROM   employees
  WHERE  terminated_at IS NULL;

-- Grant SELECT on the view only
GRANT SELECT ON employee_directory TO hr_partner_role;
REVOKE ALL ON employees FROM hr_partner_role;  -- no direct table access

-- HR partner can now run:
SELECT * FROM employee_directory WHERE department = 'Engineering';
-- Cannot see salary, SSN, or terminated employees

Rule of thumb: use views as a security layer for read-only access to sensitive tables when you want the constraint to be declarative and self-documenting. For write-access scenarios, combine views with INSTEAD OF triggers or handle mutations directly on the restricted columns.

Default privileges define the permissions automatically applied to future objects (tables, sequences, functions) created in a schema. Without them, a role that has SELECT on all current tables loses access as soon as a new table is created.

-- Postgres: set default privileges for future tables in a schema
-- Run this as the schema owner:
ALTER DEFAULT PRIVILEGES IN SCHEMA app
  GRANT SELECT ON TABLES TO readonly_role;

ALTER DEFAULT PRIVILEGES IN SCHEMA app
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite_role;

ALTER DEFAULT PRIVILEGES IN SCHEMA app
  GRANT USAGE, SELECT ON SEQUENCES TO readwrite_role;

-- Verify current default privileges
SELECT * FROM pg_default_acl;

-- MySQL: no native default privileges; use a provisioning script or IAM

Rule of thumb: set ALTER DEFAULT PRIVILEGES for every role when you first create the schema. Without it, each new table deployed in a migration requires a separate GRANT — easy to forget, causing silent 403s in production.

-- Postgres: check table-level privileges
SELECT grantee, table_name, privilege_type
FROM   information_schema.role_table_grants
WHERE  grantee = 'analyst_role'
ORDER  BY table_name;

-- Postgres: psql shorthand
-- \dp orders           → show ACL for the orders table
-- \du analyst_role     → show role attributes and memberships
-- \z                   → show ACLs for all tables

-- Postgres: check schema privileges
SELECT grantee, schema_name, privilege_type
FROM   information_schema.role_schema_grants
WHERE  grantee = 'analyst_role';

-- MySQL
SHOW GRANTS FOR 'app_user'@'%';

-- SQL Server
SELECT principal_name, object_name, permission_name, state_desc
FROM   sys.database_permissions dp
JOIN   sys.database_principals  pr ON dp.grantee_principal_id = pr.principal_id
WHERE  pr.name = 'analyst_role';

Rule of thumb: before revoking or changing permissions, always inspect the current state first. In Postgres, \dp <tablename> is the fastest way to spot unexpected PUBLIC grants on sensitive tables.