Policy Implemetation

The Policy Implementation article outlines the enforcement capabilities supported by the Consolidated SQL Authorizer when applying PlainID Policies to SQL-based systems. These capabilities allow fine-grained control over query results by dynamically modifying SQL statements based on user identity, context, and Policy decisions.

This article focuses on core enforcement features such as Row-Level Filtering, Column-Level Filtering, Conditional Masking, and SELECT * resolution, with a dedicated section on advanced masking capabilities—including static, conditional, and function-based masking behaviors. It also provides practical examples that illustrate how masking logic is applied under different Policy configurations.

After deploying and integrating your patterns, you can start using the capabilities below.

Google BigQuery

Google BigQuery is supported by the SQL Authorizer, enabling enforcement of PlainID Policies at the query level. This includes applying row- and column-level controls, masking sensitive data, and resolving wildcard selections based on the user’s permissions. BigQuery-specific considerations—such as how Asset Types are mapped to rows and columns—are detailed in the linked article below. Use this section to understand how the core enforcement capabilities behave in the context of BigQuery.
Refer to Google BigQuery for information about Asset Types in rows and columns.

Supported Capabilities

Masking Capabilities

The SQL Database Authorizer includes masking capabilities designed to protect sensitive data while maintaining its usability in various contexts. By implementing these masking techniques, users can ensure that sensitive information remains confidential and compliant with data protection regulations.

Some Masking capabilities require "masking instructions" as part of the PDP Policy Resolution response. These masking instructions derived from the Actions are used in Policy modeling and are returned as part of the PDP calculation.

See the Environment Variables for the Docker Container for information on how to use these masking capabilities as Environment Variables.

Below is a brief overview of each masking capability:

• Masking Null: By default if a column is not permitted, it will be masked as NULL in the modified query. This parameter allows users to set a default masking for any column with an unknown data type which not get more specific masking instruction. The parameters below allow setting specific masking characters for typed columns. DB connectivity may be required for the Authorizer to retrieve the data type. See example in Masking Capability Examples.

• Masking Alphanumeric: This variable allows users to obscure alphanumeric data, ensuring that characters are replaced or altered to prevent unauthorized access while preserving the overall structure. By default alphanumeric values will be masked using asterisks (****), but you can use this variable to define any other character for masking.

• Masking Numeric: Numeric values can be masked to hide sensitive numeric information, such as age, salary, phone number, social security number, etc., ensuring that only authorized users can view the actual figures. By default, numeric values are masked as '0' (zero) but you can use this variable to define any other number for masking, like '999', '-1' etc.

• Masking Date: Date columns can be masked to secure sensitive dates such as Date of Birth and others. By default masked date columns use 00/00/0000 but can be adjusted that with this variable.

• Masking Default Mask as String: This Boolean variable guides the Authorizer to use string masking as default instead of NULL. If set to 'true' the default masking uses the masking characters defined for MASKING_DEFAULT_STRING_MASKING.

• Masking Default String Masking: This variable is used as the default masking when MASKING_DEFAULT_MASK_AS_STRING is set to 'true'.

• Making Supported Functions: This variable is applied when using database functions (native or custom) as masking functions, guided by Actions in the Policy Resolution response.. The variable holds a list of functions. If the Action associated with column access is included in this list, masking is applied to the column by using the specified function, which will execute when the query is sent to the database.

• Masking within a Function: If the original query includes columns within functions, and these columns require masking according to Policies, masking is applied directly to the column within the function. This ensures that function use does not bypass masking and expose unauthorized data. This is not a configurable variable, but a generic masking behavior. See example in Masking Capability Examples.

• Conditional Masking: This advanced feature applies masking based on specified Conditions or Rules, ensuring that data is only revealed to authorized users under predefined circumstances.
  By setting specific Actions in the Conditional_Masking_Actions variable, users can direct the Authorizer to apply conditional masking based on privilege response sections, rather than default row-level filtering. The order of Actions in this variable determines the sequence in which conditions are applied to the masked column.
  To create a conditional masking policy, connect conditions within the same policy using Rulesets on a data asset type that represents a table, alongside a predefined Action and specific columns (as defined by a separate asset type). This predefined Action is then set in the Conditional_Masking_Actions variable, enabling conditional masking instead of row filtering.
  See example in Masking Capability Examples.

Masking Capability Examples

