MySQL(MariaDB) GRANT授权第三方增删改查权限

格式：GRANT 权限列表 ON 数据库.表名 TO ‘用户名’@‘允许访问的客户端’ IDENTIFIED BY ‘密码’;

如：授权用户x密码我x，并对demo库的所有表增删改查权限，执行完必须执行FLUSH PRIVILEGES;使操作生效。

GRANT Select,Update,Insert,Delete ON demo.* TO 'x'@'%' IDENTIFIED BY 'x';
FLUSH PRIVILEGES;

撤销授权

REVOKE Select,Update,Insert,Delete ON demo.* FROM 'x'@'%' IDENTIFIED BY 'x';
FLUSH PRIVILEGES;

授权全权限WITH GRANT OPTION表示可以给其它用户授权

GRANT ALL PRIVILEGES ON *.*  TO 'x'@'%' IDENTIFIED BY 'x' WITH GRANT OPTION;
FLUSH PRIVILEGES;

撤销全权限

REVOKE ALL PRIVILEGES ON *.*  FROM 'x'@'%' IDENTIFIED BY 'x';
FLUSH PRIVILEGES;

查看用户有哪些权限
如：查询x用户的权限

show grants for x;

+----------------------------------------------------------------------------------------------------------------------------+
| Grants for x@%                                                                                               |
+----------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `x`@`%` IDENTIFIED BY PASSWORD 'x'                                                     |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `demo`.* TO `x`@`%`                                                  |
+----------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

查看权限列表

show privileges;

具体权限列表如下，建议还是用语句查出来，防止版本不一致，导致权限失效或者SQL异常的问题。

MariaDB [(none)]> show privileges;
+--------------------------+---------------------------------------+--------------------------------------------------------------------+
| Privilege                | Context                               | Comment                                                            |
+--------------------------+---------------------------------------+--------------------------------------------------------------------+
| Alter                    | Tables                                | To alter the table                                                 |
| Alter routine            | Functions,Procedures                  | To alter or drop stored functions/procedures                       |
| Create                   | Databases,Tables,Indexes              | To create new databases and tables                                 |
| Create routine           | Databases                             | To use CREATE FUNCTION/PROCEDURE                                   |
| Create temporary tables  | Databases                             | To use CREATE TEMPORARY TABLE                                      |
| Create view              | Tables                                | To create new views                                                |
| Create user              | Server Admin                          | To create new users                                                |
| Delete                   | Tables                                | To delete existing rows                                            |
| Delete history           | Tables                                | To delete versioning table historical rows                         |
| Drop                     | Databases,Tables                      | To drop databases, tables, and views                               |
| Event                    | Server Admin                          | To create, alter, drop and execute events                          |
| Execute                  | Functions,Procedures                  | To execute stored routines                                         |
| File                     | File access on server                 | To read and write files on the server                              |
| Grant option             | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess                |
| Index                    | Tables                                | To create or drop indexes                                          |
| Insert                   | Tables                                | To insert data into tables                                         |
| Lock tables              | Databases                             | To use LOCK TABLES (together with SELECT privilege)                |
| Process                  | Server Admin                          | To view the plain text of currently executing queries              |
| Proxy                    | Server Admin                          | To make proxy user possible                                        |
| References               | Databases,Tables                      | To have references on tables                                       |
| Reload                   | Server Admin                          | To reload or refresh tables, logs and privileges                   |
| Binlog admin             | Server                                | To purge binary logs                                               |
| Binlog monitor           | Server                                | To use SHOW BINLOG STATUS and SHOW BINARY LOG                      |
| Binlog replay            | Server                                | To use BINLOG (generated by mariadb-binlog)                        |
| Replication master admin | Server                                | To monitor connected slaves                                        |
| Replication slave admin  | Server                                | To start/stop slave and apply binlog events                        |
| Slave monitor            | Server                                | To use SHOW SLAVE STATUS and SHOW RELAYLOG EVENTS                  |
| Replication slave        | Server Admin                          | To read binary log events from the master                          |
| Select                   | Tables                                | To retrieve rows from table                                        |
| Show databases           | Server Admin                          | To see all databases with SHOW DATABASES                           |
| Show view                | Tables                                | To see views with SHOW CREATE VIEW                                 |
| Shutdown                 | Server Admin                          | To shut down the server                                            |
| Super                    | Server Admin                          | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc.                |
| Trigger                  | Tables                                | To use triggers                                                    |
| Create tablespace        | Server Admin                          | To create/alter/drop tablespaces                                   |
| Update                   | Tables                                | To update existing rows                                            |
| Set user                 | Server                                | To create views and stored routines with a different definer       |
| Federated admin          | Server                                | To execute the CREATE SERVER, ALTER SERVER, DROP SERVER statements |
| Connection admin         | Server                                | To bypass connection limits and kill other users' connections      |
| Read_only admin          | Server                                | To perform write operations even if @@read_only=ON                 |
| Usage                    | Server Admin                          | No privileges - allow connect only                                 |
+--------------------------+---------------------------------------+--------------------------------------------------------------------+
41 rows in set (0.000 sec)