Mysql – Should i place Registered-Users Info in every table? to Restrict user access to his Own Data only

authenticationMySQL

Database: Relational Database – MySQL

I am making a database with multiple tables. In order to see his data from tables, User will need to log in. I only need to display those information(through web-pages) from the Table which only belongs to him, so he wont get to access data of other users.

So, should i place Registered-Users Information in every table? just so when he wants to see data, i will "SELECT data from TABLE_ABC where (login_id = Id_of_current_user_log_ined )". I will put this condition in every query so he will only be able to access his/her own data and not someone else.

I made a Relational Schema but only 1 table inherits Login-Information from Login-Table while 3 of the tables don't have information on WHO does the data belong to?

Best Answer

For table-level access restrictions:

GRANT SELECT ON tbl1 TO 'user'@'...';
GRANT SELECT ON tbl3 TO 'user'@'...';

Repeat as needed for each combination of user and table.

If you have a newer version of MySQL, see if "Roles" provides a simplification.

For row-level access restrictions, do not let the users query the tables. Instead:

  • Provide an app that they contact; it checks permissions before preforming (or not) the desired query.
  • Use Stored Procedures. See also the function CURRENT_USER()
  • Use a VIEW -- then grant access to the view but not the table; have the view check whether the user is allowed to see the row. See OP's comment.
Related Question