Introduction to data masking

BigQuery supportsdata masking at the columnlevel. You can use data masking to selectively obscure column data foruser groups, while still allowing them access to the column. Data maskingfunctionality is built on top ofcolumn-level access control,so you should familiarize yourself with that feature before you proceed.

When you use data masking in combination with column-level access control,you can configure a range of access to column data, from full access to noaccess, based on the needs of different user groups. For example,for tax ID data, you might want to grant your accounting group full access,your analyst group masked access, and your sales group no access.

Benefits

Data masking provides the following benefits:

• It streamlines the data sharing process. You can mask sensitive columnsto make it possible to share tables with larger groups.
• Unlike with column-level access control, you don't need to modify existingqueries by excluding the columns that the user cannot access. When youconfigure data masking, existing queries automatically mask column data basedon the roles the user has been granted.
• You can apply data access policies at scale. You can write a datapolicy, associate it with a policy tag, and apply the policy tag to anynumber of columns.
• It enables attribute-based access control. A policy tag attached to acolumn provides contextual data access, which is determined by the data policyand the principals that are associated with that policy tag.

Data masking workflow

There are two ways of masking data. You can create a taxonomy and policy tags,then configure data policies on the policy tags. Alternatively, you can set adata policy directly on a column.This lets you map a data masking rule on your data without handling policy tagsor creating additional taxonomies.

Set a data policy directly on a column

You can configure dynamic data masking directly on a column. To do so, perform the followingsteps:

1. Create a datapolicy.

2. Assign a datapolicyto a column.

Mask data with policy tags

Figure 1 shows the workflow for configuring data masking:

Figure 1. Data masking components.

You configure data masking with the following steps:

1. Set up a taxonomy and one or more policy tags.

2. Configuredata policies for the policy tags. A data policy maps adata masking rule and one or more principals, whichrepresent users or groups, to the policy tag.

   Whencreating a data policyby using the Google Cloud console, you create thedata masking rule and specify the principals in one step. When creating adata policy by using the BigQuery Data Policy API, you create the data policyand data masking rule in one step, and specify the principals for the datapolicy in a second step.

3. Assign the policy tags to columns in BigQuery tables toapply the data policies.

4. Assign users who should have access to masked data to theBigQuery Masked Reader role. As a best practice, assign theBigQuery Masked Reader role at the data policy level.Assigning the role at the project level or higher grants users permissions toall data policies under the project, which can lead to issues caused byexcess permissions.

   The policy tag that is associated with a data policy can also beused for column-level access control. In that case, the policy tag is alsoassociated with one or more principals who are granted theData Catalog Fine-Grained Reader role. This enables theseprincipals to access the original, unmasked column data.

Figure 2 shows how column-level access control and data masking worktogether:

Figure 2. Data masking components.

For more information about role interaction, seeHow Masked Reader and Fine-Grained Reader roles interact.For more information about policy tag inheritance, seeRoles and policy tag hierarchy.

Data masking rules

When you use data masking, a data masking rule is applied to a column at queryruntime, based on the role of the user running the query. Masking takesprecedence to any other operations involved in the query. The data masking ruledetermines the type of data masking applied to the column data.

You can use the following data masking rules:

• Custom masking routine.Returns the column's value after applying auser-defined function (UDF)to the column.Routine permissions are required tomanage the masking rule. This rule, by design, supports allBigQuery data typesexcept for theSTRUCT data type.However, support for data types other thanSTRING andBYTES is limited.The output depends on the defined function.

  For more information about creating UDFs for custom masking routines, seeCreate custom masking routines.

• Date year mask. Returns the column's value after truncating the value to itsyear, setting all non-year parts of the value to the beginning of the year. Youcan only use this rule with columns that use theDATE,DATETIME, andTIMESTAMP data types. For example:

  Type	Original	Masked
  DATE	2030-07-17	2030-01-01
  DATETIME	2030-07-17T01:45:06	2030-01-01T00:00:00
  TIMESTAMP	2030-07-17 01:45:06	2030-01-01 00:00:00

  Note: Truncation occurs according to the UTC time zone. To change this,adjust the default time zone using the@@time_zonesystem variable.

