What privileges should I give my InnoDB MySQL user?

Question

I have a MySQL user (InnoDB engine) and I was wondering what would be the safe privileges to provide it? Currently, I have granted him permission to access a specific database and these privileges:

SELECT, INSERT, UPDATE, DELETE, CREATE, INDEX, ALTER, CREATE TEMPORARY TABLES 

But since this is InnoDB, I was wondering if I need to explicitly grant LOCK TABLES privileges to this user? Also I'm not sure that I even need to provide CREATE TEMPORARY TABLES for it. Could you help me to find out what privileges I should provide? Hibernate will have access to this user, so I also wondered what would be enough to work properly?

Answer 1

You should provide the minimum privileges necessary for users to use the system.

For testing in all ways, use grant all on the database .

Block everything comes production time
But in production, you should only provide select on the tables.
Provide insert and update only in the tables that the user really needs to add or modify data.
Create is a privilege that users almost never need. (if you do not want them to create a database for you :-).
Also for index , alter , you do not want your users to own this power.
create temp tables might be OK IF your application creates temporary tables.