Integration Patterns

The SQL Database Authorizer supports multiple integration patterns to accommodate different architectures and development environments. You can interact with the Authorizer via a standard REST API, or use one of the available SDKs for Java or .NET to simplify integration and reduce implementation effort. All options provide consistent behavior and support the same enforcement capabilities, including row-level filtering, column masking, and conditional logic. Choose the pattern that best fits your system design and technology stack.

• REST API
• Java SDK
• .NET SDK

API Endpoint

You can use this API in a variety of deployment scenarios—such as data services, SQL proxies, or middleware—to ensure that access control is applied consistently before SQL queries reach your database or Google BigQuery{target=_blank} engine. The Authorizer evaluates incoming SQL statements against PlainID Policies and returns a rewritten version that enforces row-level filtering, column-level restrictions, and masking as needed.

To explore and integrate with the API, you can either download the full OpenAPI specification or use our Postman collection to test requests and understand response behavior interactively.

Download the OpenAPI Spec

Postman Collection

Use these resources to:

• Review supported request fields, response structures, and error handling
• Explore enforcement behaviors like masking and column filtering
• Test different configurations and enforcement flags using your own SQL queries

Once familiar with the structure, you can start integrating the API directly into your authorization flow. For SDK-based integration options, see the Integration Patterns guide.

Request Format

The API accepts a JSON request body with the SQL query and metadata required for Policy evaluation.

Core Parameters

Enforcement Flags

Example Requests

Simple Query

{
  "sql": "SELECT e1.first_name, e1.last_name, e1.department_id FROM employees e1 WHERE e1.first_name = 'bardak'",
  "user": "Jordan",
  "clientID": "PBMHCUXHGCQ629H234Q7",
  "clientSecret": "B0JCgbFFWbNIls3NjKy42352v0jvL2JAzb2345dg",
  "entityAttributes": {
    "DemoGroup": [{}]
  },
  "flags": {
    "expandStarColumn": true,
    "emptyRLSTreatAsDenied": false,
    "emptyCLSTreatAsPermitted": false,
    "ignoreRuntimeCLSResponse": false,
    "oppositeColumnFilteringBehavior": false,
    "policiesJoinOperation": "OR",
    "runtimeCLSAsMasked": true,
    "columnsResourceType": ""
  }
}

Complex Query

{
  "sql": "WITH ranked_salaries AS (SELECT employee_id, amount, ROW_NUMBER() OVER (ORDER BY amount DESC) AS salary_rank FROM salaries) SELECT e.id, e.first_name, e.last_name, rs.amount FROM employees e, ranked_salaries rs WHERE e.id = rs.employee_id AND rs.salary_rank <= 5 ORDER BY rs.amount DESC",
  "entityTypeId": "Main",
  "clientID": "PBMHCUXHGCQ629H234Q7",
  "clientSecret": "B0JCgbFFWbNIls3NjKy42352v0jvL2JAzb2345dg",
  "flags": {
    "expandStarColumn": true,
    "emptyRLSTreatAsDenied": false,
    "emptyCLSTreatAsPermitted": false,
    "ignoreRuntimeCLSResponse": false,
    "oppositeColumnFilteringBehavior": true,
    "policiesJoinOperation": "OR",
    "runtimeCLSAsMasked": true,
    "columnsResourceType": ""
  }
}

Example Response

{
  "sql": "SELECT e1.first_name, e1.last_name, NULL AS department_id FROM employees e1 WHERE e1.first_name = 'bardak' AND e1.location = 'US'",
  "wasModified": true,
  "error": ""
}

Response Fields

Error Example

{
  "sql": "",
  "wasModified": false,
  "error": "Error processing query: Error runtime HTTP status: 424, msg: Read timed out, agentName: internal_asset"
}

SDK Support

To streamline integration into your codebase, PlainID provides SDKs that wrap the REST API and handle request generation, flag encoding, and response parsing.

• 🔧 Java SDK
• 🛠 .NET SDK

Use these SDKs to avoid manual request building and ensure consistency with evolving API standards.

Java SDK

This library enables the use of the PlainID SQL-PDP service with Java SpringBoot and applies SQL filtering
rules to SQL queries generated by SpringBoot Hibernate.
A minimum Java version of 1.8 and Spring Boot version 2.0.0 or higher is required.

To include this library using Maven:
Add the following dependency to your pom.xml:

<dependency>
    <groupId>com.plainid.libs</groupId>
    <artifactId>plainid-sql-pdp-auth-lib</artifactId>
    <version>1.2.17</version>
</dependency>

