msyql 用户和权限操作

1、查看有哪些用户

mysql> use mysql;

Database changed

mysql> select host,user,password from user;

 

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

| host                 | user          | password                                  |

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

| localhost            | root          | *22E093CD59043430C91B3F885A8149AC99F4CDA6 |

| hexindai-bj-dfgc-156 | root          | *22E093CD59043430C91B3F885A8149AC99F4CDA6 |

| 127.0.0.1            | root          | *22E093CD59043430C91B3F885A8149AC99F4CDA6 |

| ::1                  | root          | *22E093CD59043430C91B3F885A8149AC99F4CDA6 |

| 172.16.10.154        | slave         | *22E093CD59043430C91B3F885A8149AC99F4CDA6 |

 

 

2、新增用户

grant [权限1,权限2,权限3,...] on *.* to ‘user'@'host' identified by '密码';

 

flush privileges;

 

说明：

l 常用权限: all,create,drop,insert,deete,update,select

l *.* 代表 数据库.表，*表示所有

l User 表示 用户名

l Host 表示 来源，一般为localhost,IP

举例：

mysql> grant insert,select on shanghai.login to 'liangliang'@'%' identified by 'liang';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

 

 

3、删除用户

Delete from user where user=’root’;

 

flush privileges;

 

说明：

l 通过用户表条件正常删除即可

l 删除后务必flush privileges 刷新权限表

 

 

4、新增权限

grant [权限1,权限2,权限3,...] on *.* to ‘user'@'host' identified by '密码'

 

flush privileges;

 

 

举例：

mysql> grant insert on shanghai.login to 'pipi'@'%' identified by 'root';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

 

 

5、收回权限

Revoke[权限1,权限2,权限3,...] on *.* fromuser@’192.168.1.%’;

 

flush privileges;

 

说明：

l 常用权限: all,create,drop,insert,deete,update,select

l *.* 代表 数据库.表，*表示所有

l User 表示 用户名

l 192.168.1.%   %为通配符，表示该网段下的所有IP用户

l 注意: 关键词from

 

举例：

mysql> revoke insert on shanghai.login from 'pipi'@'%';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into login values (3,'pippi','root');

ERROR 1142 (42000): INSERT command denied to user 'pipi'@'localhost' for table 'login'

 

6、登录密码更改

 

update user set password=password('111111') where user='liangliang';

 

flush privileges;