SQL 91 🛢️ Roles, Permissions, and Data Security

Database security is built on a layered model: roles group privileges, privileges control access to objects, and additional mechanisms—row-level security, encryption, masking, and auditing—protect data beyond simple access control. The SQL standard defines GRANT and REVOKE as the verbs for assigning and removing privileges, with PUBLIC as the keyword for all users . Every major database implements this foundation, but each extends it differently: SQL Server has fixed server and database roles , PostgreSQL treats roles as the universal principal (users and groups are the same concept), and MySQL requires roles to be explicitly activated within a session .

The distinction between authentication (who you are) and authorization (what you can do) is where roles and permissions live. A user authenticates once, but their effective privileges depend on the roles granted to them, the roles currently active, and any row-level or column-level policies that filter what they can see. Understanding this stack is essential for designing a schema that protects sensitive data without making normal application operations impossible.

This chapter covers the role and privilege model in the major engines, the mechanisms for protecting data at finer granularity (row-level security and column-level encryption/masking), and the auditing layer that records who did what.

Key point: Roles group privileges so they can be granted and revoked as a unit; GRANT and REVOKE control object access; row-level security filters which rows a user can see, and column-level encryption or masking protects sensitive values even from users with table access.

---

Why roles and layered security exist

The permission management problem. Granting privileges to individual users does not scale. When a new team member joins, you must replicate the exact set of grants that the previous member had. When a team member leaves, you must find and revoke every grant. Roles solve this by making the grant target a named group. A role is a security principal that can own privileges and be granted to other principals . In SQL Server, fixed server roles like sysadmin and securityadmin bundle administrative capabilities; fixed database roles like db_owner and db_securityadmin bundle database-level capabilities . In PostgreSQL, roles are more flexible: a role can act as a user (with LOGIN), a group, or both, and roles can be granted to other roles to form hierarchies.

The least privilege principle. Granting ALL PRIVILEGES is convenient but dangerous. An application user that only needs to read from one table should not have write access to the entire database. The SQL standard supports granular privileges—SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER, EXECUTE—that can be granted on specific objects . PostgreSQL extends this further with schema-level and database-level privileges. The goal is to grant the minimum set of privileges required for a principal to perform its function.

The row-level problem. Table-level privileges are all-or-nothing: if a user has SELECT on a table, they can see every row. In multi-tenant applications or scenarios where users should only see their own data, this is insufficient. Row-level security (RLS) adds a policy layer that filters rows based on the current user or session context . PostgreSQL and SQL Server support RLS natively; MySQL and SQLite do not .

The column-level problem. Even within a row, some columns are more sensitive than others. A customer support representative may need to see a customer’s order history but not their full credit card number. Column-level privileges (granting SELECT on specific columns) exist in the SQL standard and are supported by most engines . Encryption and masking go further: they protect the data even from users who have legitimate access to the column, by storing ciphertext or displaying obfuscated values.

The audit problem. Access control prevents unauthorized actions, but it does not record authorized actions. For compliance and forensics, you need to know who accessed what and when. Database activity monitoring captures queries, logins, and schema changes, either through engine-native audit logs or through a gateway that sits in front of the database .

---

a. Roles and privileges across engines

SQL Server uses a two-tier role model. Fixed server roles (sysadmin, securityadmin, serveradmin, dbcreator, etc.) have server-wide scope and fixed permissions . Fixed database roles (db_owner, db_securityadmin, db_datareader, db_datawriter, etc.) exist in every database and cannot be dropped . The public role is special: every login belongs to it automatically, and permissions granted to public are inherited by all users . Custom roles can be created at the database level but not at the server level. Privileges are granted with GRANT SELECT ON SCHEMA::HumanResources TO role_HumanResourcesDept and revoked with REVOKE .

PostgreSQL unifies users and roles. CREATE ROLE creates a principal; CREATE USER is equivalent to CREATE ROLE ... LOGIN . Roles can be granted to other roles, creating a hierarchy where a login role inherits the privileges of the group roles granted to it. The PUBLIC pseudo-role applies to all users; by default, PostgreSQL grants CONNECT and CREATE on the public schema to PUBLIC, which is often too permissive for production. The recommended hardening step is REVOKE CREATE ON SCHEMA public FROM PUBLIC and then granting specific privileges to specific roles . Privileges on tables include SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, and TRIGGER; schema privileges include USAGE and CREATE.

MySQL separates account management from role management. A user account is created with CREATE USER, and roles are created with CREATE ROLE. Roles are granted to users with GRANT role TO user, but the critical distinction is that granted roles are not active by default . A user must either have a default role set with SET DEFAULT ROLE or activate roles manually with SET ROLE in the session. The CURRENT_ROLE() function shows which roles are active. This design gives users control over which privileges they exercise at any moment, but it also means that a user who has been granted a role may still be unable to perform the action until the role is activated.