• Default masking value. Returns a default masking value for the columnbased on the column's data type. Use this when you want to hide the value ofthe column but reveal the data type. When this datamasking rule is applied to a column, it makes it less useful in queryJOINoperations for users with Masked Reader access. This is because a defaultvalue isn't sufficiently unique to be useful when joining tables.

  The following table shows the default masking value for each data type:

  Data type	Default masking value
  STRING	""
  BYTES	b''
  INTEGER	0
  FLOAT	0.0
  NUMERIC	0
  BOOLEAN	FALSE
  TIMESTAMP	1970-01-01 00:00:00 UTC
  DATE	1970-01-01
  TIME	00:00:00
  DATETIME	1970-01-01T00:00:00
  GEOGRAPHY	POINT(0 0)
  BIGNUMERIC	0
  ARRAY	[]
  STRUCT	NOT_APPLICABLE Policy tags can't be applied to columns that use theSTRUCT data type, but they can be associated with the leaf fields of such columns.
  JSON	null

• Email mask. Returns the column's value after replacing the username of avalid email withXXXXX. If the column's value is not a valid email address,then it returns the column's value after it has been run through theSHA-256 hashfunction. You can only use this rule with columns that use theSTRINGdata type. For example:

  Original	Masked
  abc123@gmail.com	XXXXX@gmail.com
  randomtext	jQHDyQuj7vJcveEe59ygb3Zcvj0B5FJINBzgM6Bypgw=
  test@gmail@gmail.com	Qdje6MO+GLwI0u+KyRyAICDjHbLF1ImxRqaW08tY52k=

• First four characters. Returns the first 4 characters of the column'svalue, replacing the rest of the string withXXXXX. If the column's value isequal to or less than 4 characters in length, then it returns the column'svalue after it has been run through theSHA-256 hashfunction. You can only use this rule with columns that use theSTRING datatype.

• Hash (SHA-256). Returns the column's value after it has been run throughtheSHA-256 hashfunction. Use thiswhen you want the end user to be able to use this column in aJOIN operationfor a query. You can only use this rule with columns that use theSTRINGorBYTES data types.

  The SHA-256 function used in data masking is type preserving, so the hashvalue it returns has the same data type as the column value. For example, thehash value for aSTRING column value also has aSTRING datatype.

  Important: SHA-256 is a deterministic hashing function; an initial value alwaysresolves to the same hash value. However, it does not require encryption keys.This makes it possible for a malicious actor to use a brute force attack todetermine the original value, by running all possible original values throughthe SHA-256 algorithm and seeing which one produces a hash that matches the hashreturned by data masking.

• Random hash. Returns a hash of the column's value using a salted hashalgorithm. Random hash provides stronger security than the standardHash(SHA-256) rule.

  • Non-deterministic: A unique random value (salt) is generated foreach query. The same column value produces different hash resultsacross different queries. This helps prevent brute-force attacks andanalysis of masked data patterns over time.
  • Joinability control:
    • Joins on columns masked withRANDOM_HASH are only possiblewithinthe same query.
    • Joins across different queries aren't possible because of theper-query random salt.
    • Joins are supported only if the data policies applied to the columnsbelong to thesame Google Cloud project. This is enforced byincluding the data policy's project ID in the hash input.
  • Use case: Suitable when you need the referential integrity ofhashing for joins within a query but require stronger security levelagainst offline attacks than standardSHA-256.
  • Supported data types: Can be used with columns ofSTRING orBYTES data types.
  • Limitations:
    • Random hash is a predefined masking rule, and it can't be used incustom masking routines.
    • Random hash is only supported with data policies that are set oncolumns, not policy tags.

