CODEWITHSUNDEEP
mysql
Catalin Bozianu
Catalin Bozianu

Reputation: 13

MySQL logic issue: Which is the best way to give permission to users to different rows from different tables?

We have a few tables: users, units, classes, schedules and marks (can be more)

I want to give permission to one user or another to view / update / delete one row from one table.

I can't use roles, because I don't have a particular rule for this.

My solution for this is: create a table named: permissions ( id, table, id_table, id_user, view, update, delete ) and passing users with rows tables, but when this table will have 70k rows, the next statement will take a while ( I tested with a db seeder ): 

SELECT *
FROM marks
WHERE id_mark IN (SELECT id_table FROM permissions WHERE table = 'marks' AND id_user = '1')

Which is the best way to give permission to users to different rows from different tables?

Upvotes: 0

Views: 62

Answers (1)

Shadow
Shadow

Reputation: 34232

You could try to create updatable views that reference the permissions table with a join and use WITH CHECK OPTION to "to constrain inserts or updates to rows in tables referenced by the view." (source) The permissions will be granted to the views and not to the tables themselves.

You can check if a view is updatable:

MySQL sets a flag, called the view updatability flag, at CREATE VIEW time. The flag is set to YES (true) if UPDATE and DELETE (and similar operations) are legal for the view. Otherwise, the flag is set to NO (false). The IS_UPDATABLE column in the INFORMATION_SCHEMA.VIEWS table displays the status of this flag.

Upvotes: 1

Related Questions