Skip to content

Google BigQuery (GBQ)

Google BigQuery provides fine-grained access control on BigQuery Datasets. This includes

  • Table-level Access Control
  • Column-level Access Control
  • Native/Dynamic secure view-based Row Filter
  • Masking With Dynamic secure views created using PolicySync

Privacera access control for BigQuery relies on the privacera_bigquery connector service.

Prerequisites

Create PrivaceraPolicySyncRole IAM Role

You need to give Privacera PolicySync basic access to GCP. To grant that access, create PrivaceraPolicySyncRole IAM role in your GCP project or GCP organization using the following commands on Google Cloud's shell (gcloud). The shell can be installed and accessed locally or through Google Console.

Run the following command to create the file containing the permissions required for the PrivaceraPolicySyncRole role:

ROLE_NAME="PrivaceraPolicySyncRole"

cat << EOF > ${ROLE_NAME}.yaml
title: "${ROLE_NAME}"
description: "${ROLE_NAME}"
stage: "ALPHA"
includedPermissions:
- resourcemanager.projects.get
- resourcemanager.projects.getIamPolicy
- resourcemanager.projects.setIamPolicy
- iam.roles.list
- iam.roles.get
- iam.roles.create
- iam.roles.update
- bigquery.jobs.create
- bigquery.datasets.get
- bigquery.datasets.create
- bigquery.datasets.update
- bigquery.datasets.delete
- bigquery.datasets.getIamPolicy
- bigquery.datasets.setIamPolicy
- bigquery.tables.list
- bigquery.tables.get
- bigquery.tables.getData
- bigquery.tables.create
- bigquery.tables.update
- bigquery.tables.delete
- bigquery.tables.getIamPolicy
- bigquery.tables.setIamPolicy
- bigquery.rowAccessPolicies.list
- bigquery.rowAccessPolicies.create
- bigquery.rowAccessPolicies.update
- bigquery.rowAccessPolicies.delete
- bigquery.rowAccessPolicies.getIamPolicy
- bigquery.rowAccessPolicies.setIamPolicy

EOF

GCP Project-level access

Note

If you have multiple projects in your GCP organization and want them to be managed by a single BigQuery connector, then repeat the steps below for each project. Assign the role to the same service account which will be used across multiple projects.

  1. Run the following command. Replace <GCP_PROJECT_ID> with your GCP project ID.

    PROJECT_ID="<GCP_PROJECT_ID>"
    
  2. To create PrivaceraPolicySyncRole role in your GCP project, run the following command.

    gcloud iam roles create ${ROLE_NAME} --project=${PROJECT_ID} --file=${ROLE_NAME}.yaml
    

GCP Organization-level access

  1. Run the following command. Replace <GCP_ORGANIZATION_ID> with your GCP organization ID.

    ORGANIZATION_ID="<GCP_ORGANIZATION_ID>"
    
  2. To create PrivaceraPolicySyncRole role in your GCP organization, run the following command.

    gcloud iam roles create ${ROLE_NAME} --organization=${ORGANIZATION_ID} --file=${ROLE_NAME}.yaml
    

Attach IAM Role to Service Account

To attach the PrivaceraPolicySyncRole IAM role created above, do the following steps:

  1. Log in to your GCP console.

  2. Select IAM & admin > Service accounts and click + CREATE SERVICE ACCOUNT.

  3. Enter values in the fields and click CREATE.

  4. In Grant this service account access to project, select the role as PrivaceraPolicySyncRole.

  5. On the Services Account Page, find the newly created service account and copy the email address of the new service account for use in a later step.

    Note

    This email will be the Service Account Email for configuring PolicySync in Privacera Manager.

  6. If you are using a Google VM machine to configure GBQ for PolicySync, then you can attach the service account created above to your VM machine and skip below steps.

  7. On the Services Account Page, go to the Keys tab and click Add Key and select Create New Key.

  8. Select the JSON key type, and click CREATE. A JSON key file downloads to your system. Store the file at an accessible location. It will be used for configuring PolicySync in Privacera Manager.

