SQL Security — Users, Roles, GRANT & REVOKE

-- CREATE USER (creates a login account)
CREATE USER api_user WITH PASSWORD 'SecurePass123!';
CREATE USER reporting_user WITH PASSWORD 'Reports2024!';
CREATE USER admin_user WITH PASSWORD 'AdminPass!' CREATEDB CREATEROLE;

-- Attributes that control login capabilities:
-- SUPERUSER / NOSUPERUSER: full admin access (avoid granting this!)
-- CREATEDB: can create databases
-- CREATEROLE: can create other roles
-- LOGIN / NOLOGIN: can this account log in?
-- CONNECTION LIMIT n: max concurrent connections (-1 = unlimited)

-- CREATE ROLE (like a group — typically no LOGIN by default)
CREATE ROLE readonly;       -- Group role: no login, just permission grouping
CREATE ROLE readwrite;
CREATE ROLE finance_team;
CREATE ROLE app_backend;

-- GRANT role to user (assign group membership)
GRANT readonly      TO reporting_user;
GRANT readwrite     TO api_user;
GRANT finance_team  TO reporting_user;
GRANT app_backend   TO api_user;

-- A user inherits all privileges of their assigned roles
-- reporting_user has: readonly + finance_team permissions

-- GRANT: give privileges
-- Syntax: GRANT privilege ON object TO user/role

-- TABLE privileges
GRANT SELECT ON Employee TO readonly;           -- Read only
GRANT SELECT, INSERT, UPDATE ON Orders TO readwrite;  -- Read + write
GRANT ALL PRIVILEGES ON Product TO admin_user;  -- Full control

-- Grant on all tables in schema
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

-- Auto-grant on future tables too (PostgreSQL)
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO readonly;

-- Specific column-level privileges (PostgreSQL/SQL Server)
GRANT SELECT (EmpID, Name, DeptID) ON Employee TO reporting_user;
-- reporting_user can query EmpID, Name, DeptID but NOT Salary or SSN!

-- Grant with GRANT OPTION (allows the grantee to grant to others)
GRANT SELECT ON Product TO team_lead WITH GRANT OPTION;

-- DATABASE-level privileges
GRANT CONNECT ON DATABASE mydb TO api_user;
GRANT CREATE  ON DATABASE mydb TO admin_user;

-- SCHEMA-level privileges
GRANT USAGE ON SCHEMA public TO readonly;   -- Can see objects in schema
GRANT CREATE ON SCHEMA public TO dev_user;  -- Can create objects

-- FUNCTION / PROCEDURE privileges
GRANT EXECUTE ON FUNCTION GetEmployeeGrade(INT) TO api_user;

-- REVOKE: remove privileges
REVOKE INSERT ON Orders FROM readwrite;    -- Remove specific privilege
REVOKE ALL PRIVILEGES ON Employee FROM reporting_user;  -- Remove all
REVOKE GRANT OPTION FOR SELECT ON Product FROM team_lead;  -- Remove grant option only

-- Check current privileges
SELECT grantee, privilege_type, table_name
FROM information_schema.role_table_grants
WHERE table_name = 'employee';

-- PostgreSQL: show all grants on a table
dp employee

-- RLS: restrict which rows a user can read/modify
-- Use case: SaaS multi-tenant database — each tenant only sees their own data

-- 1. Enable RLS on the table
ALTER TABLE Orders ENABLE ROW LEVEL SECURITY;

-- 2. Create policies defining which rows are visible
-- Policy: each user can only see orders belonging to their company
CREATE POLICY tenant_isolation ON Orders
    USING (tenant_id = current_setting('app.current_tenant_id')::INT);

-- Policy: managers can see all orders; staff only see their own
CREATE POLICY manager_sees_all ON Employee
    USING (
        current_user = 'hr_manager'    -- HR manager sees everything
        OR EmpID = (SELECT EmpID FROM UserMapping WHERE Username = current_user)
    );

-- Policy: sales reps can only UPDATE their own opportunities
CREATE POLICY sales_rep_update ON Opportunity
    FOR UPDATE
    USING (assigned_to = current_user)
    WITH CHECK (assigned_to = current_user);

-- BYPASSRLS: superusers bypass RLS (for admin operations)
ALTER USER admin_user BYPASSRLS;

-- Set the tenant context when connecting (app-level)
SET app.current_tenant_id = '42';
SELECT * FROM Orders;  -- Only returns orders where tenant_id = 42

-- View: alternative to RLS for simpler cases
CREATE VIEW MyOrders AS
    SELECT * FROM Orders WHERE CustomerID = current_user_id();
GRANT SELECT ON MyOrders TO customer_role;