0

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?

  • @MichaelKutz Thanks , I am going to look into RLS now. Thanks for guiding me in the right direction. – Sayed Muhammad Talha May 25 at 23:52
0

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.
  • Thanks for providing me more information, that makes things easier for me. I searched for row level security and got better understanding of what i am supposed to do. What you said is exactly right. MySQL don't have Row Level Security feature, so i will place an Additional Column "OwnerOfData" in each table. Then i will make VIEW for the table " CREATE VIEW Books_Restricted as (SELECT * from Books where ( Books.OwnerOfData = CURRENT_USER() )". After that i will GRANT the Users access to the Books_Restricted ONLY. The user won't be able to access data of others then. Thanks for your answer – Sayed Muhammad Talha May 26 at 3:53
  • @SayedMuhammadTalha - Ah, yes, a VIEW is also a good way to do it; maybe the 'best'. – Rick James May 26 at 4:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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