Related Information

Refer the Google documentation for a detailed information on creating a service account - Click here.

Configure Logs for Auditing

A sink is required to collect all the logs from GBQ. To create a sink, do the following steps:

  1. In the search bar, search for Logging, and then click Logs Router, and click Create Sink.

  2. Enter the sink name as PolicySyncBigQueryAuditSink, and then click Next.

  3. Enter the sink destination.

    1. In the Select sink service, select BigQuery.

    2. In SelectBigQuerydataset, click Create newBigQuerydataset.

    3. Enter the Dataset ID as bigquery_audits and click Create Dataset.

    4. Click Next.

  4. Add theBigQuerylogs in the sink:

    In the Build an inclusion filter, add the following line:

    resource.type="bigquery_resource"
    
  5. Click Create Sink.

Related Information

Refer the Google documentation for a detailed information on creating a sink - Click here.

CLI Configuration

  1. SSH to the instance where Privacera is installed.

  2. Do the following, if you’re not using VM attached credentials:

    1. Access the JSON file of the service account you downloaded using the steps above.

    2. Copy the JSON to the config/custom-vars folder.

  3. Run the following commands.

    cd ~/privacera/privacera-manager/
    cp config/sample-vars/vars.policysync.bigquery.yml config/custom-vars/
    vi config/custom-vars/vars.policysync.bigquery.yml
    
  4. Set the properties for your specific installation. For property details and descriptions, see the Configuration Properties section that follows.

    Note

    Along with the above properties, you can add custom properties that are not included by default. For more information about these properties, see BigQuery Connector.

  5. Run the update.

    cd ~/privacera/privacera-manager 
    ./privacera-manager.sh update
    

Configuration Properties

JDBC configuration properties

Name Type Default Required Description
BIGQUERY_PROJECT_LOCATION string us Yes

Specifies the geographical region where the taxonomy for the PolicySync should be created.

BIGQUERY_PROJECT_ID string Yes

Specifies the Google project ID where your Google BigQuery data source resides. For example: privacera-demo-project.

BIGQUERY_JDBC_URL string jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443 No

Specifies the JDBC URL for the Google BigQuery connector.

BIGQUERY_USE_VM_CREDENTIALS boolean false No

Specifies whether the PolicySync uses the service account attached to your virtual machine for the credentials to connect to the data source.

When set to true you do not need to specify a value for BIGQUERY_OAUTH_PRIVATE_KEY_PATH.

BIGQUERY_OAUTH_SERVICE_ACCOUNT_EMAIL string Yes

Specifies the service account email address that PolicySync uses. You must specify this value if you are not using a Google Cloud Platform (GCP) virtual machine attached service account.

BIGQUERY_OAUTH_PRIVATE_KEY_PATH string /workdir/policysync/cust_conf/policysync-gbq-service-account.json Yes

Specifies the path of the service account credentials JSON file that you downloaded from your Google Cloud Platform (GCP) account. You must specify this property if BIGQUERY_USE_VM_CREDENTIALS is set to false.

BIGQUERY_OAUTH_PRIVATE_KEY_FILE_NAME string Yes

Specifies the name of the JSON file that contains your Google Cloud Platform service account credentials. If specified, this value is combined with BIGQUERY_OAUTH_PRIVATE_KEY_PATH to provide a full path to the credentials JSON file. If you specify BIGQUERY_OAUTH_PRIVATE_KEY_PATH then you must specify this value as well.

Custom IAM roles

Name Type Default Required Description
BIGQUERY_CREATE_CUSTOM_IAM_ROLES boolean true No

Specifies whether PolicySync automatically creates custom IAM roles in your Google Cloud Platform project or organization for fine-grained access control (FGAC). If set to false, you must create all required custom IAM roles manually in your GCP project or organization. The default value is true.

BIGQUERY_CUSTOM_IAM_ROLES_SCOPE string project No

Specifies whether PolicySync creates and uses custom IAM roles at the project or organizational level in Google Cloud Platform (GCP). The following values are allowed:

  • project: Create and use custom IAM roles from each individual project level.
  • org: Create and use custom IAM roles at the organizational level.