• Last four characters. Returns the last 4 characters of the column's value,replacing the rest of the string withXXXXX. If the column's value is equalto or less than 4 characters in length, then it returns the column's valueafter it has been run through theSHA-256 hashfunction. You can only use this rule with columns that use theSTRING datatype.

• Nullify. ReturnsNULL instead of the column value. Use this when youwant to hide both the value and the data type of the column. When this datamasking rule is applied to a column, it makes it less useful in queryJOINoperations for users with Masked Reader access. This is because aNULLvalue isn't sufficiently unique to be useful when joining tables.

Data masking rule hierarchy

You can configure up to nine data policies for a policy tag, each with adifferent data masking rule associated with it. One of thesepolicies is reserved forcolumn-level access control settings.This makes it possible for several data policies to be applied to acolumn in a user's query, based on the groups that the user is a member of.When this happens, BigQuery chooses which data masking rule toapply based on the following hierarchy:

1. Custom masking routine
2. Random Hash
3. Hash (SHA-256)
4. Email mask
5. Last four characters
6. First four characters
7. Date year mask
8. Default masking value
9. Nullify

For example, user A is a member of both the employees and the accountinggroups. User A runs a query that includes thesales_total field, whichhas theconfidential policy tag applied. Theconfidential policy tag hastwo data policies associated with it: one that has the employees role as theprincipal and applies the nullify data masking rule, and one that has theaccounting role as the principal and applies the hash (SHA-256) data maskingrule. In this case, the hash (SHA-256) data masking rule is prioritized overthe nullify data masking rule, so the hash (SHA-256) rule is applied to thesales_total field value in user A's query.

Figure 3 shows this scenario:

Figure 3. Data masking rule prioritization.

Roles and permissions

Roles for managing taxonomies and policy tags

You need the Data Catalog Policy Tag Admin role to create andmanage taxonomies and policy tags.

Roles for creating and managing data policies

You need one of the following BigQuery roles to create and managedata policies:

Roles for attaching policy tags to columns

You need thedatacatalog.taxonomies.get andbigquery.tables.setCategorypermissions to attach policy tags to columns.datacatalog.taxonomies.get is included in theData Catalog Policy Tags Admin and Viewer roles.bigquery.tables.setCategory is included in theBigQuery Admin (roles/bigquery.admin) andBigQuery Data Owner (roles/bigquery.dataOwner) roles.

Roles for querying masked data

You need theBigQuery Masked Readerrole to query the data from a column that has data masking applied.

How Masked Reader and Fine-Grained Reader roles interact

Data masking builds on top of column-level access control. For a given column,it is possible to have some users with the BigQuery Masked Readerrole that allows them to read masked data, some users with theData Catalog Fine-Grained Reader role that allows themto read unmasked data, some users with both, and some users with neither. Theseroles interact as follows:

• User with both Fine-Grained Reader and Masked Reader roles: what the user seesdepends on where in the policy tag hierarchy each role is granted. For moreinformation, seeAuthorization inheritance in a policy tag hierarchy.
• User with Fine-Grained Reader role: can see unmasked (unobscured) column data.
• User with Masked Reader role: can see masked (obscured) column data.
• User with neither role: permission denied.

In the case where a table has columns that are secured or secured andmasked, in order to run aSELECT * FROM statement on that table,a user must be a member of appropriate groups such that they are grantedMasked Reader or Fine-Grained Reader roles on all of these columns.

A user who is not granted these roles must instead specify only columns thatthey have access to in theSELECT statement, or useSELECT * EXCEPT(restricted_columns) FROM to exclude the secured ormasked columns.

Authorization inheritance in a policy tag hierarchy

Roles are evaluated starting at the policy tag associated with a column,and then checked at each ascending level of the taxonomy, until the user eitheris determined to have appropriate permissions or the top of the policy taghierarchy is reached.

For example, take the policy tag and data policy configuration shownin Figure 4:

