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)
