SELECT member FROM [SHOW GRANTS ON ROLE admin] WHERE member = current_user();

-- List all users and their role memberships
SELECT
  username,
  options,
  member_of
FROM [SHOW USERS]
ORDER BY username;

-- Count admin role members
SELECT COUNT(*) AS admin_count
FROM [SHOW GRANTS ON ROLE admin];

-- List all admin users
SELECT member AS admin_user
FROM [SHOW GRANTS ON ROLE admin]
WHERE is_admin = true
ORDER BY member;

-- Admin users — each should have a documented reason for admin access
SELECT member AS admin_user
FROM [SHOW GRANTS ON ROLE admin]
WHERE is_admin = true
ORDER BY member;

-- Check what PUBLIC can do (these apply to ALL users)
SELECT
  database_name,
  schema_name,
  object_name,
  object_type,
  privilege_type
FROM [SHOW GRANTS FOR public]
WHERE privilege_type NOT IN ('USAGE')
  AND schema_name = 'public'
ORDER BY database_name, object_name;

-- Users with sensitive system privileges
SELECT grantee, privilege_type
FROM [SHOW SYSTEM GRANTS]
WHERE privilege_type IN (
  'MODIFYCLUSTERSETTING',
  'CANCELQUERY',
  'CANCELSESSION',
  'VIEWACTIVITY',
  'CREATEDB',
  'CREATELOGIN'
)
ORDER BY privilege_type, grantee;

-- Read-only role for analysts
CREATE ROLE analyst_reader;
GRANT SELECT ON DATABASE <app_db> TO analyst_reader;

-- Application service role (read + write, no DDL)
CREATE ROLE app_service;
GRANT SELECT, INSERT, UPDATE, DELETE ON DATABASE <app_db> TO app_service;

-- Schema management role (DDL only)
CREATE ROLE schema_manager;
GRANT CREATE ON DATABASE <app_db> TO schema_manager;

-- Monitoring role (read-only system visibility)
CREATE ROLE monitoring;
GRANT SYSTEM VIEWACTIVITYREDACTED TO monitoring;

-- Operations role (triage + cancel, no data access)
CREATE ROLE ops_triage;
GRANT SYSTEM VIEWACTIVITYREDACTED, CANCELQUERY TO ops_triage;

-- Assign users to their appropriate roles
GRANT analyst_reader TO analyst_user;
GRANT app_service TO payment_service, order_service;
GRANT schema_manager TO migration_user;
GRANT monitoring TO monitoring_user;
GRANT ops_triage TO oncall_sre;

-- Revoke admin from specific users
REVOKE admin FROM analyst_user;
REVOKE admin FROM payment_service;
REVOKE admin FROM monitoring_user;

-- Revoke SELECT from PUBLIC on application databases
REVOKE SELECT ON DATABASE <app_db> FROM public;

-- Revoke all data privileges from PUBLIC on specific tables
REVOKE ALL ON TABLE <sensitive_table> FROM public;

-- Revoke system privileges from users who don't need them
REVOKE SYSTEM MODIFYCLUSTERSETTING FROM <username>;
REVOKE SYSTEM CREATEDB FROM <username>;

-- Confirm admin count is reduced
SELECT COUNT(*) AS admin_count FROM [SHOW GRANTS ON ROLE admin];

-- Confirm PUBLIC privileges are minimal
SELECT database_name, privilege_type
FROM [SHOW GRANTS FOR public]
WHERE privilege_type NOT IN ('USAGE');

-- Verify specific user's effective privileges
SHOW GRANTS FOR <username>;

-- Re-grant admin (emergency)
GRANT admin TO <username>;

-- Re-grant specific privileges
GRANT SELECT, INSERT, UPDATE ON DATABASE <app_db> TO <username>;

-- Re-grant PUBLIC privileges
GRANT SELECT ON DATABASE <app_db> TO public;

-- Snapshot current grants before changes
SELECT * FROM [SHOW GRANTS FOR <username>];
SELECT * FROM [SHOW GRANTS FOR public];
SELECT * FROM [SHOW SYSTEM GRANTS];