Row Level Permissions

There may be times when you want to control what data users in your project can see more granularly than between tables and datasets (e.g., showing field organizers only data from specific volunteers or counties). In this case, we recommend leveraging BigQuery row-level security policies. This lets you filter data and enable access to specific rows based on pre-set user conditions.

Setting row-level security policies

As an administrator in your project, you can set row-level security policies using the SQL Editor in PAD. 

Here’s an example: 

CREATE ROW ACCESS POLICY my_row_filter
ON project.dataset.my_table
GRANT TO ('user:example.user@cta-tech.app',
          'group:my-project-viewers@cta-tech.app')
FILTER USING (my_field = 'filtered value');

Some key things to remember when setting up a new policy are: 

• Each row-level access policy on a table must have a unique name.
• Like a WHERE clause, the “filter_expression” matches the data you want to be visible to the members of the “grantee_list.”
• You can combine a series of users and groups in the “grantee_list” list if they are comma-separated and quoted separately.

In addition to setting the row-level policy, you’ll also have to ensure that the granted users have the BigQuery FilteredDataViewer IAM role. Here’s how to grant that permission:

1. Navigate to the table you want to grant access to (these instructions can also be done for a dataset if you want to grant permission at that level instead) that already has row access policies in place. Click the menu icon and select “Open”.
   
   
    
2. Once the table is opened, click the “Share” button.
   
   
    
3. Click the “Add Principal” button.
   
   

4. Give your user the “BigQuery Filtered Data Viewer” role and save.