Row Level Security (RLS)

1. Introduction

Row Level Security (RLS) is a data access control mechanism that restricts users’ access to records at the row level. Instead of controlling access only at the object or table level, RLS ensures that users can view or modify only those records they are explicitly permitted to access.

In this application, RLS is implemented at the application layer using metadata-driven rules and permission objects. The system dynamically injects filtering conditions into queries based on the current user, their permissions, and configured RLS rules.

---

2. Key Concepts

2.1 Source Object

The object that represents users or user groups.

Examples:

• users

• roles

• departments

---

2.2 Target Object

A Target Object is the data object on which RLS is enforced. Examples include business data such as orders, profit and loss records, or uploaded files

---

2.3 Permission Object

A Permission Object is a special object used to define row-level access rules. It acts as a bridge between users and the data they are allowed to access.

Each Permission Object contains:

• system_user_id / citta_user_id – Reference to the platform user

• citta_permissions – Bitwise integer representing access level

• One or more business fields – Used to match rows in the target object (e.g., department, business unit, owner_id)

The Permission Object does not store data; it stores who can access which data.

---

2.4 Permission Bits

The citta_permissions field controls allowed operations using a 2-bit binary model:

Decimal Value	Binary	Access Level
1	01	Read only
2	10	Write (Insert, Update, Delete)
3	11	Read + Write

Write permission implicitly includes Insert, Update, and Delete operations.

---

2.5 Derived Permissions

Derived Permissions allow RLS to work across indirect relationships.

When the Source Object is not directly linked to the Target Object, intermediate objects can be marked as:

"citta": {
  "isUsedToDerivePermissions": true
}

This enables RLS to traverse multiple relationships (e.g., Country → Business Unit → Team) to derive valid rows.

---

3. RLS Modes

3.1 None Mode

• RLS is disabled

• No row-level filtering is applied

• All data is visible (subject to object-level permissions)

• This mode is by default selected for all objects.

---

3.2 Standard Mode

Standard Mode is used for simple RLS scenarios where filtering is based on a single field.

Configuration includes:

• Target Object field used for filtering

• Permission Object selection

Example:

• Target Object field: department

• Permission Object field: department

Resulting logic:

• Users can only see rows where the department matches their permission record

This mode automatically generates filtering conditions without requiring SQL input.

---

3.3 Advanced Mode

Advanced Mode is used for complex RLS scenarios involving:

• Multiple fields

• Hierarchical data

• Custom business logic

In this mode, administrators define a custom SQL query.

Example pattern:

SELECT p.citta_user_id,
       p.citta_permissions,
       t.team_id AS rls_on
FROM permission_object p
JOIN mapping_table m ON ...
JOIN target_table t ON ...

The system uses the query result to dynamically restrict rows visible to the user.

---

4. Role-Based Behavior

Role	RLS Behavior
Tenant Admin	Sees all data (RLS bypassed)
Environment Admin	RLS applied
Application Admin	Sees all data (RLS bypassed)
Application User	RLS applied

---

5. Failure Scenarios

Scenario	Result
No permission record	No data visible
Invalid SQL in Advanced Mode	RLS not applied / error
Permission removed	Data access revoked immediately

---

6. Example: Employees & Departments

Objects

Source Object: Department

Field	Type
department_id	Number (PK)
department_name	String

Target Object: Employee

Field	Type
employee_id	Number (PK)
employee_name	String
department_id	Lookup → Department

Permission Object: Department_User_Permission

Field	Type
citta_user_id	Number
department_id	Lookup → Department
citta_permissions	Number (1 / 2 / 3)

---

Sample Data

Departments

department_id	department_name
1	HR
2	Engineering

Employees

employee_id	employee_name	department_id
101	Alice	1
102	Bob	2
103	Charlie	2

Department_User_Permission

citta_user_id	department_id	citta_permissions
5001	2	1

Result:

• User 5001 logs in

• RLS enabled in Standard mode

• User can see:

  • Bob (Engineering)

  • Charlie (Engineering)

• User cannot see:

  • Alice (HR)

---

7. Summary

Row Level Security provides a flexible, metadata-driven mechanism to enforce fine-grained data access. By separating permission logic from data objects and supporting both simple and advanced configurations, the system enables secure, scalable, and maintainable access control across complex data models.

---