PlainID SQL Rewriter - Interactive Demo

Interactive SQL Query Rewriting Demo

See how PlainID dynamically modifies SQL queries based on user context and policies.

●

Select User Context

Admin Full Access

Manager Regional Access

Employee Limited Access

Original SQL Query

SELECT * FROM customers WHERE status = 'active'

◈

Modified Query Result

▣

Query Results

How It Works

PlainID intercepts SQL queries before they reach the database and modifies them based on:

User identity and attributes
Configured PBAC policies
Resource access rules
Environmental context

System Architecture

Query Processing Flow

Application

Sends SQL query with user context

SQL Authorizer

Intercepts and evaluates query

Policy Engine

Applies PBAC policies

Query Rewriter

Modifies SQL based on policies

Database

Executes modified query

▢

Core Components

SQL PDP Service (Docker container)
Policy Decision Point
Integration Libraries (.NET, Spring Boot)
Runtime Resolution API

◈

Configuration

Environment variables
Database connection settings
Runtime URL configuration
Logging levels

▣

Supported Databases

PostgreSQL
Microsoft SQL Server
Amazon Redshift
Google BigQuery
And more...

Docker

docker run -d -t -i \
  -e RUNTIME_URL=http://runtime:8080/api/runtime/resolution/v3 \
  -e HTTP_PORT=8080 \
  -e DB_USER=sa \
  -e DB_HOST=YOUR_MSSQL_SERVER \
  -e DB_PORT=1433 \
  -e DB_DEFAULT=testdb \
  -e DB_ENABLED=true \
  -e DB_DRIVER=sqlserver \
  -p 8080:8080 \
  --name authz-sql-pdp-modifier \
  plainid/authz-sql-pdp-modifier:latest

Key Features

▬

Row-Level Security

Automatically adds WHERE clauses to filter rows based on user permissions. Control data access at the most granular level.

▮

Column-Level Filtering

Dynamically removes or masks columns from SELECT statements based on user attributes and classification levels.

◉

Cell-Level Masking

Combines row and column controls to provide cell-level data protection. Mask sensitive data like SSNs or credit card numbers.

►

Real-Time Processing

Query modification happens in milliseconds, ensuring minimal latency while maintaining security.

◈

Dynamic Policies

Policies are evaluated at runtime based on current context, allowing for adaptive security based on risk signals.

▣

Audit & Compliance

Comprehensive logging of all query modifications for compliance reporting and security auditing.

Query Modification Examples

Example 1: Row Filtering

// Original Query
SELECT * FROM orders

// Modified for Regional Manager
SELECT * FROM orders
WHERE region = 'North America'

Example 2: Column Filtering

// Original Query
SELECT * FROM employees

// Modified for HR Representative
SELECT id, name, department, title
FROM employees
// Salary and SSN columns removed

Example 3: Data Masking

// Original Query
SELECT name, ssn, salary
FROM employees

// Modified with Masking
SELECT name,
CASE WHEN 1=1 THEN 'XXX-XX-' + RIGHT(ssn, 4) END AS ssn,
CASE WHEN role = 'manager' THEN salary ELSE '[REDACTED]' END AS salary
FROM employees

Integration Guide

Select an integration method to view implementation details and code examples.

Spring Boot

Java-based integration with Spring Boot applications using interceptors

Java Spring JPA

.NET Framework

C# integration with Entity Framework Core for .NET applications

C# .NET Core EF Core

Direct API

REST API integration for custom implementations in any language

REST JSON Any Language

Spring Boot Integration

Integrate PlainID SQL Rewriter with your Spring Boot application using our Java SDK:

Step 1: Add Dependencies

XML

<dependency>
    <groupId>com.plainid</groupId>
    <artifactId>sql-authorizer-spring</artifactId>
    <version>2.5.0</version>
</dependency>

Step 2: Configure PlainID Interceptor

Java

// Configuration class
@Configuration
public class PlainIDConfig {
    
    @Bean
    public PlainIDbCommandInterceptor plainIDInterceptor() {
        return new PlainIDbCommandInterceptor();
    }
    
    @Bean
    public DataSource dataSource() {
        HikariDataSource dataSource = new HikariDataSource();
        dataSource.setJdbcUrl("jdbc:postgresql://localhost:5432/mydb");
        dataSource.setUsername("user");
        dataSource.setPassword("password");
        return dataSource;
    }
}

Step 3: Implement Secure Service

Java

@Service
public class SecureDataService {
    
    @Autowired
    private PlainContext plainContext;
    
    @Autowired
    private CustomerRepository customerRepository;
    
    public List<Customer> getCustomers() {
        // Set user context - PlainID will handle the rest
        plainContext.setUser(SecurityContextHolder
            .getContext()
            .getAuthentication()
            .getName());
        
        // Query will be automatically modified based on policies
        return customerRepository.findAll();
    }
    