The WITH GRANT OPTION clause allows the grantee to grant the same privilege to others. In the SQL standard, this is the mechanism for delegated administration. In PostgreSQL, a role with CREATEROLE can create and manage roles, but PostgreSQL 16 changed the rules: a CREATEROLE user can only grant membership in roles they have admin rights on, rather than any role .

---

b. Row-level security and column-level protection

Row-level security (RLS) attaches a policy to a table. The policy is a boolean expression evaluated for each row, using functions like current_user or session variables to determine visibility. In PostgreSQL, CREATE POLICY defines the policy, and ALTER TABLE ... ENABLE ROW LEVEL SECURITY activates it. In SQL Server, CREATE SECURITY POLICY with filter predicates achieves the same result . RLS is transparent to the application: the same SELECT * FROM orders returns different rows depending on who runs it. The trade-off is that every query on the table now carries the policy evaluation cost, and debugging permission issues becomes harder because the filtered rows are invisible without inspecting the policy.

Column-level encryption protects data at rest and in transit within the database. Each engine provides native functions: MySQL has AES_ENCRYPT() and AES_DECRYPT(); PostgreSQL has the pgcrypto extension with pgp_sym_encrypt() and pgp_sym_decrypt(); SQL Server has ENCRYPTBYKEY() and DECRYPTBYKEY(), plus the Always Encrypted feature where the server never sees plaintext . The key management is the hard part: keys must not be stored in the database or in application code, and rotation must be planned. A common pattern is to store the encryption key in a secrets manager (Vault, AWS KMS) and pass it to the application at runtime.

Data masking is a lighter alternative for non-production environments and display purposes. Instead of encrypting and decrypting, the database returns an obfuscated value: a credit card number shows only the last four digits, a phone number shows 138****1234. Masking can be implemented with SQL functions (SUBSTRING and CONCAT) or with views that expose masked columns . It does not protect the underlying data—anyone with direct table access sees the real value—but it prevents accidental exposure in logs, dashboards, and support tools.

Column-level privileges sit between table-level grants and encryption. GRANT SELECT (name, email) ON users TO support_role allows the role to read only those columns. The SQL standard supports this, and most engines implement it . It is simpler than encryption and does not require key management, but it only controls access; it does not obfuscate the value for users who are legitimately granted the column.

---

c. Auditing and monitoring

Engine-native auditing varies widely. SQL Server has SQL Server Audit, which can capture login attempts, DDL changes, and DML operations to a file or the Windows event log . PostgreSQL has pg_stat_statements for query statistics and the logging collector for activity logs, but full auditing often requires the pgaudit extension. MySQL has the general query log and the slow query log, plus enterprise audit plugins. These native logs are the foundation, but they have a common weakness: they attribute actions to the database connection, not necessarily to the human who initiated them. If an application connects as app_user, every query in the log shows app_user, regardless of which end user triggered it .

A database activity monitoring (DAM) solution or a gateway like Bytebase addresses this by sitting in front of the database and capturing the identity of the person who submitted the query, along with the approval context and the SQL text . This is especially relevant for compliance frameworks (GDPR, HIPAA, PCI DSS) that require an audit trail linking actions to individuals. For organizations without a gateway, the pattern is to ensure that each human user has their own database credentials for administrative access, and that application connections are clearly distinguished from human connections in the audit log.

---

Complete Example Session

The following session builds a role hierarchy in PostgreSQL, grants column-level and table-level privileges, and demonstrates row-level security.

-- ============================================
-- PART 1: CREATE ROLES
-- ============================================
CREATE ROLE app_read NOLOGIN;
CREATE ROLE app_write NOLOGIN;
CREATE ROLE support_agent LOGIN PASSWORD 'change_me';

-- ============================================
-- PART 2: GRANT TABLE PRIVILEGES TO ROLES
-- ============================================
GRANT SELECT ON orders TO app_read;
GRANT INSERT, UPDATE, DELETE ON orders TO app_write;

-- ============================================
-- PART 3: GRANT COLUMN-LEVEL PRIVILEGE
-- ============================================
-- Support agents can see order metadata but not payment details
GRANT SELECT (id, customer_id, status, created_at) ON orders TO support_agent;

-- ============================================
-- PART 4: GRANT ROLE MEMBERSHIP
-- ============================================
GRANT app_read TO support_agent;

-- ============================================
-- PART 5: ENABLE ROW-LEVEL SECURITY
-- ============================================
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY support_own_orders ON orders
  FOR SELECT
  TO support_agent
  USING (customer_id = current_setting('app.current_customer_id')::int);

