Row-Level-Security (RLS) based on Organizational Hierarchy in Bold BI

Hierarchy refers to a system or structure where elements are ranked or organized in levels, with higher levels having authority or precedence over lower levels. It’s often represented as a tree-like structure with parent-child relationships. Hierarchies are common in organizations, file systems, or any system where there is a clear top-to-bottom flow of control or relationship.

Key Points of Hierarchical Structure:

• Levels of Authority/Dependence: Higher levels (parents) control or influence lower levels (children). For example, in a company hierarchy, a manager (parent) oversees employees (children).
• Parent-Child Relationships: Each element in the structure is linked to another, forming chains of connection. In a family tree, a parent has children, and those children can become parents to others.
• Flow of Information or Responsibility: In a hierarchy, decisions, data, or tasks flow from the top to the bottom. For example, directives from a CEO move down to managers and then to employees.
• Top-Down or Bottom-Up Analysis: You can study the hierarchy by starting from the top (top-down) or the bottom (bottom-up), depending on what you want to analyze (e.g., from CEO to employees, or from specific employees to their supervisors).

Example:

Consider a company where the CEO oversees managers, and each manager supervises their own team of employees. When data is filtered hierarchically, the following applies:

1. The CEO can view all managers and employees under their supervision.
2. A manager can access information about employees who directly report to them.
3. A reporting person can see only those individuals who report to them.
4. An employee can only view their personal information.

Organizational structure Tree View

Table

Create and insert query for this example

CREATE TABLE employees (
   employee_id INT PRIMARY KEY,                          -- Unique identifier for each employee
   employee_name VARCHAR(100) NOT NULL,                  -- Employee's name
   employee_email VARCHAR(100) NOT NULL UNIQUE,          -- Employee's email, must be unique
   designation VARCHAR(50),                              -- Job title or designation
   employee_role VARCHAR(50),                            -- Role of the employee (e.g., Executive, Manager, etc.)
   created_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP,       -- Date when the record was created
   last_modified_on TIMESTAMP, -- Last modification timestamp
   branch_name VARCHAR(100),                             -- Branch name the employee belongs to
   category VARCHAR(50),                                 -- Employee category (e.g., Full-time, Part-time)
   current_user_location VARCHAR(100),                   -- Current location of the employee
   high_level_team_name VARCHAR(100),                    -- High-level team name the employee is part of
   reporting_person VARCHAR(100),                        -- Reporting person's name
   reporting_person_employee_id INT,                     -- Reporting person's employee ID
   reporting_person_employee_email VARCHAR(100),         -- Reporting person's email address
   status VARCHAR(20) CHECK (status IN ('Active', 'Inactive')), -- Status of the employee (Active/Inactive)
   team_name VARCHAR(100),                               -- Name of the team the employee belongs to
   manager_employee_id INT,                              -- Manager's employee ID
   manager_name VARCHAR(100),                            -- Manager's name
   manager_employee_email VARCHAR(100),                  -- Manager's email address
   FOREIGN KEY (reporting_person_employee_id) REFERENCES employees(employee_id),   -- Foreign key for reporting person
   FOREIGN KEY (reporting_person_employee_email) REFERENCES employees(employee_email),  -- Foreign key for reporting person's email
   FOREIGN KEY (manager_employee_id) REFERENCES employees(employee_id),            -- Foreign key for manager's ID
   FOREIGN KEY (manager_employee_email) REFERENCES employees(employee_email)       -- Foreign key for manager's email
); 

-- Inserting data hierarchically
INSERT INTO employees (employee_id, employee_name, employee_email, designation, employee_role, created_on, last_modified_on, branch_name, category, current_user_location, high_level_team_name, reporting_person, reporting_person_employee_id, reporting_person_employee_email, status, team_name, manager_employee_id, manager_name, manager_employee_email)
VALUES 
(1, 'Alice Johnson', '[email protected]', 'CEO', 'Executive', '2020-01-01', '2024-01-01', 'New York', 'Full-time', 'New York', 'Executive Team', NULL, NULL, NULL, 'Active', 'Executive Team', NULL, NULL, NULL),
(2, 'Bob Brown', '[email protected]', 'CTO', 'Executive', '2020-01-15', '2024-01-01', 'New York', 'Full-time', 'New York', 'Tech Team', 'Alice Johnson', 1, '[email protected]', 'Active', 'Tech Team', 1, 'Alice Johnson', '[email protected]'),
(3, 'Cathy White', '[email protected]', 'CFO', 'Executive', '2020-02-01', '2024-01-01', 'San Francisco', 'Full-time', 'San Francisco', 'Finance Team', 'Alice Johnson', 1, '[email protected]', 'Active', 'Finance Team', 1, 'Alice Johnson', '[email protected]'),
(4, 'David Black', '[email protected]', 'HR Manager', 'Manager', '2021-03-01', '2024-01-01', 'Chicago', 'Full-time', 'Chicago', 'HR Team', 'Cathy White', 3, '[email protected]', 'Active', 'HR Team', 3, 'Cathy White', '[email protected]'),
(5, 'Eve Green', '[email protected]', 'QA Manager', 'Manager', '2021-06-15', '2024-01-01', 'New York', 'Full-time', 'New York', 'QA Team', 'Bob Brown', 2, '[email protected]', 'Active', 'QA Team', 2, 'Bob Brown', '[email protected]'),
(6, 'Frank Orange', '[email protected]', 'Finance Manager', 'Manager', '2021-09-01', '2024-01-01', 'San Francisco', 'Full-time', 'San Francisco', 'Finance Team', 'Cathy White', 3, '[email protected]', 'Active', 'Finance Team', 3, 'Cathy White', '[email protected]'),
(7, 'Grace Blue', '[email protected]', 'HR Specialist', 'Specialist', '2022-01-01', '2024-01-01', 'Chicago', 'Full-time', 'Chicago', 'HR Team', 'David Black', 4, '[email protected]', 'Active', 'HR Team', 4, 'David Black', '[email protected]'),
(8, 'Hank Green', '[email protected]', 'Finance Analyst', 'Analyst', '2022-04-01', '2024-01-01', 'San Francisco', 'Full-time', 'San Francisco', 'Finance Team', 'Frank Orange', 6, '[email protected]', 'Active', 'Finance Team', 6, 'Frank Orange', '[email protected]'),
(9, 'Ivy Red', '[email protected]', 'QA Specialist', 'Specialist', '2022-05-01', '2024-01-01', 'New York', 'Full-time', 'New York', 'QA Team', 'Eve Green', 5, '[email protected]', 'Active', 'QA Team', 5, 'Eve Green', '[email protected]'),
(10, 'Jake Yellow', '[email protected]', 'Developer', 'Developer', '2022-07-01', '2024-01-01', 'New York', 'Full-time', 'New York', 'Tech Team', 'Bob Brown', 2, '[email protected]', 'Active', 'Tech Team', 2, 'Bob Brown', '[email protected]'); 

How to achieve hierarchical filtering in Bold BI?

In Bold BI, this can be achieved using code view mode and user-based filters. Follow the below steps explained with the above example:

1. Connect to a data source containing hierarchical data, such as employees, their reporting persons, and managers, which can be filtered based on the logged-in user’s email. Identify and select all employees who report to the current logged-in user (Employee).

WITH RECURSIVE Hierarchy AS (
   -- Base case: Select the logged-in user's information (root of the hierarchy)
   SELECT 
       employee_email, 
       reporting_person_employee_email, 
       manager_employee_email
   FROM 
       "public"."employees"
   WHERE 
       employee_email = @{{:CURRENTUSER.EMAIL}}  -- Filter based on logged-in user's email

   UNION ALL

   -- Recursive case: Select all employees who report to the user found in the previous result
   SELECT 
       u.employee_email, 
       u.reporting_person_employee_email, 
       u.manager_employee_email
   FROM 
       "public"."employees" u
   INNER JOIN 
       Hierarchy h 
       ON u.reporting_person_employee_email = h.employee_email  -- Recursive condition: find the direct reports
)
-- Final select to display the employee hierarchy
SELECT 
   employee_email, 
   reporting_person_employee_email, 
   manager_employee_email,
   @{{:CURRENTUSER.EMAIL}} AS currentmail  -- To show the current logged-in user for reference
FROM 
   Hierarchy

2. Create another data source and apply a user-specific filter based on your email ID and the current email in the hierarchical data source column.
   

   
   

3. Develop a dashboard utilizing your newly created data source.
   
   

In the dashboard view, according to the organizational chart shown below:

• Alice Johnson should be able to see all employee data.
• Cathy White should see 5 records, which include the HR team (2 records), the Finance team (2 records), and her own record.
• Frank Orange should see the Finance team’s report, which includes 2 records (1 employee and his own record).
• Hand Green should only see his own records.

This illustrates how hierarchical organizational row-level security should function

Logging in with Alice Johnson

Logging with Cathy White

Logging with Frank Orange

Logging with Hand Green

Additional References

Creating a new data source
User filter advanced data source mode
Manage Users