BIGQUERY_ORGANIZATION_ID string No

Specifies the Google Cloud Platform (GCP) organizational ID. Specify this only if you configured PolicySync to use custom IAM roles at the organizational level.

BIGQUERY_CUSTOM_IAM_ROLES_NAME_MAPPING string No

Specifies a list of mappings between PolicySync custom IAM role names and your custom role names. Use the following format when specifying your custom role names:

<PRIVACERA_DEFAULT_ROLE_NAME_1>:<CUSTOM_ROLE_NAME_1>
<PRIVACERA_DEFAULT_ROLE_NAME_2>:<CUSTOM_ROLE_NAME_2>

The following is a list of the default custom role names:

  • PrivaceraGBQProjectListRole
  • PrivaceraGBQJobListRole
  • PrivaceraGBQJobListAllRole
  • PrivaceraGBQJobCreateRole
  • PrivaceraGBQJobGetRole
  • PrivaceraGBQJobUpdateRole
  • PrivaceraGBQJobDeleteRole
  • PrivaceraGBQDatasetCreateRole
  • PrivaceraGBQDatasetGetMetadataRole
  • PrivaceraGBQDatasetUpdateRole
  • PrivaceraGBQDatasetDeleteRole
  • PrivaceraGBQTableListRole
  • PrivaceraGBQTableCreateRole
  • PrivaceraGBQTableGetMetadataRole
  • PrivaceraGBQTableQueryRole
  • PrivaceraGBQTableExportRole
  • PrivaceraGBQTableUpdateMetadataRole
  • PrivaceraGBQTableUpdateRole
  • PrivaceraGBQTableSetCategoryRole
  • PrivaceraGBQTableDeleteRole
  • PrivaceraGBQTransferUpdateRole
  • PrivaceraGBQTransferGetRole

Load keys and intervals

Name Type Default Required Description
BIGQUERY_LOAD_RESOURCES_KEY string load_from_dataset_columns No

Specifies how PolicySync loads resources from Google BigQuery. The following values are allowed:

  • load_md: Load resources from Google BigQuery with a top-down resources approach, that is, it first loads the project and then the dataset followed by tables and its columns.

  • load_from_dataset_columns: Load resources one by one for each resource type that is, it loads all projects first, then it loads all datasets in all projects, followed by all tables in all datasets and its columns. This mode is recommended since it is faster than the load mode.

BIGQUERY_RESOURCE_SYNC_INTERVAL integer 60 No

Specifies the interval in seconds for PolicySync to wait before checking for new resources or changes to existing resources.

BIGQUERY_PRINCIPAL_SYNC_INTERVAL integer 420 No

Specifies the interval in seconds for PolicySync to wait before reconciling principals with those in the data source, such as users, groups, and roles. When differences are detected, PolicySync updates the principals in the data source accordingly.

BIGQUERY_PERMISSION_SYNC_INTERVAL integer 540 No

Specifies the interval in seconds for PolicySync to wait before reconciling Apache Ranger access control policies with those in the data source. When differences are detected, PolicySync updates the access control permissions on data source accordingly.

BIGQUERY_AUDIT_SYNC_INTERVAL integer 30 No

Specifies the interval in seconds to elapse before PolicySync retrieves access audits and saves the data in Privacera.

Resources management

Name Type Default Required Description
BIGQUERY_MANAGE_PROJECT_LIST string Yes

Specifies a comma-separated list of project names to which access control is managed by PolicySync. If unset, PolicySync manages all projects. If specified, use the following format. You can use wildcards.

The list of projects to ignore takes precedence over any projects specified by this setting.

An example list of projects might resemble the following: testproject1,testproject2,sales_project*.

BIGQUERY_MANAGE_DATASET_LIST string Yes

Specifies a list of comma-separated datasets that PolicySync manages access control to. You can use wildcards in the value. If you want to manage all datasets, do not set a value. For example:

