CODEWITHSUNDEEP
mysqlpermissions
Kevin Rave
Kevin Rave

Reputation: 14426

Returning the results only when the user has permissions

I am wondering if there are any better ways to deal with this scenario. Although, its simple, keeping number of hits in mind, I am trying to eliminate a query.

I have list of Books. Only users assigned to this book can see books and other contents in it. Its basically API call. /Books/, /Books/Chapters/, /Books/Chapters/Pages/, etc. The permissions are attached to Books entry in the database.

Books, 'Chapters,Pages`, etc are all in different tables. User permissions are stored in a different table. The table is something like this.

    UserID,  BookID
    --------------
     101      1
     102      1
     102      2
     ...

User info is grabbed from the API call itself (Basic Auth). The API should return 401 Unauthorized, if the requested Book exist, but the user does not have permissions to that Book. If the requested 'Bookdoes not exist, then it should return404 Not found`.

Usually, first I need to check the permission for that user and for the requested Book, with a query. If the record exists in the user permission table for that user, then I should run the another query to get the Book, 'Chapter`, etc.

Currently, I am combing these two queries with 'WHERE EXISTScondition that checks for the existence of the record for this user for the requestedBook` in the permissions table. So the records will be returned only when there is a record in the user permission table.

But with this, I will not know, if the Book does not exist or if the user does not have permission, though I could cut down one database call to check user permission separately. And with this, I cannot distinguish 401 and 404.

Is there a better way to do this while being able to distinguish 401 and 404 with a singe query? Or its better to do two database calls?

Your thoughts?

Upvotes: 0

Views: 303

Answers (2)

hall.stephenk
hall.stephenk

Reputation: 1165

I propose you embed a subquery like this.

SELECT 
    (SELECT
         1
     FROM
         permissions
     WHERE
         permissions.user_id=<user ID> AND permissions.book_id=<book ID>)
    ) AS Permission,
    *
FROM
    books
WHERE
    books.id=<book ID>

You'll have a new column named "Permission" which will be 1 if the user has permission or NULL if the user doesn't have permission to the book.

EDIT: I simplified the subquery by having it return either "1" or "NULL".

Upvotes: 1

Reza S
Reza S

Reputation: 9748

You need to have your applicatin ACLs (Access Control Lists) That way you can assign permissions to various entities in your database. Have a look at this question

Upvotes: 0

Related Questions