Most best-practices lists tell you what to grant. The ones that survive an audit tell you what to review. This guide covers the second kind: what production-grade database access control (DAC) looks 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 this 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 fine 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, and nothing past that. 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, whether permanent or temporary, only says what a role can do. It says nothing about whether a specific statement, on a specific table, by a specific principal, should run 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 they are not enough on their own. Every statement is a control point.
Separation of duties
The person who writes a database change does not also approve and deploy it. This is what stops both the accidental mistake and the 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 opened the production database for a debugging session six months ago probably still has those credentials sitting around. 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 pushes standing privileges down to near zero. Auditors like it because every access event arrives with a documented reason and an approval chain attached.
Role-based access control (RBAC)
Managing permissions one user at a time 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 the associated permissions in a single 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. When several developers share admin@production, every query becomes untraceable. An auditor asks who ran a destructive query, and the honest answer is "anyone on the team." Use individual accounts and map every connection back 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 catch is that the existing user has been accumulating permissions for months. The new hire inherits permissions they will never use, and the privilege creep compounds from there.
3. Never revoking access. People change teams, leave the company, or stop touching certain databases. Without a regular access review cycle, monthly or quarterly, stale accounts pile up. Automate de-provisioning through your identity provider wherever you can.
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 the specific need shows up.
5. No separation between application and human access. Application service accounts and human developer accounts should not be the same accounts. Applications need predictable, narrow permissions. Humans need broader permissions, but only temporarily and with an audit trail behind them.
6. Ignoring schema-level isolation. Databases that support schemas (PostgreSQL, SQL Server) hand you a natural boundary for access control. Instead of granting access table by table, 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 several mistakes from this list rolled into one: 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 person who asked for it.
How Bytebase handles database access control
Bytebase enforces database access control as a statement-level workflow rather than 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, and custom roles are 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 and have it expire on a set date.
- Grant requests with expiration. Developers submit a grant request specifying the database, role, and duration. Once approved, the access expires on its own. This is the closest equivalent to just-in-time access in the database world.
- Approval workflows. Schema changes and data exports run 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 they need to, 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 if your team changes often. Automated tools that flag unused accounts, or permissions that haven't been exercised in 90 days, cut down 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 work 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 the 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. Then apply the same statement-level review, masking, and approval policies that human accounts already pass through.