testproject1.dataset1,testproject2.dataset2,sales_project*.sales*

You can configure the postfix by specifying BIGQUERY_SECURE_VIEW_DATASET_NAME_POSTFIX.

If specified, the BIGQUERY_IGNORE_DATASET_LIST setting takes precedence over this setting.

BIGQUERY_MANAGE_TABLE_LIST string No

Specifies a comma-separated list of table names for which PolicySync manages access control. You can use wildcards.

Use the following format when specifying a table:

<PROJECT_NAME>.<DATASET_NAME>.<TABLE_NAME>

If specified, BIGQUERY_IGNORE_TABLE_LIST takes precedence over this setting.

If you specify a wildcard, such as in the following example, all matched tables are managed:

<PROJECT_NAME>.<DATASET_NAME>.*

The specified value, if any, is interpreted in the following ways:

  • If unset, access control is managed for all datasets.
  • If set to none no datasets are managed.
BIGQUERY_IGNORE_PROJECT_LIST string No

Specifies a comma-separated list of project names that PolicySync does not provide access control for. You can specify wildcards. If not specified, all projects are subject to access control.

For example: testproject1,testproject2,sales_project*.

This setting supersedes any values specified by BIGQUERY_MANAGE_PROJECT_LIST.

BIGQUERY_IGNORE_DATASET_LIST string No

Specifies a comma-separated list of dataset names that PolicySync does not provide access control for. You can specify wildcards. If not specified, all datasets are subject to access control.

For example: testproject1.dataset1,testproject2.dataset2,sales_project*.sales*.

This setting supersedes any values specified by BIGQUERY_MANAGE_DATASET_LIST.

BIGQUERY_IGNORE_TABLE_LIST string No

Specifies a comma-separated list of table names that PolicySync does not provide access control for. You can specify wildcards. If not specified, all tables are subject to access control. Specify tables using the following format:

<PROJECT_NAME>.<DATASET_NAME>.<TABLE_NAME>

This setting supersedes any values specified by BIGQUERY_MANAGE_TABLE_LIST.

Users, groups, and roles management

