mysql修改用户表引起的血案

由于公司公网ip地址更改或者员工离职，需要调整mysql用户权限

原来的用户表如图：

mysql> select host,user,password from user;
+-------------------------+------------------+-------------------------------------------+
| host                    | user             | password                                  |
+-------------------------+------------------+-------------------------------------------+
| localhost               | root             |                                           |
| 127.0.0.1               | root             |                                           |
| ::1                     | root             |                                           |
| localhost               |                  |                                           |
| 10.0.0.1       |root        | *B31BEF312C8FE02D2687C34ED921E2C3CF509312 |
+-------------------------+------------------+-------------------------------------------+

需要改为10.0.0.2

update user set host='10.0.0.2' where host='10.0.0.1';

执行完后

mysql> select host,user,password from user;
+-------------------------+------------------+-------------------------------------------+
| host                    | user             | password                                  |
+-------------------------+------------------+-------------------------------------------+
| localhost               | root             |                                           |
| 127.0.0.1               | root             |                                           |
| ::1                     | root             |                                           |
| localhost               |                  |                                           |
| 10.0.0.2       |root        | *B31BEF312C8FE02D2687C34ED921E2C3CF509312 |
+-------------------------+------------------+-------------------------------------------+

检查  db表看下权限

一切没问题了。

flush privileges;

测试连接，一切都OK

但是过几天发现，mysql作业报错，但是events表里显示执行是成功的

问题出现在

mysql> select definer,name from mysql.event;
+----------------+--------------------------+
| definer        | name                     |
+----------------+--------------------------+
| root@'10.0.0.1' | insert_delete_art_day    | 
| root@'10.0.0.1'| insert_delete_aut_day    | 
| root@'10.0.0.1' | insert_delete_book_day   | 
| root@'10.0.0.1' | insert_delete_art_month  | 
| root@'10.0.0.1' | insert_delete_aut_month  | 
| root@'10.0.0.1' | insert_delete_book_month | 
| root@'10.0.0.1' | insert_delete_word_month | 
| root@'10.0.0.1'| insert_delete_word_day   | 
+----------------+--------------------------+

存贮过程

mysql> select replace(definer,'localhost','10.0.0.1'),name from mysql.proc;
+-----------------------------------------+--------------------------+
| replace(definer,'localhost','10.0.0.1') | name                     |
+-----------------------------------------+--------------------------+
| root@10.0.0.1                           | insert_delete_art_day    | 
| root@10.0.0.1                           | insert_delete_aut_day    | 
| root@10.0.0.1                           | insert_delete_book_day   | 
| root@10.0.0.1                           | insert_delete_art_month  | 
| root@10.0.0.1                           | insert_delete_aut_month  | 
| root@10.0.0.1                           | insert_delete_book_month | 
| root@10.0.0.1                           | insert_delete_word_day   | 
| root@10.0.0.1                           | insert_delete_word_month | 
+-----------------------------------------+--------------------------+
8 rows in set (0.00 sec)

当修改user表的时候，event和proc表的definer字段并没有修改，

这会导致执行更新语句或作业执行是检查definer里用户不存在，导致更新或作业失败

主从同步报错

MySQL异常：The user specified as a definer (XXX@XXX) does not exist，视图更新的语句

这个错误也是由于权限更新维护不当引起的

mysql> select TABLE_NAME,DEFINER from information_schema.views;
+-----------------------------------+------------------------------+
| TABLE_NAME                        | DEFINER                      |
+-----------------------------------+------------------------------+
| v_button                          | root@192.168.0.0/255.255.0.0 |
| v_function                        | root@192.168.0.0/255.255.0.0 |
| v_role_model                      | root@192.168.0.0/255.255.0.0 |
| v_user_role                       | root@192.168.0.0/255.255.0.0 |
| bt_v_areauser                     | root@%                       |

总结：

更改用户权限，要检查视图，表，索引，触发器、作业、存贮过程的DEFINER是否存在，保证数据的完成性可用性

MySQL存储过程权限检查主要点

一、权限相关：

1.修改sql security

Sql代码

ALTER PROCEDURE www SQL SECURITY INVOKER ；

ALTER PROCEDURE www SQL SECURITY DEFINER ；

（1）MySQL存储过程是通过指定SQL SECURITY子句指定执行存储过程的实际用户；

（2）如果SQL SECURITY子句指定为DEFINER，存储过程将使用存储过程的DEFINER执行存储过程，验证调用存储过程的用户是否具有存储过程的execute权限和DEFINER用户是否具有存储过程引用的相关对象的权限；

（3）如果SQL SECURITY子句指定为INVOKER，那么MySQL将使用当前调用存储过程的用户执行此过程，并验证用户是否具有存储过程的execute权限和存储过程引用的相关对象的权限；

（4）如果不显示的指定SQL SECURITY子句，MySQL默认将以DEFINER执行存储过程。

create event语法

CREATE


    [DEFINER = { user | CURRENT_USER }]
    EVENT
    [IF NOT EXISTS]
    event_name
    ON SCHEDULE schedule
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'comment']
    DO event_body;


schedule:


    AT timestamp [+ INTERVAL interval] ...
    | EVERY interval
    [STARTS timestamp [+ INTERVAL interval] ...]
    [ENDS timestamp [+ INTERVAL interval] ...]


interval:


    quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE | WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE | DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

最基本的create event只需要三个部分：

1. create event关键字以及一个event名称

2. on schedule子句

3. do子句

例如：

create event evt_insert 
      on schedule every 10 second
      do update myschema.mytable set mycol = mycol + 1;

对于语句的详细解释：

definer：说明该event的用户。服务器在执行该事件时，使用该用户来检查权限。默认用户为当前用户，即definer = current_user。如果明确指明了definer，则必须遵循如下规则：

1.如果没有super权限，唯一允许的值就是自己当前用户，而不能设置为其他用户。

2.如果具有super权限，则可以指定任意存在的用户。如果指定的用户不存在，则事件在执行时会报错。