    public Customer getCustomerById(Long id) {
        plainContext.setUser(getCurrentUser());
        plainContext.setEntityAttributes(
            Map.of("department", "sales", "region", "north")
        );
        
        return customerRepository.findById(id).orElse(null);
    }
}

Step 4: Application Properties

Properties

# PlainID Configuration
plainid.client.id=YOUR_CLIENT_ID
plainid.client.secret=YOUR_SECRET
plainid.modifier.url=http://sql-authorizer:8080
plainid.runtime.url=http://runtime:8080/api/runtime/resolution/v3
plainid.entity.type=customers
plainid.logging.level=INFO

.NET Framework Integration

Integrate with Entity Framework Core in your .NET applications:

Step 1: Install NuGet Package

PowerShell

Install-Package PlainID.SQL.PDP.Auth.Lib -Version 2.5.0

Step 2: Configure in Startup.cs

public class Startup
{
    public void ConfigureServices(IServiceCollection services)
    {
        // Configure PlainID options
        var plainOptions = new Options
        {
            ClientId = Configuration["PlainID:ClientId"],
            ClientSecret = Configuration["PlainID:ClientSecret"],
            ModifierServiceUrl = Configuration["PlainID:ModifierUrl"],
            EntityTypeId = "customers"
        };
        
        // Hook PlainID into the pipeline
        PlainLibrary.HookAll(services, plainOptions);
        
        // Add EF Core with PlainID interceptor
        services.AddDbContext<MyDbContext>(options =>
            options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection"))
                   .AddInterceptors(new PlainIDbCommandInterceptor()));
        
        services.AddControllers();
    }
    
    public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
    {
        app.UseAuthentication();
        app.UseAuthorization();
        app.UseMiddleware<PlainIDMiddleware>();
        app.UseMvc();
    }
}

Step 3: Implement Secure Service

public class SecureDataService
{
    private readonly MyDbContext _context;
    
    public SecureDataService(MyDbContext context)
    {
        _context = context;
    }
    
    public async Task<List<Order>> GetUserOrders()
    {
        // User context is automatically extracted from claims
        PlainContext.SetUser(HttpContext.User.Identity.Name);
        
        // Set additional attributes for policy evaluation
        var entityAttributes = new EntityAttributes();
        entityAttributes.AddAttribute("department", "sales");
        entityAttributes.AddAttribute("clearance_level", "3");
        PlainContext.SetEntityAttributes(entityAttributes);
        
        // Query is modified based on policies before execution
        return await _context.Orders
            .Include(o => o.Customer)
            .Where(o => o.Status == "Active")
            .ToListAsync();
    }
    
    public async Task<Customer> GetCustomerDetails(int customerId)
    {
        PlainContext.SetUser(User.Identity.Name);
        
        // PlainID will filter columns and mask sensitive data
        return await _context.Customers
            .FirstOrDefaultAsync(c => c.Id == customerId);
    }
}

Step 4: Configuration (appsettings.json)

JSON

{
  "PlainID": {
    "ClientId": "YOUR_CLIENT_ID",
    "ClientSecret": "YOUR_SECRET",
    "ModifierUrl": "http://sql-authorizer:8080",
    "RuntimeUrl": "http://runtime:8080/api/runtime/resolution/v3",
    "EnableStarExpansion": true,
    "CacheTimeout": 300,
    "LogLevel": "Information"
  },
  "ConnectionStrings": {
    "DefaultConnection": "Server=localhost;Database=MyApp;Trusted_Connection=true;"
  }
}

Direct API Integration

Use the REST API directly for custom implementations in any programming language:

API Endpoint

HTTP

POST http://sql-authorizer:8080/api/modify
Content-Type: application/json
Authorization: Bearer {token}

JavaScript/Node.js Example

JavaScript

class PlainIDAuthorizer {
    constructor(config) {
        this.baseUrl = config.baseUrl;
        this.clientId = config.clientId;
        this.clientSecret = config.clientSecret;
    }
    
    async modifyQuery(sql, userId, context = {}) {
        const response = await fetch(`${this.baseUrl}/api/modify`, {
            method: 'POST',
            headers: {
                'Content-Type': 'application/json',
                'X-Client-Id': this.clientId,
                'X-Client-Secret': this.clientSecret,
                'X-User-Id': userId
            },
            body: JSON.stringify({
                sql: sql,
                entityType: 'customers',
                contextData: context,
                flags: {
                    expandStar: true,
                    applyRowFilter: true,
                    applyColumnMask: true
                }
            })
        });
        
        const result = await response.json();
        
        if (result.error) {
            throw new Error(`PlainID Error: ${result.error}`);
        }
        
        return {
            modifiedSql: result.sql,
            wasModified: result.wasModified,
            appliedPolicies: result.policies
        };
    }
}

