Best practice implementing row level authorization?

(1) By rongcuid on 2022-08-07 15:33:06 [link] [source]

I am trying to implement role-based authorization on an application that uses SQLite as its data store. I am split on where I should put such logic.

Suppose it is a blog service, with tables users and blogs. There are the following authorization requirements:

A blog can only be edited/deleted by its author or users with Editor role
A deleted blog can only be viewed by its author

I have the following choices (I think):

Implement authorization on application level, checking user roles and blog permissions in separate queries
Implement a large query that includes the current user's id, joins the user/blog table, and leave columns NULL if unauthorized
Implement a view that creates a CROSS JOIN of users.id and blogs.id, essentially computing a function users.id x blogs.id -> permission, then use it in query

For SQLite, which method would be better? In terms of maintainability, performance, etc.

(2) By anonymous on 2022-08-07 19:37:30 in reply to 1 [source]

It depends whether or not users can submit their own SQL queries.

If not, then it can be implemented in the application level. Use a host parameter or SQL function for the current user ID, and permissions could be checked once when loading the user record.

But if users can submit SQL queries, then, at least how I would do it is to implement a view to access the blogs table with restrictions, and then add a authorizer callback to prevent direct access to the blogs table, requiring that users only access the view (you can add triggers to the view, to check for authorization for writing).

(3) By rongcuid on 2022-08-07 23:50:40 in reply to 2 [link] [source]

I am referring to the following.

1. Application level (assume viewing)

SELECT role FROM users WHERE id = ?user_id
SELECT author_id, public, deleted
In application logic, determine if user_id with role has access to the blog
SELECT * FROM blogs WHERE id = ?blog_id

2. DB level, with subqueries:

WITH permissions AS
(SELECT b.id
 FROM blogs b 
  INNER JOIN users u ON b.author_id = u.id
 WHERE u.role = 'moderator'
   OR b.author_id = ?user_id)
SELECT blogs.* FROM permissions LEFT JOIN blogs ON permissions.id = blogs.id

3. DB level, with views:

CREATE VIEW permission_view AS
SELECT u.id user_id, b.id blog_id, CASE (a complicated statement to determine permission) END AS permission
FROM users u CROSS JOIN blogs

Then in queries

SELECT blogs.* FROM blogs b INNER JOIN permission_view p ON b.id = p.blog_id 
 WHERE p.user_id = ?user_id AND p.permission = 'read'

I am currently using the second method. However, I don't know if I should really use the first so I move logic to application layer, or use third to move into DB layer. How's their performance implication? What about security or long term maintainability?

(7) By anonymous on 2022-08-09 00:02:16 in reply to 3 [link] [source]

Are you aware of the statement_vtab extension? It allows to create parametrized table valued functions, and these can make your life a lot easier as you implement row level permissions at the DB level, consider it option #4.

If we apply this to your example, we could come up with something like this:

CREATE VIRTUAL TABLE is_mod USING statement ((
   SELECT true FROM users WHERE id = ?1 AND role = 'moderator'
))

Then later on in your query:

SELECT * FROM blogs WHERE id = ?1 AND user_id = ?2 OR (SELECT * FROM is_mod(?2))

In the above, the varialbles ?1 and ?2 refer to the blog_id and the user_id, respectively.

You can create more complex table valued functions to make your life even easier, for example by creating a is_mod_or_owner function that would render your subsequent queries shorter.

(4) By Gunter Hick (gunter_hick) on 2022-08-08 05:58:29 in reply to 1 [link] [source]

See https://sqlite.org/c3ref/set_authorizer.html for the documentation on using the authorizer callback.

You can allow/deny read/write/delete access on a table/column basis.

E.g. in your application, call sqlite3_set_authorizer(<conn>,<func>,'guest') to set access for anonymous access, and again with the real user name as soon as the user logs in.

For allowing/denying actions on rows, use WHERE constraints to exclude rows not covered by the permission of the user.

... (blog.state != 'deleted' OR blog.author == current_user()) ...


UPDATE and DELETE can also be prohibited by an appropriate BEFORE trigger

... SELECT raise(ABORT,'not permitted') WHERE new.author != current_user() and current_role != 'editor';

NOTE: Instead of the assumed application defined functions for getting the current user and role, you could also use a single row temporary table and replace function calls with subselects (e.g. SELECT userid from CURRENT).

(5) By rongcuid on 2022-08-08 17:01:10 in reply to 4 [link] [source]

I don't know SQLite well enough, but what is this current_user() function or the CURRENT keyword?

(6) By Gunter Hick (gunter_hick) on 2022-08-08 17:24:37 in reply to 5 [link] [source]

current_user() would be a function that you write to give SQLite access to what your application thinks is the current user.

Alternatively you could

CREATE TEMP TABLE current (userId INTEGER, roleId INTEGER);
INSERT INTO current VALUES (0,0);

Then your application could update the only record in the table to reflect what it thinks is the current user, and the triggers/constraints would be able to access it.