Wednesday, November 27, 2013

MySQL privileges update for creating procedures, functions, etc.

After creating a new user you need to update his privileges to grant creation of procedures, functions and so on. Here is a SQL for this.

UPDATE user SET 
Select_priv="Y", Insert_priv="Y", Update_priv="Y", Delete_priv="Y", Create_priv="Y", Drop_priv="Y", Reload_priv="Y", Process_priv="Y", File_priv="Y", References_priv="Y", Index_priv="Y", Alter_priv="Y", Super_priv="Y", Create_tmp_table_priv="Y", Lock_tables_priv="Y", Execute_priv="Y", Create_view_priv="Y", Show_view_priv="Y", Create_routine_priv="Y", Alter_routine_priv="Y", Event_priv="Y", Trigger_priv="Y"
WHERE user = "xxxxx";
FLUSH PRIVILEGES;