Mysql用户权限管理
1. 查看mysql用户
select User,Host,authentication_string from mysql.user; +------+--------------+-----------------------+ | User | Host | authentication_string | +------+--------------+-----------------------+ | root | localhost | | | root | b7d0cf7b6e2f | | | root | 127.0.0.1 | | | root | ::1 | | | | localhost | NULL | | | b7d0cf7b6e2f | NULL | +------+--------------+-----------------------+
- 可以先查看user表中的字段,select自己想要的字段信息,一般有user,host,password(有些版本是authentication_string)
2. 增加用户
create user ‘新用户名‘@‘localhost‘ identified by ‘密码‘; # 允许所有ip连接 create user ‘新用户名‘@‘%‘ identified by ‘密码‘;
3. 给新用户赋予权限
# 基本格式如下 grant all privileges on 数据库名.表名 to ‘新用户名‘@‘指定ip‘ identified by ‘新用户密码‘ ; # 允许访问所有数据库下的所有表 grant all privileges on *.* to ‘新用户名‘@‘指定ip‘ identified by ‘新用户密码‘ ; # 指定数据库下的指定表 grant all privileges on test.test to ‘新用户名‘@‘指定ip‘ identified by ‘新用户密码‘ ;
4. 删除用户
DROP USER ;
5. 修改用户权限
#设置用户拥有所有权限也就是管理员 grant all privileges on *.* to ‘用户名‘@‘指定ip‘ identified by ‘用户密码‘ WITH GRANT OPTION; #拥有查询权限 grant select on *.* to ‘用户名‘@‘指定ip‘ identified by ‘用户密码‘ WITH GRANT OPTION; #其它操作权限说明,select查询 insert插入 delete删除 update修改 #设置用户拥有查询插入的权限 grant select,insert on *.* to ‘用户名‘@‘指定ip‘ identified by ‘用户密码‘ WITH GRANT OPTION; #取消用户查询的查询权限 REVOKE select ON what FROM ‘用户名‘;
6. 修改后刷新权限
flush privileges;
7. mysql启动命令及连接
# 启动mysql mysqld 或 mysqld --initialize-insecure service mysql start # 连接mysql mysql -uroot -p
相关推荐
苏康申 2020-11-13
vitasfly 2020-11-12
emmm00 2020-11-17
王艺强 2020-11-17
aydh 2020-11-12
世樹 2020-11-11
zry 2020-11-11
URML 2020-11-11
spurity 2020-11-10
yifangs 2020-10-13
Andrea0 2020-09-18
Ida 2020-09-16
ltd00 2020-09-12
tufeiax 2020-09-03
xjd0 2020-09-10
greatboylc 2020-09-10
adsadadaddadasda 2020-09-08
疯狂老司机 2020-09-08
CoderToy 2020-11-16