Sharing data and reports securely

Friday, November 6, is about access. The store manager dashboard is nearly ready, and Lena's next question is a careful one: each manager should see their own store in detail and only regional totals for the others, and nobody outside Finance should see individual transaction lines, which include payment types. Ravi uses the request to walk through BigQuery's layers of access control and how they combine with Data Studio's sharing.

The principle: least privilege, granted to groups

Every access decision follows one principle: give people the least access that lets them do their job, and no more. A second rule makes that manageable: grant access to groups, not individuals. Birchline has Google groups such as store-managers@, analytics-team@ and finance-reporting@. When a manager joins or leaves, the identity team changes group membership, and every grant follows automatically. Grants to individual accounts are hard to audit and tend to outlive the job they were made for.

IAM roles for BigQuery

BigQuery access is controlled with Identity and Access Management (IAM) roles, granted at the level of the organization, a project, a dataset, or a single table or view. Access to data and permission to run queries are separate. To query a table, a person needs permission to read the data, such as BigQuery Data Viewer on the dataset, and permission to run jobs in some project, such as BigQuery Job User, because every query is a job that is billed to a project.

Birchline grants analytics-team@ Data Viewer on curated and reference, Job User on the analytics project, and BigQuery User on a separate sandbox project for experiments. Finance gets Data Viewer on a finance dataset that includes the transaction lines. Store managers get nothing on the raw or curated datasets at all; what they see comes through the reporting layer described below.

Sharing results, not tables: authorized views and datasets

Lesson 5 introduced authorized views. Ravi creates a dataset called shared_reporting in the same location as curated, creates the view v_store_daily_reporting there, and authorizes it on the curated dataset. Store managers receive Data Viewer on shared_reporting only. They can query the view, which returns daily totals per store, but they cannot read curated.sales, see its transaction lines or write their own query against it. An authorized dataset extends the same idea: every view in an authorized dataset is allowed to read the source dataset, which saves authorizing views one at a time.

Row-level security

Lena's first requirement, detail for your own store and totals for others, needs rows filtered by who is asking. Row-level security in BigQuery uses row access policies attached to a table. Each policy names a set of users or groups and a filter; a user sees only the rows matching the filters of the policies that include them. Once any row access policy exists on a table, users who are not covered by any policy see no rows at all.

-- Each store's managers see their own store's rows in the detail table
CREATE ROW ACCESS POLICY store_12_managers
ON `bl-data-prod.shared_reporting.store_daily_detail`
GRANT TO ("group:store-012-managers@birchline.example")
FILTER USING (store_id = 12);

-- Regional managers see every store in their region
CREATE ROW ACCESS POLICY east_region
ON `bl-data-prod.shared_reporting.store_daily_detail`
GRANT TO ("group:region-east-managers@birchline.example")
FILTER USING (region = "East");

-- Analysts and finance see everything
CREATE ROW ACCESS POLICY all_rows
ON `bl-data-prod.shared_reporting.store_daily_detail`
GRANT TO ("group:analytics-team@birchline.example", "group:finance-reporting@birchline.example")
FILTER USING (TRUE);

Writing a policy for each of forty stores is repetitive, so Ravi uses a common alternative: a mapping table that lists each manager's email and store, and a filter that uses SESSION_USER() to look up the current user. Either way, the important property is the same. The filter is applied by BigQuery itself, so it holds in every tool: the console, a notebook, Connected Sheets or a report using viewer's credentials.

Mateo models the policy logic in Python to make sure he understands what each person will see, including the person no policy covers.

rows = [  # (store_id, region, net_sales) for one day
    (12, "East", 9100), (15, "East", 7800), (22, "Central", 6600), (31, "West", 9400),
]
policies = {   # group -> filter
    "store-012-managers": lambda r: r[0] == 12,
    "region-east-managers": lambda r: r[1] == "East",
    "analytics-team": lambda r: True,
}
users = {
    "manager of store 12": ["store-012-managers"],
    "East regional manager": ["region-east-managers"],
    "analyst": ["analytics-team"],
    "new hire, no group yet": [],
}
for user, groups in users.items():
    visible = [r for r in rows if any(policies[g](r) for g in groups)]
    total = sum(r[2] for r in visible)
    print(f"{user:24} sees {len(visible)} rows, total {total:,}")

manager of store 12      sees 1 rows, total 9,100
East regional manager    sees 2 rows, total 16,900
analyst                  sees 4 rows, total 32,900
new hire, no group yet   sees 0 rows, total 0

Column-level security and masking

Lena's second requirement, no transaction lines or payment types outside Finance, is about columns. Column-level security uses policy tags from a taxonomy managed in Dataplex. An administrator tags sensitive columns, such as tender_type or customer_email, and grants the Fine-Grained Reader role on each tag to the groups allowed to see it. A user without that role who queries the column gets an access error. Dynamic data masking goes one step further: instead of an error, users without access see a masked value, such as NULL, a hash or only the last four characters, according to a masking rule. Masking lets one table serve everyone while protecting the sensitive values.

For sharing data outside the organization, BigQuery offers data exchanges and listings, a feature long called Analytics Hub and now presented in the console as BigQuery sharing. A publisher lists a dataset; a subscriber in another organization gets a linked dataset that reads the publisher's data in place, without copying it, and queries are billed to the subscriber. Birchline uses it to receive weather data from a provider and plans to share sell-through figures with its two largest suppliers.

Sharing reports

Data Studio sharing works like Google Drive. A report can be shared with people or groups as viewers or editors, and link sharing can be restricted to the organization or turned off. Sharing the report and sharing the data are separate: what a viewer sees depends on the data source's credentials. With owner's credentials, viewers see whatever the source returns to its owner; with viewer's credentials, they see what their own BigQuery permissions allow, including row access policies.

Ravi's design uses both deliberately. The overview page reads a summary of regional totals through owner's credentials, because every manager may see regional totals and nobody needs BigQuery access for that. The store detail page reads store_daily_detail through viewer's credentials, so the row access policies decide which store each manager sees. Editors are limited to the analytics team, and the report is owned by a Data Studio Pro team workspace, not by Ravi's personal account.

Connected Sheets gives business users another route: from Google Sheets, they can pivot, chart and filter BigQuery tables of billions of rows, with the queries running in BigQuery under their own credentials. Finance's month-end workbook uses Connected Sheets on the governed finance views, so the familiar spreadsheet layout reads warehouse data directly instead of exported copies. Exported copies are the most common way data escapes its controls, which is why Birchline discourages downloading curated data to files.

Privacy obligations and auditing

Access control is also a legal matter when personal information is involved. Under PIPEDA, Canada's federal private-sector privacy law, personal information should be limited to what is needed for the purpose and protected by safeguards suited to its sensitivity, which in practice means masking, minimizing and restricting customer columns. Ravi's rule is that curated sales carry no customer names or emails at all; the customer key is a pseudonymous ID, and the mapping to real customers lives in a separate, tightly restricted dataset.

Finally, Cloud Audit Logs record who changed access and, through data access logs, who read which tables. BigQuery's INFORMATION_SCHEMA views show grants and jobs. A quarterly review of who has access to what, run by the data owners, catches the grants that should have been removed.

Service accounts deserve the same care as people. The Data Studio Pro workspace, the scheduled queries and the Looker connection each run as a service account with only the roles they need, and none of them holds BigQuery Admin.