mysql user %_mysql（user相关操作）

1、添加用户(3步操作)

create user "username"@"host" identified by "password";

username： 用户名

host: 指定host，% 代表任何主机均能远程访问， localhost代表只能本地登录，

92.168.0.100代表只能再指定的主机上登录

password: 用户密码

grant all privileges on database_name.table_name to username;

all privileges: 代表所有权限，可以指定某一个权限，

database_name.table_name: 指定数据库和指定表，database_name.*代表指定库中所有的表, *.*代表所有的库和表

username: 给该用户赋予权限

如下demo:

grant create,select,insert,update,delete,drop on test_db.test_table to bingo

向bingo用户赋予对test_db库中的test_table表create等权限

flush privileges;

刷新权限， 之前赋予的权限才能生效

create user "bingo"@"%" identified by "12345";

grant all privileges on *.* to bingo;

flush privileges;

效果如下：

mysql> create user "bingo"@"%" identified by "12345";

Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on *.* to bingo;

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> select user, host from mysql.user;

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

| user | host |

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

| bingo | % |

| debian-sys-maint | localhost |

| mysql.session | localhost |

| mysql.sys | localhost |

| root | localhost |

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

5 rows in set (0.00 sec)

2、删除用户

drop user "username"@"host";

mysql> select user, host from mysql.user;

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

| user | host |

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

| bingo | % |

| bingo1 | % |

| debian-sys-maint | localhost |

| mysql.session | localhost |

| mysql.sys | localhost |

| root | localhost |

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

6 rows in set (0.00 sec)

mysql> drop user "bingo1"@"%";

Query OK, 0 rows affected (0.00 sec)

mysql> select user, host from mysql.user;

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

| user | host |

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

| bingo | % |

| debian-sys-maint | localhost |

| mysql.session | localhost |

| mysql.sys | localhost |

| root | localhost |

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

5 rows in set (0.00 sec)

3、修改用户相关

修改用户名： rename user "bingo"@"%" to "bingo1"@"%";

修改密码：set password for "bingo1"@"%" = password("123456")

撤销用户权限：revoke all privileges on *.* from "bingo1"@"%";

查看用户权限：show grants for "bingo1"@"%";

mysql> show grants for "bingo1"@"%";

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

| Grants for bingo1@% |

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

| GRANT USAGE ON *.* TO 'bingo1'@'%' |

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

1 row in set (0.00 sec)