The first part of this article described how to set up the database tables to implement simple row level permissions. This part shows how to actually use the tables and query books with different access rights. To query access rights I use an extensive SQL query. The basic idea is to first find books that have some restrictions set and then list the rest of the books, which do not have any restrictions and are publicly available. For the following examples we add some permission into the table. Book title "Cooking for the weekend" is only available for users working for "Bestseller Cookhouse" company. John and Mary can see the "Miss Marble’s detective story" book while it is hidden for others. All other books are visible to all users.
bookId companyId userId
------ --------- ------
4 100 NULL
3 NULL 10
3 NULL 11Next I start building the query to check the rights. The first query finds individual users who have rights to certain books.
SELECT bookId, userId
FROM Permissions
WHERE userId IS NOT NULL;This will return two rows as expected:
bookId userId
------ ------
3 10
3 11Following the requirements we need to check company rights, which inherit to the users working in those companies. The following query will return three rows.
SELECT p.bookId, m.userId
FROM Permissions p, Members m
WHERE p.companyId = m.companyId
AND p.companyId IS NOT NULL;All users (John, Mary and Evelyn) work for company ID 100 (Bestseller Cookhouse), which was listed in the Permissions table. Administrators and editors have always all rights to all books, so the following query returns six rows.
SELECT p.bookId, u.id as userId
FROM Permissions p, Users u
WHERE u.role IN ('admin', 'editor');The Permissions table currently has two books, which have special access rights. One of these books is listed twice, because two users have personal rights to these items. Totaling three rows and one administrator and one editor makes a Cartesian product, which contains six rows. Next we need to combine all these queries with UNION and if using UNION DISTINCT of MySQL, we can get rid of the duplicate rows.
SELECT bookId, userId
FROM Permissions
WHERE userId IS NOT NULL
UNION DISTINCT
SELECT p.bookId, m.userId
FROM Permissions p, Members m
WHERE m.companyId = p.companyId
AND p.companyId IS NOT NULL
UNION DISTINCT
SELECT p.bookId, u.id as userId
FROM Permissions p, Users u
WHERE u.role IN ('admin', 'editor');The list of special access rights retrieved from the Permissions table looks like this:
bookId userId
------ ------
3 10
3 11
3 14
3 15
4 10
4 11
4 13
4 14
4 15The list is still missing the books, which have no access restrictions. All such books will have no user ID to check to, so I let the query return NULL value in place of userId column. I query only books, which are not listed in the Permissions table and use an inner query for simplicity in this example. The following SQL does the job:
SELECT id AS bookId, NULL as userId
FROM Books
WHERE id NOT IN (
SELECT DISTINCT bookId
FROM Permissions
);Combining this with the rest of the queries produces an SQL, which I wrap as a view:
CREATE OR REPLACE VIEW Rights AS
SELECT bookId, userId
FROM Permissions
WHERE userId IS NOT NULL
UNION DISTINCT
SELECT p.bookId, m.userId
FROM Permissions p, Members m
WHERE m.companyId = p.companyId
AND p.companyId IS NOT NULL
UNION DISTINCT
SELECT p.bookId, u.id as userId
FROM Permissions p, Users u
WHERE u.role IN ('admin', 'editor')
UNION DISTINCT
SELECT id AS bookId, NULL as userId
FROM Books
WHERE id NOT IN (
SELECT DISTINCT bookId
FROM Permissions
);In addition to the earlier list this query will add three more rows:
bookId userId
------ ------
1 NULL
2 NULL
5 NULLSo, books with ID values 1, 2 and 5 are freely available with no restrictions. I like to work with simple structures and this is why I wrap the huge query as a database view, which is a lot easier to use in the application logic later.
Using the Rights view
When the user logs in the application, his or her user ID is stored in the session and used each time the system makes queries to the Books table. Let’s assume that Peter has logged in. His user ID is 12 and the query looks like this:
SELECT b.*
FROM Books b, Rights r
WHERE b.id = r.bookId
AND (r.userId = 12
OR r.userId IS NULL);Peter sees a book listing:
id title
-- ------------------------------
1 Summer holiday in Spain
2 Illustrated English dictionary
5 Sailing around the worldPeter has no personal access rights to special books nor does he work for any company that is listed in the Permissions table, and he is able to view only the publicly available books. Now Cheryl the editor logs in. Her user ID is 15 and the books she sees using the query above are listed below:
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 worldNow that Cheryl has editor role and all editors have access to all books, she gets the whole list. Evelyn works for company ID 100 (Bestseller Cookhouse), which has access rights to book ID 4 (Cooking for the weekend). Evelyn gets a list of free books in addition to the book available to her company:
id title
-- ------------------------------
1 Summer holiday in Spain
2 Illustrated English dictionary
4 Cooking for the weekend
5 Sailing around the worldShe cannot see book "Miss Marble’s detective story", because it is explicitly granted to John and Mary only.
Final words
Setting up simple row level permissions requires an additional Permissions table and a view to check the access rights with each user. Heavy queries and views usually have their drawbacks as decreased performance. You can improve this by using indices and simple data structures.