mysql-用户权限相关

一、mysql新建用户：

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

alter user 'root'@'localhost' IDENTIFIED BY 'Love2jzinfo@1';

host为”%”表示允许所有主机访问；
二、用户授权：

GRANT privileges ON databasename.tablename TO 'username'@'host'

all     所以权限
create  创建
drop    删除
select  查询
alter   修改表字段，索引
delete  删除表数据
insert  插入数据
update  更新数据    

注意:用以上命令授权的用户不能给其它用户授权,如果想让该用户可以授权,用以下命令:
GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;

三、更新用户密码

SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');

也可：

mysql> UPDATE user SET password=PASSWORD("new password") WHERE user='name'; 

四、撤销用户权限

REVOKE privilege ON databasename.tablename FROM 'username'@'host';

五、删除用户

 DROP USER 'username'@'host';

六、查看已有用户创建语句

mysql> select user,host,authentication_string from mysql.user;
+---------------+-----------+-------------------------------------------+
| user          | host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *663B41A2CB7F842A499FC4F9C2946E912A2FE945 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| zww           | %         | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+---------------+-----------+-------------------------------------------+
4 rows in set (0.00 sec)

mysql> show grants for 'zww'@'%';
+------------------------------------------+
| Grants for zww@%                         |
+------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'zww'@'%' |
+------------------------------------------+
1 row in set (0.00 sec)