Mysql用户相关操作

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")
                  alter user "root"@"%" identified by "root1";(针对8.0以上版本)
                  mysqladmin -uroot -proot password root1(或者使用mysqladmin)
撤销用户权限：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)