GRANT <privileges>
This statement allocates privileges to a pre-existing user in TiDB. The privilege system in TiDB follows MySQL, where credentials are assigned based on a database/table pattern. Executing this statement requires the GRANT OPTION privilege and all privileges you allocate.
Synopsis
- GrantStmt
- PrivElemList
- PrivElem
- PrivType
- ObjectType
- PrivLevel
- UserSpecList
- RequireClauseOpt
- RequireListElement
GrantStmt ::=
'GRANT' PrivElemList 'ON' ObjectType PrivLevel 'TO' UserSpecList RequireClauseOpt WithGrantOptionOpt
PrivElemList ::=
PrivElem ( ',' PrivElem )*
PrivElem ::=
PrivType ( '(' ColumnNameList ')' )?
PrivType ::=
'ALL' 'PRIVILEGES'?
| 'ALTER' 'ROUTINE'?
| 'CREATE' ( 'USER' | 'TEMPORARY' 'TABLES' | 'VIEW' | 'ROLE' | 'ROUTINE' )?
| 'TRIGGER'
| 'DELETE'
| 'DROP' 'ROLE'?
| 'PROCESS'
| 'EXECUTE'
| 'INDEX'
| 'INSERT'
| 'SELECT'
| 'SUPER'
| 'SHOW' ( 'DATABASES' | 'VIEW' )
| 'UPDATE'
| 'GRANT' 'OPTION'
| 'REFERENCES'
| 'REPLICATION' ( 'SLAVE' | 'CLIENT' )
| 'USAGE'
| 'RELOAD'
| 'FILE'
| 'CONFIG'
| 'LOCK' 'TABLES'
| 'EVENT'
| 'SHUTDOWN'
ObjectType ::=
'TABLE'?
PrivLevel ::=
'*' ( '.' '*' )?
| Identifier ( '.' ( '*' | Identifier ) )?
UserSpecList ::=
UserSpec ( ',' UserSpec )*
RequireClauseOpt ::= ('REQUIRE' ('NONE' | 'SSL' | 'X509' | RequireListElement ('AND'? RequireListElement)*))?
RequireListElement ::= 'ISSUER' Issuer | 'SUBJECT' Subject | 'CIPHER' Cipher | 'SAN' SAN | 'TOKEN_ISSUER' TokenIssuer
Examples
mysql> CREATE USER 'newuser' IDENTIFIED BY 'mypassword';
Query OK, 1 row affected (0.02 sec)
mysql> GRANT ALL ON test.* TO 'newuser';
Query OK, 0 rows affected (0.03 sec)
mysql> SHOW GRANTS FOR 'newuser';
+-------------------------------------------------+
| Grants for newuser@% |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO 'newuser'@'%' |
| GRANT ALL PRIVILEGES ON test.* TO 'newuser'@'%' |
+-------------------------------------------------+
2 rows in set (0.00 sec)
MySQL compatibility
- Similar to MySQL, the
USAGEprivilege denotes the ability to log into a TiDB server. - Column level privileges are not currently supported.
- Similar to MySQL, when the
NO_AUTO_CREATE_USERsql mode is not present, theGRANTstatement will automatically create a new user with an empty password when a user does not exist. Removing this sql-mode (it is enabled by default) presents a security risk. - In TiDB, after the
GRANT <privileges>statement is executed successfully, the execution result takes effect immediately on the current connection. Whereas in MySQL, for some privileges, the execution results take effect only on subsequent connections. See TiDB #39356 for details.