Managing Users

MySQL user accounts and information are stored in a MySQL database named mysql. You usually do not need to access the mysql database and tables directly (as you will soon see), but sometimes you might. One of those times is when you want to obtain a list of all user accounts. To do that, use the following code:

• Input

USE mysql; SELECT user FROM user;

• Output

+------+ | user | +------+ | root | +------+

• Analysis

The mysql database contains a table named user which contains all user accounts. user contains a column named user that contains the user login name. A newly installed server might have a single user listed (as seen here); established servers will likely have far more.

Tip

Test Using Multiple Clients The easiest way to test changes made to user accounts and rights is to open multiple database clients (multiple copies of the mysql command-line utility, for example), one logged in with the administrative login and the others logged in as the users being tested.

Creating User Accounts

To create a new user account, use the CREATE USER statement, as seen here:

• Input

CREATE USER ben IDENTIFIED BY 'p@$$w0rd';

• Analysis

CREATE USER creates a new user account. A password need not be specified at user account creation time, but this example does specify a password using IDENTIFIED BY 'p@$$w0rd'.

If you were to list the user accounts again, you'd see the new account listed in the output.

Tip

Specifying a Hashed Password The password specified by IDENTIFIED BY is plain text that MySQL will encrypt before saving it in the user table. To specify the password as a hashed value, use IDENTIFIED BY PASSWORD instead.

Note

Using GRANT or INSERT The GRANT statement (which we will get to shortly) can also create user accounts, but generally CREATE USER is the cleanest and simplest syntax. In addition, it is possible to add users by inserting rows into user directly, but to be safe this is generally not recommended. The tables used by MySQL to store user account information (as well as table schemas and more) are extremely important, and any damage to them could seriously harm the MySQL server. As such, it is always better to use tags and functions to manipulate these tables as opposed to manipulating them directly.

To rename a user account, use the RENAME USER statement like this:

• Input

RENAME USER ben TO bforta;

Note

Pre MySQL 5 RENAME USER is only supported in MySQL 5 or later. To rename a user in earlier versions of MySQL, use UPDATE to update the user table directly.

Deleting User Accounts

To delete a user account (along with any associated rights and privileges), use the DROP USER statement as seen here:

• Input

DROP USER bforta;

Note

Pre MySQL 5 As of MySQL 5, DROP USER deletes user accounts and all associated account rights and privileges. Prior to MySQL 5 DROP USER could only be used to drop user accounts with no associated account rights and privileges. As such, if you are using an older version of MySQL you will need to first remove associated account rights and privileges using REVOKE, and then use DROP USER to delete the actual account.

Setting Access Rights

With user accounts created, you must next assign access rights and privileges. Newly created user accounts have no access at all. They can log into MySQL but will see no data and will be unable to perform any database operations.

To see the rights granted to a user account, use SHOW GRANTS FOR as seen in this example:

• Input

SHOW GRANTS FOR bforta;

• Output

+-------------------------------------------------+ | Grants for bforta@%                             | +-------------------------------------------------+ | GRANT USAGE ON *.* TO 'bforta'@'%'              | +-------------------------------------------------+

• Analysis

The output shows that user bforta has a single right granted, USAGE ON *.*. USAGE means no rights at all (not overly intuitive, I know), so the results mean no rights to anything on any database and any table.

Note

Users Are Defined As user@host MySQL privileges are defined using a combination of user name and hostname. If no host name is specified, a default hostname of % is used (effectively granting access to the user regardless of the hostname).

To set rights the GRANT statement is used. At a minimum, GRANT requires that you specify

• The privilege being granted

• The database or table being granted access to

• The user name

The following example demonstrates the use of GRANT:

• Input

GRANT SELECT ON crashcourse.* TO beforta;

• Analysis

This GRANT allows the use of SELECT on crashcourse.* (crashcourse database, all tables). By granting SELECT access only, user bforta has read-only access to all data in the crashcourse database.

SHOW GRANTS reflects this change:

• Input

SHOW GRANTS FOR bforta;

• Output

+-------------------------------------------------+ | Grants for bforta@%                             | +-------------------------------------------------+ | GRANT USAGE ON *.* TO 'bforta'@'%'              | | GRANT SELECT ON 'crashcourse'.* TO 'bforta'@'%' | +-------------------------------------------------+

• Analysis

Each GRANT adds (or updates) a permission statement for the user. MySQL reads all of the grants and determines the rights and permissions based on them.

The opposite of GRANT is REVOKE, which is used to revoke specific rights and permissions. Here is an example:

• Input

REVOKE SELECT ON crashcourse.* FROM beforta;

• Analysis

This REVOKE statement takes away the SELECT access just granted to user bforta. The access being revoked must exist or an error will be thrown.

GRANT and REVOKE can be used to control access at several levels:

• Entire server, using GRANT ALL and REVOKE ALL

• Entire database, using ON database.*

• Specific tables, using ON database.table

• Specific columns

• Specific stored procedures

Table 28.1 lists each of the rights and privileges that may be granted or revoked.

Table 28.1. Rights and Privileges

Privilege	Description
ALL	All privileges except GRANT OPTION
ALTER	Use of ALTER TABLE
ALTER ROUTINE	Use of ALTER PROCEDURE and DROP PROCEDURE
CREATE	Use of CREATE TABLE
CREATE ROUTINE	Use of CREATE PROCEDURE
CREATE TEMPORARY TABLES	Use of CREATE TEMPORARY TABLE
CREATE USER	Use of CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES
CREATE VIEW	Use of CREATE VIEW
DELETE	Use of DELETE
DROP	Use of DROP TABLE
EXECUTE	Use of CALL and stored procedures
FILE	Use of SELECT INTO OUTFILE and LOAD DATA INFILE
GRANT OPTION	Use of GRANT and REVOKE
INDEX	Use of CREATE INDEX and DROP INDEX
INSERT	Use of INSERT
LOCK TABLES	Use of LOCK TABLES
PROCESS	Use of SHOW FULL PROCESSLIST
RELOAD	Use of FLUSH
REPLICATION CLIENT	Access to location of servers
REPLICATION SLAVE	Used by replication slaves
SELECT	Use of SELECT
SHOW DATABASES	Use of SHOW DATABASES
SHOW VIEW	Use of SHOW CREATE VIEW
SHUTDOWN	Use of mysqladmin shutdown (used to shut down MySQL)
SUPER	Use of CHANGE MASTER, KILL, LOGS, PURGE MASTER, and SET GLOBAL. Also allows mysqladmin debug login.
UPDATE	Use of UPDATE
USAGE	No access

Using GRANT and REVOKE in conjunction with the privileges listed in Table 28.1, you have complete control over what users can and cannot do with your precious data.

Note

Granting for the Future When using GRANT and REVOKE, the user account must exist, but the objects being referred to need not. This allows administrators to design and implement security before databases and tables are even created.

A side effect of this is that if a database or table is removed (with a DROP statement) any associated access rights will still exist. And if the database or table is re-created in the future, those rights will apply to them.

Tip

Simplifying Multiple Grants Multiple GRANT statements may be strung together by listing the privileges and comma delimiting them, as seen in this example:

GRANT SELECT, INSERT ON crashcourse.* TO beforta;

Changing Passwords

To change user passwords use the SET PASSWORD statement. New passwords must be encrypted as seen here:

• Input

SET PASSWORD FOR bforta = Password('n3w p@$$w0rd');

• Analysis

SET PASSWORD updates a user password. The new password must be encrypted by being passed to the Password() function.

SET PASSWORD can also be used to set your own password:

• Input

SET PASSWORD = Password('n3w p@$$w0rd');

• Analysis

When no user name is specified, SET PASSWORD updates the password for the currently logged in user.