Movatterモバイル変換

• CREATE USER andDROP USER create and remove accounts.

• GRANT andREVOKE assign privileges to and revoke privileges from accounts.

• SHOW GRANTS displays account privilege assignments.

Direct modification of grant tables using statements such asINSERT,UPDATE, orDELETE 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. SeeChapter 3,Upgrading MySQL.

• Creating Accounts and Granting Privileges

• Checking Account Privileges and Properties

• Revoking Account Privileges

• Dropping Accounts

The following examples show how to use themysql client program to set up new accounts. These examples assume that the MySQLroot account has theCREATE USER privilege and all privileges that it grants to other accounts.

At the command line, connect to the server as the MySQLroot user, supplying the appropriate password at the password prompt:

$> mysql -u root -pEnter password:(enter root password here)

After connecting to the server, you can add new accounts. The following example usesCREATE USER andGRANT statements to set up four accounts (where you see'password', substitute an appropriate 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:

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 ofcustom, 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:

To see the privileges for an account, useSHOW 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, useSHOW 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 0x24412430303524301D0E17054E2241362B1419313C3E44326F294133734B30792F436E77764270373039612E32445250786D43594F45354532324B6169794F47457852796E32REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCKPASSWORD HISTORY DEFAULTPASSWORD REUSE INTERVAL DEFAULTPASSWORD REQUIRE CURRENT DEFAULT

Enabling theprint_identified_with_as_hex system variable causesSHOW CREATE USER to display hash values that contain unprintable characters as hexadecimal strings rather than as regular string literals.

To revoke account privileges, use theREVOKE 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, useSHOW GRANTS:

mysql> SHOW GRANTS FOR 'admin'@'localhost';+---------------------------------------------+| Grants for admin@localhost                  |+---------------------------------------------+| GRANT PROCESS ON *.* TO `admin`@`localhost` |+---------------------------------------------+

To remove an account, use theDROP 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';