Initializing the Library Configuration

Configure the library by adding the following settings to your application.yamlor application.properties. Alternatively, you can choose not to define properties in your application.properties file and instead, set everything through Environment variables:

plainid.sqlpdp.pdpUrl=http://localhost:8080/sql
plainid.sqlpdp.clientID=XXXXXX
plainid.sqlpdp.clientSecret=XXXXXXXXXX
plainid.sqlpdp.entityTypeId=Main
plainid.sqlpdp.emptyCLSTreatAsPermitted=true
plainid.sqlpdp.emptyRLSTreatAsDenied=false
plainid.sqlpdp.ignoreRuntimeCLSResponse=true
plainid.sqlpdp.ignoreSqlPdpServiceErrors=true 
plainid.sqlpdp.oppositeColumnFilteringBehavior=true
plainid.sqlpdp.expandStarColumn=true
plainid.sqlpdp.policiesJoinOperation=OR
plainid.sqlpdp.runtimeCLSAsMasked=true
plainid.sqlpdp.requestMapping='[JSON_ARRAY_OF_REQUEST_MAPPING_CONFIGS]'
plainid.sqlpdp.jwtPassthrough=true
plainid.sqlpdp.entityIdJwtMapping=user_id
plainid.sqlpdp.columnsResourceType=CustomColumns

To configure the way a request is sent to the PDP service, create a bean of type
QueryModificationFlags and set the desired flags according to the following parameters or set everything via the Environment variables:

@Bean
public QueryModificationFlags queryModificationFlags() {
    return new QueryModificationFlags(true, true, false, true, true, "OR", true);
}

Request Mapping

The parameter accepts configuration properties that define mappings between various sources and their mapping targets in the PDP/Runtime calls. A source refers where data is retrieved from and a target specifies where the data should be mapped.

Sources

The following sources are supported:

• headers: Data is retrieved from the request headers.
• url: Data is extracted from the request URL based on a regex pattern.
• qs (query-string parameter): Data is retrieved from the query string parameters of the request.
• env (environment variable): Data is retrieved from the environment variables of the application.

Targets

The following targets are supported:

• entityAttributes: The data is mapped to the entityAttributes section in the Policy resolution call.
• environment: The data is mapped to the Environment section in the Policy resolution call.
• contextData: The data is mapped to the contextData section in the Policy resolution call.

Configuration Structure

The configuration parameter is passed as a key / value mapping entries. Each object should have the following structure:

{ "source.sourceKey": "target.targetKey" }

Fields:

• source: Specifies the source type. It can be headers, url, env or qs.
• sourceKey: For headers it is the header to look for, following an optional possible JSON path extraction rule, for qs, this is the exact key to look for. For url, this is a regex pattern to match and extract data.
• target: Specifies the target type. It can be entityAttributes, environment, or contextData.
• targetKey: This is the key under which the data from the source is stored in the target.

Example of a configuration:

