应用刚上线,为了防止误删数据,准备建一个用户,只能查询和修改,不能删除。
下面记录下步骤:
1、添加用户:
先进入mysql:mysql -hlocalhost -uroot -ppassword
insert into mysql.user(Host,User,Password) values("localhost","liliang",password("liliang123"));
退出mysql,用mysql -hlocalhost -uliliang -pliliang123登录,登录不上。
再用root用户登录mysql,执行修改密码:
mysql> update mysql.user set password=password('liliang') where User='liliang' and Host='localhost';
mysql> flush privileges;
mysql> exit;
退出后使用mysql -hlocalhost -uliliang -pliliang,这次就登录成功了。不知道第一次是不是输错密码了。
2、查看当前登录用户:
mysql> select current_user();
+-------------------+
| current_user() |
+-------------------+
| liliang@localhost |
+-------------------+
1 row in set (0.00 sec)
3、设置用户权限:
mysql> grant select,update on model_manager.* to liliang@localhost identified by 'liliang123';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
给用户赋予所有权限:grant all privileges on model_manager.* to liliang@'localhost';
4、查询用户权限:
show grants for liliang@'localhost';
+-----------------------------------------------------------------------------------------------------------------+
| Grants for liliang@localhost |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT SELECT ON *.* TO 'liliang'@'localhost' IDENTIFIED BY PASSWORD '*8B033CFE2F67312D6F596F698110934FCDD28B23' |
| GRANT UPDATE ON `model_manager`.* TO 'liliang'@'localhost' |
+-----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
5、收回用户权限:
revoke update on model_manager.* from liliang@'localhost';
执行完这个之后再试试更新表中的数据,已经无法更新了。