以mysql 5.7 数据库为例
创建用户
# 语法
create user username@'host' identified by 'password';
# 示例
create user test_01@'localhost' identified by '123456';
- host: 授权用户能够登录的主机,
%
全部机器,可指定 IP 或子网 - password: 登录密码可以
授权
# 语法
grant all privileges on db.tablename to username@'host'
# 示例
grant all privileges on *.* to 'test_01'@'localhost';
# 指定某一个数据
grant all privileges on ikang_db.* to 'test_01'@'localhost';
- privileges: 用户的操作权限,如SELECT,INSERT,UPDATE等,如果要授予所的权限则使用
all privileges
- db.tablename: 要授权的数据,可以指定某一个数据库,
*.*
全部数据库 - host: 授权那些机器能够登录。
%
全部机器,可指定 IP 或子网
修改密码
# 语法
set password for 'username'@'host' = PASSWORD('newpassword');
set password = PASSWORD("newpassword");
# 示例
SET password FOR 'test_01'@'localhost' = PASSWORD('123123');
# 自身设置密码
set password = PASSWORD("121212");
权限查看
# 查看用户 host 授权方式
mysql> select * from user where user='test_01' \G;
#
mysql> SHOW GRANTS FOR 'test_01'@'localhost';
+---------------------------------------------+
| Grants for test_01@localhost |
+---------------------------------------------+
| GRANT USAGE ON *.* TO 'test_01'@'localhost' |
+---------------------------------------------+
1 row in set (0.00 sec)
撤销权限
# 语法
revoke privilege on databasename.tablename FROM 'username'@'host';
# 示例,
revoke privilege on *.* FROM 'test_01'@'localhost';
怎么赋予权限,怎么撤销
删除用户
# 语法
drop user username@'host';
# 示例
drop user test_01@'localhost';
刷新
flush privileges