How to Design Role and Permission Tables in Three Steps

by Souy Soeng /October 21, 2026

Diagram of users, roles and permissions tables connected by user_roles and role_permissions join tables

Role and permission tables are a small set of database tables that answer one question: is this user allowed to do this thing? In this guide you'll build them in three steps. You'll create separate tables for users, roles and permissions, link them with two join tables, and then check access with a single query.

We'll use one example throughout: a simple blog where some people can edit posts and others can only read them. The design works in almost any app with more than one user. It also stays tidy as the app grows, because each table does exactly one job.

In short

Give permissions to roles, then give roles to users. This is role-based access control (RBAC).
Step 1: create separate users, roles and permissions tables, each with a primary key.
Step 2: link users to roles with a user_roles join table.
Step 3: link roles to permissions with role_permissions, then query through both join tables.
In your code, check permissions, not role names.

Gather what you need before you start

Before writing any SQL, be clear about the goal. You are building a design that answers one question quickly and reliably: can this person do this action, yes or no? Everything else in this guide supports that one question.

The finished design has three main tables and two join tables. The users, roles and permissions tables each hold their own data. The two small linking tables connect them without repeating anything. In the blog example, editors can edit and read posts, and readers can only read them.

A SQL database such as PostgreSQL (MySQL or SQLite also work)
A way to run SQL queries, like a terminal or a database tool
A basic understanding of what a primary key is
An app with several users, which is where access starts to matter

Understand how role-based access control works

RBAC stands for Role Based Access Control. Put simply, what you're allowed to do depends on the role you hold, not on who you are. You never give a permission directly to a person. You give them a role, and the role carries the permissions.

Here is how that looks in the example. Ana is a user. Ana has the Editor role. The Editor role grants edit_post. Ana never holds the permission herself; the role sits in the middle and does the work. If you later give editors a new permission, every editor gets it at once. You change one row instead of a hundred user accounts.

This extra layer is the reason so much software stays manageable. Without roles, every new user means setting permissions one by one. With roles, you pick a role and you're done. You'll find this pattern in admin panels that hide dangerous buttons, SaaS apps that manage team members, content sites with writers and editors, and many other multi-user business apps.

Roles also reduce mistakes. When rights live on roles, you're far less likely to leave one forgotten account with old, risky permissions. When a rule changes, such as letting editors delete comments, you update the role once and every holder follows.

Permissions belong to roles
Roles are assigned to users
One change to a role updates everyone who holds it

Create the users, roles and permissions tables

Step one is to create the three main tables, each with its own primary key. Keep it simple. Each kind of thing gets its own table, so nothing gets mixed together. Create the users table with an id as the primary key and a name. Then create the roles table the same way, and finally the permissions table. After running all three statements, you should have three empty tables.

Primary keys matter most here. Each row gets a unique id, and the join tables will point to those ids later. Without them you have nothing reliable to link to. Unique names on roles and permissions prevent duplicates. Two rows both called editor would leave nobody sure which one counts.

Notice that the users table has no role column. That's on purpose. Keeping the three tables separate is what makes the flexible links in the next steps possible.

Every table has an id primary key
Role and permission names are unique
The users table has no role column

CREATE TABLE users (
  id INT PRIMARY KEY, name TEXT);
CREATE TABLE roles (
  id INT PRIMARY KEY, name TEXT UNIQUE);
CREATE TABLE permissions (
  id INT PRIMARY KEY, name TEXT UNIQUE);

Link users to roles with a user_roles table

Step two connects users to roles through a join table called user_roles. You need it because this is a many-to-many relationship. Ana might be both an editor and a moderator, and the editor role belongs to many people. A single column on the users table can't express that.

The user_roles table holds a user id that references users and a role id that references roles. Its primary key is the pair of both columns. Once it exists, insert one row giving user one the role with id two.

Each row means one thing: this user has this role. Because both columns are foreign keys, the database checks that both ids really exist. Using the pair as the primary key means the same user can't get the same role twice, so the links stay clean and easy to trust.

user_id references users
role_id references roles
The (user_id, role_id) pair is the primary key

CREATE TABLE user_roles (
  user_id INT REFERENCES users(id),
  role_id INT REFERENCES roles(id),
  PRIMARY KEY (user_id, role_id));