Below are a few examples of Masking Capability SQL requests and responses based on an original query labelled accordingly:

Original Query

SELECT id, fullName, dateOfBirth, ssn, location, revenue, customerType 
FROM Customers c
WHERE c.customerType = 'private'

Full Query Response Table

Masking Null Example
SQL Query

SELECT id, NULL as fullName, NULL as dateOfBirth, NULL as ssn, location, NULL as revenue, customerType 
FROM Customers c
WHERE c.customerType = 'private'

Response Table:

Masking Default String Masking Example
SQL Query

SELECT id, '****' as fullName, '00/00/0000' as dateOfBirth, 0 as ssn, location, 0 as revenue, customerType 
FROM Customers c
WHERE c.customerType = 'private'

Response Table

Masking Function for SSN - Keeping the last three characters
SQL Query

SELECT id, NULL as fullName, NULL as dateOfBirth, ThreeCharsMasking(ssn) as ssn, location, NULL as revenue, customerType 
FROM Customers c
WHERE c.customerType = 'private'

Response Table

Masking within a Function Example

This function allows sensitive data to be masked even when used as arguments in functions. This ensures that any sensitive information processed remains protected throughout the execution of queries.

Original SQL Query:

SELECT id, CONCAT(firstName,lastName), DATE_PART('year', dateOfBirth) as yearOfBirth, ssn, location, ROUND(revenue) as revenue, customerType FROM Customers c

Modified SQL Query:

SELECT 
  id, CONCAT('***', lastName), DATE_PART('year', '00/00/0000') as yearOfBirth,  ssn,  location, ROUND(0) as revenue, customerType FROM Customers c

Response Table

Conditional Masking Example:
In this example, we demonstrate how multiple conditional masking policies interact to determine the masking outcome for sensitive data, specifically Social Security Numbers (SSNs).

Policies Defined:

Policy 1: Mask SSN with ‘*’ when the location is ‘NY’.
Policy 2: Mask SSN using a function that keeps the last three characters when the account type is ‘Private’.
Policy 3: Apply Access action on records when location is not ‘CH’.

Conditional Masking Actions Configured: asteriskMasking, 3chars (in this order).

Outcome:

1. David: SSN masked according to Policy 2 (keeping the last three characters): ------789.
2. John: Both Policy 1 and Policy 2 apply, resulting in conflicting masking rules. The masking is resolved based on the order of conditional masking actions, resulting in full masking as per Policy 1: *********.
3. Robert: SSN masked according to Policy 1: *********.
4. Alice: No Policies apply, so the SSN remains unmasked: 8734651295.
5. Sidney: Although Sidney's SSN fits Policy 2, the record is filtered out completely due to Policy 3 (location = 'CH').

Table Data Overview:

HasValue Masking

The HASVALUE function enables conditional masking based on whether a specific Attribute has a value. It supports checks for NULL, EMPTY, or both (NULLANDEMPTY), and is typically used in Calculated Attributes to control when sensitive data should be shown or masked.
To use this capability, define a Calculated Attribute that evaluates the presence of another Attribute.
Example:

HASVALUE({{role}}, NULLANDEMPTY)

This expression returns true only when the role Attribute is present and not empty. In the SQL Database Authorizer, Policies using this logic can dynamically trigger masking—hiding columns for users who lack the required Attribute. The Authorizer automatically translates this condition into SQL logic, such as IS NULL or empty string checks, ensuring enforcement is based on the completeness of identity data.

For more information about how HASVALUE is defined and used in Calculated Attributes, refer to Working with Calculated Attributes.

Example: HasValue Masking

Original Query

SELECT id, fullName, dateOfBirth, ssn, location, revenue, customerType 
FROM Customers c
WHERE c.customerType = 'private'

Policy Condition

HASVALUE({{role}}, NULLANDEMPTY)

This condition instructs the Authorizer to mask data for users whose role Attribute is either null, an empty string, or an empty array.

Masked Query for Users Without Role Value (HASVALUE = false)

SELECT id, NULL as fullName, NULL as dateOfBirth, NULL as ssn, location, NULL as revenue, customerType 
FROM Customers c
WHERE c.customerType = 'private'

Response Table

Known Limitations

• Database Connectivity Required: Some features—such as SELECT * resolution and type-aware masking—require a live connection to the database.
• Masking Type Limitations: Certain masking types may be restricted based on the underlying database engine.