Skip to content

PolicySync#

Google BigQuery (GBQ)#

Create IAM Role#

Create PrivaceraPolicySyncRole IAM role in your GCP project. Run the following commands on Google Cloud's shell (gcloud). The shell can be installed and accessed locally or through Google Console.

  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.

    ROLE_NAME="PrivaceraPolicySyncRole"
    
    cat << EOF > ${ROLE_NAME}.yaml
    title: "${ROLE_NAME}"
    description: "${ROLE_NAME}"
    stage: "ALPHA"
    includedPermissions:
    - bigquery.datasets.create
    - bigquery.datasets.delete
    - bigquery.datasets.get
    - bigquery.datasets.getIamPolicy
    - bigquery.datasets.setIamPolicy
    - bigquery.datasets.update
    - bigquery.datasets.updateTag
    - bigquery.jobs.create
    - bigquery.jobs.get
    - bigquery.jobs.list
    - bigquery.jobs.listAll
    - bigquery.models.create
    - bigquery.models.delete
    - bigquery.models.export
    - bigquery.models.getData
    - bigquery.models.getMetadata
    - bigquery.models.list
    - bigquery.models.updateData
    - bigquery.models.updateMetadata
    - bigquery.models.updateTag
    - bigquery.routines.create
    - bigquery.routines.delete
    - bigquery.routines.get
    - bigquery.routines.list
    - bigquery.routines.update
    - bigquery.tables.create
    - bigquery.tables.delete
    - bigquery.tables.export
    - bigquery.tables.get
    - bigquery.tables.getData
    - bigquery.tables.getIamPolicy
    - bigquery.tables.list
    - bigquery.tables.setCategory
    - bigquery.tables.setIamPolicy
    - bigquery.tables.update
    - bigquery.tables.updateData
    - bigquery.tables.updateTag
    - datacatalog.categories.getIamPolicy
    - datacatalog.categories.setIamPolicy
    - datacatalog.taxonomies.create
    - datacatalog.taxonomies.delete
    - datacatalog.taxonomies.get
    - datacatalog.taxonomies.getIamPolicy
    - datacatalog.taxonomies.list
    - datacatalog.taxonomies.setIamPolicy
    - datacatalog.taxonomies.update
    - resourcemanager.projects.get
    - bigquery.rowAccessPolicies.create
    - bigquery.rowAccessPolicies.delete
    - bigquery.rowAccessPolicies.getIamPolicy
    - bigquery.rowAccessPolicies.list
    - bigquery.rowAccessPolicies.setIamPolicy
    - bigquery.rowAccessPolicies.update
    EOF
    
    gcloud iam roles create ${ROLE_NAME} --project=${PROJECT_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 created 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 Select BigQuery dataset, click Create new BigQuery dataset.

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

    4. Click Next.

  4. Add the BigQuery logs 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.

Configuration#

  1. SSH to Privacera Host VM.

  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. Edit the properties. For more information on the properties, click here.

  5. Run the update.

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

Limitations#

Policy Permissions Limitation
Dataset-level Access Control BigQuery supports only ACLs access control for datasets and that won’t allow us to provide fine grained access control on datasets.
View-based Column-level Access Control Column-level access control on views is not supported since BigQuery doesn't provide any native implementation.
Column Access Control Type Only 100 tags in one taxonomy and 20 taxonomies per GCP project can be used. So, a total number of 2000 tags and 2000 columns can be used. It is recommended to use
view in BIGQUERY_COLUMN_ACCESS_CONTROL_TYPE property. For more information, refer Content Limits.
View-based Row Filter Only the first satisfied condition is evaluated from row filter policy items.
Native Row Filter All the row filter policy items from the row filter policy will be evaluated as policysync translates each policy item as single row access policy on the BigQuery table and then the BigQuery handles evaluation part.
Lag on Access Control Enforcement PolicySync only process any changes in the policy in Ranger and submit it as a request to BigQuery. BigQuery takes its time to enforce the policies.

For more information, see the following:
User/Group/Role Policies BigQuery connector won’t create any user/group/roles in Google. Only user/group level policies will work.

Validation#

Let's test the access control using BigQuery by 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 in BigQuery - 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 GCP as emily with the BigQuery Job User role. For a detailed information on the steps, click here.

    1. Log in to GCP console, navigate to IAM & Admin, and then click Add.

    2. In the New members, enter the email of the test user whom you are granting access to. For example, emily.yourcompany@gmail.com.

    3. In the Role dropdown, select BigQuery Job User.

    4. Click +ADD ANOTHER ROLE.

    5. In the Role dropdown, select BigQuery Metadata Viewer.

    6. Click Save.

    Note

    Ensure that user emily does not have Full Admin or Full Reader access on BigQuery.

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

  4. 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;
      

      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;
      

      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.

    Note

    To perform a SELECT on secure view, you need to create one more explicit policy for granting access to secure view.

    1. In Privacera Portal, do the following:

      1. create a policy Secure View Access for accessing secure view on the table.

      2. 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.

      1. Query to view all the columns in the table.

        SELECT * FROM customer_dataset_secure.customer_data;
        

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

      2. Query to select a list of columns in the table.

        SELECT id, domain, ssn, country, us_phone, address, account_id, zipcode FROM customer_dataset_secure.customer_data;
        
    5. In Privacera Portal, disable the Customer Data Column Level Access policy.

    C) Row-level Filter

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

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

    3. Navigate to BigQuery.

    4. Run the following queries.

      1. Query to view data from customer data table. It shows data from countries US and UK.

        SELECT * FROM customer_dataset.customer_data;
        

        User emily can access and view the data.

      2. 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.

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

        SELECT * FROM customer_dataset.customer_data;
        
      2. 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.


Last update: August 24, 2021