mysql限制用户的权限

首先检查某个用户的权限:

mysql> show grants for 'root'@'localhost';
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*2A16806678155C02992E560DFF297AE1065AF505' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

在实际的业务中 ，为了安全起见，可以新建一个用户，然后grant一些增删改查权限，不要授权drop等权限；或者通过revoke撤回一些特定的权限。 撤回root用户的drop权限:

mysql> revoke drop on *.* from 'root'@'localhost';
Query OK, 0 rows affected (0.01 sec)

mysql> show grants for 'root'@'localhost';
+------------------------------------------------------------------------------                                                  -------------------------------------------------------------------------------                                                  -------------------------------------------------------------------------------                                                  -------------------------------------------------------------------------------                                                  -------------------------------------------------------------------------------                                                  -------------------------------------+
| Grants for root@localhost                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
+------------------------------------------------------------------------------                                                  -------------------------------------------------------------------------------                                                  -------------------------------------------------------------------------------                                                  -------------------------------------------------------------------------------                                                  -------------------------------------------------------------------------------                                                  -------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, RELOAD, SHUTDOWN, PROCESS, FILE                                                  , REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOC                                                  K TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIE                                                  W, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPAC                                                  E ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*2A16806678155C02992E560                                                  DFF297AE1065AF505' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION 

revoke结合上文《mysql sql_safe_updates》提到的sql_safe_updates，可以为mysql带来更高的安全。