Many applications store values in a database and usually grant various access rights to the stored data. Not all users have access to all information and even the access right levels vary. Setting up an access control system to certain entity is simple, if we only need to restrict some parts of the application or information. The application could, for example, allow some users to edit items while others can just view them. In case we need item (database row) level permissions, the programmer’s task becomes a bit trickier. Some users may edit only part of the items stored in the database and this requirement brings a whole new set of challenges.
Let’s assume we have a database with a product table. Typically the table would look something like this:
id title
-- ------------------------------
1 Summer holiday in Spain
2 Illustrated English dictionary
3 Miss Marble’s detective story
4 Cooking for the weekend
5 Sailing around the worldCREATE TABLE Books (
id INT NOT NULL,
title VARCHAR(32) NOT NULL,
PRIMARY KEY (id)
);We also need a table for users. These users will be using the application and eventually have some access rights to the books table. Below is a simple user table.
id name role
-- --------------- ------
10 John Doe user
11 Mary Jennings user
12 Peter Moore user
13 Evelyn Light user
14 Mark Millington admin
15 Cheryl Wood editorCREATE TABLE Users (
id INT NOT NULL,
name VARCHAR(32) NOT NULL,
role VARCHAR(8) NOT NULL,
PRIMARY KEY (id)
);Without any access restrictions all users would have access to all books in the database. We now set a bunch of requirements to add some flavor to this example.
- The application has three roles: users, editors and administrators.
- Editors and administrators have all access rights to all books.
- Editor or administrator may set the access rights to a user or a company the user works for.
- Each user belonging in some company inherits the access rights granted to that company.
- If no access rights are set, the book is publicly available to all users.
- User may belong to multiple companies simultaneously.
According to the requirements we need a company table.
id name
--- --------------------
100 Bestseller Cookhouse
101 Editorial MagazineCREATE TABLE Companies (
id INT NOT NULL,
name VARCHAR(32) NOT NULL,
PRIMARY KEY (id)
);Each user belongs to at least one company. For this linking we need another table, for example Members, which is just a simply link table.
companyId userId
--------- ------
100 10
100 11
101 11
101 12
100 13CREATE TABLE Members (
companyId INT NOT NULL,
userId INT NOT NULL,
FOREIGN KEY (companyId) REFERENCES Companies (id)
ON DELETE CASCADE
ON UPDATE CASCADE,
FOREIGN KEY (userId) REFERENCES Users (id)
ON DELETE CASCADE
ON UPDATE CASCADE
);The Members table tells that user ID 10 (John Doe) works for company ID 100 (Bestseller Cookhouse). User 11 (Mary Jennings) works for two companies, namely 100 and 101. The Permissions table contains the access rights to books and it also tells, which company or user has been granted to the item.
CREATE TABLE Permissions (
bookId INT NOT NULL,
companyId INT DEFAULT NULL,
userId INT DEFAULT NULL,
FOREIGN KEY (bookId) REFERENCES Books (id)
ON DELETE CASCADE
ON UPDATE CASCADE,
FOREIGN KEY (companyId) REFERENCES Companies (id)
ON DELETE SET NULL
ON UPDATE CASCADE,
FOREIGN KEY (userId) REFERENCES Users (id)
ON DELETE SET NULL
ON UPDATE CASCADE
);This table has columns for the book item as well as company and user. When some user is granted to access a book, his or her user ID is inserted into this table along with the book ID. If a company is granted an access right, then the company ID is inserted instead of the user ID. Note that only company ID or user ID is populated. When the user ID is set, then the company ID is set to NULL and vice versa.
We have now set up the database tables needed to implement simple row level permissions. The next step includes granting some access rights to users and companies and creating a database view to query the rights. I will walk through these tasks in the latter part of this article.