Name Type Default Required Description
BIGQUERY_USER_NAME_REPLACE_FROM_REGEX string [~`$&+:;=?@#|'<>.^*()_%\\\\[\\\\]!\\\\-\\\\/\\\\\\\\{}] No

Specifies a regular expression to apply to a username and replaces each matching character with the value specified by the BIGQUERY_USER_NAME_REPLACE_TO_STRING setting.

If not specified, no find and replace operation is performed.

BIGQUERY_USER_NAME_REPLACE_TO_STRING string _ No

Specifies a string to replace the characters matched by the regex specified by the BIGQUERY_USER_NAME_REPLACE_FROM_REGEX setting.

If not specified, no find and replace operation is performed.

BIGQUERY_GROUP_NAME_REPLACE_FROM_REGEX string [~`$&+:;=?@#|'<>.^*()_%\\\\[\\\\]!\\\\-\\\\/\\\\\\\\{}] No

Specifies a regular expression to apply to a group and replaces each matching character with the value specified by the BIGQUERY_GROUP_NAME_REPLACE_TO_STRING setting.

If not specified, no find and replace operation is performed.

BIGQUERY_GROUP_NAME_REPLACE_TO_STRING string _ No

Specifies a string to replace the characters matched by the regex specified by the BIGQUERY_GROUP_NAME_REPLACE_FROM_REGEX setting.

If not specified, no find and replace operation is performed.

BIGQUERY_MANAGE_USER_LIST string No

Specifies a comma-separated list of user names for which PolicySync manages access control. You can use wildcards.

If not specified, PolicySync manages access control for all users.

If specified, BIGQUERY_IGNORE_USER_LIST takes precedence over this setting.

An example user list might resemble the following: user1,user2,dev_user*.

BIGQUERY_MANAGE_GROUP_LIST string No

Specifies a comma-separated list of group names for which PolicySync manages access control. If unset, access control is managed for all groups. If specified, use the following format. You can use wildcards.

An example list of projects might resemble the following: group1,group2,dev_group*.

If specified, BIGQUERY_IGNORE_GROUP_LIST takes precedence over this setting.

BIGQUERY_IGNORE_USER_LIST string No

Specifies a comma-separated list of user names that PolicySync does not provide access control for. You can specify wildcards. If not specified, all users are subject to access control.

This setting supersedes any values specified by BIGQUERY_MANAGE_USER_LIST.

BIGQUERY_IGNORE_GROUP_LIST string No

Specifies a comma-separated list of group names that PolicySync does not provide access control for. You can specify wildcards. If not specified, all groups are subject to access control.

This setting supersedes any values specified by BIGQUERY_MANAGE_GROUP_LIST.

BIGQUERY_NATIVE_PUBLIC_GROUP_IDENTITY_NAME string Yes

Set this property to your preferred value, policysync uses this native public group for access grants whenever there is policy created referring to public group inside it. The following values are allowed:

  • ALL_AUTHENTICATED_USERS: All gcp project authenticated users.

  • ALL_USERS: All google authenticated users.

BIGQUERY_MANAGE_USER_FILTERBY_GROUP boolean false No

Specifies whether to manage only the users that are members of groups specified by BIGQUERY_MANAGE_GROUP_LIST. The default value is false.

Access control management

Name Type Default Required Description
BIGQUERY_COLUMN_ACCESS_CONTROL_TYPE string view No

Specifies how PolicySync manages column-level access control. The following values are allowed:

  • view: Use view-based column level access control. Any columns that a user cannot access appears as null in the secure view of the table or the secure view of the native view.
BIGQUERY_POLICY_NAME_SEPARATOR string _ No

Specifies a string to use as part of the name of native row filter and masking policies.

BIGQUERY_ROW_FILTER_POLICY_NAME_TEMPLATE string row_filter_item_ No

Specifies a template for the name that PolicySync uses when creating a row filter policy. For example, given a table data from the ds dataset that resides in the proj project, the row filter policy name might resemble the following:

proj_priv_ds_priv_data_<ROW_FILTER_ITEM_NUMBER>
BIGQUERY_ENABLE_ROW_FILTER boolean false No

Specifies whether to use the data source native row filter functionality. This setting is disabled by default. When enabled, you can create row filters only on tables, but not on views.

BIGQUERY_ENABLE_VIEW_BASED_MASKING boolean true No

Specifies whether to use secure view based masking. The default value is true.

BIGQUERY_ENABLE_VIEW_BASED_ROW_FILTER boolean true No

Specifies whether to use secure view based row filtering. The default value is true.

While Google BigQuery supports native filtering, PolicySync provides additional functionality that is not available natively. Enabling this setting is recommended.

BIGQUERY_SECURE_VIEW_CREATE_FOR_ALL boolean true No

Specifies whether to create secure views for all tables and views that are created by users. If enabled, PolicySync creates secure views for resources regardless of whether masking or filtering policies are enabled.

Access audits management

Name Type Default Required Description
BIGQUERY_MASKING_FUNCTIONS_DATASET string privacera_dataset No

Specifies the name of the dataset where PolicySync creates custom masking functions.

BIGQUERY_MASKED_NUMBER_VALUE integer 0 No

Specifies the masking value used for numeric data types.

BIGQUERY_MASKED_TEXT_VALUE string <MASKED> No

Specifies the masking value used for text or string data types.

BIGQUERY_SECURE_VIEW_NAME_PREFIX string No

Specifies a prefix string for secure views. By default view-based row filter and masking-related secure views have the same dataset name as the table dataset name.

If you want to change the secure view dataset name prefix, specify a value for this setting. For example, if the prefix is dev_, then the secure view name for a table named example1 is dev_example1.

BIGQUERY_SECURE_VIEW_NAME_POSTFIX string No

Specifies a postfix string for secure views. By default view-based row filter and masking-related secure views have the same dataset name as the table dataset name.

If you want to change the secure view dataset name postfix, specify a value for this setting. For example, if the postfix is _dev, then the secure view name for a table named example1 is example1_dev.

BIGQUERY_SECURE_VIEW_DATASET_NAME_PREFIX string No

Specifies a prefix string for secure views. By default view-based row filter and masking-related secure views have the same dataset name as the table dataset name.

If you want to change the secure view dataset name prefix, specify a value for this setting. For example, if the prefix is dev_, then the secure view name for a dataset named example1 is dev_example1.

BIGQUERY_SECURE_VIEW_DATASET_NAME_POSTFIX string _secure No

Specifies a postfix string for secure views. By default view-based row filter and masking-related secure views have the same dataset name as the table dataset name.

If you want to change the secure view dataset name postfix, specify a value for this setting. For example, if the postfix is _dev, then the secure view name for a dataset named example1 is example1_dev.

BIGQUERY_SECURE_VIEW_NAME_REMOVE_SUFFIX_LIST string No

Specifies a suffix to remove from a table or view name. For example, if the table is named example_suffix you can remove the _suffix string. This transformation is applied before any custom prefix or postfix is applied.

You can specify a single suffix or a comma separated list of suffixes.

BIGQUERY_SECURE_VIEW_DATASET_NAME_REMOVE_SUFFIX_LIST string No

Specifies a suffix to remove from a secure view dataset name. For example, if the dataset is named some_name_ds you can remove the _ds string. This transformation is applied before any custom prefix or postfix is applied.

You can specify a single suffix or a comma separated list of suffixes, such as _raw,_qa,_prod.

BIGQUERY_AUTHORIZED_VIEW_ACL_UPDATER_INTERVAL integer 10 No

Specifies the interval at which the authorized view ACLs updater thread updates the permissions in the dataset if any permission updates are pending.

BIGQUERY_GRANT_UPDATES boolean true Yes

Specifies whether PolicySync performs grants and revokes for access control and creates, updates, and deletes queries for users, groups, and roles. The default value is true.

BIGQUERY_GRANT_UPDATES_MAX_RETRY_ATTEMPTS integer 2 No

Specifies the maximum number of attempts that PolicySync makes to execute a grant query if it is unable to do so successfully. The default value is 2.

BIGQUERY_GRANT_UPDATES_BATCH boolean true No

Specifies whether PolicySync applies grants and revokes in batches. If enabled, this behavior improves overall performance of applying permission changes.

BIGQUERY_ENABLE_DATA_ADMIN boolean true No

This property is used to enable the data admin feature. With this feature enabled you can create all the policies on native tables/views, and respective grants will be made on the secure views of those native tables/views. These secure views will have row filter and masking capability. In case you need to grant permission on the native tables/views then you can select the permission you want plus data admin in the policy. Then those permissions will be granted on both the native table/view as well as its secure view.

BIGQUERY_AUDIT_ENABLE boolean false Yes

Specifies whether Privacera fetches access audit data from the data source.

BIGQUERY_AUDIT_EXCLUDED_USERS string No

Specifies a comma separated list of users to exclude when fetching access audits. For example: "user1,user2,user3".

BIGQUERY_AUDIT_PROJECT_ID string No

Specifies the project ID where Google BigQuery stores audit log data.

BIGQUERY_AUDIT_DATASET_NAME string No

Specifies the name of the dataset where Google BigQuery logs audit data. Privacera uses this data for running audit queries.

BIGQUERY_AUDIT_LOAD_MAX_INTERVAL_MINUTES integer 30 No

Specifies the maximum interval, in minutes, for queries that retrieve access audit information to run.

Validation

Let's test the access control usingBigQueryby defining some test policies for a test user (emily).

  1. Prepare test data in GCP. Refer Google documentation for a detailed information on running queries inBigQuery- Click here.

    1. Log in to GCP console, navigate to BigQuery and then click Compose New Query.

    2. In the Query editor text area, copy the following query:

      -- Create Dataset
      CREATE SCHEMA customer_dataset;
      
      -- Create Table
      CREATE TABLE IF NOT EXISTS customer_dataset.customer_data (
      id INT64, 
      person_name string,
      domain string,
      ssn string,
      country string,
      us_phone string, 
      address string, 
      account_id string, 
      zipcode string);
      
      -- Insert Data into table
      insert into customer_dataset.customer_data values (1, 'Nancy','nancy@yahoo.com','201-99-5532','US','856-232-9702','939 Park Avenue','159635478','33317');
      insert into customer_dataset.customer_data values (2,'Gene','gene@google.us','202-99-5532','UK','954-583-0575','303 Johnston Blvd','236854569','95202');
      insert into customer_dataset.customer_data values (3,'Edward','edward@facebook.com','203-99-5532','US','209-626-9041','130 Hollister','365412985','60173');
      insert into customer_dataset.customer_data values (4,'Pearlene','pearlene@gmail.com','204-99-5532','US','708-471-6810','17 Warren Rd','452189732','90017');
      insert into customer_dataset.customer_data values (5,'James','james@cuvox.de','205-99-5532','US','661-338-6787','898 Newport Gray Rd','517836427','94041');
      insert into customer_dataset.customer_data values (6,'Pamela','pamela@cuvox.de','206-99-5532','UK','650-526-5259','861 Strick Rd','685231473','80214');
      insert into customer_dataset.customer_data values (7,'Donna','donna@fleckens.hu','207-99-5532','US','303-239-4282','1784 S Shore Dr','789563258','1730');
      insert into customer_dataset.customer_data values (8,'Amy','amy@gustr.com','208-99-5532','US','774-553-4736','9522 Apple Valley Dr','854126945','55102');
      insert into customer_dataset.customer_data values (9,'Adam','adam@teleworm.us','209-99-5532','UK','651-297-1448','745 Old Springville Rd','965412381','43201');
      insert into customer_dataset.customer_data values (10,'Lucille','lucille@armyspy.com','210-99-5532','US','740-320-1270','4223  Midway Road','785651236','89102');
      insert into customer_dataset.customer_data values (11,'Edard','edu@gustr.com','211-99-5532','UK','702-257-8796','3659  Dye Street','965121354','53207');
      insert into customer_dataset.customer_data values (12,'Nick','nick@jourrapide.com','212-99-5532','US','414-483-8638','2966  Nutters Barn Lane','563515264','72764');
      insert into customer_dataset.customer_data values (13,'Brian','brian@einrot.com','213-99-5532','US','479-872-9783','3300  Worthington Drive','654621233','91303');
      insert into customer_dataset.customer_data values (14,'Stella','stella@jourrapide.com','214-99-5532','US','818-596-6681','1893  Ingram Road','261613654','35816');
      insert into customer_dataset.customer_data values (15,'Leona','leona@dayrep.com','215-99-5532','UK','256-250-5413','4244  Burnside Court','986513211','75069');
      
      -- Verify table by running select query
      SELECT * FROM customer_dataset.customer_data;
      
    3. Click Run.

  2. Create test user in Privacera Portal as emily. For more information, click here.

  3. In GCP console, the user emily gets added after you create the user in step 2.

    1. Check the user emily can list the projects inside your organization.

    2. Check user emily does not have Full Admin or Full Reader access on BigQuery.

  4. Create a policy for emily to run queries and list dataset and tables.

  5. Check the access control on the test data in GCP.

    A) Table-level Access Control

    1. In Privacera Portal, create a policy Customer Data Full Access for accessing table.

    2. Log in to GCP console with credentials of the test user emily.

    3. Navigate to BigQuery.

    4. Run the following query.

      SELECT * FROM customer_dataset.customer_data;
      SELECT * FROM customer_dataset_secure.customer_data;
      

      User emily can access and view the data.

    5. In Privacera Portal, disable the policy.

    6. In GCP, run the following query.

      SELECT * FROM customer_dataset.customer_data;
      SELECT * FROM customer_dataset_secure.customer_data;
      

      User emily can not access and view the data.

    B) View-based Column-level Access Control

    In the view-based column-level access control, you have to create a column-level policy on the table. The columns which are not permitted from that policy will be shown as NULL in the secure view of the table.

    1. In Privacera Portal, do the following:

      Create a policy Customer Data Column Level Access granting access to a few columns.

    2. Log in to GCP console with credentials of the test user emily.

    3. Navigate to BigQuery.

    4. Run the following queries.

      User emily will not see person_name column in the secure view of customer_data table..

      SELECT * FROM customer_dataset_secure.customer_data;
      
    5. In Privacera Portal, disable the Customer Data Column Level Access policy.

    C) View-based Row-level Filter

    1. In Privacera Portal, enable Customer Data Full Access policy created above.

      emily can view customer_data for US and UK from table and in secure view.

    2. Log in to GCP console with credentials of the test user emily.

    3. Navigate to BigQuery.

    4. Run the following queries.

      Query to view data from customer data secure view. It will show data from countries US and UK.

      SELECT * FROM customer_dataset_secure.customer_data;
      

      User emily can access and view the data.

    5. In Privacera Portal, create a Customer Access by Country policy to access data only from UK.

    6. Run the following queries.

      Query to view data from customer data secure view. The row filter policy gets applied showing data from country UK.

      SELECT * FROM customer_dataset_secure.customer_data;
      

    D) View-based Masking

    1. Log in to GCP console with credentials of the test user emily.

    2. Navigate to BigQuery.

    3. Run the following query.

      SELECT * FROM customer_dataset_secure.customer_data;
      

      User emily can view the SSN values.

    4. In Privacera Portal, create a Mask SSN policy to mask SSN values for emily.

    5. Run the following query.

      SELECT * FROM customer_dataset_secure.customer_data;
      

      User emily cannot view the the SSN values, since it is masked using md5 encryption.

