Introduction

Enterprise applications contain highly sensitive data. Not every user is allowed to see every row or column. In many systems, permissions are hardcoded inside service classes or SQL queries. This becomes a maintenance burden when business rules change frequently.

A Dynamic Row and Column Permission System solves this problem by:

This article explains a complete implementation using:

It also includes workflow diagrams, flowcharts, and production-ready patterns.

The goal is to design a system that can scale across departments, tenants, and modules without code changes.

High-level requirements

A dynamic permission system must support:

  1. Row-level security

    • Example: A Sales Executive should only see documents of their region.

    • Example: A Manager can see all employees under their business unit.

  2. Column-level security

    • Example: A support user should not see salary column.

    • Example: A vendor user should not see internal cost price.

  3. Field masking

    • Show partial values like:

      • Phone: 98XXXX432

      • Email: j***@company.com

  4. Rule-based evaluation

    • Conditions defined using attributes like Role, Department, Location, Grade, Ownership.

  5. Versioned rules

    • Update rules without affecting existing sessions.

  6. Caching layer

    • Improves performance for large rule sets.

  7. Audit logging

    • Who accessed what data and using which permission.

Overall architecture

Angular UI
  |
  v
.NET API
  |
  v
Rule Engine (Row Rules + Column Rules)
  |
  v
Data Access Layer
  |
  v
SQL Server (Data + Permission Metadata)

Workflow diagram (end-to-end flow)

 +---------------+
 |  Angular App  |
 |  Makes Request|
 +-------+-------+
         |
         v
 +---------------------+
 | .NET API Controller |
 +---------+-----------+
           |
           v
 +---------------------------+
 | Permission Rule Evaluator |
 +------+--------------------+
        | Row Filter
        | Column Filter
        v
 +-------------------------+
 | Data Access + SQL Query |
 +-----------+-------------+
             |
             v
 +--------------------------+
 | Filtered Result Returned |
 +--------------------------+

Flowchart (permission evaluation)

            +----------------------+
            | Incoming API Request |
            +----------+-----------+
                       |
                       v
            +----------------------+
            | Identify User Roles  |
            +----------+-----------+
                       |
                       v
         +-------------------------------+
         | Load Row + Column Rules       |
         | From Database / Cache         |
         +---------------+---------------+
                         |
                         v
             +--------------------------+
             | Apply Row Filter         |
             | Create SQL Predicate     |
             +-------------+------------+
                           |
                           v
             +--------------------------+
             | Fetch Data from SQL      |
             +-------------+------------+
                           |
                           v
             +--------------------------+
             | Apply Column-Level Rules |
             | Remove/Mask Columns      |
             +-------------+------------+
                           |
                           v
             +--------------------------+
             | Send Secured Response    |
             +--------------------------+

Data model design

table: PermissionRuleSet

Stores metadata for each module or table.

RuleSetId INT PK
ModuleName VARCHAR(200)
EntityName VARCHAR(200)
IsActive BIT
Version INT

table: RowPermissionRule

Defines row-level access conditions.

RuleId INT PK
RuleSetId INT FK
LeftOperand VARCHAR(200)      -- Column Name
Operator VARCHAR(20)          -- =, !=, IN, LIKE, etc.
RightOperand VARCHAR(500)     -- Value or token like {User.RegionId}
LogicGroup VARCHAR(5)         -- AND/OR

table: ColumnPermissionRule

Defines which fields to hide or mask.

RuleId INT PK
RuleSetId INT FK
ColumnName VARCHAR(200)
AccessType VARCHAR(20)        -- FULL, HIDDEN, MASK
MaskPattern VARCHAR(100)      -- ***####*, etc.

table: PermissionAssignment

Maps which roles or users get which rule sets.

AssignmentId INT PK
RuleSetId INT FK
RoleName VARCHAR(200)
UserId INT NULL

Implementing row-level security

Row filtering must happen before executing SQL. The rule engine converts metadata to a SQL predicate.

example rules for Employees table

Rule 1: DepartmentId = {User.DepartmentId}
Rule 2: LocationId IN ({User.AllowedLocations})

SQL predicate generated

WHERE DepartmentId = 4 AND LocationId IN (2,5)

.NET code: build row filter

