INFRA-0006: eSHARS Database Access Model
Status
Accepted
Date
2026-04-21
Context
eSHARS has three Azure SQL databases per environment (data, log, audit) across six environments (dev, qa, uat, training, regression, prod). Six Entra ID security groups were created as a starting point (sg-eshars-db-{nonprod|prod}-{readonly|readwrite}, sg-eshars-dba-{nonprod|prod}). Each group was intended to grant a uniform access level across all three databases in its environment tier.
Collecting developer access requirements via a SharePoint list revealed this model is too coarse. A standard developer needs:
- Read-Write on the
datadatabase in nonprod (for feature development and testing) - Read-only on the
logandauditdatabases in nonprod (for troubleshooting) - Read-only on the
prod datadatabase (for production investigations)
Assigning sg-eshars-db-nonprod-readwrite to a developer would grant Read-Write on log and audit as well — a least-privilege violation and an audit concern given that audit is a compliance database.
The root issue is that SQL Server (Azure SQL) grants permissions at the database level, not at the group level. A single Entra group can hold db_datareader in one database and db_datawriter in another — this is configured via ALTER ROLE inside each database, not via the group definition itself. The group is just a principal; the permission set is the combination of (group, database, role).
Decision
Replace the 6 coarse groups with 7 purpose-built groups organized by (environment tier, access profile):
| Group | data | log | audit |
|---|---|---|---|
sg-eshars-db-nonprod-reader | R | R | R |
sg-eshars-db-nonprod-writer | RW | R | R |
sg-eshars-db-nonprod-dba | DBA | DBA | DBA |
sg-eshars-db-prod-reader | R | — | — |
sg-eshars-db-prod-writer | RW | R | R |
sg-eshars-db-prod-auditor | — | — | R |
sg-eshars-db-prod-dba | DBA | DBA | DBA |
Developers are assigned 1–2 groups. Rare one-off patterns that don’t match any group (e.g. a user needing DBA on a single database while holding read-only on others) are handled via a direct SQL user registered in the Exceptions Registry, not by creating additional groups.
The SQL-side role grants (CREATE USER ... FROM EXTERNAL PROVIDER + ALTER ROLE) are one-time, per group, per database. Adding a new developer only requires an Entra group membership change — no SQL work needed.
Alternatives Considered
Keep the existing 6 groups, accept over-grant — simple, but violates least-privilege and cannot express the data=RW, log=R, audit=R pattern without also granting write on log and audit. Rejected.
54 per-DB groups (sg-eshars-{db}-{env}-{level} for all combinations) — precise control, but ungovernable at scale and requires 18 group assignments per developer. Rejected.
Individual user SQL permissions (no groups) — manageable for 10 developers, breaks at 50. Auditing becomes impossible. Rejected.
Scope down to two levels (dev / dba only) — simplest, but ignores the intentional isolation of the audit database, which may be a compliance or SOX-style requirement. Rejected unless audit DB isolation is explicitly removed as a requirement.
Consequences
- Most developers get 1–2 group assignments instead of up to 18 DB-level grants.
- Adding a new environment or database requires SQL grants for each group that applies, but no new Entra groups (unless the access profile changes).
- Exceptions must be tracked manually in the Exceptions Registry and reviewed quarterly to prevent drift.
prod-writershould be treated as a break-glass group — membership should be time-boxed and removed after the incident or data-fix closes. Consider Azure AD Privileged Identity Management (PIM) for just-in-time elevation.- Entra group membership is flat (user → group directly). Nested groups are not used because Azure SQL does not reliably resolve transitive group membership in all SKUs.