Figure 4. Policy tag and data policy configuration.

You have a table column that is annotated with theFinancial policy tag,and a user who is a member of both the ftes@example.com and analysts@example.comgroups. When this user runs a query that includes the annotated column, theiraccess is determined by the hierarchy defined in thepolicy tag taxonomy. Because the user is grantedthe Data Catalog Fine-Grained Reader role by theFinancialpolicy tag, the query returns unmasked column data.

If another user who isonly a member of the ftes@example.com role runs a query that includes theannotated column, the query returns column data that has been hashed usingthe SHA-256 algorithm, because the user is granted the BigQueryMasked Reader role by theConfidential policy tag, which is the parent oftheFinancial policy tag.

A user who is not a member of either of thoseroles gets an access denied error if they try to query the annotated column.

In contrast with the preceding scenario, take the policy tag and data policyconfiguration shown in Figure 5:

Figure 5. Policy tag and data policy configuration.

You have the same situation as shown in Figure 4, but the user is granted theFine-Grained Reader role at a higher level of the policy tag hierarchy, and theMasked Reader role at a lower level of the policy tag hierarchy.Because of this, the query returns masked column data for this user. Thishappens even though the user is granted the Fine-Grained Readerrole further up the tag hierarchy, because the service uses the firstassigned role it encounters as it ascends the policy tag hierarchy to checkfor user access.

If you want to create a single data policy and have it apply to several levelsof a policy tag hierarchy, you can set the data policy on the policy tag thatrepresents the topmost hierarchy level to which it should apply. For example,take a taxonomy with the following structure:

• Policy tag 1
  • Policy tag 1a
    • Policy tag 1ai
  • Policy tag 1b
    • Policy tag 1bi
    • Policy tag 1bii

If you want a data policy to apply to all of these policy tags, set the datapolicy on policy tag 1. If you want a data policy to apply to policy tag 1b andits children, set the data policy on policy tag 1b.

Data masking with incompatible features

When you useBigQuery features that aren't compatible with data masking,the service treats the masked column as a secured column, and only grants accessto users who have the Data Catalog Fine-Grained Reader role.

For example, take the policy tag and data policy configuration shownin Figure 6:

Figure 6. Policy tag and data policy configuration.

You have a table column that is annotated with theFinancial policy tag, anda user who is a member of the analysts@example.com group. When this user triesto access the annotated column through one of the incompatible features, theyget an access denied error. This is because they are granted theBigQuery Masked Reader byFinancial policy tag, but in thiscase, they must have the Data Catalog Fine-Grained Reader role.Because the service has already determined an applicable role for the user, itdoes not continue to check farther up the policy tag hierarchy for additionalpermissions.

Data masking example with output

To see how tags, principals, and roles work together, consider thisexample.

At example.com, basic access is granted through the data-users@example.comgroup. All employees who need regular access to BigQuery dataare members of this group, which is assigned all the necessary permissions toread from tables as well as the BigQuery Masked Reader role.

Employees are assigned to additional groups that provide access to secured ormasked columns where that is required for their work. All members of theseadditional groups are also members of data-users@example.com. You can see howthese groups are associated with appropriate roles in Figure 7:

Figure 7. Policy tags and data policies for example.com.

The policy tags are then associated with table columns, as shown in Figure 8:

Figure 8. Example.com policy tags associated with table columns.

Given the tags that are associated with the columns, runningSELECT * FROM Accounts; leads to thefollowing results for the different groups:

• data-users@example.com: This group has been granted theBigQuery Masked Reader role on both thePII andConfidentialpolicy tags. The following results are returned:

  SSN	Priority	Lifetime value	Creation date	Email
  NULL	""	0	March 8, 1983	NULL
  NULL	""	0	December 29, 2009	NULL
  NULL	""	0	July 14, 2021	NULL
  NULL	""	0	May 5, 1997	NULL

