![]() ![]() For statements except ALTER.DROP and ALTER.RENAME TO, users must have the INSERT and CREATE privileges for the corresponding table.For all ALTER statements, users must have the ALTER privilege for the corresponding table.You can check privileges of TiDB users in the INFORMATION_SCHEMA.USER_PRIVILEGES table. | GRANT Insert,Update ON test.write_table TO | CREATE USER SELECT ON *.* TO INSERT, UPDATE ON `test`.`write_table` TO granted privileges of the user: SHOW GRANTS FOR Grants for | SHOW GRANTS FOR - show grants for a specific userįor example, create a user and grant the user with write privilege on the test.write_table table and global read privilege. | GRANT ALL PRIVILEGES ON *.* TO WITH GRANT OPTION | For example: SHOW GRANTS - show grants for the current user You can use the SHOW GRANTS statement to see what privileges are granted to a user. For example: mysql> CREATE TABLE `select` (id int) If you want to use special keywords as table names, enclose them in backticks (``). Mysql> GRANT ALL PRIVILEGES ON `test`.* TO OK, 0 rows affected (0.00 sec) Syntax to use near ''test'.* to at line 1 Manual that corresponds to your MySQL server version for the right See the differences below: mysql> GRANT ALL PRIVILEGES ON 'test'.* TO 1064 (42000): You have an error in your SQL syntax check the Note that the % uses the \ escape character so that % is not considered as a wildcard.Ī string is enclosed in single quotation marks(''), while an identifier is enclosed in backticks (``). This example uses exact match to find the database named te%. If the matching result cannot be found, an error will be displayed: mysql> REVOKE ALL PRIVILEGES ON `te%`.* FROM 1141 (42000): There is no such grant defined for user 'genius' on host '%'Ībout fuzzy matching, escape, string and identifier: mysql> GRANT ALL PRIVILEGES ON `te\%`.* TO OK, 0 rows affected (0.00 sec) The REVOKE statement corresponds with the REVOKE statement: REVOKE ALL PRIVILEGES ON `test`.* FROM revoke privileges, you need the exact match. The REVOKE statement enables system administrators to revoke privileges from the user accounts. In this example, because of the % in te%, all the databases starting with te are granted the privilege. Mysql> SELECT user,host,db FROM mysql.db WHERE user='genius' mysql> GRANT ALL PRIVILEGES ON `te%`.* TO genius You can use fuzzy matching in GRANT to grant privileges to databases. | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | This is not recommended since it presents a security risk: miss-spelling a username will result in a new user created with an empty password: mysql> SET OK, 0 rows affected (0.00 sec) In the following example, the user idontexist is automatically created with an empty password because the SQL Mode NO_AUTO_CREATE_USER was not set. Mysql> SELECT user,host,password FROM er WHERE user='idontexist' Mysql> GRANT ALL PRIVILEGES ON test.* TO 'idontexist' ĮRROR 1105 (HY000): You are not allowed to create a user with GRANT Mysql> SELECT * FROM er WHERE user='idontexist' | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | This behavior depends on if the SQL Mode NO_AUTO_CREATE_USER is specified: mysql> SET sql_mode=DEFAULT GRANT SELECT ON test.* TO the following statement to grant the xxx user all privileges on all databases: GRANT ALL PRIVILEGES ON *.* TO default, GRANT statements will return an error if the user specified does not exist. The GRANT statement grants privileges to the user accounts.įor example, use the following statement to grant the xxx user the privilege to read the test database. Privilege-related operations Grant privileges This document introduces privilege-related TiDB operations, privileges required for TiDB operations and implementation of the privilege system. Starting with TiDB 3.0, support for SQL Roles is also available. TiDB supports MySQL 5.7's privilege management system, including the syntax and privilege types. It is recommended that you use the latest LTS version of the TiDB database. ![]() You are viewing the documentation of an older version of the TiDB database (TiDB v5.0). ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |