Security
Database Grant Cleanup: Remove Support Read Access After Privacy Views Ship
Database grant cleanup begins when table permissions remain after a report, export, service, or analyst workflow moved elsewhere. A quiet grant is still a data access path, and the cleanup has to prove both that the old reader is gone and that revocation will not break low-frequency reporting.
For stale support read grants on tables now served by privacy views, the review has to connect risk acceptance, reachability, compensating controls, and the current owner. The useful output is a database grant cleanup record with grantee map, query evidence, data sensitivity, staged revoke, and rollback owner: Narrow broad grants to read-only or column-scoped access before full revocation, keep proof of the security decision, and avoid letting revoking quiet support workflows or leaving direct table access after privacy controls move become the hidden default.
Key takeaways
- Review stale support read grants on tables now served by privacy views through Grantee purpose, Query history, Data sensitivity, not age alone.
- Use one full reporting cycle including month-end, exports, and support workflows before deciding that quiet means unused.
- Start with the reversible move: narrow broad grants to read-only or column-scoped access before full revocation.
- Slow down when revoking quiet support workflows or leaving direct table access after privacy controls move is still plausible.
- Prevent repeat cleanup by making teams create database grants with owner, purpose, data class, expiry, and reporting dependency.
Map Table Grantees
Start with one schema or dataset across table grants, roles, service accounts, BI reports, query logs, exports, and data owner approvals. The best cleanup scope is small enough that owners can answer quickly but wide enough to include the attachments that make removal risky.
| Field | Why it matters |
|---|---|
| Owner | Cleanup needs a person or team that can accept the decision |
| Current purpose | A short reason to keep the item, written in present tense |
| Last meaningful use | last use, permission scope, owner, rotation age, and reachable systems |
| Dependency evidence | audit logs, deployment references, identity provider records, and service owners |
| Risk if wrong | The outage, data loss, access failure, or rollback gap the review must avoid |
| Next action | Keep, reduce, archive, disable, remove, or investigate |
Do not make the inventory larger than the decision. A short list with owners and evidence beats a perfect spreadsheet that nobody is willing to act on.
Grant Evidence to Collect
The useful question is not “how old is it?” It is “what would break, become harder to recover, or lose accountability if this disappeared?” For database grant cleanup for support read access, collect enough evidence to answer that without relying on naming conventions.
| Check | What to look for | Cleanup signal |
|---|---|---|
| Grantee purpose | Role membership, service account owner, access request, and ticket history | No current owner can justify the grant |
| Query history | Last reads, scheduled reports, month-end jobs, exports, and notebooks | The grant is unused across the reporting window |
| Data sensitivity | PII columns, finance fields, customer data, and masking policies | Risk decreases when the grant is narrowed or revoked |
| Replacement path | New dataset, report migration, service credential, and rollback owner | Valid consumers have moved to a clearer access path |
Use several signals together. Activity can miss monthly jobs and incident-only paths. Ownership can be stale. Cost can distract from security or recovery risk. The strongest case combines runtime data, dependency checks, owner review, and a rollback plan.
If the evidence conflicts, label the item “investigate” with a named owner and review date. That is still progress because the next review starts with a narrower question.
Example Table Grant Review
List table privileges and pair them with query history before revoking access.
SELECT grantee, table_schema, table_name, privilege_type
FROM information_schema.role_table_grants
WHERE table_schema = 'analytics'
ORDER BY grantee, table_name;
SELECT grantee, max(last_query_at) AS last_seen
FROM access_review_query_log
GROUP BY grantee;Treat the output as a candidate list. Do not pipe these checks into delete commands; add owner review, dependency checks, and a rollback path first.
Stage Access Revocation
Use the least permanent move that proves the decision. In database grant cleanup for support read access, removal is only one possible outcome; reducing size, narrowing permission, shortening retention, archiving, or disabling a trigger may produce the same benefit with less risk.
- Narrow broad grants to read-only or column-scoped access before full revocation.
- Disable access for a named role during a monitored window before deleting the grant.
- Keep owner approval and query evidence beside the access review.
Track the cleanup candidate with a simple priority score:
| Score | Good sign | Bad sign |
|---|---|---|
| Impact | Meaningful spend, risk, toil, noise, or confusion disappears | The item is cheap and low-risk but politically distracting |
| Confidence | Owner, purpose, and dependency path are understood | The team is guessing from age or name |
| Reversibility | Restore, recreate, re-enable, or rollback path exists | Deletion would be the first real test |
| Prevention | A rule can stop recurrence | The same pattern will return next month |
Start with high-impact, high-confidence, reversible candidates. Defer confusing items only if they get an owner and a date; otherwise “defer” becomes another word for keeping waste permanently.
Reports That Read Quietly
Some cleanup candidates are supposed to look quiet. Do not rush these cases:
- Month-end reports, customer exports, finance close, and incident-only support queries.
- Shared roles where one unused grant is attached to active users.
- Views or stored procedures that read through definer rights.
For these cases, use a longer observation window, explicit owner approval, and a staged reduction. The point is not to avoid cleanup; it is to avoid making the first proof of dependency an outage.
Run the Grant Cleanup
Run database grant cleanup for support read access as a decision review, not an open-ended hygiene project.
- Pick the narrow scope and export the candidate list.
- Add owner, current purpose, last-use evidence, dependency checks, and risk if wrong.
- Remove obvious false positives, then ask owners to choose keep, reduce, archive, disable, remove, or investigate.
- Apply the least permanent useful change first.
- Watch the signals that would reveal a bad decision.
- Complete the final removal only after the review window closes.
- Save a database grant cleanup record with grantee map, query evidence, data sensitivity, staged revoke, and rollback owner.
For broader cleanup planning, use the cleanup library to pair this guide with related notes.
Create Grants With Expiry
Prevention should change the creation path, not just the cleanup path. For database grant cleanup for support read access, the useful prevention fields are owner, expiry date, least-privilege scope, rotation schedule, and removal notes. Make those fields part of normal creation and review.
- Create database grants with owner, purpose, data class, expiry, and reporting dependency.
- Review grants when dashboards, exports, and service accounts move.
- Prefer role groups with clear data products over direct user grants.
The recurring review should be short: sort by impact, pick the unclear items, assign owners, and close the loop on anything nobody claims. If the review keeps producing the same class of candidate, fix the creation path instead of celebrating repeated cleanup.
Example Decision Record
Use a compact record so the cleanup can be reviewed later without reconstructing the whole investigation.
| Field | Example entry for this cleanup |
|---|---|
| Candidate | Stale support read grants on tables now served by privacy views in transactional databases, warehouses, support tools, service accounts, access reviews, and sensitive data classifications |
| Why it looked stale | Low recent activity, unclear owner, or no current consumer after the first review |
| Evidence checked | Grantee purpose, Query history, and owner confirmation |
| First reversible move | Narrow broad grants to read-only or column-scoped access before full revocation |
| Watch signal | The metric, alert, job, route, query, or owner complaint that would show the cleanup was wrong |
| Final action | Keep, reduce, archive, disable, or remove after one full reporting cycle including month-end, exports, and support workflows |
| Prevention rule | Create database grants with owner, purpose, data class, expiry, and reporting dependency |
This record is intentionally small. If the decision needs a long narrative, the candidate is probably not ready for removal yet. Keep investigating until the owner, evidence, reversible move, and prevention rule are clear.
FAQ
How often should teams do database grant cleanup for support read access?
Use one full reporting cycle including month-end, exports, and support workflows for the first decision, then set a recurring cadence based on change rate. Fast-moving non-production systems may need monthly review; slower systems can be quarterly if every unclear item has an owner and a review date.
What is the safest first action?
The safest first action is usually ownership repair plus evidence collection. After that, narrow broad grants to read-only or column-scoped access before full revocation. That creates a visible test before permanent deletion.
What should not be removed quickly?
Do not rush anything connected to month-end reports, customer exports, finance close, and incident-only support queries. Also slow down when the cleanup affects recovery, compliance, customer-specific behavior, rare schedules, or security response.
How do you make the decision useful later?
Write the decision as a small operational record: candidate, owner, evidence, chosen action, watch signals, rollback path, final date, and prevention rule. That format helps future engineers, search engines, and AI assistants understand the cleanup without guessing.