0

I have a couple of tables within an SQLite v3.37 meant to track Users, Roles, and Permissions.

For the sake of the question, the users has just an id and an email. roles and permissions are also simple, with just an id and a roleName and permName respectively.

CREATE TABLE permissions (id integer primary key not null, permName text unique not null, dateCreated text default CURRENT_TIMESTAMP, dateUpdated text default CURRENT_TIMESTAMP);

I have two auxiliary tables, userRoles and rolePerms which track what roles a user has been assigned, and what permissions have been assigned to each role respectively.

CREATE TABLE userRoles (userID integer not null, roleID integer not null, FOREIGN KEY (userID) REFERENCES users(rowid), FOREIGN KEY (roleID) REFERENCES roles(id));

Here is a rough diagram of the schema:

A database schema diagram

I am trying to create an SQL query that from an email address and a permName, determine if that user has been granted a role with that permission.

I have tried writing a query something like this:

SELECT users.rowid, users.email, roles.rowid, roles.roleName, permissions.rowid, permissions.permName, userRoles.*, rolePerms.* FROM users
    INNER JOIN userRoles on userRoles.userID=users.rowid AND userRoles.roleID=roles.roleID 
    INNER JOIN rolePerms on rolePerms.roleID=roles.rowid 
    WHERE users.email = '[email protected]' AND permName='testPermission';

But this query obviously isn't getting the job done. I have seen this question about joining 4 tables, but those tables are all related to a single table, not two like the schema I am using.

EDIT: I believe the following query to be working:

SELECT DISTINCT(permName) FROM users INNER JOIN userRoles ON userRoles.userID=users.rowID INNER JOIN roles ON userRoles.roleID=roles.id INNER JOIN rolePerms ON rolePerms.roleID=roles.id INNER JOIN permissions ON permissions.id=rolePerms.permID WHERE users.email='[email protected]';

The big change is to reference roles via a JOIN ON userRoles, otherwise the SQL query is not aware of the table(?)

1 Answer 1

0

Your query is almost correct, you just need to join with the permissions table as well, plus remove some confusion about column names and remove an extra join condition:

SELECT users.rowid, users.email, roles.id, roles.roleName, permissions.id, permissions.permName, userRoles.*, rolePerms.* 
FROM users
INNER JOIN userRoles ON userRoles.userID=users.rowid
INNER JOIN roles ON roles.id=userRoles.roleId
INNER JOIN rolePerms ON rolePerms.roleID=roles.id
INNER JOIN permissions ON permissions.id=rolePerms.permID
WHERE users.email= '[email protected]' AND permissions.permName='testPermission';
3
  • For some reason, I am getting a "no such column: roles.id" when running this query. However when running .schema roles, I can clearly see there is a column named "id"
    – JLCarveth
    Sep 15, 2022 at 14:05
  • If I change roles.id, roles.roleName to roles.* in the query, then I am told that there is 'no such table: roles'. My database is not corrupt or anything, as I checked with PRAGMA integrity_check;
    – JLCarveth
    Sep 15, 2022 at 14:14
  • ©JLCarveth Apologies, I had forgotten to join with the roles table. I've updated the query in the answer. Does that work now?
    – dbdemon
    Sep 15, 2022 at 15:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.