---
title: Data Segmentation with Server-Side Filtering and Lookup Keys
description: Data Segmentation with Server-Side Filtering and Lookup Keys
image: https://blog.dreamfactory.com/hubfs/Imported_Blog_Media/team-todd-May-07-2024-08-13-09-2699-AM.jpg
---

[![DreamFactory logo](https://cdn.prod.website-files.com/64ed8da8a866be7a702fbae0/68d51994d3678214b54acb60_dreamfactory-navbar-logo.svg)](https://www.dreamfactory.com/)

 Products & Services

[AI Data Gateway](https://www.dreamfactory.com/ai-data-gateway/overview)

[Overview Why DreamFactory exists](https://www.dreamfactory.com/ai-data-gateway/overview) [Data Gov, Comp, Security Policy enforcement at the API layer](https://www.dreamfactory.com/ai-data-gateway/ai-data-governance) [Standard API Layer One contract for every backend](https://www.dreamfactory.com/ai-data-gateway/standard-api-layer) [API Gateway Functionality Routing, auth, rate limits, observability](https://www.dreamfactory.com/ai-data-gateway/api-gateway-functionality) [Deployment & Integration Self-hosted, cloud, hybrid](https://www.dreamfactory.com/ai-data-gateway/on-premise-deployment-and-integration) [Developer Productivity Auto-generated, never hand-coded](https://www.dreamfactory.com/ai-data-gateway/ai-development-accelerated) [AI App Architectures Patterns for RAG, agents, MCP](https://www.dreamfactory.com/ai-data-gateway/enterprise-ai-architectures)

AI Data Models

[AIOpenAI](https://www.dreamfactory.com/use-cases/openai) [GGoogle Gemini](https://www.dreamfactory.com/use-cases/google-gemini) [CAnthropic Claude](https://www.dreamfactory.com/use-cases/anthropic-claude-landing) [LMeta Llama](https://www.dreamfactory.com/use-cases/meta-llama) [MMistral AI](https://www.dreamfactory.com/use-cases/mistral) [CoCohere](https://www.dreamfactory.com/use-cases/cohere)

Services and support

[Quickstart Service Packages Expert-led Quickstarts to production](https://www.dreamfactory.com/services-and-support/quickstart-services-packages)

API Management

[Generate & Manage REST APIs From any database, in seconds](https://www.dreamfactory.com/api-management/generate-rest-apis) [Features Security, scripting, self-hosted & more](https://www.dreamfactory.com/api-management/features) [API Generation The complete guide to auto-generated APIs](https://blog.dreamfactory.com/a-complete-guide-to-api-generation) [API Management Concepts, tools, and best practises](https://blog.dreamfactory.com/what-is-api-management-a-brief-overview-of-api-management-concepts-and-tools?_gl=1*jl0njh*_gcl_au*MjQzMjgwMTc3LjE3ODIzMjI3MzY)

 Use Cases

AI Use Cases

[AI Data Access Secure, governed reads for your LLMs](https://www.dreamfactory.com/use-cases/ai-data-access) [MCP Server Drop-in Model Context Protocol](https://www.dreamfactory.com/use-cases/mcp-server) [Legacy Modernization Wrap mainframes with REST](https://www.dreamfactory.com/use-cases/legacy-modernization) [Data Governance Audit every call, enforce every policy](https://www.dreamfactory.com/use-cases/data-governance)

[Customer Case Studies](https://www.dreamfactory.com/case-studies)

[Energy Modernization](https://www.dreamfactory.com/case-studies/energy-snowflake-modernization) [Government Modernization](https://www.dreamfactory.com/case-studies/government-mainframe-oracle-modernization) [Government Business Intelligence](https://www.dreamfactory.com/case-studies/government-sql-server-bi-analyst-queries) [Manufacturing Modernization](https://www.dreamfactory.com/case-studies/steel-manufacturing-sap-erp-modernization) [Financial Services Investor Portal](https://www.dreamfactory.com/case-studies/financial-services-sql-server-investor-portal) [Non-Profit Partner Data Sharing](https://www.dreamfactory.com/case-studies/non-profit-sql-server-partner-data-sharing) [Professional Services Exec Dashboards](https://www.dreamfactory.com/case-studies/professional-services-erp-dashboards) [Education HR and External Data Sharing](https://www.dreamfactory.com/case-studies/education-student-hr-sql-server-mysql-external-data-sharing)

 Industries

Industries

#### [Healthcare HIPAA-grade APIs across EHR, claims, and labs.](https://www.dreamfactory.com/use-cases/healthcare)

#### [Financial Services Portfolios, partners, and portals on one layer.](https://www.dreamfactory.com/use-cases/financial-services)

#### [Government Modernize mainframes without re-platforming.](https://www.dreamfactory.com/use-cases/government)

#### [Manufacturing SAP, MES, and shop-floor data, governed.](https://www.dreamfactory.com/use-cases/manufacturing)

#### [Spotlight How enterprises run on DreamFactory From healthcare to energy to finance — governance baked into every endpoint. Browse case studies →](https://www.dreamfactory.com/case-studies)

 Connectors

SQL Database

[SQL SQL Server](https://www.dreamfactory.com/connectors/sql-server) [OR Oracle](https://www.dreamfactory.com/connectors/oracle) [PG PostgreSQL](https://www.dreamfactory.com/connectors/postgresql) [My MySQL](https://www.dreamfactory.com/connectors/mysql)

NoSQL & Docs

[Dy DynamoDB](https://www.dreamfactory.com/connectors/dynamodb) [Do DocumentDB](https://www.dreamfactory.com/connectors/azure-documentdb) [Mo MongoDB](https://www.dreamfactory.com/connectors/mongodb) [Cb CouchDB](https://www.dreamfactory.com/connectors/couch-db)

Cloud Warehouses

[S3 S3](https://www.dreamfactory.com/connectors/amazon-s3) [Ab Azureblob](https://www.dreamfactory.com/connectors/azure-blob) [FS FTP/SFTP](https://www.dreamfactory.com/connectors/ftp-sftp) [LS Local Storage](https://www.dreamfactory.com/connectors/local-storage)

C & SaaS

[Sf Salesforce](https://www.dreamfactory.com/connectors/salesforce) [API REST / SOAP](https://www.dreamfactory.com/connectors/soap-to-rest)

[See all 30+ connectors](https://www.dreamfactory.com/connectors)

[Blog](https://blog.dreamfactory.com/)

[FREE 30 Minute Demo](https://www.dreamfactory.com/demo)

[![DreamFactory logo](https://cdn.prod.website-files.com/64ed8da8a866be7a702fbae0/68d51994d3678214b54acb60_dreamfactory-navbar-logo.svg)](https://www.dreamfactory.com/)

![hamburger](https://blog.dreamfactory.com/hubfs/raw_assets/public/dreamfactory/images/megamenu/menu-hamburger.svg) ![close](https://blog.dreamfactory.com/hubfs/raw_assets/public/dreamfactory/images/megamenu/close-menu.svg)

 Back to main menu

 Products & Services

 Use Cases

 Industries

 Connectors

[Blog](https://blog.dreamfactory.com/)

[FREE 30 Minute Demo](https://www.dreamfactory.com/demo)

[AI Data Gateway](https://www.dreamfactory.com/ai-data-gateway/overview)

[Overview Why DreamFactory exists](https://www.dreamfactory.com/ai-data-gateway/overview) [Data Gov, Comp, Security Policy enforcement at the API layer](https://www.dreamfactory.com/ai-data-gateway/ai-data-governance) [Standard API Layer One contract for every backend](https://www.dreamfactory.com/ai-data-gateway/standard-api-layer) [API Gateway Functionality Routing, auth, rate limits, observability](https://www.dreamfactory.com/ai-data-gateway/api-gateway-functionality) [Deployment & Integration Self-hosted, cloud, hybrid](https://www.dreamfactory.com/ai-data-gateway/on-premise-deployment-and-integration) [Developer Productivity Auto-generated, never hand-coded](https://www.dreamfactory.com/ai-data-gateway/ai-development-accelerated) [AI App Architectures Patterns for RAG, agents, MCP](https://www.dreamfactory.com/ai-data-gateway/enterprise-ai-architectures)

AI Data Models

[AIOpenAI](https://www.dreamfactory.com/use-cases/openai) [GGoogle Gemini](https://www.dreamfactory.com/use-cases/google-gemini) [CAnthropic Claude](https://www.dreamfactory.com/use-cases/anthropic-claude-landing) [LMeta Llama](https://www.dreamfactory.com/use-cases/meta-llama) [MMistral AI](https://www.dreamfactory.com/use-cases/mistral) [CoCohere](https://www.dreamfactory.com/use-cases/cohere)

Services and support

[Quickstart Service Packages Expert-led Quickstarts to production](https://www.dreamfactory.com/services-and-support/quickstart-services-packages)

AI Data Models

[Generate & Manage REST APIs From any database, in seconds](https://www.dreamfactory.com/api-management/generate-rest-apis) [Features Security, scripting, self-hosted & more](https://www.dreamfactory.com/api-management/features) [API Generation The complete guide to auto-generated APIs](https://blog.dreamfactory.com/a-complete-guide-to-api-generation) [API Management Concepts, tools, and best practises](https://blog.dreamfactory.com/what-is-api-management-a-brief-overview-of-api-management-concepts-and-tools?_gl=1*jl0njh*_gcl_au*MjQzMjgwMTc3LjE3ODIzMjI3MzY)

AI Use Cases

[AI Data Access Secure, governed reads for your LLMs](https://www.dreamfactory.com/use-cases/ai-data-access) [MCP Server Drop-in Model Context Protocol](https://www.dreamfactory.com/use-cases/mcp-server) [Legacy Modernization Wrap mainframes with REST](https://www.dreamfactory.com/use-cases/legacy-modernization) [Data Governance Audit every call, enforce every policy](https://www.dreamfactory.com/use-cases/data-governance)

[Customer Case Studies](https://www.dreamfactory.com/case-studies)

[Energy Modernization](https://www.dreamfactory.com/case-studies/energy-snowflake-modernization) [Government Modernization](https://www.dreamfactory.com/case-studies/government-mainframe-oracle-modernization) [Government Business Intelligence](https://www.dreamfactory.com/case-studies/government-sql-server-bi-analyst-queries) [Manufacturing Modernization](https://www.dreamfactory.com/case-studies/steel-manufacturing-sap-erp-modernization) [Financial Services Investor Portal](https://www.dreamfactory.com/case-studies/financial-services-sql-server-investor-portal) [Non-Profit Partner Data Sharing](https://www.dreamfactory.com/case-studies/non-profit-sql-server-partner-data-sharing) [Professional Services Exec Dashboards](https://www.dreamfactory.com/case-studies/professional-services-erp-dashboards) [Education HR and External Data Sharing](https://www.dreamfactory.com/case-studies/education-student-hr-sql-server-mysql-external-data-sharing)

Industries

#### [Healthcare HIPAA-grade APIs across EHR, claims, and labs.](https://www.dreamfactory.com/use-cases/healthcare)

#### [Financial Services Portfolios, partners, and portals on one layer.](https://www.dreamfactory.com/use-cases/financial-services)

#### [Government Modernize mainframes without re-platforming.](https://www.dreamfactory.com/use-cases/government)

#### [Manufacturing SAP, MES, and shop-floor data, governed.](https://www.dreamfactory.com/use-cases/manufacturing)

SQL Database

[SQL SQL Server](https://www.dreamfactory.com/connectors/sql-server) [OR Oracle](https://www.dreamfactory.com/connectors/oracle) [PG PostgreSQL](https://www.dreamfactory.com/connectors/postgresql) [My MySQL](https://www.dreamfactory.com/connectors/mysql)

NoSQL & Docs

[Dy DynamoDB](https://www.dreamfactory.com/connectors/dynamodb) [Do DocumentDB](https://www.dreamfactory.com/connectors/azure-documentdb) [Mo MongoDB](https://www.dreamfactory.com/connectors/mongodb) [Cb CouchDB](https://www.dreamfactory.com/connectors/couch-db)

Cloud Warehouses

[S3 S3](https://www.dreamfactory.com/connectors/amazon-s3) [Ab Azureblob](https://www.dreamfactory.com/connectors/azure-blob) [FS FTP/SFTP](https://www.dreamfactory.com/connectors/ftp-sftp) [LS Local Storage](https://www.dreamfactory.com/connectors/local-storage)

C & SaaS

[Sf Salesforce](https://www.dreamfactory.com/connectors/salesforce) [API REST / SOAP](https://www.dreamfactory.com/connectors/soap-to-rest)

[See all 30+ connectors](https://www.dreamfactory.com/connectors)

[![back arrow](https://blog.dreamfactory.com/hubfs/raw_assets/public/dreamfactory/images/orange-arrow.svg) Blog](https://blog.dreamfactory.com/)

# Data Segmentation with Server-Side Filtering and Lookup Keys

 by Terence Bennett

![calendar icon](https://blog.dreamfactory.com/hubfs/raw_assets/public/dreamfactory/images/calendar-icon.svg) December 31, 2026

Table of contents

RECOMMENDED ARTICLES

- [A Complete Guide to API Generation](https://blog.dreamfactory.com/a-complete-guide-to-api-generation)
- [10 Best API Management Tools](https://blog.dreamfactory.com/what-is-api-management-a-brief-overview-of-api-management-concepts-and-tools)
- [Creating a Microsoft SQL Server API in Less Than 5 minutes with DreamFactory](https://blog.dreamfactory.com/creating-a-microsoft-sql-server-api-in-less-than-5-minutes-with-dreamfactory)
- [Hasura vs. DreamFactory: A Comprehensive Comparison](https://blog.dreamfactory.com/hasura-vs-dreamfactory)
- [Build A Snowflake REST API in Less Than 5 Minutes](https://blog.dreamfactory.com/generate-a-snowflake-rest-api-in-less-than-5-minutes)

![ToddAppleton](https://blog.dreamfactory.com/hubfs/Imported_Blog_Media/team-todd-May-07-2024-08-13-09-2699-AM.jpg)

The 1.5 release of the DreamFactory Services Platform (DSP) introduces the ability to easily create server-side filters that control database access at the record level. Click [here](https://blog.dreamfactory.com/dreamfactory-introduces-server-side-filters/) for an introduction to this feature, including how to restrict access by OwnerId. As a follow up to that post I want to give you a detailed example of how you can use this feature to "assign" records to certain users based on the value of a particular field. I'm not going to present an app that uses this feature, but I will show you all the details of how to set it up for your own DSP. Once everything is configured properly, the server will apply the filters you defined to all API calls.

For this example consider a simple table named Opportunity that has the following schema. If you like you can import this JSON into your DSP from the Schema tab in the DSP admin console.

```
{
    "table": [
        {
            "name": "opportunity",
            "label": "Opportunity",
            "plural": "Opportunities",
            "field": [
                {
                    "name": "id",
                    "label": "Id",
                    "type": "id"
                },
                {
                    "name": "name",
                    "label": "Name",
                    "type": "string",
                    "size": 80,
                    "allow_null": false
                },
                {
                    "name": "amount",
                    "label": "Amount",
                    "type": "money",
                    "decimals": 0
                }
            ]
        }
    ]
}
```

 There is a currency field named amount that contains the amount of the opportunity. Let's say that we want opportunities where 0 <= amount < 10000 to be handled by user group A, and opportunities where 10000 <= amount < 50000 to be handled by user group B. Group A should have no access to group B's records and vice versa. This is accomplished by configuring two roles in the system, one for group A and another for group B. When you create a role you select which users are assigned to that role and also the filter that role should enforce.  In this case the filter is "amount >= 0 and amount < 10000" for group A or "amount >= 10000 and amount < 50000" for group B. Note that admin users will have access to all records regardless of amount, because roles and filters do not apply to admin users.

### Step 1 - Add some users

To make this example worthwhile we need at least two non-admin users. One of them will be assigned the role for group A and the other will be assigned the role for group B. You can have as many users as you like in either group. We are just showing one in each for this example. Go to the users tab in the admin console and click Create New User. Complete the form as shown then click Save. Don't worry about assigning the role yet, we'll do that in a minute. Click "Set password manually" and enter a password for the new user to avoid requiring confirmation.

Now repeat this for the second user. Names are not important just make sure they are not an admin user.

### Step 2 - Create the Roles

 Now comes the fun part. As part of creating the roles you create the filters that determine who has access to what records. In your admin console go to the Roles tab and click Create New Role. The first role is for group A and we will assign the first user to this role. Complete the form as shown. Check the Active checkbox to make the role active. You want to grant full access to the database table named opportunity. Select Database from the Service menu and opportunity from that Component menu. To add a filter click the Show/Hide under Advanced Filters and create a filter for "amount >= 0 and amount < 10000". The AND is accomplished by clicking the plus icon to add a new line to the filter. You can click to select AND or OR but not both in the same filter.

![2](https://blog.dreamfactory.com/hubfs/Imported_Blog_Media/2-1.png)

![3](https://blog.dreamfactory.com/hubfs/Imported_Blog_Media/3.png)

Click Save then repeat to create the role for group B which is assigned to the second user with filter set to "amount >= 10000 and amount < 50000".

![7](https://blog.dreamfactory.com/hubfs/Imported_Blog_Media/7.png)

![4](https://blog.dreamfactory.com/hubfs/Imported_Blog_Media/4-2.png)

Click Save and you should see both roles in the list in the second column of the admin console. That's it! Now when you log in as a group A user and make an API call to access the Opportunity table you will only be allowed access to records where 0 <= amount < 10000. When you log in as a group B user you will only be allowed access to records where 10000 <= amount < 50000.  If you log in as an admin user you can see all records.

### Step 3 - Add Lookup Keys

 Having the values 0, 10000, and 50000 hardcoded in the role filters is somewhat limiting and could become hard to maintain. A better way to handle this is to add lookup keys to each role that define these values. A lookup key is simply a key-value pair that can be used as a value in the role filters. We want to define lookups on each role named MIN\_AMT and MAX\_AMT. Select the group A role and add a lookup key named MIN\_AMT with value set to 0. Add a second lookup key named MAX\_AMT with value set to 10000. Under the service access section change the filter to "amount >= {MIN\_AMT} and amount < {MAX\_AMT}". The lookup key names must always be in curly brackets.

![5](https://blog.dreamfactory.com/hubfs/Imported_Blog_Media/5.png)

Now select the group B role and add the same lookup keys and filter.  This time set MIN\_AMT to 10000 and MAX\_AMT to 50000.

![6](https://blog.dreamfactory.com/hubfs/Imported_Blog_Media/6.png)

An awesome feature of lookup keys is that they obey a hierarchy starting with user lookups, then role lookups, and finally global lookups (global lookup keys are defined in the DSP config settings in the admin console). This makes it easy to implement a system of overrides and default values. For example, if I wanted a special MIN\_AMT and MAX\_AMT for a certain user in group A I could add user lookup keys to that user that would apply only to that one user. For that user only, the role filter would be evaluated using the user lookup keys, NOT the role lookup keys. Other users in group A would be bound to the values defined in the role lookup keys. If you wanted to make 0 and 10000 the default values for all users then you could define them in the global lookup keys. These values would be used for any users with no matching user or role lookup keys defined.

The system is designed to be incredibly flexible. You just have to decide the best setup for users, roles, filters, and lookup keys for your organization.  You can change these settings at any time, for one, some, or all users. Of course we are here to help should you have any questions about how to do this. 

TAGS: [Social Links Menu](https://blog.dreamfactory.com/tag/social-links-menu)

![Terence Bennett](https://blog.dreamfactory.com/hs-fs/hubfs/1624046620145.jpg?width=100&height=100&name=1624046620145.jpg)

Terence Bennett

Terence Bennett, CEO of DreamFactory, has a wealth of experience in government IT systems and Google Cloud. His impressive background includes being a former U.S. Navy Intelligence Officer and a former member of Google's Red Team. Prior to becoming CEO, he served as COO at DreamFactory Software.

 Stay Connected with   
 The Connector Newsletter!

 Subscribe to stay up-to-date with DreamFactory's latest product updates, API best practices, and tech humor in your inbox.

[![Dreamfactory Logo](https://blog.dreamfactory.com/hubfs/raw_assets/public/dreamfactory/images/megamenu/Megamenu-logo.svg)](https://www.dreamfactory.com/)

[Call Sales +1 (415) 993-5877](tel:+14159935877)

Open – Mon–Fri 9–5 PT

[FREE 30 Minute Demo](https://www.dreamfactory.com/demo)

#### Follow us

- [GitHub](https://github.com/dreamfactorysoftware/dreamfactory)
- [Facebook](https://www.facebook.com/dfsoftwareinc/)
- [X (Twitter)](https://twitter.com/dfsoftwareinc)
- [LinkedIn](https://www.linkedin.com/company/dreamfactory-software)
- [YouTube](https://www.youtube.com/c/dreamfactorysoftware)

### Features

[Features](https://www.dreamfactory.com/features) [Self hosted](https://www.dreamfactory.com/features#self) [API Generation](https://www.dreamfactory.com/features#api) [Security](https://www.dreamfactory.com/features#secure) [Customization](https://www.dreamfactory.com/features#custom) [Pricing](https://www.dreamfactory.com/pricing)

### Installers

[Linux](https://www.dreamfactory.com/features#installer) [Docker](https://www.dreamfactory.com/features#installer) [Kubernetes](https://www.dreamfactory.com/features#installer)

### API Resources

[Documentation](https://docs.dreamfactory.com/) [Case Studies](https://www.dreamfactory.com/stories) [White Papers](https://www.dreamfactory.com/resources/whitepapers) [Academy](https://www.dreamfactory.com/academy) [API Calculator](https://calculator.dreamfactory.com) [Open Source](https://github.com/dreamfactorysoftware)

### Company

[Blog](https://blog.dreamfactory.com/) [Hub](https://www.dreamfactory.com/hub) [About us](https://www.dreamfactory.com/about) [Partners](https://www.dreamfactory.com/partners) [Support](https://www.dreamfactory.com/support) [Connectors](https://www.dreamfactory.com/connectors) [Contact Us](https://www.dreamfactory.com/demo)

 © 2025 DreamFactory. All rights reserved.

[Terms of Use](https://www.dreamfactory.com/terms-of-use) [Privacy Policy](https://www.dreamfactory.com/privacy-policy) [LLMs](https://www.dreamfactory.com/llms.txt)

```json
{
  "@context" : "https://schema.org",
  "@type" : "BlogPosting",
  "author" : {
    "@type" : "Person",
    "name" : "Terence Bennett",
    "url" : "https://blog.dreamfactory.com/author/terencebennett"
  },
  "dateModified" : "2024-05-07T08:19:17.133Z",
  "datePublished" : "2026-01-01T00:00:00.000Z",
  "headline" : "Data Segmentation with Server-Side Filtering and Lookup Keys",
  "image" : [ "https://blog.dreamfactory.com/hubfs/Imported_Blog_Media/team-todd-May-07-2024-08-13-09-2699-AM.jpg" ],
  "mainEntityOfPage" : {
    "@id" : "https://blog.dreamfactory.com/data-segmentation-with-server-side-filtering-and-lookup-keys",
    "@type" : "WebPage"
  },
  "publisher" : {
    "@type" : "Organization",
    "logo" : {
      "@type" : "ImageObject",
      "url" : "https://blog.dreamfactory.com/hubfs/DreamFactory%20-%20Orange%20-%20Transparent-1.png"
    }
  }
}
```