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:
Decoupling permissions from code
Loading access rules from database
Enforcing restrictions at API level
Applying field-level masking at run-time
Allowing admin users to define rules without deployment
Providing audit logs for compliance
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:
Row-level security
Column-level security
Field masking
Rule-based evaluation
Conditions defined using attributes like Role, Department, Location, Grade, Ownership.
Versioned rules
Caching layer
Audit logging
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:
UserId
Roles
Departments
Locations
Grade
Permissions
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:
UserId
RuleSetId
Timestamp
Query executed
Columns hidden or masked
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:
User.Location
User.ShiftType
User.Grade
User.SecurityClearance
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
Never perform filtering in Angular.
Ensure SQL injection safety in dynamic predicates.
Use parameterized queries.
Restrict admin rule-editing screens.
Validate mask patterns to avoid bypass.
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:
Row-level filtering design
Column-level masking logic
Metadata structure
Angular integration
.NET implementation
Workflow diagrams
Security guidelines
Real-world best practices
This architecture is used in CRM, ERP, Banking, HRMS, and multi-tenant SaaS platforms where data access must be tightly controlled.