Restricting users to proper read/write/update permission in MySQL

Question

Let's assume there is a website called as mysite.com -

• Now, mysite has users who have registered for the website.
• Say Jane and John are two registered users on the website.
• mysite.com uses MySQL to store its data.
• Say there are tables such as Users which stores username, address, contact, etc,.

My question is how do i make it possible such that Jane -

• can only read her rows from the Users table.
• can only modify her rows from the Users table.
• Also, while modification she cannot update the value in for username cell.

Also, what general roles are given to Jane and John with respect to the database? They cannot obviously cannot connect to MySQL as root/admin users. How to handle this situation?

Answer 1

I've done this in my mini project once, my project was based on HTML, CSS, JS, PHP, and MySQL where I had created one website to store employee database, and employee can log in to there account and make some changes like to address or phone number, but I didn't give permission to changes Name, Government ID, or something which is permanent. All this I did in front-end and not in back-end.

You can do this to, say Jane and John account where they can change there own information.

There is disabled attribute that is used in HTML where you can restrict the user from inputting any value

This is what I believe your answer would be based on how you explained your problem. If it worked, your welcome!

Answer 2

You would not try to control this at the level of database users. Really, your database users are strictly for different pieces of code that interact directly with your database. Within your back-end code you would want to check which user of your software is logged in, and then restrict them to only reading or writing certain rows or data.

There is no way to set a MySQL user to be able to read/write some rows of a table and not others.

To expand on this... You may have a piece of software that lets Jane read some information but not write anything. That piece of software should connect to your database as a MySQL user with the minimum permission necessary to accomplish it's task. E.g. it can select but not update or insert. Another piece of software may allow users to edit rows, so you set up a MySQL user for that software which does have the update permission.