Katkat
Back

Design an RBAC Relational Schema

ByteDanceSoftware Engineer · Technical Screen
MediumSoftware EngineerTechnical ScreenSystem Design

Last updated

This problem tests your ability to design a normalized relational database schema that implements Role-Based Access Control (RBAC) to support hierarchical users, roles, and resource-level permissions.

Scenario

You are designing the core authorization service for a large content platform. You need to implement an RBAC system that supports the following requirements:

  1. Users: A user can be assigned one or more roles.
  2. Roles: A role defines a collection of permissions. Roles should support a simple hierarchy (e.g., 'Editor' can inherit from 'Viewer').
  3. Resources & Permissions: Permissions are granular actions (e.g., 'read', 'write', 'delete') on specific resource types (e.g., 'article', 'comment').
  4. Scoped Access: The system must support permissions granted either globally or restricted to a specific resource instance (e.g., 'Editor' for Article #123).

Requirements

  1. Provide the Entity-Relationship (ER) model or table schema, including primary keys, foreign keys, and necessary link tables.
  2. Explain how to handle the role hierarchy (e.g., if a user has the 'Editor' role, they automatically possess all permissions of the 'Viewer' role).
  3. Write an SQL query to check if a specific user has 'delete' permission on 'article' with ID '500'.

Constraints

  • The schema must be normalized to at least 3NF.
  • Minimize the number of joins required for the most common authorization check (checking a user's permissions).
  • Assume a scale of 10 million users and 100 million resources.

Hints

Hint 1

Consider a junction table to manage the many-to-many relationship between users and roles, and another for roles and permissions.

Hint 2

For the role hierarchy, consider an adjacency list model where a role table has a self-referencing foreign key to define parent-child relationships.

Hint 3

To handle resource-specific scoping, your permission table needs columns to store 'resource_type' and optional 'resource_id', where a NULL 'resource_id' implies a global permission.

Follow-up questions

  • How would you optimize the authorization check performance if the role hierarchy becomes deeply nested or if authorization calls occur on every API request?
  • How would you modify your schema to support Attribute-Based Access Control (ABAC), such as allowing access only if the user's 'department' matches the resource's 'owner_department'?
  • What strategies would you use to audit permission changes for security and compliance requirements?