I've a MySQL user (InnoDB engine) and I was wondering what would be the safe privileges to grant for it? Currently I've granted it permission to access the specific database and these privileges:
SELECT, INSERT, UPDATE, DELETE, CREATE, INDEX, ALTER, CREATE TEMPORARY TABLES
But since it's InnoDB I was wondering if I need to explicitly grant LOCK TABLES privilege to this user? Also I'm not sure whether I even need to grant CREATE TEMPORARY TABLES for it. Could you help me out here to figure out which privileges should I grant? Hibernate will have access to this user so I was also wondering what would be enough for it to work properly?
You should grant the minimum privileges the users needs in order to use the system.
For testing by all means use a grant all on the database.
Lock things down come production time
But in production you should only grant select on the tables.
Grant insert and update only on tables the user actually needs to add or alter data in.
Create is a privilege that users almost never need. (unless you want them to design the database for you :-).
Ditto for index, alter, you don't want your users to wield that power.
create temp tables may be OK, IF your app creates temp tables.