PolicySync#
Google BigQuery (GBQ)#
Create IAM Role#
Create PrivaceraPolicySyncRole
IAM role in your GCP project 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. Replace
<GCP_PROJECT_ID>
with your GCP project ID.PROJECT_ID="<GCP_PROJECT_ID>"
-
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:
-
Log in to your GCP console.
-
Select IAM & admin > Service accounts and click + CREATE SERVICE ACCOUNT.
-
Enter values in the fields and click CREATE.
-
In Grant this service account access to project, select the role as
PrivaceraPolicySyncRole
. -
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.
-
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.
-
On the Services Account Page, go to the Keys tab and click Add Key and select Create New Key.
-
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:
-
In the search bar, search for Logging, and then click Logs Router, and click Create Sink.
-
Enter the sink name as PolicySyncBigQueryAuditSink, and then click Next.
-
Enter the sink destination.
-
In the Select sink service, select BigQuery.
-
In Select BigQuery dataset, click Create new BigQuery dataset.
-
Enter the Dataset ID as bigquery_audits and click Create Dataset.
-
Click Next.
-
-
Add the BigQuery logs in the sink:
In the Build an inclusion filter, add the following line:
resource.type="bigquery_resource"
-
Click Create Sink.
Related Information
Refer the Google documentation for a detailed information on creating a sink - Click here.
Configuration#
-
SSH to Privacera Host VM.
-
Do the following, if you’re not using VM attached credentials:
-
Access the JSON file of the service account you downloaded using the steps above.
-
Copy the JSON to the
config/custom-vars
folder.
-
-
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
-
Edit the properties. For more information on the properties, click here.
-
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).
-
Prepare test data in GCP. Refer Google documentation for a detailed information on running queries in BigQuery - Click here.
-
Log in to GCP console, navigate to BigQuery and then click Compose New Query.
-
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;
-
Click Run.
-
-
Create test user in GCP as emily with the BigQuery Job User role. For a detailed information on the steps, click here.
-
Log in to GCP console, navigate to IAM & Admin, and then click Add.
-
In the New members, enter the email of the test user whom you are granting access to. For example, emily.yourcompany@gmail.com.
-
In the Role dropdown, select BigQuery Job User.
-
Click +ADD ANOTHER ROLE.
-
In the Role dropdown, select BigQuery Metadata Viewer.
-
Click Save.
Note
Ensure that user emily does not have Full Admin or Full Reader access on BigQuery.
-
-
Create test user in Privacera Portal as emily. For more information, click here.
-
Check the access control on the test data in GCP.
A) Table-level Access Control
-
In Privacera Portal, create a policy Customer Data Full Access for accessing table.
-
Log in to GCP console with credentials of the test user emily.
-
Navigate to BigQuery.
-
Run the following query.
SELECT * FROM customer_dataset.customer_data;
User emily can access and view the data.
-
In Privacera Portal, disable the policy.
-
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.
-
In Privacera Portal, do the following:
-
Log in to GCP console with credentials of the test user emily.
-
Navigate to BigQuery.
-
Run the following queries.
-
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.
-
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;
-
-
In Privacera Portal, disable the Customer Data Column Level Access policy.
C) Row-level Filter
-
In Privacera Portal, enable Customer Data Full Access policy created above.
-
Log in to GCP console with credentials of the test user emily.
-
Navigate to BigQuery.
-
Run the following queries.
-
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.
-
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.
-
-
In Privacera Portal, create a Customer Access by Country policy to access data only from UK.
-
Run the following queries.
-
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;
-
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
-
Log in to GCP console with credentials of the test user emily.
-
Navigate to BigQuery.
-
Run the following query.
SELECT * FROM customer_dataset_secure.customer_data;
User emily can view the SSN values.
-
In Privacera Portal, create a Mask SSN policy to mask SSN values for emily.
-
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.
-