public string BuildRowPredicate(IEnumerable<RowPermissionRule> rules, UserContext user)
{
    var parts = new List<string>();

    foreach (var rule in rules)
    {
        var rightValue = ResolveToken(rule.RightOperand, user);

        string condition = rule.Operator.ToUpper() switch
        {
            "=" => $"{rule.LeftOperand} = '{rightValue}'",
            "IN" => $"{rule.LeftOperand} IN ({rightValue})",
            "LIKE" => $"{rule.LeftOperand} LIKE '%{rightValue}%'",
            _ => throw new Exception("Unsupported operator")
        };

        parts.Add(condition);
    }

    return string.Join(" AND ", parts);
}

private string ResolveToken(string token, UserContext user)
{
    if (token == "{User.DepartmentId}")
        return user.DepartmentId.ToString();

    if (token == "{User.AllowedLocations}")
        return string.Join(",", user.AllowedLocations);

    return token;
}

Implementing column-level security

Column-level rules are applied after data is fetched from SQL but before returning JSON.

.NET DTO filtering

public object ApplyColumnRules(object data, IEnumerable<ColumnPermissionRule> rules)
{
    var json = JObject.FromObject(data);

    foreach (var rule in rules)
    {
        if (rule.AccessType == "HIDDEN")
        {
            json.Remove(rule.ColumnName);
        }
        else if (rule.AccessType == "MASK")
        {
            json[rule.ColumnName] = MaskValue(json[rule.ColumnName]?.ToString(), rule.MaskPattern);
        }
    }

    return json;
}

masking function

private string MaskValue(string value, string pattern)
{
    if (string.IsNullOrEmpty(value)) return value;

    // Simple pattern: ***####  
    int maskCount = pattern.TakeWhile(c => c == '*').Count();
    int showCount = pattern.Count(c => c == '#');

    string masked = new string('*', maskCount);
    string visible = value.Length >= showCount
                    ? value.Substring(value.Length - showCount)
                    : value;

    return masked + visible;
}

.NET middleware for security evaluation

A custom middleware can intercept each API request and enrich request context with:

app.Use(async (context, next) =>
{
    var user = await userContextLoader.Load(context);
    context.Items["UserContext"] = user;

    await next();
});

Angular front-end integration

Angular should not implement permission logic. It just obeys the final response from the API.

example: columns returned from API

{
  "employeeName": "Ravi Kumar",
  "phone": "*******321",
  "salary": null,
  "department": "IT"
}

Angular table

this.http.get('/api/employees')
  .subscribe((rows: any[]) => {
    this.columns = Object.keys(rows[0]);
    this.data = rows;
});

dynamic UI rendering

Angular dynamically generates columns based on server response.

Admin rule editor (optional)

You can build a rule editor in Angular:

A drag-and-drop UI can improve usability.

Caching and performance

recommended approach

Row rules and column rules rarely change, so caching is safe.

Audit logging

Every filtered API response should log:

This is mandatory for compliance-driven industries like finance, healthcare, defense.

Advanced use cases

1. multi-tenant filtering

Each tenant sees only its own data.

2. workflow-dependent filters

For example, users can only see records in a workflow stage relevant to them.

3. attribute-based access control (ABAC)

Rules built using attributes like:

4. hybrid security system

Combine role-based + row-based + column-based security.

Example end-to-end scenario

user

Role: Sales Executive
RegionId: 3
Grade: 5

rule set

Row rule: RegionId = {User.RegionId}
Column rule: Salary → MASK pattern ***####
Column rule: InternalCost → HIDDEN

response returned to Angular

[
  {
    "EmployeeName": "Suresh",
    "RegionId": 3,
    "Phone": "*******812",
    "Salary": "***9123",
    "InternalCost": null
  }
]

Security considerations

  1. Never perform filtering in Angular.

  2. Ensure SQL injection safety in dynamic predicates.

  3. Use parameterized queries.

  4. Restrict admin rule-editing screens.

  5. Validate mask patterns to avoid bypass.

  6. Protect metadata APIs with strong authentication.

Conclusion

A Dynamic Row and Column Permission System ensures that sensitive data is shown only to authorised users without modifying code every time business rules change. By centralising rules in the database and evaluating them in a rule engine, the application becomes more flexible, secure, and maintainable.

This article provided:

This architecture is used in CRM, ERP, Banking, HRMS, and multi-tenant SaaS platforms where data access must be tightly controlled.