Authorizer Service

About SQL Database Authorizer Service

The SQL Database Authorizer functions as a proxy service, receiving intercepted SQL queries sent to databases, whether from PlainID's SQL Libraries or directly from an Application, and modifying them dynamically according to PlainID's policy-based Authorization calculations. This process not only enhances data integrity and security by guaranteeing that only authorized data is retrieved through query execution, but it also safeguards sensitive information and ensures compliance with Access Control Policies.

This Authorizer supports standard SQL queries, including the basic SQL statement clauses such as SELECT, FROM, and WHERE. It also supports more complex SQL statements using JOIN, and even WITH clauses.

Note: If using different SQL statements or query options, consult with PlainID Support for support or required enhancements.

Running the SQL Database Authorizer Service via Docker

Make sure the necessary credentials are obtained from the PlainID team. These credentials are vital for downloading the Docker image.

To run the Authorizer Service:

1. Launch the service within a Docker container.
2. Determine the PDP endpoint URL. This URL specifies which Runtime service the Authorizer connects to.

Example of a Docker command to execute the service:

docker run -d -t -i \

-e RUNTIME_URL=http://policy-runtime.plainid-pdp/api/runtime/resolution/v3 \

-e HTTP_PORT=8080 \

-e DB_USER=sa \

-e DB_PWD=XXXXX \

-e DB_HOST=YOUR_MSSQL_SERVER \

-e DB_PORT=1433 \

-e DB_DEFAULT=testdb \

-e DB_ENABLED=true \

-e DB_DRIVER=sqlserver \

-e LOGGING_ROOT_LEVEL=trace \

-p 8080:8080 \

--name authz-sql-pdp-modifier plainid/authz-sql-pdp-modifier:latest

Environment Variables for the Docker Container

Environment variables used within the Docker container:

Example PDP response
This response illustrates COLUMN_ATTR, TABLE_ATTR, and COLUMNS_RESOURCE_TYPE Attribute definitions:

"response": [
        {
            "access": [
                {
                    "path": "employees.first_name",
                    "attributes": {
                        "column": [
                            "first_name"
                        ],
                        "table": [
                            "employees"
                        ]
                    },
                    "resourceType": "columns",
                    "actions": [
                        {
                            "action": "ACCESS"
                        }
                    ]
                }
            ],

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:

Running the SQL Database Authorizer Service via Helm Chart

The SQL Database Authorizer service is deployed using the Helm Chart.
Currently, the Helm Chart is provided by the PlainID team separately.

The chart is provided as a tgz file that looks like this: authz-sql-pdp-modifier-1.0.0-fd6d953f.tgz.

To run the Authorizer via Helm Chart:

1. Extract the tgz file.
2. Edit the values.yaml file and set the mandatory fields for the chart like the pdp-service URL, database, connectivity details, etc.*
3. Run the Helm install command to deploy the chart. You can use key=value pairs to bypass the values specified in the values.yaml file without modifying it directly. For example:

helm install authz-sql-pdp-modifier . --namespace YOUR_NAMESPACE --set key1=value1,key2=value2,etc ...

The values.yaml in the chart contain further details about the fields.

Note that the chart contains a README.md file with further details on how to deploy it.

API Overview

The ReSQL API is a powerful tool that enables you to modify SQL queries over HTTP. It provides several customization options, encapsulated within a request, allowing the user to fine-tune their SQL query behavior based on PlainID policies. The API also returns a structured JSON response, containing potential error messages and indicating whether the query was modified.

API Endpoint

URL: http://ADDRESS_OF_AUTHORIZER_SERVICE/resql

Method: POST

Request

The request is a JSON object with the following parameters:

The flags field is another JSON object with the parameters described in the C# library section above.

Response

The API returns a JSON object representing the SQL query result with the following parameters:

Example Request

{
    "clientId": "xxx",
    "clientSecret": "yyy",
    "user": "zzz",
    "entityTypeId": "User",
    "sql": "select uid,e_name, salary, location from employees",
    "flags": {
        "oppositeColumnFilteringBehavior": true,
        "runtimeCLSAsMasked": true
     }
}

Example Response

{
    "sql": "SELECT uid, e_name, NULL AS salary, location FROM employees WHERE location = 'Chicago' OR location = 'New York'",
    "wasModified": true,
    "error": ""
}

In the example response, the wasModified field is true, indicating that the SQL query was modified by the API. The star (*) column expands into specific fields. The error field is an empty string, indicating that the query was executed successfully.