Movatterモバイル変換

• GRANT General Overview

• Object Quoting Guidelines

• Account Names

• Privileges Supported by MySQL

• Global Privileges

• Database Privileges

• Table Privileges

• Column Privileges

• Stored Routine Privileges

• Proxy User Privileges

• Granting Roles

• TheAS Clause and Privilege Restrictions

• Other Account Characteristics

• MySQL and Standard SQL Versions of GRANT

TheGRANT statement enables system administrators to grant privileges and roles, which can be granted to user accounts and roles. These syntax restrictions apply:

For more information about roles, seeSection 8.2.10, “Using Roles”.

To grant a privilege withGRANT, you must have theGRANT OPTION privilege, and you must have the privileges that you are granting. (Alternatively, if you have theUPDATE privilege for the grant tables in themysql system schema, you can grant any account any privilege.) When theread_only system variable is enabled,GRANT additionally requires theCONNECTION_ADMIN privilege (or the deprecatedSUPER privilege).

GRANT either succeeds for all named users and roles or rolls back and has no effect if any error occurs. The statement is written to the binary log only if it succeeds for all named users and roles.

TheREVOKE statement is related toGRANT and enables administrators to remove account privileges. SeeSection 15.7.1.8, “REVOKE Statement”.

Each account name uses the format described inSection 8.2.4, “Specifying Account Names”. Each role name uses the format described inSection 8.2.5, “Specifying Role Names”. For example:

GRANT ALL ON db1.* TO 'jeffrey'@'localhost';GRANT 'role1', 'role2' TO 'user1'@'localhost', 'user2'@'localhost';GRANT SELECT ON world.* TO 'role3';

The host name part of the account or role name, if omitted, defaults to'%'.

Normally, a database administrator first usesCREATE USER to create an account and define its nonprivilege characteristics such as its password, whether it uses secure connections, and limits on access to server resources, then usesGRANT to define its privileges.ALTER USER may be used to change the nonprivilege characteristics of existing accounts. For example:

CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'password';GRANT ALL ON db1.* TO 'jeffrey'@'localhost';GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost';ALTER USER 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;

From themysql program,GRANT responds withQuery OK, 0 rows affected when executed successfully. To determine what privileges result from the operation, useSHOW GRANTS. SeeSection 15.7.7.21, “SHOW GRANTS Statement”.

GRANT supports host names up to 255 characters long (60 characters prior to MySQL 8.0.17). User names can be up to 32 characters. Database, table, column, and routine names can be up to 64 characters.

Several objects withinGRANT statements are subject to quoting, although quoting is optional in many cases: Account, role, database, table, column, and routine names. For example, if auser_name orhost_name value in an account name is legal as an unquoted identifier, you need not quote it. However, quotation marks are necessary to specify auser_name string containing special characters (such as-), or ahost_name string containing special characters or wildcard characters such as% (for example,'test-user'@'%.com'). Quote the user name and host name separately.

To specify quoted values:

For string-quoting and identifier-quoting guidelines, seeSection 11.1.1, “String Literals”, andSection 11.2, “Schema Object Names”.

The_ and% wildcards are permitted when specifying database names inGRANT statements that grant privileges at the database level (GRANT ... ONdb_name.*). This means, for example, that to use a_ character as part of a database name, specify it using the\ escape character as\_ in theGRANT statement, to prevent the user from being able to access additional databases matching the wildcard pattern (for example,GRANT ... ON `foo\_bar`.* TO ...).

Issuing multipleGRANT statements containing wildcards may not have the expected effect on DML statements; when resolving grants involving wildcards, MySQL takes only the first matching grant into consideration. In other words, if a user has two database-level grants using wildcards that match the same database, the grant which was created first is applied. Consider the databasedb and tablet created using the statements shown here:

mysql> CREATE DATABASE db;Query OK, 1 row affected (0.01 sec)mysql> CREATE TABLE db.t (c INT);Query OK, 0 rows affected (0.01 sec)mysql> INSERT INTO db.t VALUES ROW(1);Query OK, 1 row affected (0.00 sec)

Next (assuming that the current account is the MySQLroot account or another account having the necessary privileges), we create a useru then issue twoGRANT statements containing wildcards, like this:

mysql> CREATE USER u;Query OK, 0 rows affected (0.01 sec)mysql> GRANT SELECT ON `d_`.* TO u;Query OK, 0 rows affected (0.01 sec)mysql> GRANT INSERT ON `d%`.* TO u;Query OK, 0 rows affected (0.00 sec)mysql> EXIT

