Adding user to Mysql

mysql> GRANT ALL ON cacti.* TO cactiuser@localhost IDENTIFIED BY 'somepassword';
mysql> flush privileges;

or adding a new user to MySQL you just need to add a new entry to user table in database mysql. Below is an example of adding new user phpcake with SELECT, INSERT and UPDATE privileges with the password  mypass  the SQL query is :
mysql> use mysql;
Database changed

mysql> INSERT INTO user (host, user, password, select_priv, insert_priv, update_priv) VALUES ('localhost', 'phpcake', PASSWORD('mypass'), 'Y', 'Y', 'Y');
Query OK, 1 row affected (0.20 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT host, user, password FROM user WHERE user = 'phpcake';
+-----------+---------+------------------+
| host      | user    | password         |
+-----------+---------+------------------+
| localhost | phpcake | 6f8c114b58f2ce9e |
+-----------+---------+------------------+
1 row in set (0.00 sec)



When adding a new user remember to encrypt the new password using PASSWORD() function provided by MySQL. As you can see in the above example the password mypass is encrypted to 6f8c114b58f2ce9e.

Notice the the FLUSH PRIVILEGES statement. This tells the server to reload the grant tables. If you don't use it then you won't be able to connect to mysql using the new user account (at least until the server is reloaded).

You can also specify other privileges to a new user by setting the values of these columns in user table to 'Y' when executing the INSERT query :

    * Select_priv
    * Insert_priv
    * Update_priv
    * Delete_priv
    * Create_priv
    * Drop_priv
    * Reload_priv
    * Shutdown_priv
    * Process_priv
    * File_priv
    * Grant_priv
    * References_priv
    * Index_priv
    * Alter_priv

I think you can guess what those privileges serve by reading it's name


I was just working on a new Bugzilla installation, and noticed their grant command example, and thought I'd share it here as another example:

mysql> GRANT SELECT, INSERT,
       UPDATE, DELETE, INDEX, ALTER, CREATE, LOCK TABLES,
       CREATE TEMPORARY TABLES, DROP, REFERENCES ON bugs.*
       TO bugs@localhost IDENTIFIED BY '$db_pass';

mysql> FLUSH PRIVILEGES;

They also show this syntax for changing the MySQL root user password, which is a little different than the syntax I normally use, so I thought I'd share that here as well:

mysql> UPDATE user
       SET password = password('new_password')
       WHERE user = 'root';

mysql> FLUSH PRIVILEGES;