Create custom IAM roles

By default, PolicySync creates all the IAM roles listed in the table below automatically to perform access control in GBQ. If you want to create the custom IAM roles manually, then disable the BIGQUERY_CREATE_CUSTOM_IAM_ROLES by setting its value to false.

In the GCP console, you need the map the roles with its appropriate permissions. The role and its permissions mapping are given in the table below. For creating a custom role in GCP and adding its corresponding permissions, see Creating a custom role.

Role Name GCP Permissions
PrivaceraGBQProjectListRole resourcemanager.projects.get
PrivaceraGBQJobListRole bigquery.jobs.list
PrivaceraGBQJobListAllRole bigquery.jobs.listAll
PrivaceraGBQJobCreateRole bigquery.jobs.create
PrivaceraGBQJobGetRole bigquery.jobs.get
PrivaceraGBQJobUpdateRole bigquery.jobs.update
PrivaceraGBQJobDeleteRole bigquery.jobs.delete
PrivaceraGBQDatasetCreateRole bigquery.datasets.create
PrivaceraGBQDatasetGetMetadataRole bigquery.datasets.get
PrivaceraGBQDatasetUpdateRole bigquery.datasets.update
PrivaceraGBQDatasetDeleteRole bigquery.datasets.delete
PrivaceraGBQTableListRole bigquery.tables.list
PrivaceraGBQTableCreateRole bigquery.tables.create
PrivaceraGBQTableGetMetadataRole bigquery.tables.get
PrivaceraGBQTableQueryRole bigquery.tables.getData
PrivaceraGBQTableExportRole bigquery.tables.export
PrivaceraGBQTableUpdateMetadataRole bigquery.tables.update
PrivaceraGBQTableUpdateRole bigquery.tables.updateData
PrivaceraGBQTableSetCategoryRole bigquery.tables.setCategory
PrivaceraGBQTableDeleteRole bigquery.tables.delete
PrivaceraGBQTransferUpdateRole bigquery.transfers.update
PrivaceraGBQTransferGetRole bigquery.transfers.get

Based on the GCP Resource hierarchy, you can create the roles above at the organization or project level. If you have multiple projects in your GCP organization, it is recommended to create all the roles at the organization level instead at the project level. Once the roles are defined at the organization level, all the projects under that organization will inherit all the roles.