In this section, you learn how to manage user accounts and grant and revoke privileges.
Creating a User
Use the CREATE USER command to create a new user. The username given should be a username and a host, in the format user@host. Include the keywords IDENTIFIED BY to set a password for the new user.
The following example creates a new user named chris that can connect only from localhost, with the password mypass:
mysql> CREATE USER chris@localhost IDENTIFIED BY 'mypass'; Query OK, 0 rows affected (0.02 sec)
You usually create new users when connected to MySQL as the root user, but if you want to allow other users to use the CREATE USER command, you should assign them the INSERT privilege on the mysql database. The underlying operation is an INSERT command that adds a record to mysql.user.
Setting or Changing a Password
The password values stored in the Password column of mysql.user are encrypted using a hashing algorithm that cannot be decoded back to the original password. You can perform the same hash using the PASSWORD() function.
You can use the SET PASSWORD command to reset a user's password, but you must use PASSWORD() to produce the encrypted password as part of this command. The following is an example:
mysql> SET PASSWORD FOR chris@localhost = PASSWORD('newpass'); Query OK, 0 rows affected (0.00 sec)
If you omit the PASSWORD() function, MySQL warns you that you did not supply a valid password.
mysql> SET PASSWORD FOR chris@localhost = 'newpass'; ERROR 1372 (HY000): Password hash should be a 41-digit hexadecimal number
Any user can use SET PASSWORD, although only a superuser can change other users' passwords. To change the password for the logged-in user, omit the FOR user part, as follows:
mysql> SET PASSWORD = PASSWORD('newpass'); Query OK, 0 rows affected (0.00 sec)
To assign a privilege to a user, use the GRANT command. This can be done through an individual privilege, a list, or the ALL PRIVILEGES keyword, which allocates every privilege shown in Table 18.1 to the user.
The following example grants the SELECT and INSERT privileges on the products table in the mysql10 database to the new user chris@localhost:
mysql> GRANT SELECT, INSERT -> ON mysql10.products -> TO chris@localhost; Query OK, 0 rows affected (0.00 sec)
If you connect as this user, the SHOW TABLES command reveals just one table; tables you do not have the SELECT privilege for are not displayed.
mysql> show tables; +-------------------+ | Tables_in_mysql10 | +-------------------+ | products | +-------------------+ 1 row in set (0.00 sec)
You can query the products table and insert new rows, but if you attempt to use the DELETE command, permission will be denied.
mysql> INSERT INTO products -> (product_code, name, weight, price) -> VALUES -> ('MYPROD', 'My new product', 1.00, 1.99); Query OK, 1 row affected (0.03 sec)
mysql> DELETE FROM products; ERROR 1142 (42000): DELETE command denied to user 'chris'@'localhost' for table 'products'
Passing on Privileges
When you grant a privilege to a user, that user will be able to take advantage of the new access rights but cannot pass that privilege on to another user unless you specifically grant permission to do so.
To do this, use the keywords WITH GRANT OPTION in a GRANT statement. The following statement allows chris@localhost not only to query the products table, but also to use the GRANT command himself to give the SELECT privilege to other users:
mysql> GRANT SELECT -> ON mysql10.products -> TO chris@localhost -> WITH GRANT OPTION; Query OK, 0 rows affected (0.00 sec)
Even though only the SELECT privilege is named in the previous statement, WITH GRANT OPTION allows this user to grant any privileges he possesses to another user.
The opposite of granting a privilege is revoking, performed using the REVOKE command. Its syntax is similar to GRANT, as shown in the following example, which removes the INSERT privilege that was granted in the previous section.
mysql> REVOKE INSERT -> ON mysql10.products -> FROM chris@localhost; Query OK, 0 rows affected (1.25 sec)
Only the INSERT privilege is revoked; the SELECT privilege that was assigned to this user at the same time as INSERT is unaffected.