{
{
  "env.MY_ENV_VAR": "entityAttributes.myEnvVar",
  "headers.x-user-info.$.user.department": "contextData.userInfo",
  "headers.Accept-Encoding": "entityAttributes.Accept-Encoding-Target",
  "qs.difficulty": "environment.difficulty",
  "url./api/([^/]+)(/|$)": "environment.urlPartExample"
}

• Data from the MY_ENV_VAR environment variable is mapped to entityAttributes section under the key myEnvVar.
• Data from the x-user-info header is extracted and mapped through json path $.user.department to contextData section under the key userInfo.
• Data from the Accept-Encoding header is mapped to contextData section under the key Accept-Encoding-Target.
• Data from the difficulty querystring parameter is mapped to the environment section.
• Data matching the regex pattern /api/([^/]+)(/|$) from the URL is mapped to entityAttributes under the key urlPartExample, for example this extracts users from the URL http://localhost:8080/api/users.

The configuration should be passed as a JSON string to the plainid.sqlpdp.requestMapping property or environment variable: PLAINID_RUNTIME_REQUEST_MAPPING.

Using Context and Authorization

To fetch the current user and security context sent to the SQL Database Authorizer Service, the library retrieves the information through a call to Spring Security Core Context by default:

 Authentication authentication=SecurityContextHolder.getContext().getAuthentication();
String u=authentication==null null:authentication.getName();

To override this behavior and inject your user ID and context, use the following code before performing database actions:

 com.plainid.sqlpdplib.Context.setUsername("YOUR_USERNAME");

To inject entity attributes within the Runtime for further Policy resolution based on Dynamic Groups, insert the following code before performing any database actions:

EntityAttributes entityAttributes = new EntityAttributes();
entityAttributes.addAttribute("ATTRIBUTE_NAME", "ATTRIBUTE_VALUE");
com.plainid.sqlpdplib.Context.setEntityAttributes(entityAttributes);

.NET SDK

The PlainID SQL PDP Authorizer Library enables the use of the SQL PDP service with Entity Framework Core. It applies SQL filtering rules in addition to the SQL queries generated by Entity Framework Core. The library is compatible with .NET Core 6 and .NET Core 7, and it also supports .NET Core/Standard 2 for ADO.NET-like solutions (see the relevant section below).

Installing via NuGET

To install the library, use the following command in your code editor:

dotnet add package PlainID.SQL.PDP.Auth.Lib-v 1.0.13

Quick Library Setup using the "Magic Hook" Method

This method provides a single call and automatically adds the DB interceptor and the authorization middleware to the services collection by calling the HookAuthorizationMiddleware and HookDb:

PlainLibrary.HookAll(IServiceCollection services, Options plainOptions = null)

For advanced setup:

Authorization Middleware:

PlainLibrary.HookAuthorizationMiddleware(IServiceCollection services, Options plainOptions = null)

DB interceptor:

PlainLibrary.HookDb(Options plainOptions = null)

Initializing Library Options

Library options can be configured using the OptionsBuilder. These options can be set through code or environment variables:

 // Reading from environment variables.
  OptionsBuilder builder = new OptionsBuilder();
            builder.LoadFromEnvironmentVariables();
            _options = builder.Build();

 // Assigning options manually.           
 var builder = new OptionsBuilder();
        builder
            .WithClientId("PIXEPN6N4AKKCYES51FL")
            .WithClientSecret("xUGcm3UF2q7NKzSDuNLtfq1utN38H2nF0Exrvr3O")
            .WithModifierServiceURL("https://sdl-pdp-modifier.local.platform.test/resql")
            .WithFlags(new QueryModificationFlags {
                EmptyCLSTreatAsPermitted = true,
                IgnoreRuntimeCLSResponse = false,
                IgnoreSqlPdpServiceErrors = false,
                OppositeColumnFilteringBehavior = true,
                RuntimeCLSAsMasked = true,
                PoliciesJoinOperation = "OR",
                ExpandStarColumn = true,
                JwtPassthrough = true                
                ColumnsResourceType = "CustomColumns"
            });

 PlainLibrary.SetOptions(builder.Build());           

Environment Variables

Request Mapping

The parameter accepts configuration properties that define mappings between various sources and their mapping targets in the PDP/Runtime calls. A source refers to where data is retrieved from, and a target specifies where the data should be mapped.

Sources

The following sources are supported:

• headers: Data is retrieved from the request headers.
• url: Data is extracted from the request URL based on a regex pattern.
• qs (query-string parameter): Data is retrieved from the query string parameters of the request.

Targets

The following targets are supported:

• entityAttributes: The data is mapped to the entityAttributes section in the Policy Resolution call.
• environment: The data is mapped to the Environment section in the Policy Resolution call.
• contextData: The data is mapped to the contextData section in the Policy Resolution call.

Configuration Structure

The configuration parameter is passed as key/value mapping entries. Each object should have the following structure:

{ "source.sourceKey": "target.targetKey" }

Fields

• source: Specifies the source type. It can be headers, url, or qs.
• sourceKey: For headers, it is the header to look for, following an optional possible JSON path extraction rule. For qs, this is the exact key to look for. For url, this is a regex pattern to match and extract data.
• target: Specifies the target type. It can be entityAttributes, environment, or contextData.
• targetKey: This is the key under which the data from the source is stored in the target.

Example of a configuration:

{
  "headers.x-user-info.$.user.department": "contextData.userInfo",
  "headers.Accept-Encoding": "entityAttributes.Accept-Encoding-Target",
  "qs.difficulty": "environment.difficulty",
  "url./api/([^/]+)(/|$)": "environment.urlPartExample"
}

• Data from the x-user-info header is extracted and mapped through the JSON path $.user.department to the contextData section under the key userInfo.
• Data from the Accept-Encoding header is mapped to the contextData section under the key Accept-Encoding-Target.
• Data from the difficulty query string parameter is mapped to the environment section.
• Data matching the regex pattern /api/([^/]+)(/|$) from the URL is mapped to entityAttributes under the key urlPartExample. For example, this extracts users from the request URL /api/users/roles.

The configuration should be passed as a JSON string to the RequestMappingConfigs property or environment variable: PLAINID_RUNTIME_REQUEST_MAPPING.

Setting up the Library Using Dependency Injection

DB Interceptor

This feature translates Entity Framework Core queries going to the SQL Server and modifies them according to PlainID policies.

To add a DB Interceptor:

public void ConfigureServices(IServiceCollection services)
{
    services.AddDbContext<MyDbContext>(options => 
        options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection"))
               .AddInterceptors(new PlainIDbCommandInterceptor()));
}

Authorization Middleware

This adds standard middleware that extracts the user identity and passes it to the PlainID library as user context when calling the SQL-PDP service.

To add default Authorization middleware:

public void ConfigureServices(IServiceCollection services)
{
    services.AddControllersWithViews(options => { options.Filters.Add<PlainAuthFilter>(); });
}

Manually Injecting User Context

To manually inject user context from your Authorization layer middleware (if applicable):

PlainContext.SetUser("SomeUSERID");

Use the following code before performing database actions (if applicable) to inject entity attributes to the PDP for further Policy resolution based on Dynamic Groups:

EntityAttributes entityAttributes = new EntityAttributes();
entityAttributes.AddAttribute("ATTRIBUTE_NAME", "ATTRIBUTE_VALUE");
PlainContext.SetEntityAttributes(entityAttributes);

Example usage within a filtering controller:

public class MyCustomActionFilter : IActionFilter
{
    public void OnActionExecuting(ActionExecutingContext context)
    {
        // Extract the user id from the authorization context.
        var userId = context.HttpContext?.User?.Identity?.Name;
        if (userId != null) {
            // Inject the userId to use in the PlainID filtering library.
            PlainContext.SetUser(userId);
        }
    }
}

Limitations

The library's hook features will not function correctly on arm64 architecture, such as M1 Macs.

Legacy Access Directly to the API

The library can be used to directly access the API without using Entity Framework Core, supporting legacy code or direct use of the functionality.

The following code illustrates how this can be done, directly calling the authorizer-sql service and returning the modified SQL query:

using Microsoft.Extensions.Logging;
using NLog.Config;
using NLog.Extensions.Logging;
using NLog.Targets;
using PlainID.SQL.PDP.Auth.Lib;
using LogLevel = NLog.LogLevel;

public class MainClass {
    static ILogger logger;

    static void createLogger() {
        var config = new LoggingConfiguration();
        var consoleTarget = new ColoredConsoleTarget() {
            Layout = @"${date:format=HH\:mm\:ss} ${logger} ${message}"
        };
        config.AddTarget("console", consoleTarget);
        config.AddRule(LogLevel.Trace, LogLevel.Off, consoleTarget);

        var loggerFactory = new LoggerFactory();
        loggerFactory.AddNLog();
        loggerFactory.ConfigureNLog(config);
        
        logger = loggerFactory.CreateLogger<MainClass>();
    }
    
    public static void Main() {
        createLogger();
        
        logger.LogInformation("Starting Direct-Mode Test");
        
        var optionsBuilder = new OptionsBuilder();
        optionsBuilder.LoadFromEnvironmentVariables();
        optionsBuilder
            .WithClientId("PBMZCUX4GCQ6BQH1CFQ7")
            .WithClientSecret("B0JCgbzMbNIls3NjKy4P12CTv0jvL2JAzbF5T8dg")
            .WithEntityTypeId("Main")
            .WithModifierServiceURL("https://authz-sql-pdp-modifier.local.platform.test/resql")            
            .WithFlags(new QueryModificationFlags {
                EmptyCLSTreatAsPermitted = true,
                IgnoreRuntimeCLSResponse = false,
                IgnoreSqlPdpServiceErrors = true,
                OppositeColumnFilteringBehavior = true,
                RuntimeCLSAsMasked = true
            });
        
        var options = optionsBuilder.Build();
        
        var entityAttributes = new MapArrayAttributes();
        entityAttributes.AddAttribute("DemoGroup", new object[] { "Jordan" });
            
        PlainContext.SetEntityAttributes(entityAttributes);
        PlainContext.SetUser("Jordan");
        PlainLibrary.SetLogger(logger);
        PlainLibrary.SetOptions(options);
        
        AuthServiceApi authServiceApi = new AuthServiceApi();
        var modifiedSql = authServiceApi.CallSqlModifer("SELECT first_name, last_name, department_id FROM employees");
        logger.LogInformation("Modified SQL: {modifiedSql}", modifiedSql);
        
        logger.LogInformation("Ending Direct-Mode Test");
    }
}