Web Application Security Basics: Database Access Controls

Why access control matters more than you think

Most teams assume "the app is authenticated, so the DB is safe." That's a dangerous assumption. If an attacker compromises your application (via RCE, SSRF, SQL injection, or a leaked admin panel), your database is the next target. Access controls are your last line of defense between a compromised app and full data exfiltration.

Mindset shift: Ask yourself, "If an attacker has shell access to my app server, how much of my database can they still reach?"

Core principles of database access control

1. Least privilege by design

Your application should never connect to the database as root or any admin account. Use separate database users with narrowly scoped permissions:

• Read-only user for reporting/analytics modules
• Write user for CRUD operations on specific tables
• Admin user (only for migrations, never in the app runtime)

-- Create role-specific users (PostgreSQL/MySQL examples)
CREATE ROLE app_read_only;
CREATE ROLE app_write_user;
GRANT SELECT ON users, orders, products TO app_read_only;
GRANT SELECT, INSERT, UPDATE ON users, orders TO app_write_user;
-- NEVER grant DROP, TRUNCATE, or ALTER to app users

This limits blast radius: if your app is compromised, the attacker can't drop tables or dump the entire schema.

2. Separate credentials by trust boundary

Don't use the same DB credentials for:

• User-facing application
• Admin dashboard
• Background jobs / cron scripts
• Reporting/analytics

Each trust level should have distinct credentials. If your reporting module is vulnerable, it shouldn't have write access to user data.

3. Connection strings: never hardcode, always encrypt

Hardcoded connection strings in source code are a common leak vector (GitHub repos, Docker images, logs). Instead:

• Store connection strings in environment variables or secrets managers (AWS Secrets Manager, HashiCorp Vault, Azure Key Vault)
• Encrypt connection config files at rest
• Use IAM-based authentication where available (e.g., AWS RDS IAM auth)

// Bad: hardcoded credentials
$pdo = new PDO('mysql:host=db;dbname=app', 'root', 'SuperSecret123');
// Good: from environment + secrets manager
$dsn = getenv('DB_CONNECTION');
$user = getenv('DB_USERNAME');
$pass = getenv('DB_PASSWORD'); // fetched from vault at runtime
$pdo = new PDO($dsn, $user, $pass);

Practical implementation patterns

Parameterized queries are non-negotiable

Even with access controls, SQL injection can bypass your application logic. Always use parameterized queries:

// Vulnerable
$query = "SELECT * FROM users WHERE email = '$email'";
// Secure
$stmt = $pdo->prepare('SELECT * FROM users WHERE email = ?');
$stmt->execute([$email]);

This prevents attackers from injecting malicious SQL even if they control input.

Use stored procedures to abstract data access

Stored procedures let you:

• Grant execute permissions without direct table access
• Centralize business logic and validation
• Audit all data modifications in one place

-- Create stored procedure
CREATE PROCEDURE create_user(IN email VARCHAR(255), IN pass_hash VARCHAR(255))
BEGIN INSERT INTO users (email, password_hash, created_at) VALUES (email, pass_hash, NOW());
END;
-- Grant execute only
GRANT EXECUTE ON PROCEDURE create_user TO app_write_user;

Your app user can now create users without direct INSERT permissions on the table.

Close connections promptly

Leaving connections open increases attack surface:

• Use connection pooling with proper timeouts
• Close connections after each request (or use framework-managed pools)
• Set max_connections limits at the database level

// Ensure connection is closed
$pdo = null; // or use try-finally with explicit close

Database hardening checklist

Beyond application-level controls, harden the database itself:

• Remove default accounts: Change or disable root, sa, postgres default passwords
• Disable unnecessary features: Turn off UDFs, file I/O, remote execution if not needed
• Restrict network access: Bind DB to private interfaces only, use firewall rules
• Enable audit logging: Log failed auth attempts, privilege escalations, schema changes
• Update regularly: Patch database software to close known CVEs

-- Example: disable dangerous MySQL features
SET GLOBAL local_infile = 0;
SET GLOBAL secure_file_priv = '/var/lib/mysql-files';

Monitoring and alerting

Access controls are useless if you don't know when they're bypassed. Implement:

• Query logging: Track unusual queries (e.g., SELECT * FROM users at 3 AM)
• Failed auth alerts: Multiple failed login attempts from same IP
• Privilege escalation detection: Any GRANT, ALTER USER, or CREATE ROLE events
• Data exfiltration patterns: Unusual bulk SELECT or COPY operations

-- PostgreSQL audit extension example
CREATE EXTENSION pgaudit;
ALTER SYSTEM SET pgaudit.log = 'write, ddl';

Integrate these logs with your SIEM or monitoring stack (e.g., Grafana, ELK, Splunk).

Common misconceptions

"My ORM handles this"

ORMs help but don't replace access controls. They can still execute arbitrary queries if misused. Always layer database-level restrictions on top.

"I'm behind a firewall, so I'm safe"

Firewalls don't stop compromised apps, insider threats, or misconfigured cloud security groups. Defense in depth is essential.

"Access controls slow down my app"

Minimal impact. Properly scoped permissions and connection pooling actually improve performance by reducing attack surface and resource contention.

Conclusion

Database access controls are your safety net when everything else fails. They enforce least privilege, limit blast radius, and buy you time to detect and respond to breaches. Like encryption, they're not optional—they're foundational.

Next up: Encrypting your database—because even with perfect access controls, you need to assume your data might be exfiltrated.