Menu
How can I make a single table in mysql read only for a user while he still has write access to other tables in the same db?
Additional info
thanks!
868
MySQL doesn't support WITH READ ONLY for CREATE VIEW . The line DEFINER = CURRENT_USER is not needed if you use the limited user to create the view. Or you can use an admin user to create the view and in this case the line DEFINER = must contain the name of the user that will own the view.
Select the statements for only the tables you would like to grant access to and run those queries. For example, if we only wanted to grant access to the Users and Visitors table we would run: GRANT SELECT, SHOW VIEW ON mydatabase. `Users` to 'myuser'@`myhost`; GRANT SELECT, SHOW VIEW ON mydatabase.
You should at least have the necessary privileges on your specific table. If that's the case, try to repair the table (it may have crashed). If you're still getting “read only” messages, check the file permissions in /var/lib/mysql/dbname/tbl_name (assuming your database is in /var/lib/mysql).
To turn it off, you can use the SET GLOBAL statement as shown below: SET GLOBAL read_only=0; The option should now be turned off.
Revoke all previous privileges and then grant the specific new privileges:
REVOKE ALL ON db.table FROM user;
REVOKE ALL ON db.othertable FROM user;
GRANT SELECT ON db.table TO user;
GRANT SELECT, INSERT, UPDATE, DELETE ON db.othertable TO user;
You can make a single MyISAM table read only by compressing the table. Use myisampack on the command line to pack the table.
More info can be found in the MySQL Manual: http://dev.mysql.com/doc/refman/5.0/en/myisampack.html
36
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