• accounting@example.com: This group has been granted theData Catalog Fine-Grained Reader role on theSSNpolicy tag. The following results are returned:

  SSN	Priority	Lifetime value	Creation date	NULL
  123-45-6789	""	0	March 8, 1983	NULL
  234-56-7891	""	0	December 29, 2009	NULL
  345-67-8912	""	0	July 14, 2021	NULL
  456-78-9123	""	0	May 5, 1997	NULL

• sales-exec@example.com: This group has been granted theData Catalog Fine-Grained Reader role on theConfidentialpolicy tag. The following results are returned:

  SSN	Priority	Lifetime value	Creation date	Email
  NULL	High	90,000	March 8, 1983	NULL
  NULL	High	84,875	December 29, 2009	NULL
  NULL	Medium	38,000	July 14, 2021	NULL
  NULL	Low	245	May 5, 1997	NULL

• fin-dev@example.com: This group has been granted theBigQuery Masked Reader role on theFinancialpolicy tag. The following results are returned:

  SSN	Priority	Lifetime value	Creation date	Email
  NULL	""	Zmy9vydG5q=	March 8, 1983	NULL
  NULL	""	GhwTwq6Ynm=	December 29, 2009	NULL
  NULL	""	B6y7dsgaT9=	July 14, 2021	NULL
  NULL	""	Uh02hnR1sg=	May 5, 1997	NULL

• All other users: Any user who does not belong to one of the listedgroups gets an access denied error, because they haven't been granted theData Catalog Fine-Grained Reader orBigQuery Masked Reader roles. To query theAccounts table, they must instead specify only columns thatthey have access to in theSELECT * EXCEPT(restricted_columns) FROM Accounts to exclude thesecured or masked columns.

Cost considerations

Data masking might indirectly affect the number of bytes processed, andtherefore affect the cost of the query. If a user queries a column that ismasked for them with the Nullify or Default Masking Value rules, then thatcolumn isn't scanned at all, resulting in fewer bytes processed.

Restrictions and limitations

The following sections describe the categories of restrictions and limitationsthat data masking is subject to.

Data policy management

• This feature may not be available when using reservations that are createdwith certain BigQuery editions. For more information aboutwhich features are enabled in each edition, seeIntroduction to BigQuery editions.
• You can create up to nine data policies for each policy tag. One of thesepolicies is reserved forcolumn-level access control settings.
• Data policies, their associated policy tags, and any routines that use themmust be in the same project.

Policy tags

• The project containing the policy tag taxonomy must belong to an organization.

• A policy tag hierarchy can be no more than five levels deep from the root nodeto the lowest-level subtag, as shown in the following screenshot:

  

Set access control

After a taxonomy has a data policy associated with at least one of its policytags,access controlis automatically enforced. If you want to turn off access control, you mustfirst delete all of the data policies associated with the taxonomy.

Materialized views and repeated record masking queries

If you have existing materialized views, repeated record masking queries on theassociated base table fail. To resolve this issue, delete thematerialized view. If the materialized view is needed for other reasons, youcan create it in another dataset.

Query masked columns in partitioned tables

Queries that include data masking on the partitioned or clusteredcolumns are not supported.

SQL dialects

Legacy SQL is not supported.

Custom masking routines

Custom masking routines are subject to the following limitations:

• Custom data masking supports allBigQuery data typesexceptSTRUCT, because data masking can only apply to leaf fields of theSTRUCT data type.
• Deleting a custom masking routine doesn't delete all data policies that useit. However, the data policies that use the deleted masking routine are leftwith an empty masking rule. Users with the Masked Reader role on other datapolicies with the same tag can see masked data. Others see the messagePermission denied. Dangling references to empty masking rules might becleaned by automated processes after seven days.

Compatibility with other BigQuery features

BigQuery API

Not compatible with thetabledata.list method. Tocalltabledata.list, you need full access to all of the columns returned bythis method. The Data Catalog Fine-Grained Reader role grantsappropriate access.