Bye

If we end the session and then log in again with themysql client, this time asu, we see that this account has only the privilege provided by the first matching grant, but not the second:

$> mysql -uu -hlocalhost

Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 10Server version: 8.0.46-tr Source distributionCopyright (c) 2000, 2023, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current inputstatement.mysql> TABLE db.t;+------+| c    |+------+|    1 |+------+1 row in set (0.00 sec)mysql> INSERT INTO db.t VALUES ROW(2);ERROR 1142 (42000): INSERT command denied to user 'u'@'localhost' for table 't'

In privilege assignments, MySQL interprets occurrences of unescaped_ and% SQL wildcard characters in database names as literal characters under these circumstances:

Auser value in aGRANT statement indicates a MySQL account to which the statement applies. To accommodate granting rights to users from arbitrary hosts, MySQL supports specifying theuser value in the form'user_name'@'host_name'.

You can specify wildcards in the host name. For example,'user_name'@'%.example.com' applies touser_name for any host in theexample.com domain, and'user_name'@'198.51.100.%' applies touser_name for any host in the198.51.100 class C subnet.

The simple form'user_name' is a synonym for'user_name'@'%'.

MySQL does not support wildcards in user names. To refer to an anonymous user, specify an account with an empty user name with theGRANT statement:

GRANT ALL ON test.* TO ''@'localhost' ...;

In this case, any user who connects from the local host with the correct password for the anonymous user is permitted access, with the privileges associated with the anonymous-user account.

For additional information about user name and host name values in account names, seeSection 8.2.4, “Specifying Account Names”.

SELECT Host, User FROM mysql.user WHERE User='';

DROP USER ''@'localhost';

The following tables summarize the permissible static and dynamicpriv_type privilege types that can be specified for theGRANT andREVOKE statements, and the levels at which each privilege can be granted. For additional information about each privilege, seeSection 8.2.2, “Privileges Provided by MySQL”. For information about the differences between static and dynamic privileges, seeStatic Versus Dynamic Privileges.

A trigger is associated with a table. To create or drop a trigger, you must have theTRIGGER privilege for the table, not the trigger.

InGRANT statements, theALL [PRIVILEGES] orPROXY privilege must be named by itself and cannot be specified along with other privileges.ALL [PRIVILEGES] stands for all privileges available for the level at which privileges are to be granted except for theGRANT OPTION andPROXY privileges.

MySQL account information is stored in the tables of themysql system schema. For additional details, consultSection 8.2, “Access Control and Account Management”, which discusses themysql system schema and the access control system extensively.

If the grant tables hold privilege rows that contain mixed-case database or table names and thelower_case_table_names system variable is set to a nonzero value,REVOKE cannot be used to revoke these privileges. It is necessary in such cases to manipulate the grant tables directly. (GRANT does not create such rows whenlower_case_table_names is set, but such rows might have been created prior to setting that variable. Thelower_case_table_names setting can only be configured at server startup.)

Privileges can be granted at several levels, depending on the syntax used for theON clause. ForREVOKE, the sameON syntax specifies which privileges to remove.

For the global, database, table, and routine levels,GRANT ALL assigns only the privileges that exist at the level you are granting. For example,GRANT ALL ONdb_name.* is a database-level statement, so it does not grant any global-only privileges such asFILE. GrantingALL does not assign theGRANT OPTION orPROXY privilege.

Theobject_type clause, if present, should be specified asTABLE,FUNCTION, orPROCEDURE when the following object is a table, a stored function, or a stored procedure.

The privileges that a user holds for a database, table, column, or routine are formed additively as the logicalOR of the account privileges at each of the privilege levels, including the global level. It is not possible to deny a privilege granted at a higher level by absence of that privilege at a lower level. For example, this statement grants theSELECT andINSERT privileges globally:

GRANT SELECT, INSERT ON *.* TO u1;

The globally granted privileges apply to all databases, tables, and columns, even though not granted at any of those lower levels.

As of MySQL 8.0.16, it is possible to explicitly deny a privilege granted at the global level by revoking it for particular databases, if thepartial_revokes system variable is enabled:

GRANT SELECT, INSERT, UPDATE ON *.* TO u1;REVOKE INSERT, UPDATE ON db1.* FROM u1;

The result of the preceding statements is thatSELECT applies globally to all tables, whereasINSERT andUPDATE apply globally except to tables indb1. Account access todb1 is read only.

