mysql增加用户

1. 新增用户 Sql代码 mysql insert into mysql. user (Host, User , Password ) values ( localhost , lionbule , password ( hello1234 )); mysqlflush privileges ; 或者 CREATEUSER ' username'@'host 'IDENTIFIEDBY'password'; 例子:CREATEUSER'dog'@'lo

1. 新增用户

Sql代码

1. mysql>insert into mysql.user(Host,User,Password) values("localhost","lionbule",password("hello1234"));
2. mysql>flush privileges;

或者

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

例子: CREATE USER 'dog'@'localhost' IDENTIFIED BY '123456';

CREATE USER 'pig'@'192.168.1.101_' IDENDIFIED BY '123456';

CREATE USER 'pig'@'%' IDENTIFIED BY '123456';

CREATE USER 'pig'@'%' IDENTIFIED BY '';

CREATE USER 'pig'@'%';

2. 修改用户密码

Sql代码

1. mysql>update mysql.user set password=password('new password') where User="lionbule" and Host="localhost";
2. mysql>flush privileges;

或

a. 使用mysqladmin语法：mysqladmin -u用户名 -p旧密码 password 新密码

例如：mysqladmin -u root -p 123 password 456；

或者

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

如果是当前登陆用户用SET PASSWORD = PASSWORD("newpassword");

3. 删除用户

Sql代码

1. mysql>DELETE FROM user WHERE User="lionbule" and Host="localhost";
2. mysql>flush privileges;

或者

drop user username@'%'

drop user username@localhost

4. 权限分配

4.1. grant用法

grant 权限 on 数据库.* to 用户名@'登录主机' identified by '密码'

Doc代码

1. 权限：
2. 常用总结, ALL/ALTER/CREATE/DROP/SELECT/UPDATE/DELETE
3. 数据库：
4. *.* 表示所有库的所有表
5. test.* 表示test库的所有表
6. test.test_table 表示test库的test_table表
7. 用户名：
8. mysql账户名
9. 登陆主机：
10. 允许登陆mysql server的客户端ip
11. '%'表示所有ip
12. 'localhost' 表示本机
13. '192.168.10.2' 特定IP
14. 密码：
15. 账户对应的登陆密码

取消授权用户:

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

例子: REVOKE SELECT ON *.* FROM 'pig'@'%';

REVOKE SELECT ON test.user FROM 'pig'@'%';

revoke all on *.* from sss@localhost ;

revoke all on user.* from 'admin'@'%';

SHOW GRANTS FOR 'pig'@'%'; //查看授权

取消授权用户:

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

例子: REVOKE SELECT ON *.* FROM 'pig'@'%';

REVOKE SELECT ON test.user FROM 'pig'@'%';

revoke all on *.* from sss@localhost ;

revoke all on user.* from 'admin'@'%';

SHOW GRANTS FOR 'pig'@'%'; //查看授权