Access control when the data belongs to your client

The first thing to internalise about BigQuery access is that it is additive. A grant made higher in the hierarchy flows down, and a lower grant cannot take it away. If someone holds Data Viewer on the project, every dataset in that project is readable, including the one you created this morning and have not thought about since.

Google's resource hierarchy documentation sets out how organisation, folder, project and dataset nest, and policy inherits downward at every level. There is no routine deny-by-exception at the dataset level. You get least privilege by granting less, not by clawing back.

For a single company that is a mild inconvenience. For an agency holding data for a dozen clients it is the entire design problem, because every accidental project-level grant is a client-wide exposure, and the exposure grows every time someone adds a dataset.

So the working rule is narrow. Nobody holds a project-level data role except the ingestion service account and the person who administers the project. Every other grant sits at dataset level or below, and is made deliberately.

The practical consequence is that an access review has to start at the top. Reading a dataset's grants tells you who was granted the dataset; it does not tell you who can read it. The complete answer is the dataset's grants plus the project's plus anything above the project, and if that chain runs more than two levels deep in your setup, nobody is going to check it properly.

The strongest isolation BigQuery gives you without extra machinery is the project. That is why one project per client is the layout that survives growth, and it is worth settling that question first, because access control is downstream of layout and cannot rescue a bad one.

Inside a project, the dataset is the next boundary and it does most of the daily work. The IAM roles reference lists the predefined roles and the permissions each carries. Two matter constantly for client work: Data Viewer, which reads data, and Job User, which is what allows someone to run a query at all.

That split trips people up. Data Viewer on its own cannot run a query, because running a query creates a job and a job is billed to a project. An analyst needs Job User somewhere plus Data Viewer on the data. Where you put Job User decides where the bill lands.

Treat that as a lever rather than a nuisance. Give a client Job User in their own project and their exploratory queries bill to them. Give it to them in yours and they do not, and you will find out at the end of the month.

A grant to a personal Google account outlives the person's involvement. It is invisible during a contract review, invisible during onboarding of their replacement, and it surfaces during an audit or an incident, which are the two worst moments to discover it.

Use a Google group per role per client. Something like analytics-acme-readers and analytics-acme-admins. Access changes become group membership changes, which one person can make in half a minute and which leave an audit trail nobody has to reconstruct afterwards.

Automated access is a separate category. Pipelines and scheduled queries should run as a service account, never as the account of whoever happened to build them. Google's guidance on controlling access to resources with IAM covers granting roles to service accounts, and the reason to bother is blunt: a pipeline running as a departed employee is a pipeline that stops the day their account is disabled.

I have watched a client's Monday reporting fail at nine in the morning for exactly that reason. The account was correctly disabled on the Friday. The scheduled query had been running as it for two years and nobody knew.

One more habit worth adopting. Separate the account that administers a client's project from the account you use for daily work. Admin rights held permanently by a working account mean every browser session and every script you run carries the ability to change permissions, and the incidents that follow are configuration mistakes rather than attacks.

The dataset row is the default for almost every human grant. It is precise enough to separate raw from marts, and coarse enough that you are not maintaining a hundred individual table grants.

The last row is a legitimate option and a bad habit. Table-level grants work perfectly well, and a warehouse held together by two hundred of them cannot be reviewed by any one person, which in practice means it stops being reviewed at all.

The blast radius column is the one to read out loud in a planning conversation. It turns an abstract argument about least privilege into a sentence a client understands: if this grant is wrong, here is exactly what is exposed and to whom.

There is one common requirement the four levels cannot meet: a client should see a filtered or aggregated slice without holding any access to the tables underneath. An authorised view solves that. The view lives in its own dataset and is authorised against the source dataset, so anyone granted the view's dataset can query it with no grant on the source at all.

The shape for client work is consistent. Source tables sit in a dataset only your service accounts can read. Client-facing views sit in a second dataset the client group can read. The second dataset is authorised against the first, and that authorisation is the only bridge between them.

It is more machinery than a plain dataset grant, so reach for it when the requirement is genuinely a subset rather than a layer. If the client should see all of marts, grant marts and stop. If they should see marts without the columns holding customer email addresses, that is a different tool, and it is column-level security.

If the slice is per location or per business unit rather than per column, the right tool is row-level security, which filters rows inside one table instead of restricting whole objects. Choosing the wrong one of the three produces a design that works and cannot be maintained.

There is a dataset-level version of the same idea. An entire dataset can be authorised against a source dataset, which saves re-authorising each time you add a view. Use that form when the client-facing layer is a set of views that will keep growing, and the per-view form when it is two objects that will not.

Judge any access design by one question. When this client leaves, or this contractor's engagement ends, how many actions revoke everything, and can you list those actions from memory right now?

With project-per-client and group-based grants the answer is one or two. Remove the person from the groups. Hand over or delete the project. With grants scattered across datasets and personal accounts the answer is a search, and searches miss things quietly.

Run that test on a calendar rather than on an incident. Once a quarter, list the members of every group and read the IAM policy of every client project. For a dozen clients it is twenty minutes, and it is the only reliable way stale access surfaces before somebody else finds it.

A franchise operator with more than fifteen clinics reading live per-location quarterly reporting needs each location's operator to see their own numbers and nobody else's. That is not a policy statement, it is a grant structure, and it belongs in the handover documents of a marketing data warehouse build rather than in someone's head.

The same applies to the reporting layer running on top. If automated client reporting is going to run unattended for months, the accounts it runs as should be ones you would be comfortable reading aloud in an audit.