// Usage example
const authorizer = new PlainIDAuthorizer({
    baseUrl: 'http://sql-authorizer:8080',
    clientId: 'YOUR_CLIENT_ID',
    clientSecret: 'YOUR_SECRET'
});

async function getCustomerData(userId) {
    const originalQuery = "SELECT * FROM customers WHERE status = 'active'";
    
    try {
        const result = await authorizer.modifyQuery(
            originalQuery, 
            userId,
            {
                department: 'sales',
                region: 'north',
                clearanceLevel: 2
            }
        );
        
        if (result.wasModified) {
            console.log('Query was modified by policies');
            console.log('Applied policies:', result.appliedPolicies);
        }
        
        // Execute the modified query against your database
        return await db.query(result.modifiedSql);
    } catch (error) {
        console.error('Authorization failed:', error);
        throw error;
    }
}

Python Example

Python

import requests
import json

class PlainIDAuthorizer:
    def __init__(self, base_url, client_id, client_secret):
        self.base_url = base_url
        self.client_id = client_id
        self.client_secret = client_secret
    
    def modify_query(self, sql, user_id, context=None):
        headers = {
            'Content-Type': 'application/json',
            'X-Client-Id': self.client_id,
            'X-Client-Secret': self.client_secret,
            'X-User-Id': user_id
        }
        
        payload = {
            'sql': sql,
            'entityType': 'customers',
            'contextData': context or {},
            'flags': {
                'expandStar': True,
                'applyRowFilter': True,
                'applyColumnMask': True
            }
        }
        
        response = requests.post(
            f'{self.base_url}/api/modify',
            headers=headers,
            json=payload
        )
        
        result = response.json()
        
        if 'error' in result:
            raise Exception(f"PlainID Error: {result['error']}")
        
        return {
            'modified_sql': result['sql'],
            'was_modified': result['wasModified'],
            'applied_policies': result.get('policies', [])
        }

# Usage
authorizer = PlainIDAuthorizer(
    base_url='http://sql-authorizer:8080',
    client_id='YOUR_CLIENT_ID',
    client_secret='YOUR_SECRET'
)

def get_customer_data(user_id):
    original_query = "SELECT * FROM customers WHERE status = 'active'"
    
    result = authorizer.modify_query(
        original_query,
        user_id,
        context={
            'department': 'sales',
            'region': 'north',
            'clearanceLevel': 2
        }
    )
    
    if result['was_modified']:
        print(f"Query modified. Policies: {result['applied_policies']}")
    
    # Execute modified query with your database connection
    return db.execute(result['modified_sql'])

Response Format

JSON

{
  "sql": "SELECT id, name, email FROM customers WHERE status = 'active' AND region = 'north'",
  "wasModified": true,
  "error": null,
  "policies": [
    {
      "name": "Regional Access Control",
      "type": "ROW_FILTER",
      "applied": true
    },
    {
      "name": "PII Column Masking",
      "type": "COLUMN_MASK",
      "applied": true
    }
  ],
  "metadata": {
    "processingTime": 12,
    "policiesEvaluated": 5,
    "columnsRemoved": ["ssn", "salary"],
    "rowFilterApplied": "region = 'north'"
  }
}

Policy Configuration

Regional Data Access Policy Active

Condition: User.Role = "Regional_Manager"
Action: Add filter WHERE region = User.Region
Resources: orders, customers, sales_data

PII Data Masking Policy Active

Condition: Column.Classification = "PII" AND User.ClearanceLevel < 3
Action: Mask column values
Resources: All tables with PII columns

Department Isolation Policy Active

Condition: User.Department != "HR"
Action: Remove columns: salary, ssn, performance_rating
Resources: employees table

Policy Definition Example

JSON

{
  "policyName": "Customer Data Access",
  "description": "Controls access to customer data based on user attributes",
  "rules": [
    {
      "name": "Regional Filter",
      "condition": {
        "userAttribute": "region",
        "operator": "equals",
        "value": "${user.region}"
      },
      "action": {
        "type": "ROW_FILTER",
        "filter": "customer_region = '${user.region}'"
      }
    },
    {
      "name": "Sensitive Data Masking",
      "condition": {
        "columnClassification": "SENSITIVE",
        "userClearance": {
          "operator": "lessThan",
          "value": 3
        }
      },
      "action": {
        "type": "COLUMN_MASK",
        "maskingFunction": "PARTIAL_MASK"
      }
    }
  ],
  "priority": 100,
  "enabled": true
}

Policy Best Practices

Start with coarse-grained policies and refine as needed
Use attribute-based conditions for flexibility
Test policies in a non-production environment first
Monitor policy performance and query impact
Document policy decisions and business justifications
Regularly review and audit active policies