Generic best-practices lists describe what you should grant. Audit-surviving practices describe what you should review. This guide covers the second kind — what production-grade database access control (DAC) best practices look like across machine, human, and AI agent workloads, and the mistakes that fail SOC 2, HIPAA, and GDPR audits.
Note: "DAC" is also used in security literature for discretionary access control, the access-control model contrasted with MAC and RBAC. This guide uses DAC for database access control throughout.
Get them wrong and you are one DROP TABLE away from a production incident, or one leaked credential away from a compliance violation. Most teams start with a shared admin account and a handful of application credentials. That works until the first SOC 2 audit asks "who ran this query on March 3rd?" and nobody can answer.
Core principles
Least privilege
Every user and application gets the minimum permissions required to do their job. A developer who needs to run SELECT queries against a staging database does not need DROP or ALTER privileges on production.
In practice, least privilege means:
- Application accounts get only
SELECT,INSERT,UPDATE,DELETEon the tables they use - Schema migration accounts are separate from application accounts
- Read-only replicas use credentials that cannot write
- Admin access is reserved for DBAs and granted per-session, not permanently
Enforce at the statement, not just the connection
A grant — permanent or temporary — only says what a role can do. It does not say whether a specific statement, on a specific table, by a specific principal, should be allowed at this moment. Production-grade DAC reviews the SQL itself:
- Schema changes go through proposal, review, and approval before execution
- Sensitive queries are flagged or blocked by SQL review rules
- Mutating production data requires an approval reference on the request
- Masked columns stay masked even when the role has
SELECTon the table
Connection-time controls (RBAC, PAM, VPN) are necessary but not sufficient. Every statement is a control point.
Separation of duties
The person who writes a database change does not approve and deploy it. This prevents both accidental mistakes and intentional abuse.
A typical separation:
- Developer writes the migration SQL
- DBA or tech lead reviews the SQL for correctness and safety
- Automated pipeline or separate role executes the approved change
- Audit log records who did what at each step
Just-in-time access
Permanent standing access is the default in most organizations, and it is the wrong default. A developer who accessed the production database for a debugging session six months ago probably still has those credentials. If those credentials leak, the blast radius is the entire production dataset.
Just-in-time (JIT) access replaces permanent credentials with temporary, scoped grants:
- User requests access to a specific database for a stated reason
- An approver reviews and approves the request
- The system grants access with a time limit (e.g., 2 hours)
- Access is automatically revoked when the window closes
JIT reduces standing privileges to near zero. Auditors prefer it because every access event has a documented reason and approval chain.
Role-based access control (RBAC)
Managing permissions per user does not scale. RBAC groups permissions into roles and assigns roles to users. When someone joins a team, they inherit the team's role. When they leave, revoking the role removes all associated permissions in one step.
Common database roles:
| Role | Typical permissions |
|---|---|
app_readonly | SELECT on application tables |
app_readwrite | SELECT, INSERT, UPDATE, DELETE on application tables |
schema_migrator | CREATE, ALTER, DROP on schema objects |
dba_admin | Full privileges, granted via JIT only |
analyst | SELECT on reporting views and materialized views |
Common mistakes
1. Shared service accounts. Multiple developers sharing admin@production makes every query untraceable. When an auditor asks who ran a destructive query, the answer is "anyone on the team." Use individual accounts and map every connection to a real person.
2. Copy-pasting permissions from another user. When a new hire joins, the fastest path is to clone an existing user's grants. The problem is that the existing user has accumulated permissions over months. The new hire inherits permissions they do not need and the privilege creep compounds.
3. Never revoking access. People change teams, leave the company, or stop using certain databases. Without a regular access review cycle (monthly or quarterly), stale accounts accumulate. Automate de-provisioning through your identity provider where possible.
4. Granting ALL PRIVILEGES because it is faster. GRANT ALL PRIVILEGES ON *.* TO 'app'@'%' takes five seconds to type and months to clean up. Start with the minimum and add permissions as specific needs arise.
5. No separation between application and human access. Application service accounts and human developer accounts should be different. Applications need predictable, narrow permissions. Humans need broader permissions but only temporarily and with an audit trail.
6. Ignoring schema-level isolation. Databases that support schemas (PostgreSQL, SQL Server) give you a natural boundary for access control. Instead of granting access to individual tables, grant at the schema level and organize tables by access pattern.
7. Treating AI agents as machines. Giving a coding assistant or MCP-connected agent a permanent service account is the worst pattern in this list combined: machine-shaped permanence with human-shaped unpredictability, and no record of which prompt produced which DELETE. Agent accounts need statement-level review, masking, and a binding from the agent's session back to the requesting person.
How Bytebase handles database access control
Bytebase enforces database access control as a statement-level workflow, not a connection-level RBAC bolt-on. Same identity model, same audit log, same masking policy — across every engine and every workload (machine, human, agent).
Every human-to-database scenario maps to a specific Bytebase capability:
| Scenario | Who | Bytebase capability |
|---|---|---|
| Schema change | Developer, DBA | Schema change workflow — proposed, reviewed, approved, deployed |
| Ad-hoc data correction | Developer, DBA | Data change workflow — same gates as schema changes |
| Interactive query | Developer, DBA | SQL Editor read-only mode with row limits and masking applied |
| Admin operations | DBA | SQL Editor admin mode, reserved by role and audited |
The system underneath:
- Two-level RBAC. Workspace-level roles (Admin, DBA, Member) and project-level roles (Owner, Developer, Releaser, SQL Editor User, Viewer). Each role maps to 100+ granular permissions. Custom roles available for specialized permission sets.
- Conditional access with CEL expressions. IAM bindings support CEL conditions, so access can be scoped by database name, schema, or time window. A binding can grant a developer query access to specific databases that expires on a set date.
- Grant requests with expiration. Developers submit a grant request specifying the database, role, and duration. Once approved, the access expires automatically — the closest equivalent to just-in-time access in the database access world.
- Approval workflows. Schema changes and data exports go through configurable approval chains with CEL-based matching rules. Approvers resolve by role, so requests route automatically to the right DBA or project owner.
- Dynamic Data Masking. Masking rules use CEL conditions to match columns by environment, project, instance, database, table, column, or classification level. Exemption policies let specific users or groups bypass masking when needed, also with CEL conditions including time-based expiration.
- Query data policies. Workspace and project policies control SQL Editor behavior: maximum result rows, whether data export is allowed, whether copy-paste is enabled.
- Audit logging. Every query, schema change, and permission change is recorded with the real user identity, request metadata, and RPC latency. Logs are searchable with CEL filters and exportable. This audit trail is what auditors need for SOC 2, HIPAA, and ISO 27001.
- Groups, service accounts, and agent identities. Users organize into groups and inherit roles by group. Service accounts, workload identities (for CI/CD using OIDC), and AI agent sessions are first-class in IAM bindings — with the requesting principal recorded alongside the service account on every action the agent takes.
FAQ
How often should database access be reviewed?
Quarterly reviews are the minimum for compliance. Monthly reviews are better for organizations with frequent team changes. Automated tools that flag unused accounts or permissions that haven't been exercised in 90 days reduce the manual work.
Should application and human accounts share roles?
No. Application accounts need predictable, narrow, long-lived permissions deployed alongside the application. Human accounts need broader permissions but only temporarily and with an audit trail. Sharing roles collapses both shapes into the worst of each: too broad for the app, too permanent for the human.
How is JIT access different from a VPN or bastion host?
A VPN controls which network can reach the database; JIT controls which person can access it and for how long. They operate at different layers. A VPN alone does not satisfy least privilege because every user on the VPN has the same reachability. JIT adds identity-based, time-limited, auditable access on top of network controls.
How do you control access for AI agents and coding assistants?
The same way you control any other workload, with two additions. Use a dedicated service account per agent (or per agent + project), never a shared one. Bind the requesting human's identity to every action the agent takes, so the audit trail records which prompt produced which statement. Apply the same statement-level review, masking, and approval policies that human accounts pass through.