GRANT SELECT,INSERT,UPDATE,DELETE,DROP,INDEX
ON databasename.*
TO 'username'@'hostname'
IDENTIFIED BY 'password';
Explanation:
-- You may consider FILE privileges also, but that gives at least read access to any file, on mysql can look at and thus compromises security big-time.
-- Obviously you don't want to grant RELOAD and SHUTDOWN privileges, since they are used for server-ops, which should only be the root and other administrators.
-- PROCESS you might grant, but it shows what is running and can kill processes.
-- REFERENCES is a bogus privilege, used for SQL-92 compatibility.
-- ON databasename.* defines, that the user only has those privileges, WITHIN that database's context. So he can't create a database and can't alter mysql grant tables.
-- TO is best used in the syntax described above (with the quotes) since that allows for alternate syntaxes, like:
'user'@'%' and 'user'@'192.168.0.1/255.255.255.0'
There's no need to do a FLUSH PRIVILEGES command, since GRANT updates user permissions dynamically, in contrary to alterations of the mysql grant tables, via INSERT/ALTER/DELETE SQL commands.
Hope this helps.
