mysql mixed privileges

Question

Hello,

I am trying to create a separate set of privileges for a database that contains both "read only" tables and "read write" tables. To be more specific, the database contains 10 tables that I want the user to only read from (i.e. select only). It also contains around 30 tables which the user is free to read and write to and from. Lastly, I would also like the user to be able to create/drop/alter tables created by him thus granting him full access to his own tables under the same database.

Is something like this possible? I have tried to think of the ways I can use GRANT but my head is getting stuck.

Thanks

Answer 1

What you want is possible except I'm not sure about the last sentence. I don't think mySQL tables have "owners" as such, but you can limit access to tables with certain prefixes. Maybe that is a way to go.

• Obligatory manual reference: 12.4.1.3. GRANT Syntax

• I recommend using a GUI like HeidiSQL (for Windows) to point-and-click the required privilege rules. It produces proper SQL to look at and learn from as well. Here is a screen shot of its excellent user manager.