Details of the privilege-checking procedure are presented inSection 8.2.7, “Access Control, Stage 2: Request Verification”.

If you are using table, column, or routine privileges for even one user, the server examines table, column, and routine privileges for all users and this slows down MySQL a bit. Similarly, if you limit the number of queries, updates, or connections for any users, the server must monitor these values.

MySQL enables you to grant privileges on databases or tables that do not exist. For tables, the privileges to be granted must include theCREATE privilege.This behavior is by design, and is intended to enable the database administrator to prepare user accounts and privileges for databases or tables that are to be created at a later time.

Global privileges are administrative or apply to all databases on a given server. To assign global privileges, useON *.* syntax:

GRANT ALL ON *.* TO 'someuser'@'somehost';GRANT SELECT, INSERT ON *.* TO 'someuser'@'somehost';

TheCREATE TABLESPACE,CREATE USER,FILE,PROCESS,RELOAD,REPLICATION CLIENT,REPLICATION SLAVE,SHOW DATABASES,SHUTDOWN, andSUPER,CREATE ROLE andDROP ROLE static privileges are administrative and can only be granted globally.

Dynamic privileges are all global and can only be granted globally.

Other privileges can be granted globally or at more specific levels.

The effect ofGRANT OPTION granted at the global level differs for static and dynamic privileges:

GRANT ALL at the global level grants all static global privileges and all currently registered dynamic privileges. A dynamic privilege registered subsequent to execution of theGRANT statement is not granted retroactively to any account.

MySQL stores global privileges in themysql.user system table.

Database privileges apply to all objects in a given database. To assign database-level privileges, useONdb_name.* syntax:

GRANT ALL ON mydb.* TO 'someuser'@'somehost';GRANT SELECT, INSERT ON mydb.* TO 'someuser'@'somehost';

If you useON * syntax (rather thanON *.*), privileges are assigned at the database level for the default database. An error occurs if there is no default database.

TheCREATE,DROP,EVENT,GRANT OPTION,LOCK TABLES, andREFERENCES privileges can be specified at the database level. Table or routine privileges also can be specified at the database level, in which case they apply to all tables or routines in the database.

MySQL stores database privileges in themysql.db system table.

Table privileges apply to all columns in a given table. To assign table-level privileges, useONdb_name.tbl_name syntax:

GRANT ALL ON mydb.mytbl TO 'someuser'@'somehost';GRANT SELECT, INSERT ON mydb.mytbl TO 'someuser'@'somehost';

If you specifytbl_name rather thandb_name.tbl_name, the statement applies totbl_name in the default database. An error occurs if there is no default database.

The permissiblepriv_type values at the table level areALTER,CREATE VIEW,CREATE,DELETE,DROP,GRANT OPTION,INDEX,INSERT,REFERENCES,SELECT,SHOW VIEW,TRIGGER, andUPDATE.

Table-level privileges apply to base tables and views. They do not apply to tables created withCREATE TEMPORARY TABLE, even if the table names match. For information aboutTEMPORARY table privileges, seeSection 15.1.20.2, “CREATE TEMPORARY TABLE Statement”.

MySQL stores table privileges in themysql.tables_priv system table.

Column privileges apply to single columns in a given table. Each privilege to be granted at the column level must be followed by the column or columns, enclosed within parentheses.

GRANT SELECT (col1), INSERT (col1, col2) ON mydb.mytbl TO 'someuser'@'somehost';

The permissiblepriv_type values for a column (that is, when you use acolumn_list clause) areINSERT,REFERENCES,SELECT, andUPDATE.

MySQL stores column privileges in themysql.columns_priv system table.

TheALTER ROUTINE,CREATE ROUTINE,EXECUTE, andGRANT OPTION privileges apply to stored routines (procedures and functions). They can be granted at the global and database levels. Except forCREATE ROUTINE, these privileges can be granted at the routine level for individual routines.

GRANT CREATE ROUTINE ON mydb.* TO 'someuser'@'somehost';GRANT EXECUTE ON PROCEDURE mydb.myproc TO 'someuser'@'somehost';

The permissiblepriv_type values at the routine level areALTER ROUTINE,EXECUTE, andGRANT OPTION.CREATE ROUTINE is not a routine-level privilege because you must have the privilege at the global or database level to create a routine in the first place.

MySQL stores routine-level privileges in themysql.procs_priv system table.

ThePROXY privilege enables one user to be a proxy for another. The proxy user impersonates or takes the identity of the proxied user; that is, it assumes the privileges of the proxied user.

GRANT PROXY ON 'localuser'@'localhost' TO 'externaluser'@'somehost';

WhenPROXY is granted, it must be the only privilege named in theGRANT statement, and the only permittedWITH option isWITH GRANT OPTION.

Proxying requires that the proxy user authenticate through a plugin that returns the name of the proxied user to the server when the proxy user connects, and that the proxy user have thePROXY privilege for the proxied user. For details and examples, seeSection 8.2.19, “Proxy Users”.

MySQL stores proxy privileges in themysql.proxies_priv system table.

GRANT syntax without anON clause grants roles rather than individual privileges. A role is a named collection of privileges; seeSection 8.2.10, “Using Roles”. For example:

GRANT 'role1', 'role2' TO 'user1'@'localhost', 'user2'@'localhost';

Each role to be granted must exist, as well as each user account or role to which it is to be granted. As of MySQL 8.0.16, roles cannot be granted to anonymous users.

Granting a role does not automatically cause the role to be active. For information about role activation and inactivation, seeActivating Roles.

These privileges are required to grant roles:

It is possible to create circular references withGRANT. For example:

CREATE USER 'u1', 'u2';CREATE ROLE 'r1', 'r2';GRANT 'u1' TO 'u1';   -- simple loop: u1 => u1GRANT 'r1' TO 'r1';   -- simple loop: r1 => r1GRANT 'r2' TO 'u2';GRANT 'u2' TO 'r2';   -- mixed user/role loop: u2 => r2 => u2

Circular grant references are permitted but add no new privileges or roles to the grantee because a user or role already has its privileges and roles.

As of MySQL 8.0.16,GRANT has anASuser [WITH ROLE] clause that specifies additional information about the privilege context to use for statement execution. This syntax is visible at the SQL level, although its primary purpose is to enable uniform replication across all nodes of grantor privilege restrictions imposed by partial revokes, by causing those restrictions to appear in the binary log. For information about partial revokes, seeSection 8.2.12, “Privilege Restriction Using Partial Revokes”.

When theASuser clause is specified, statement execution takes into account any privilege restrictions associated with the named user, including all roles specified byWITH ROLE, if present. The result is that the privileges actually granted by the statement may be reduced relative to those specified.

These conditions apply to theASuser clause:

The following example illustrates the effect of theAS clause. Create a useru1 that has some global privileges, as well as restrictions on those privileges:

CREATE USER u1;GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO u1;REVOKE INSERT, UPDATE ON schema1.* FROM u1;REVOKE SELECT ON schema2.* FROM u1;

Also create a roler1 that lifts some of the privilege restrictions and grant the role tou1:

CREATE ROLE r1;GRANT INSERT ON schema1.* TO r1;GRANT SELECT ON schema2.* TO r1;GRANT r1 TO u1;

Now, using an account that has no privilege restrictions of its own, grant to multiple users the same set of global privileges, but each with different restrictions imposed by theAS clause, and check which privileges are actually granted.

If aGRANT statement includes anASuser clause, privilege restrictions on the user who executes the statement are ignored (rather than applied as they would be in the absence of anAS clause).

The optionalWITH clause is used to enable a user to grant privileges to other users. TheWITH GRANT OPTION clause gives the user the ability to give to other users any privileges the user has at the specified privilege level.

To grant theGRANT OPTION privilege to an account without otherwise changing its privileges, do this:

GRANT USAGE ON *.* TO 'someuser'@'somehost' WITH GRANT OPTION;

Be careful to whom you give theGRANT OPTION privilege because two users with different privileges may be able to combine privileges!

You cannot grant another user a privilege which you yourself do not have; theGRANT OPTION privilege enables you to assign only those privileges which you yourself possess.

Be aware that when you grant a user theGRANT OPTION privilege at a particular privilege level, any privileges the user possesses (or may be given in the future) at that level can also be granted by that user to other users. Suppose that you grant a user theINSERT privilege on a database. If you then grant theSELECT privilege on the database and specifyWITH GRANT OPTION, that user can give to other users not only theSELECT privilege, but alsoINSERT. If you then grant theUPDATE privilege to the user on the database, the user can grantINSERT,SELECT, andUPDATE.

For a nonadministrative user, you should not grant theALTER privilege globally or for themysql system schema. If you do that, the user can try to subvert the privilege system by renaming tables!

For additional information about security risks associated with particular privileges, seeSection 8.2.2, “Privileges Provided by MySQL”.

The biggest differences between the MySQL and standard SQL versions ofGRANT are: