Question: How to set a user, read-only permission for few tables, and alter/insert/delete permission for the rest of the tables?
This can be achieved using MySQL grant’s, as MySQL do allow to allow/revoke privileges at a table level. Normally, a database administrator first uses CREATE USER to create an account, then GRANT to define its privileges and characteristics. For example:
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'password'; GRANT ALL ON db1.* TO 'jeffrey'@'localhost'; GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost'; GRANT USAGE ON *.* TO 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;
All the possible privileges can be found in the Grant Privileges section on the online manual. The same applies to stored procedures privileges as mentioned here.
Note: The below syntax is not supported:
GRANT INSERT, DELETE, ALTER, UPDATE ON *.myTableName TO 'myUser'@'%';
Meaning it’s not possible to specify the same table name on all existing databases.