Implementing Database Security
1. Creating Database Users and Roles
| Command | Effect |
|---|---|
| CREATE ROLE name LOGIN PASSWORD '...' | Create user |
| CREATE ROLE app_read NOINHERIT | Group role |
| GRANT app_read TO alice | Add user to role |
| REVOKE ... FROM ... | Remove privileges |
| DROP ROLE | Delete (must reassign owned objects) |
2. Granting and Revoking Permissions
| Privilege | Scope |
|---|---|
| SELECT / INSERT / UPDATE / DELETE | Tables / views |
| REFERENCES | Create FK pointing to |
| TRIGGER | Create trigger |
| EXECUTE | Functions / procedures |
| USAGE | Schemas, sequences |
| ALL PRIVILEGES | Everything (avoid in prod) |
| WITH GRANT OPTION | Allow regrant |
3. Implementing Row-Level Security
Example: Postgres RLS
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON orders
USING (tenant_id = current_setting('app.tenant_id')::BIGINT);
GRANT SELECT, INSERT, UPDATE, DELETE ON orders TO app_role;| Feature | Detail |
|---|---|
| USING clause | Read filter |
| WITH CHECK | Write filter |
| FORCE ROW LEVEL SECURITY | Apply to table owner too |
| Bypass | BYPASSRLS role attribute |
4. Implementing Column-Level Security
| Approach | Detail |
|---|---|
| GRANT on specific cols | GRANT SELECT(name, email) ON users TO ... |
| Views | Project allowed cols only |
| Data masking (SQL Server, Oracle) | Built-in static/dynamic masking |
| Encryption | App-side encrypt sensitive cols |
5. Using Database Encryption
| Layer | Mechanism |
|---|---|
| In-transit | TLS (require, verify-full) |
| At-rest (disk) | LUKS, EBS encryption, TDE |
| TDE | Transparent Data Encryption (Oracle/SQL Server) |
| Column-level (DB) | pgcrypto, Always Encrypted |
| App-level | AES-GCM with envelope keys (KMS) |
6. Implementing SQL Injection Prevention
| Defense | Detail |
|---|---|
| Parameterized queries | Always — never concatenate user input |
| ORM / query builder | Treats inputs as data |
| Least privilege | App role can't DROP/ALTER |
| Stored procedures | Defined query shape |
| Input validation | Allow-list for identifiers (table/col names) |
| WAF | Second line of defense |
Warning: Dynamic SQL is the #1 source of SQLi. If you must build queries dynamically, validate identifiers against an allow-list and bind values as parameters.
7. Auditing Database Access
| Tool | DB |
|---|---|
| pgaudit extension | Postgres |
| Audit log plugin | MySQL Enterprise |
| SQL Server Audit | Built-in |
| Unified Audit | Oracle 12c+ |
| Ship logs | SIEM (Splunk, ELK) |
8. Managing Database Authentication
| Method | Detail |
|---|---|
| Password (SCRAM-SHA-256) | Default modern method (PG 14+) |
| Certificate (mTLS) | Strongest; key rotation needed |
| LDAP / Kerberos / SAML | Enterprise SSO integration |
| IAM tokens (RDS/Aurora) | Short-lived AWS-signed tokens |
| OAuth (some managed DBs) | App identity federation |
9. Implementing Principle of Least Privilege
| Practice | Detail |
|---|---|
| App role ≠ owner | App can't change schema |
| Separate roles | read-only / read-write / migration |
| Default DENY | Grant only what's needed |
| Revoke from PUBLIC | Don't rely on default grants |
| Time-bound roles | Just-in-time elevation |
10. Securing Database Connections
| Setting | Detail |
|---|---|
| sslmode=verify-full | Hostname + CA check |
| VPC / private subnet | No public access |
| IP allow-list / SG | Restrict source |
| SSH tunnel / bastion | Operator access path |
| Rotate keys | Automated, < 90 day cadence |