Hiding Columns in Google BigQuery
Managing fine-grained data access control for sensitive data
BigQuery provides fine-grained access to columns using policy tags. When a user tries to access a restricted column at query time, BigQuery checks for column policy tag and its policy to see if the user is authorized to access the data. Following steps outlines implementation strategy for managing data access control in BigQuery:
■ Step-1: Define data catalog roles for admins and application users
■ Step-2: Define data catalog taxonomy and policy tags
■ Step-3: Apply column-level security on BigQuery column
■ Step-4: Verify data access with column-level security
Identity and Access Management (IAM) Roles
Applying column-level security requires multiple IAM roles. BigQuery column-level security requires following Data Catalog roles :
Create a Business Taxonomy
Data Catalog taxonomy is used to create hierarchical groups of policy tags to control column access in BigQuery. Following steps required to create Data Catalog taxonomy and Policy tags:
1. Open Data Catalog
2. Navigate to “Policy Tags” and click “Create and Manage Policy Tag”
3. Click Create
4. Enter taxonomy name (example: Business Taxonomy)and description
5. Select project name and location
6. Enter a policy tag name and description
7. Click “Add Child Policy Tag” to add a child policy tag (if required)
Data Access without Column-Level Security
Data access without column-level security allows to access all columns without any restriction.
$ bq query --use_legacy_sql=false 'select * from bq_poc.employee'+-------+--------+--------+--------+
| empno | ename | salary | deptno |
+-------+--------+--------+--------+
| 7782 | CLARK | 2450 | 10 |
| 7654 | MARTIN | 1250 | 30 |
| 7566 | JONES | 2975 | 20 |
+-------+--------+--------+--------+
Apply Column-Level Security on BigQuery Column
In BigQuery, column-level security is implemented on a column through policy tag. As a result, only authorized users can able to access the restricted column and other users have access to the remaining columns. Policy tag is added to a column using following steps:
1. Open BigQuery
2. Click on Table (example: employee)
3. Under Schema tab, click “Edit Schema”
4. Select a column (example: salary) and click “Add Policy Tag”
5. In “Add a Policy Tag” screen, select the policy tag (example: Restricted)
Data Access with Column-Level Security
In this example, SELECT * command failed because column-level security is added on a column (salary) and the user does not have the “Data Catalog Fine-Grained Reader” role to access the restricted column.
$ bq query --use_legacy_sql=false 'select * from bq_poc.employee'BigQuery error in query operation: Error processing job 'data-lab:bqjob_r22cdf84d6fe1d46f_00000173c98b3a33_1': Access Denied: BigQuery BigQuery: User does not have permission to access policy tag "Business Taxonomy : Restricted" on column bq_poc.employee.salary.
The query is modified to exclude the column(salary) that the user cannot access but have access to the remaining columns. Now, the query is executed successfully.
$ bq query --use_legacy_sql=false \
'select * EXCEPT (salary) from bq_poc.employee'+-------+--------+--------+
| empno | ename | deptno |
+-------+--------+--------+
| 7782 | CLARK | 10 |
| 7654 | MARTIN | 30 |
| 7566 | JONES | 20 |
+-------+--------+--------+
Conclusion
Data access control is an integral part of an organization to safeguard sensitive data and restricting column data from unauthorized user access helps to build trust and security compliance.
