How would you set up a database account for analysts and dashboards that need to query production data, so their queries cannot damage or disrupt the transactional workload?

## Read-only is a privilege statement, not a safety statement The naive version of this is `GRANT SELECT ON ALL TABLES TO analyst` and a shared password. That gets the privilege side roughly right and misses the two things that actually cause incidents: resource contention and data exposure. ## Privileges Start with a role that has `SELECT` and nothing else - no INSERT/UPDATE/DELETE, no CREATE, no EXECUTE on functions that write. Grant on named objects rather than 'everything', so a new table containing tokens or payment data is not automatically visible. Where a table mixes sensitive and non-sensitive columns, grant on a **view** that projects only the safe columns, and do not grant on the base table. The view becomes the contract; the underlying schema can change without changing what analysts see. (Column-level restriction mechanics are a topic of their own - here the point is that the reporting account's grants should target the curated surface, not the raw one.) Also revoke inherited access: in engines where `PUBLIC` implicitly holds rights on a schema or on newly created objects, a 'read-only' role can quietly see more than you granted it. ## Placement Analytical queries are long, scan-heavy, and unpredictable; OLTP queries are short and latency-sensitive. Running both on the same instance means the analytical workload evicts the transactional working set from the buffer cache and competes for I/O. The default answer is a read replica dedicated to reporting. It gives physical isolation of resources, and it makes the account's read-only nature structural on engines where replicas reject writes outright. The cost is replication lag - reports may be seconds or minutes behind - which needs to be stated up front rather than discovered when a finance number disagrees with the app. ## Resource containment The failure modes of a read-only account are real: - **Runaway scans** - a missing join predicate produces a cross product; set a per-role `statement_timeout` so it dies in minutes rather than hours. - **Long-held snapshots** - in MVCC engines a long-running read keeps old row versions alive, bloating tables and, on a replica, potentially conflicting with replay. Bound query duration; on replicas, know your engine's setting for how long replay will wait for a reader before cancelling it. - **Connection exhaustion** - a BI tool that opens fifty connections can starve the application. Cap the role's concurrent connections and give reporting its own pool rather than sharing the app's. - **Idle transactions** - a client that opens a transaction and goes to lunch holds resources; set an idle-in-transaction timeout. These limits are best attached to the role itself, so they apply no matter which tool connects. ## Identity and auditing A shared reporting password destroys attribution: when an export of the customer table shows up in the audit log, you want a name. Give each analyst their own login and grant them membership in the reporting role; the privileges live on the role, the identity stays personal. Where the engine or tooling forces a single service account (many BI servers do), compensate by recording the end-user identity in the tool's own audit log and correlating, and by keeping that account's grants especially narrow. ## When read-only is not enough If analysts need to create their own tables for intermediate results, do not widen the production account - give them a separate schema (or a separate analytics database) where they own their objects, with `SELECT` on production data and `CREATE` only in their sandbox. That keeps the write capability off the production schema while still meeting the real requirement.