BigLake tables

Compatible. Data masking policies are enforced onBigLaketables.

BigQuery Storage Read API

Compatible. Data masking policies are enforced in the BigQuery Storage Read API.

BigQuery BI Engine

Compatible. Data masking policies are enforced in the BI Engine.Queries that have data masking in effect are not accelerated byBI Engine. Use of such queries in Looker Studio mightcause related reports or dashboards to become slower and more expensive.

BigQuery Omni

Compatible. Data masking policies are enforced on theBigQuery Omni tables.

Collation

Partially compatible. You can apply DDM to collated columns, but masking isapplied before collation. This order of operations can lead to unexpectedresults, as collation might not affect the masked values as intended (forexample, case-insensitive matching might not work after masking). Workaroundsare possible, such as using custom masking routines that normalize data beforeapplying the masking function.

Copy jobs

Not compatible. To copy a table from source to the destination, you needto have full access to all of the columns on the source table. TheData Catalog Fine-Grained Reader role grants appropriate access.

Data export

Compatible. If you have the BigQuery Masked Reader role, thenthe exported data is masked. If you have the Data CatalogFine-Grained Reader role, then the exported data is not masked.

Row-level security

Only compatible with queries that have non-subquery row access policies. Data maskingis applied on top of row-level security. For example, if there is a row accesspolicy applied onlocation = "US" andlocation is masked, then users areable to see rows wherelocation = "US" but the location field is masked in theresults.

Queries involving a subquery row access policy require Fine-Grained Readeraccess on columns referenced by row access policies.

Search in BigQuery

Partially compatible. You can call theSEARCHfunction on indexed or unindexed columns that have data masking applied.

When you call theSEARCH function on columns that have data masking applied,you must use search criteria compatible with your level of access. For example,if you have Masked Reader access with a Hash (SHA-256) data masking rule, youwould use the hash value in yourSEARCH clause, similar to the following:

SELECT*FROMmyDataset.CustomersWHERESEARCH(Email,"sg172y34shw94fujaweu");

If you have Fine-Grained Reader access, you would use the actual columnvalue in yourSEARCH clause, similar to the following:

SELECT*FROMmyDataset.CustomersWHERESEARCH(Email,"jane.doe@example.com");

Searching is less likely to be useful if you haveMasked Reader access to a column where the data masking rule used is Nullify orDefault Masking Value. This is because the masked results you would use assearch criteria, such asNULL or"", aren't sufficiently unique to beuseful.

When searching on an indexed column that has datamasking applied, the search index is only used if you have Fine-Grained Readeraccess to the column.

Snapshots

Not compatible. To create a snapshot of a table, you need full access to all ofthe columns on the source table. The Data Catalog Fine-GrainedReader role grants appropriate access.

Table renaming

Compatible. Table renaming is not affected by data masking.

Time travel

Compatible with bothtime decorators and theFOR SYSTEM_TIME AS OFoption inSELECT statements. The policy tags for the current dataset schemaare applied to the retrieved data.

Query caching

Partly compatible. BigQuerycaches query resultsfor approximately 24 hours, although the cache is invalidated if changes aremade to the table data or schema before that. In the following circumstance,it is possible that a user who does not have theData Catalog Fine-Grained Reader role granted on a column canstill see the column data when they run a query:

1. A user has been granted the Data Catalog Fine-Grained Readerrole on a column.
2. The user runs a query that includes the restricted column and the data iscached.
3. Within 24 hours of Step 2, the user is granted the BigQueryMasked Reader role, and has the Data Catalog Fine-Grained Readerrole revoked.
4. Within 24 hours of Step 2, the user runs that same query, and the cacheddata is returned.

Wildcard table queries

Not compatible. You need full access to all of the referenced columns on all ofthe tables matching the wildcard query. The Data CatalogFine-Grained Reader role grants appropriate access.

What's next

• Get step-by-step instructions to enabledata masking.