Menu
This question was originally using MySQL 5.1.44, but is applicable to MySQL 8.0+ too.
Let's say I have a table with records inserted by different users of my application. How can I give a specific user access to only see his/her records in that table? I've thought about creating a VIEW with his/her records, but I don't know how to create a MySQL user that can only see that VIEW.
So, is it possible to create a MySQL user that only has access to a single VIEW? Can this user also be made so they read-only access to that VIEW?
Thanks!
PS: What I call users in my example are really subsidiary offices that want to access their records with their own applications.
792
You can create a user with table level permissions in MySQL by performing the following: Connect to MySQL as a user with the Create_user_priv and Grant_priv. Determine which users have these privileges by running the following query. Your user will already need the SELECT privilege on MySQL.
To grant a privilege with GRANT , you must have the GRANT OPTION privilege, and you must have the privileges that you are granting. (Alternatively, if you have the UPDATE privilege for the grant tables in the mysql system schema, you can grant any account any privilege.)
At the mysql prompt, do one of the following steps: To give the user access to the database from any host, type the following command: grant select on database_name. * to 'read-only_user_name'@'%' identified by 'password';
GRANT SELECT ON database1.view1 TO 'someuser'@'somehost';
177
Besides
GRANT SELECT ON <database_name>.<view_name> TO <user>@<host> it's better to also do
GRANT SHOW VIEW ON <database_name>.<view_name> TO <user>@<host> so that a lot of SQL UI tool can get the view definition and work appropriately for the view.
24
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