-- ============================================
-- PART 6: TEST THE COLUMN RESTRICTION
-- ============================================
-- As support_agent:
SELECT id, status FROM orders;  -- allowed
SELECT credit_card FROM orders; -- denied (column not granted)

-- ============================================
-- PART 7: TEST ROW-LEVEL SECURITY
-- ============================================
SET app.current_customer_id = '42';
SELECT id, status FROM orders;
-- Returns only rows where customer_id = 42

-- ============================================
-- PART 8: REVOKE A PRIVILEGE
-- ============================================
REVOKE INSERT ON orders FROM app_write;

-- ============================================
-- PART 9: AUDIT QUERY FOR PRIVILEGES
-- ============================================
SELECT grantee, table_name, privilege_type
FROM information_schema.role_table_grants
WHERE table_name = 'orders';

-- ============================================
-- PART 10: CLEANUP
-- ============================================
DROP POLICY support_own_orders ON orders;
ALTER TABLE orders DISABLE ROW LEVEL SECURITY;
DROP ROLE support_agent;

The ten parts cover role creation, table grants, column-level grants, role membership, RLS policy creation, column restriction testing, row filtering testing, revocation, audit query, and cleanup. What the session demonstrates is that security is layered: role membership grants the base SELECT privilege, column grants restrict which columns are visible, and RLS filters which rows are visible within those columns.

---

Quick Reference

Privilege Statements

Statement	Purpose
GRANT privilege ON object TO principal	Assign privilege
REVOKE privilege ON object FROM principal	Remove privilege
GRANT role TO user	Grant role membership
WITH GRANT OPTION	Allow grantee to grant further

Role Models by Engine

Engine	Role Concept	Default Activation
SQL Server	Fixed server + database roles	Granted roles active
PostgreSQL	Unified user/role	Granted roles active
MySQL	Account + role	Roles inactive until SET ROLE

Protection Mechanisms

Mechanism	Scope	Engine Support
Column-level GRANT	Specific columns	Standard, most engines
Row-level security	Specific rows	PostgreSQL, SQL Server
Column encryption	Values at rest	All (native functions)
Data masking	Display values	Via views/functions

Audit Sources

Source	What It Captures
SQL Server Audit	Logins, DDL, DML
PostgreSQL pgaudit	Statement-level auditing
MySQL general log	All queries (high volume)
DAM/gateway	End-user identity + SQL

---

Best Practices

✅ Do This:

GRANT SELECT (name, email) ON users TO support;  -- ✅ column-level
CREATE POLICY tenant_isolation ON orders USING (tenant_id = current_tenant()); -- ✅ RLS
REVOKE CREATE ON SCHEMA public FROM PUBLIC;      -- ✅ PostgreSQL hardening
SET DEFAULT ROLE app_read TO 'user'@'host';      -- ✅ MySQL role activation

❌ Don’t Do This:

GRANT ALL ON *.* TO 'app'@'%';                   -- ❌ excessive privilege
-- skip RLS for multi-tenant tables              -- ❌ cross-tenant data leak
-- store encryption keys in the database         -- ❌ key compromise
-- rely on engine logs for human attribution     -- ❌ app_user masks identity

---

Common Pitfalls

Pitfall	Why It Happens	Fix
MySQL role granted but not working	Role not activated	SET DEFAULT ROLE or SET ROLE
PostgreSQL public schema too permissive	Default CREATE grant to PUBLIC	REVOKE CREATE ON SCHEMA public FROM PUBLIC
RLS policy returns no rows	Session variable not set	Set app.current_customer_id before query
Column grant not preventing access	Table-level grant also exists	Revoke table-level, grant only columns
Audit logs show only app user	Shared connection	Use separate credentials per human

---

Real-World Examples

1. PostgreSQL role hierarchy

CREATE ROLE readonly NOLOGIN;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
GRANT readonly TO reporting_user;

2. SQL Server fixed role

ALTER SERVER ROLE securityadmin ADD MEMBER [DOMAIN\admin];

3. MySQL role activation

SET DEFAULT ROLE ALL TO 'app'@'localhost';

4. Row-level security (PostgreSQL)

CREATE POLICY tenant_policy ON documents USING (tenant_id = current_setting('app.tenant')::int);

5. Column-level grant

GRANT SELECT (id, name) ON customers TO analyst;

6. Revoke default public privileges (PostgreSQL)

REVOKE ALL ON DATABASE app_db FROM PUBLIC;
GRANT CONNECT ON DATABASE app_db TO app_role;

7. Column encryption (pgcrypto)

