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)