# user 1 gets role 2
INSERT INTO user_roles VALUES (1, 2);

Link roles to permissions with role_permissions

Step three repeats the same pattern for the other side of the chain. If the user_roles table made sense, this one will feel familiar. Only the two tables being linked have changed. Create a role_permissions table with a role id that references roles and a permission id that references permissions, and make the pair the primary key.

Now one role can grant many permissions, and one permission can belong to many roles. For the blog, insert rows so the editor role gets edit_post and read_post, while the reader role gets only read_post.

Name your actions clearly, using verbs like edit_post or delete_comment instead of vague labels. Clear names make your access rules readable at a glance. With this table filled in, every link in the chain exists.

Editor role gets edit_post and read_post
Reader role gets only read_post
Permission names describe an action

CREATE TABLE role_permissions (
  role_id INT REFERENCES roles(id),
  permission_id INT
    REFERENCES permissions(id),
  PRIMARY KEY (role_id, permission_id));

Query whether a user has a permission

With all the links in place, one query can walk through both join tables. Your app will ask this question every time someone clicks an edit button or opens an admin page. Start from user_roles, join role_permissions on the role id, then join permissions to get the permission name. Finally, filter for the user's id and the permission you care about, such as user one and edit_post.

The rule for reading the result is simple: any row means allowed, and no rows means denied. Your app doesn't need to know which role granted the permission. It only cares whether at least one row comes back, which keeps your application code short.

The path runs from users, through user_roles to find their roles, through role_permissions, and lands on permissions. One question, answered from start to finish.

One or more rows returned: allowed
Zero rows returned: denied

SELECT 1 FROM user_roles ur
JOIN role_permissions rp
  ON rp.role_id = ur.role_id
JOIN permissions p ON p.id = rp.permission_id
WHERE ur.user_id = 1
  AND p.name = 'edit_post';

Test an allowed user and a denied user

Good testing checks both sides. Allowed users must get in, and everyone else must stay out. Start with the editor. User one wants edit_post. The query finds the editor role, then that role's permissions, edit_post and read_post. It matches edit_post, so the result is allowed.

Next, change the user id to someone who holds only the reader role and run the same query. It should return no rows, which means denied. To prove the design works, add edit_post to the reader role and run the query again. Every reader is now allowed, without touching a single user record. If both tests behave as expected, your tables are working.

Editor checking edit_post returns a row
Reader checking edit_post returns no rows
Granting the reader role edit_post flips the result for all readers

Avoid the most common role table mistakes

Three mistakes come up often. None of them break things right away, but they cause trouble as your app and your team grow. The first is hardcoding roles, where your code checks whether the role is admin. Check the permission instead, so roles can change without code changes.

The second is skipping the join table and putting a single role_id column on users. That limits every user to one role. Use user_roles for the many-to-many link. The third is granting permissions that are too broad. Give each role only what it truly needs.

One rule covers most of this: your code asks about permissions, and your tables decide which roles have them.

Check permissions in code, not role names
Use user_roles instead of a role column on users
Grant each role only what it needs

Key takeaways

RBAC gives permissions to roles, and roles to users
Create separate users, roles and permissions tables, each with a primary key
Link users to roles with user_roles, and roles to permissions with role_permissions
Use one query through both join tables: a row means allowed
Always check permissions in code, never role names
Each table does one job, so access changes mean editing rows, not rewriting code

Frequently asked questions

Why not just add a role column to the users table?

A single role column limits each user to one role. A user_roles join table lets one user hold several roles, such as editor and moderator, and lets one role belong to many users.

What is a join table in a role and permission design?

A join table is a small table that stores pairs of ids to connect two other tables. Here, user_roles pairs users with roles, and role_permissions pairs roles with permissions.

Should my code check the role name or the permission?

Check the permission. If your code asks whether a user has edit_post, you can change which roles grant it in the database without touching your code.

Does this design work with MySQL or SQLite?

Yes. The example uses standard SQL concepts like primary keys, foreign keys and joins, so it works in PostgreSQL, MySQL or SQLite.

How do I know if a user is allowed to do something?

Run the query that joins user_roles, role_permissions and permissions, filtered by the user id and permission name. If at least one row comes back, the user is allowed; if none comes back, they're denied.

Watch the full video on YouTube →

Tag

Ad