INSERT INTO users (email, ssn) VALUES ('a@b.com', pgp_sym_encrypt('123-45-6789', 'key'));

8. Masking with a view

CREATE VIEW masked_cards AS SELECT id, '****-' || right(card_number, 4) AS card FROM payments;

9. Audit privilege changes (SQL Server)

CREATE SERVER AUDIT SPECIFICATION AuditPrivileges FOR SERVER AUDIT MyAudit ADD (DATABASE_PERMISSION_CHANGE_GROUP);

10. Check active roles (MySQL)

SELECT CURRENT_ROLE();

---

Visual

Layered Security Model

┌──────────────────────────────────────────────┐
│  LAYER 1: AUTHENTICATION                     │
│  Who are you? (password, certificate, SSO)   │
│         │                                    │
│         ▼                                    │
│  LAYER 2: ROLE MEMBERSHIP                    │
│  What roles do you have?                     │
│  (granted vs active in MySQL)                │
│         │                                    │
│         ▼                                    │
│  LAYER 3: OBJECT PRIVILEGES                  │
│  GRANT SELECT, INSERT, etc. on tables        │
│         │                                    │
│         ▼                                    │
│  LAYER 4: ROW-LEVEL SECURITY                 │
│  Which rows can you see?                     │
│         │                                    │
│         ▼                                    │
│  LAYER 5: COLUMN / ENCRYPTION / MASKING      │
│  What values are visible?                    │
│         │                                    │
│         ▼                                    │
│  LAYER 6: AUDIT                              │
│  What did you actually do?                   │
└──────────────────────────────────────────────┘

Role Grant vs Activation (MySQL)

┌──────────────────────────────────────────────┐
│  GRANT app_read TO 'user'@'host';            │
│         │                                    │
│         ▼                                    │
│  Role is granted but INACTIVE                │
│         │                                    │
│         ▼                                    │
│  SET DEFAULT ROLE app_read TO 'user'@'host'; │
│         │                                    │
│         ▼                                    │
│  Role activates on next login                │
│         │                                    │
│         ▼                                    │
│  CURRENT_ROLE() shows app_read               │
└──────────────────────────────────────────────┘

Row-Level Security Flow

┌──────────────────────────────────────────────┐
│  Query: SELECT * FROM orders                 │
│         │                                    │
│         ▼                                    │
│  RLS policy evaluates per row:               │
│  customer_id = current_setting('app.customer')│
│         │                                    │
│    ┌────┴────┐                               │
│    │         │                               │
│  matches   no match                          │
│    │         │                               │
│    ▼         ▼                               │
│  row       row filtered                      │
│  returned  (invisible to user)               │
└──────────────────────────────────────────────┘

Encryption vs Masking

┌──────────────────────────────────────────────┐
│  ENCRYPTION                                  │
│  Stored value: ciphertext                    │
│  Access: requires key to decrypt             │
│  Use: data at rest, compliance               │
│                                              │
│  MASKING                                     │
│  Stored value: plaintext                     │
│  Displayed: obfuscated (****-1234)           │
│  Use: non-production, display, logs          │
│  Limitation: does not protect underlying data│
└──────────────────────────────────────────────┘

---

Summary

Item	Value
Privilege verbs	GRANT, REVOKE
All users keyword	PUBLIC
SQL Server roles	Fixed server + database roles
PostgreSQL roles	Unified user/role, grantable
MySQL roles	Granted but inactive until SET ROLE
Row-level security	Filters rows per policy
RLS support	PostgreSQL, SQL Server
Column-level grant	GRANT SELECT (col) ON table
Encryption	Native functions, key management required
Auditing	Engine logs + DAM/gateway for identity

Key takeaways:

• Roles group privileges for scalable management. Grant to the role once, add users to the role as needed .
• MySQL roles require activation. A granted role does not apply until SET DEFAULT ROLE or SET ROLE is used .
• PostgreSQL’s public schema is permissive by default. Revoke CREATE from PUBLIC for production databases .
• Row-level security filters data transparently. PostgreSQL and SQL Server support it; MySQL does not .
• Column-level grants and encryption solve different problems. Grants control access; encryption protects the value even from users with access.
• Masking is for display, not storage. It does not protect the underlying plaintext data .
• Audit logs must capture human identity. Shared application connections obscure who did what .

Remember: Database security is not a single setting but a stack of layers. Roles and GRANT/REVOKE control who can touch which objects. Row-level security controls which rows they see. Column grants, encryption, and masking control which values are exposed. Auditing records what actually happened. Each layer has engine-specific behavior—MySQL’s inactive roles, PostgreSQL’s permissive public schema, SQL Server’s fixed roles—and knowing those differences is the difference between a policy that works and one that silently fails.

---

---

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!