mysql alter权限不足_Mysql中alter权限的隐患

MySQL测试环境中一张表被清空,检查binglog并没有发现有delte，drop，truncate操作。$mysqlbinlog test-150-bin.251369 |grep -i -E  -B3 'drop|delete|truncate'

检查了今天所有的binlog都没发现有delete,drop,truncate操作。

而且也排除了有人用临时关闭二进制日志的方式进行的恶意操作，因为没有权限。

看了一下表结构，居然发现ENGINE=BLACKHOLE

mysql> show create table t;

+-------+----------------------------------------------------------------------------------------+

| Table | Create Table                                                                           |

+-------+----------------------------------------------------------------------------------------+

| t     | CREATE TABLE `t` (

`aa` int(10) DEFAULT NULL

) ENGINE=BLACKHOLEDEFAULT CHARSET=utf8 |

+-------+----------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

过滤binlog中的alter，果然发现有对这个表的alter操作，把表的engine改为了BLACKHOLE

$mysqlbinlog test-150-bin.251369 |grep -i -E  -B3 'drop|delete|truncate|alter'

#141210 12:09:10 server id 150  end_log_pos 448502 CRC32 0xea5ee5cc     Query   thread_id=530   exec_time=0     error_code=0

SET TIMESTAMP=1418202550/*!*/;

ALTER TABLE `t` ENGINE=BLACKHOLE

模拟一下这个操作：

mysql> show create table t;

+-------+-----------------------------------------------+

| Table | Create Table                                  |

+-------+-----------------------------------------------+

| t     | CREATE TABLE `t` (

`aa` int(10) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

+-------+-----------------------------------------------+

1 row in set (0.02 sec)

mysql> insert into t values(1);

Query OK, 1 row affected (0.02 sec)

mysql> insert into t values(2);

Query OK, 1 row affected (0.01 sec)

mysql> select * from t;

+------+

| aa   |

+------+

|    1 |

|    2 |

+------+

2 rows in set (0.00 sec)

mysql> ALTER TABLE `t` ENGINE=BLACKHOLE;

Query OK, 2 rows affected (0.02 sec)

Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t;

Empty set (0.16 sec)

结论：不能把alter的权限赋给普通用户。