Advanced ABAC: Field-Level Permissions and DB Integration

Abstract#

Post 104 built a type-safe ABAC policy engine with a builder pattern. The can(user, action, resource, data?) function evaluates subject, resource, and action attributes through declarative conditions. Ownership, department scoping, and resource status are policy rules, not scattered helper functions.

Two gaps remain. First, can() returns a boolean; the user either sees the entire resource or nothing. There is no way to control which fields a user can read or write. An admin sees internalNotes; an author should not. An editor can update content but not publishedAt. Second, all conditions evaluate in application memory against already-loaded objects. For list views, the application loads all records, calls can() on each, and discards the ones that fail. The database should do that filtering.

The NIST SP 800-162 model also defines a fourth attribute category, environment, that Post 104 introduced but left as a forward reference. Time-based access, IP restrictions, and feature flags belong inside the policy engine, not as separate middleware checks.

This post closes all three gaps: environment conditions enter the type system, field-level permissions control read and write visibility per role, and ABAC conditions convert into database where clauses for query-level enforcement.

// Preview: what the completed system looks likeconst fields = getVisibleFields(session, 'document', docData);const documents = await db.document.findMany({  where: toPrismaWhere(toWhereClause(session, 'document', 'read')),});

Environment-Based Rules#

Extending the Type System#

Post 104's Condition<R> type receives (user: User, data: ResourceDataMap[R]) => boolean. Environment attributes (time, IP address, locale, feature flags) are external to both subject and resource. They need their own type.

// lib/permissions.ts
interface Environment {  currentTime: Date;  ipAddress?: string;  locale?: string;  featureFlags?: Record<string, boolean>;}
// Extend condition to receive environmenttype Condition<R extends Resource> = (  user: User,  data: ResourceDataMap[R],  env?: Environment) => boolean;
// Updated can() signaturefunction can<R extends Resource>(  user: User,  action: Action,  resource: R,  data?: ResourceDataMap[R],  env?: Environment): boolean

The can() signature has evolved across the series:

// Post 103 (RBAC):can(role, resource, action): boolean
// Post 104 (ABAC):can<R>(user, action, resource, data?): boolean
// Post 105 (Advanced ABAC):can<R>(user, action, resource, data?, env?): boolean

The env parameter is optional. Existing conditions from Post 104 continue working unchanged. Making environment optional preserves backward compatibility while completing the NIST four-attribute model.

Practical Examples#

Time-based restriction: billing operations only during business hours:

.role('billing_admin')  .can(['create', 'update'], 'invoice', [    (user, data, env) => {      if (!env?.currentTime) return true; // no env = no time restriction      const hour = env.currentTime.getHours();      const day = env.currentTime.getDay();      return hour >= 9 && hour < 17 && day >= 1 && day <= 5;    },  ])

IP-based restriction: admin operations restricted to office network:

.role('admin')  .can('delete', 'document', [    (user, data, env) => {      if (!env?.ipAddress) return false; // deny if no IP context      return env.ipAddress.startsWith('10.0.');    },  ])

Feature flag gating: new functionality behind feature flags:

.role('editor')  .can('publish', 'document', [    (user, doc) => user.departmentId === doc.departmentId,    (user, doc, env) => env?.featureFlags?.['bulk-publish'] === true,  ])

Note: Environment conditions follow the same AND logic as other conditions in the builder. All conditions in an array must pass for the permission to be granted.

Service Layer Integration#

The service layer constructs the Environment object from the request context. It builds it once per request, then passes it through:

function getEnvironment(request: Request): Environment {  return {    currentTime: new Date(),    ipAddress: request.headers.get('x-forwarded-for') ?? undefined,    locale: request.headers.get('accept-language')?.split(',')[0],    featureFlags: getFeatureFlags(),  };}
// In a service methodconst env = getEnvironment(request);if (!can(session, 'update', 'document', documentData, env)) {  throw new ForbiddenError();}

A key design decision: environment data comes from the request, not from the database. It should not be part of ResourceDataMap. Mixing request context with resource data breaks the NIST model and makes conditions harder to reason about.

Field-Level Read Permissions#

The Problem#

Consider the document resource with these fields: id, title, content, status, authorId, departmentId, internalNotes, reviewComments, publishedAt.

Different roles need different field visibility:

Without field-level permissions, the service returns the full object and trusts the frontend to hide fields. This is security by obscurity. The API response contains sensitive data regardless of what the UI renders.

Type System Extension#

The field permission system maps resources to their field names with conditions:

type ResourceField<R extends Resource> = keyof ResourceDataMap[R] & string;
interface FieldPermissionEntry<R extends Resource> {  resource: R;  action: 'read' | 'write';  fields: ResourceField<R>[];  conditions?: Condition<R>[];}

Add field permissions to the builder:

.role('author')  .canReadFields('document',    ['id', 'title', 'content', 'status', 'publishedAt', 'authorId'],    [(user, doc) => doc.authorId === user.userId]  )  .canReadFields('document',    ['id', 'title', 'content', 'status', 'publishedAt']    // no condition -- public fields for any document  )
.role('editor')  .canReadFields('document',    ['id', 'title', 'content', 'status', 'publishedAt', 'authorId', 'departmentId']  )  .canReadFields('document',    ['id', 'title', 'content', 'status', 'publishedAt', 'authorId', 'departmentId', 'reviewComments'],    [(user, doc) => doc.status === 'review']  )
// Admin: no canReadFields entry = all fields visible (convention)

Convention: absence of canReadFields entries means all fields are visible for that role, provided the role has read access via can().

The getVisibleFields() Function#

function getVisibleFields<R extends Resource>(  user: User,  resource: R,  data?: ResourceDataMap[R],  env?: Environment): ResourceField<R>[] {  // 1. Check if user has read access at all  if (!can(user, 'read', resource, data, env)) return [];
  // 2. Find field permission entries for this role + resource + 'read'  const fieldEntries = fieldPermissions[user.role]    ?.filter(e => e.resource === resource && e.action === 'read') ?? [];
  // 3. If no field entries exist, return all fields (no restriction)  if (fieldEntries.length === 0) {    return Object.keys(resourceSchemas[resource]) as ResourceField<R>[];  }
  // 4. Collect fields from matching entries (union of all matching)  const visibleFields = new Set<ResourceField<R>>();  for (const entry of fieldEntries) {    if (!entry.conditions || entry.conditions.length === 0) {      entry.fields.forEach(f => visibleFields.add(f));    } else if (data) {      const conditionsMet = entry.conditions.every(c => c(user, data, env));      if (conditionsMet) {        entry.fields.forEach(f => visibleFields.add(f));      }    }  }  return Array.from(visibleFields);}

The filterFields() utility takes a resource object and a list of visible fields, returning a new object with only those fields:

function filterFields<R extends Resource>(  data: ResourceDataMap[R],  fields: ResourceField<R>[]): Partial<ResourceDataMap[R]> {  const result: Partial<ResourceDataMap[R]> = {};  for (const field of fields) {    if (field in data) {      (result as Record<string, unknown>)[field] = data[field as keyof typeof data];    }  }  return result;}

Service Layer Integration#

export async function getDocument(documentId: string) {  const session = await requireSession();  const document = await db.document.findUnique({ where: { id: documentId } });  if (!document) throw new NotFoundError();
  const docData = toResourceData(document);  if (!can(session, 'read', 'document', docData)) {    throw new ForbiddenError();  }
  // Filter fields based on role and conditions  const visibleFields = getVisibleFields(session, 'document', docData);  return filterFields(docData, visibleFields);}

UI Field Hiding#

On the client side, use getVisibleFields() to conditionally render fields:

function DocumentDetail({ document, session }: Props) {  const fields = getVisibleFields(session, 'document', document);
  return (    <div>      <h1>{document.title}</h1>      {fields.includes('internalNotes') && (        <section>{document.internalNotes}</section>      )}      {fields.includes('reviewComments') && (        <section>{document.reviewComments}</section>      )}    </div>  );}

UI hiding is for UX, not security. The service layer already filtered the fields in the API response. The client cannot render what it did not receive. As established in Post 102: the server is the security boundary, the client is the UX convenience.

Field-Level Write Permissions#

Builder Extension#

Write permissions are distinct from read permissions. An editor might read internalNotes but not write to it. A moderator might write status but not content.

.role('author')  .canWriteFields('document', ['title', 'content'])
.role('editor')  .canWriteFields('document', ['title', 'content', 'status'], [    (user, doc) => user.departmentId === doc.departmentId,  ])
.role('admin')  // No canWriteFields = can write all fields (if has update access)

The write permission matrix:

The pickPermittedFields() Function#

function pickPermittedFields<R extends Resource>(  user: User,  resource: R,  input: Partial<ResourceDataMap[R]>,  data?: ResourceDataMap[R], // existing resource data for conditions  env?: Environment): Partial<ResourceDataMap[R]> {  // 1. Find write field entries for this role + resource  const fieldEntries = fieldPermissions[user.role]    ?.filter(e => e.resource === resource && e.action === 'write') ?? [];
  // 2. If no entries, all fields are permitted (admin case)  if (fieldEntries.length === 0) return input;
  // 3. Collect permitted write fields  const permitted = new Set<string>();  for (const entry of fieldEntries) {    if (!entry.conditions || entry.conditions.length === 0) {      entry.fields.forEach(f => permitted.add(f));    } else if (data) {      const conditionsMet = entry.conditions.every(c => c(user, data, env));      if (conditionsMet) entry.fields.forEach(f => permitted.add(f));    }  }
  // 4. Filter input to only permitted fields  const result: Partial<ResourceDataMap[R]> = {};  for (const [key, value] of Object.entries(input)) {    if (permitted.has(key)) {      (result as Record<string, unknown>)[key] = value;    }  }  return result;}

Silent Drop vs. Error#

Two approaches when a user submits a forbidden field:

1. Silent drop: Strip the field and proceed. The user does not know the field was ignored. Simpler for the client, but hides errors.
2. Error: Reject the entire submission with a 403. More explicit, but requires the client to know which fields are allowed before submitting.

// Option A: Silent drop (default, CASL uses this approach)const permitted = pickPermittedFields(session, 'document', input, docData);await db.document.update({ where: { id }, data: permitted });
// Option B: Explicit validation (for admin/audit contexts)function validatePermittedFields<R extends Resource>(  user: User,  resource: R,  input: Partial<ResourceDataMap[R]>,  data?: ResourceDataMap[R],  env?: Environment): void {  const permitted = pickPermittedFields(user, resource, input, data, env);  const forbidden = Object.keys(input).filter(k => !(k in permitted));  if (forbidden.length > 0) {    throw new ForbiddenError(`Cannot write fields: ${forbidden.join(', ')}`);  }}

In my experience, silent drop for APIs, error for admin contexts works well. The service layer chooses which to use based on the operation's sensitivity.

Create and Update Application#

Create Flow#

On create, there is no existing resource data. Conditions that reference resource attributes (ownership, department) cannot evaluate. Field permissions for create should use unconditional entries:

export async function createDocument(input: DocumentInput) {  const session = await requireSession();  if (!can(session, 'create', 'document')) {    throw new ForbiddenError();  }
  // Filter input to only fields this role can write  const permitted = pickPermittedFields(session, 'document', input);
  // System sets authorId, status -- not from user input  return await db.document.create({    data: {      ...permitted,      authorId: session.userId,      status: 'draft',    },  });}

authorId and status are system-managed fields, never from user input. Even if the client sends authorId, pickPermittedFields() strips it because it is not in the author's write fields.

Update Flow#

On update, existing resource data is available for conditions:

export async function updateDocument(documentId: string, input: DocumentInput) {  const session = await requireSession();  const document = await db.document.findUnique({ where: { id: documentId } });  if (!document) throw new NotFoundError();
  const docData = toResourceData(document);  if (!can(session, 'update', 'document', docData)) {    throw new ForbiddenError();  }
  // Filter input to only fields this user can write  const permitted = pickPermittedFields(session, 'document', input, docData);
  return await db.document.update({    where: { id: documentId },    data: permitted,  });}

Conditional Form Rendering#

The UI can use write field permissions to conditionally render form fields:

function DocumentForm({ document, session }: Props) {  const writeFields = getWritableFields(session, 'document', document);
  return (    <form>      {writeFields.includes('title') && (        <input name="title" defaultValue={document?.title} />      )}      {writeFields.includes('content') && (        <textarea name="content" defaultValue={document?.content} />      )}      {writeFields.includes('status') && (        <select name="status" defaultValue={document?.status}>          <option value="draft">Draft</option>          <option value="published">Published</option>        </select>      )}      {writeFields.includes('internalNotes') && (        <textarea name="internalNotes" defaultValue={document?.internalNotes} />      )}    </form>  );}

Form rendering is UX. The server-side pickPermittedFields() is the security boundary. Even if a malicious client adds hidden fields to the form submission, pickPermittedFields() strips them.

Automatic DB Query Filtering#

The ConditionDescriptor Approach#

Post 104's conditions are opaque functions. They evaluate in memory but cannot be translated to SQL. For list views ("show me all documents I can read"), loading all records and filtering with can() in a loop is wasteful.

The idea: provide a declarative descriptor alongside each condition function that describes what it does in database terms:

interface ConditionDescriptor<R extends Resource> {  // The function for in-memory evaluation (same as before)  evaluate: (user: User, data: ResourceDataMap[R], env?: Environment) => boolean;  // Declarative description for DB translation  toFilter?: (user: User, env?: Environment) => WhereClause<R> | null;}
// ORM-agnostic where clause representationtype WhereClause<R extends Resource> = {  [K in keyof ResourceDataMap[R]]?:    | ResourceDataMap[R][K]  // equality    | { $ne: ResourceDataMap[R][K] }  // not equal    | { $in: ResourceDataMap[R][K][] }  // in list    | { $gte: ResourceDataMap[R][K] }  // greater than or equal    | { $lte: ResourceDataMap[R][K] }  // less than or equal};

This syntax is similar to MongoDB's query format and CASL's conditions. Prisma, Drizzle, and other ORMs can consume it with a simple adapter.

Updated builder with descriptors:

const permissions = new PermissionBuilder()  .role('editor')    .can(['create', 'read', 'update', 'publish'], 'document', [{      evaluate: (user, doc) => user.departmentId === doc.departmentId,      toFilter: (user) => ({ departmentId: user.departmentId }),    }])
  .role('author')    .can(['read', 'update'], 'document', [{      evaluate: (user, doc) => doc.authorId === user.userId,      toFilter: (user) => ({ authorId: user.userId }),    }])
  .role('admin')    .can(['create', 'read', 'update', 'delete', 'publish'], 'document')    // No conditions = no filter = all records
  .role('viewer')    .can('read', 'document')    // No conditions = no filter = all published records
  .build();

The toWhereClause() Function#

function toWhereClause<R extends Resource>(  user: User,  resource: R,  action: Action,  env?: Environment): WhereClause<R> | null {  const entries = permissions[user.role] as PermissionEntry<R>[];
  for (const entry of entries) {    if (entry.resource !== resource) continue;    if (!entry.actions.includes(action)) continue;
    // Unconditional entry = no filter needed    if (!entry.conditions || entry.conditions.length === 0) {      return {}; // empty where = all records    }
    // Collect filters from translatable conditions    const filters: WhereClause<R> = {};    let allTranslatable = true;
    for (const condition of entry.conditions) {      if (condition.toFilter) {        const filter = condition.toFilter(user, env);        if (filter) Object.assign(filters, filter);      } else {        allTranslatable = false;      }    }
    if (allTranslatable) return filters;    // If some conditions are not translatable, return null (fallback to in-memory)    return null;  }
  return null; // no matching entry = deny}

Warning: An empty object {} and null have different semantics. {} means "no filter; return all records" (admin/viewer case). null means "no matching permission; deny access." Returning {} instead of null for a denied role would return all records. This distinction is critical for security.

ORM Adapter Layer#

The WhereClause<R> is ORM-agnostic. Adapters convert it to ORM-specific syntax:

// Prisma adapterfunction toPrismaWhere<R extends Resource>(  clause: WhereClause<R>): Record<string, unknown> {  const prismaWhere: Record<string, unknown> = {};  for (const [key, value] of Object.entries(clause)) {    if (typeof value === 'object' && value !== null && !Array.isArray(value)) {      const op = value as Record<string, unknown>;      if ('$ne' in op) prismaWhere[key] = { not: op.$ne };      else if ('$in' in op) prismaWhere[key] = { in: op.$in };      else if ('$gte' in op) prismaWhere[key] = { gte: op.$gte };      else if ('$lte' in op) prismaWhere[key] = { lte: op.$lte };    } else {      prismaWhere[key] = value; // equality    }  }  return prismaWhere;}
// Drizzle adapterfunction toDrizzleWhere<R extends Resource>(  clause: WhereClause<R>,  table: Record<string, Column>): SQL[] {  const conditions: SQL[] = [];  for (const [key, value] of Object.entries(clause)) {    if (typeof value === 'object' && value !== null) {      const op = value as Record<string, unknown>;      if ('$ne' in op) conditions.push(ne(table[key], op.$ne));      else if ('$in' in op) conditions.push(inArray(table[key], op.$in as unknown[]));      else if ('$gte' in op) conditions.push(gte(table[key], op.$gte));      else if ('$lte' in op) conditions.push(lte(table[key], op.$lte));    } else {      conditions.push(eq(table[key], value));    }  }  return conditions;}

Complete service layer integration for list views:

export async function listDocuments() {  const session = await requireSession();
  const whereClause = toWhereClause(session, 'document', 'read');
  if (whereClause === null) {    // null = deny or untranslatable conditions -> fallback to empty    return [];  }
  // Push permissions into the query  const documents = await db.document.findMany({    where: toPrismaWhere(whereClause),  });
  // Apply field-level filtering to each document  return documents.map(doc => {    const docData = toResourceData(doc);    const fields = getVisibleFields(session, 'document', docData);    return filterFields(docData, fields);  });}

What Cannot Be Pushed to the Database#

Not all conditions are translatable. Complex logic, cross-resource conditions, and environment-based checks generally stay in memory:

• (user, doc, env) => env.currentTime.getHours() >= 9: involves runtime context, not a resource attribute
• (user, doc) => doc.tags.some(t => user.expertise.includes(t)): array intersection logic
• (user, doc) => doc.wordCount > 1000 && user.role === 'senior_editor': compound logic mixing subject and resource

The toFilter field is optional. If omitted, the condition falls back to in-memory evaluation. The system degrades gracefully: translatable conditions become WHERE clauses, untranslatable ones require post-fetch filtering.

This is the same pattern used by production authorization systems. OPA's Compile API calls it "partial evaluation." Cerbos's PlanResources API returns three outcomes: ALWAYS_ALLOWED, ALWAYS_DENIED, or CONDITIONAL with an AST. The lightweight TypeScript version here follows the same principle with less infrastructure.

The Unified System#

The complete system makes the policy builder a single source of truth. Changing a condition in the builder automatically propagates to every layer:

• can(): record-level access control
• getVisibleFields(): field-level read permissions
• pickPermittedFields(): field-level write permissions
• toWhereClause(): database query filtering

No service method, React component, or database query needs to change.

Example: "Editors can now also see reviewComments if the document is in review status." One change in the builder:

.role('editor')  .canReadFields('document',    ['id', 'title', 'content', 'status', 'reviewComments', 'publishedAt'],    [(user, doc) => doc.status === 'review']  )

• getVisibleFields() now returns reviewComments for editors when status === 'review'
• The React component already has {fields.includes('reviewComments') && ...}; it renders automatically
• The API response already uses filterFields(); it includes the field automatically
• No service method changes. No component changes. No database query changes.

ABAC Pros and Cons#

When ABAC Excels#

1. 3+ contextual rules per resource: If permissions depend on ownership, department, status, time, and other attributes, ABAC eliminates the helper function proliferation from Post 103.
2. Field-level visibility requirements: When different roles see different fields of the same resource, field permissions are cleaner than ad-hoc field stripping.
3. Database-level enforcement needed: When list views must be efficient, the toWhereClause() pattern eliminates in-memory filtering.
4. Audit requirements: A centralized policy builder is easier to audit than scattered helper functions. "What can an editor do?" is answerable by reading one section of the builder.
5. Policy changes are frequent: When business rules change often, modifying one condition in the builder is faster and safer than updating all service methods and components.

When ABAC Is Overkill#

1. Simple role-based access: If permissions depend only on role with no contextual conditions, RBAC from Post 103 is simpler and equally correct.
2. Small team, few resources: With 2-3 resources and 3-4 roles, the ABAC type system overhead (generics, builders, condition descriptors) may exceed the complexity it saves.
3. No field-level requirements: If all users see all fields of a resource, the field permission layer adds complexity without benefit.
4. Prototyping stage: ABAC's type system makes refactoring harder. During rapid prototyping where resource shapes change frequently, simpler checks are more practical.

Tip: Start with RBAC (Post 103). Add ABAC conditions only when helper functions start proliferating. Add field-level permissions only when different roles need different field visibility. Add DB query filtering only when list views load too many records.

Decision Framework#

Common Pitfalls#

1. Forgetting field filtering on nested resources: If a document has a project relation, loading document.project bypasses project field permissions. Apply filterFields() to nested resources as well.
2. Condition descriptor drift: The evaluate function and toFilter descriptor must produce equivalent results. Test both against the same truth table to catch drift.
3. Overusing field-level permissions: Not every resource needs field-level control. The default (no field entries = all fields visible) keeps things simple for resources without restrictions.
4. Confusing null and {}: In toWhereClause(), {} means "no filter" (all records) and null means "no access" (deny). Getting this wrong is a security vulnerability.
5. Missing select in DB queries: toWhereClause() filters rows, not columns. For true DB-level field enforcement, combine it with column selection. In practice, application-level filterFields() is often sufficient.

What's Next#

The permission system now covers record-level access (can()), field-level visibility (getVisibleFields(), pickPermittedFields()), and database-level enforcement (toWhereClause()). Environment conditions complete the NIST four-attribute model.

Post 106 addresses the remaining production concerns: multi-tenancy (tenant isolation as a first-class permission concept), permission library evaluation (CASL, Oso, Cerbos, Cedar; when to use a library vs. custom code), and the final architectural decision framework for choosing the right authorization approach based on team size, regulatory requirements, and system complexity.

References#

• NIST SP 800-162: Guide to Attribute Based Access Control (ABAC) - The foundational NIST standard defining environment attributes as the fourth ABAC category alongside subject, resource, and action
• CASL v6 - Restricting Fields Access - Official CASL documentation on field-level restrictions using permittedFieldsOf() and field arrays in rule definitions
• CASL - Isomorphic Authorization JavaScript Library - The library that pioneered the can() + field restriction pattern in JavaScript/TypeScript with MongoDB-style conditions
• Cerbos - Filtering Database Results with Query Plans - How Cerbos PlanResources API returns an AST that adapters convert into Prisma, MongoDB, or SQL queries
• Cerbos Prisma Integration V2.0 - Converting authorization query plans into Prisma where clauses with nested field support
• OPA - Write Policy in OPA, Enforce Policy in SQL - The OPA partial evaluation pattern that compiles Rego policies into SQL WHERE clauses
• Permit.io - Why Data Filtering Matters for Database Authorization - Analysis of source-level vs. gateway-level data filtering patterns across OPA, Cerbos, and SpiceDB
• Drizzle ORM - Dynamic Query Building - Official Drizzle documentation on $dynamic() for composable query building
• RBAC vs ABAC: Differences and When to Use (Oso) - Decision criteria for when ABAC complexity is justified vs. when RBAC is sufficient
• ZenStack - Three Ways to Secure Database APIs - Comparison of database-level (RLS), ORM-level, and application-level authorization strategies
• TypeScript Generics Documentation - Official reference for the generic constraint patterns used throughout the ABAC type system
• OWASP Authorization Cheat Sheet - Best practices including centralized authorization, deny by default, and fine-grained access control recommendations