To manage MySQL accounts, use the SQL statements intended for that purpose:
CREATE USER
andDROP USER
create and remove accounts.GRANT
andREVOKE
assign privileges to and revoke privileges from accounts.SHOW GRANTS
displays account privilege assignments.
Account-management statements cause the server to make appropriate modifications to the underlying grant tables, which are discussed in Section 8.2.3, “Grant Tables”.
Direct modification of grant tables using statements such as
INSERT
,
UPDATE
, or
DELETE
is discouraged and done at
your own risk. The server is free to ignore rows that become
malformed as a result of such modifications.
For any operation that modifies a grant table, the server checks whether the table has the expected structure and produces an error if not. To update the tables to the expected structure, perform the MySQL upgrade procedure. See Chapter 3, Upgrading MySQL.
Another option for creating accounts is to use the GUI tool
MySQL Workbench. Also, several third-party programs offer capabilities
for MySQL account administration. phpMyAdmin
is
one such program.
This section discusses the following topics:
For additional information about the statements discussed here, see Section 15.7.1, “Account Management Statements”.
The following examples show how to use the
mysql client program to set up new accounts.
These examples assume that the MySQL root
account has the CREATE USER
privilege and all privileges that it grants to other accounts.
At the command line, connect to the server as the MySQL
root
user, supplying the appropriate password
at the password prompt:
$> mysql -u root -p
Enter password: (enter root password here)
After connecting to the server, you can add new accounts. The
following example uses CREATE USER
and GRANT
statements
to set up four accounts (where you see
'
,
substitute an appropriate password):
password
'
CREATE USER 'finley'@'localhost'
IDENTIFIED BY 'password';
GRANT ALL
ON *.*
TO 'finley'@'localhost'
WITH GRANT OPTION;
CREATE USER 'finley'@'%.example.com'
IDENTIFIED BY 'password';
GRANT ALL
ON *.*
TO 'finley'@'%.example.com'
WITH GRANT OPTION;
CREATE USER 'admin'@'localhost'
IDENTIFIED BY 'password';
GRANT RELOAD,PROCESS
ON *.*
TO 'admin'@'localhost';
CREATE USER 'dummy'@'localhost';
The accounts created by those statements have the following properties:
Two accounts have a user name of
finley
. Both are superuser accounts with full global privileges to do anything. The'finley'@'localhost'
account can be used only when connecting from the local host. The'finley'@'%.example.com'
account uses the'%'
wildcard in the host part, so it can be used to connect from any host in theexample.com
domain.The
'finley'@'localhost'
account is necessary if there is an anonymous-user account forlocalhost
. Without the'finley'@'localhost'
account, that anonymous-user account takes precedence whenfinley
connects from the local host andfinley
is treated as an anonymous user. The reason for this is that the anonymous-user account has a more specificHost
column value than the'finley'@'%'
account and thus comes earlier in theuser
table sort order. (For information aboutuser
table sorting, see Section 8.2.6, “Access Control, Stage 1: Connection Verification”.)The
'admin'@'localhost'
account can be used only byadmin
to connect from the local host. It is granted the globalRELOAD
andPROCESS
administrative privileges. These privileges enable theadmin
user to execute the mysqladmin reload, mysqladmin refresh, and mysqladmin flush-xxx
commands, as well as mysqladmin processlist . No privileges are granted for accessing any databases. You could add such privileges usingGRANT
statements.The
'dummy'@'localhost'
account has no password (which is insecure and not recommended). This account can be used only to connect from the local host. No privileges are granted. It is assumed that you grant specific privileges to the account usingGRANT
statements.
The previous example grants privileges at the global level. The
next example creates three accounts and grants them access at
lower levels; that is, to specific databases or objects within
databases. Each account has a user name of
custom
, but the host name parts differ:
CREATE USER 'custom'@'localhost'
IDENTIFIED BY 'password';
GRANT ALL
ON bankaccount.*
TO 'custom'@'localhost';
CREATE USER 'custom'@'host47.example.com'
IDENTIFIED BY 'password';
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
ON expenses.*
TO 'custom'@'host47.example.com';
CREATE USER 'custom'@'%.example.com'
IDENTIFIED BY 'password';
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
ON customer.addresses
TO 'custom'@'%.example.com';
The three accounts can be used as follows:
The
'custom'@'localhost'
account has all database-level privileges to access thebankaccount
database. The account can be used to connect to the server only from the local host.The
'custom'@'host47.example.com'
account has specific database-level privileges to access theexpenses
database. The account can be used to connect to the server only from the hosthost47.example.com
.The
'custom'@'%.example.com'
account has specific table-level privileges to access theaddresses
table in thecustomer
database, from any host in theexample.com
domain. The account can be used to connect to the server from all machines in the domain due to use of the%
wildcard character in the host part of the account name.
To see the privileges for an account, use
SHOW GRANTS
:
mysql> SHOW GRANTS FOR 'admin'@'localhost';
+-----------------------------------------------------+
| Grants for admin@localhost |
+-----------------------------------------------------+
| GRANT RELOAD, PROCESS ON *.* TO `admin`@`localhost` |
+-----------------------------------------------------+
To see nonprivilege properties for an account, use
SHOW CREATE USER
:
mysql> SET print_identified_with_as_hex = ON;
mysql> SHOW CREATE USER 'admin'@'localhost'\G
*************************** 1. row ***************************
CREATE USER for admin@localhost: CREATE USER `admin`@`localhost`
IDENTIFIED WITH 'caching_sha2_password'
AS 0x24412430303524301D0E17054E2241362B1419313C3E44326F294133734B30792F436E77764270373039612E32445250786D43594F45354532324B6169794F47457852796E32
REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK
PASSWORD HISTORY DEFAULT
PASSWORD REUSE INTERVAL DEFAULT
PASSWORD REQUIRE CURRENT DEFAULT
Enabling the
print_identified_with_as_hex
system variable causes SHOW CREATE
USER
to display hash values that contain unprintable
characters as hexadecimal strings rather than as regular string
literals.
To revoke account privileges, use the
REVOKE
statement. Privileges can
be revoked at different levels, just as they can be granted at
different levels.
Revoke global privileges:
REVOKE ALL
ON *.*
FROM 'finley'@'%.example.com';
REVOKE RELOAD
ON *.*
FROM 'admin'@'localhost';
Revoke database-level privileges:
REVOKE CREATE,DROP
ON expenses.*
FROM 'custom'@'host47.example.com';
Revoke table-level privileges:
REVOKE INSERT,UPDATE,DELETE
ON customer.addresses
FROM 'custom'@'%.example.com';
To check the effect of privilege revocation, use
SHOW GRANTS
:
mysql> SHOW GRANTS FOR 'admin'@'localhost';
+---------------------------------------------+
| Grants for admin@localhost |
+---------------------------------------------+
| GRANT PROCESS ON *.* TO `admin`@`localhost` |
+---------------------------------------------+
To remove an account, use the DROP
USER
statement. For example, to drop some of the
accounts created previously:
DROP USER 'finley'@'localhost';
DROP USER 'finley'@'%.example.com';
DROP USER 'admin'@'localhost';
DROP USER 'dummy'@'localhost';