Mysql 5.7用户管理授权和远程访问

深碍√TFBOYSˉ_ 2022-05-15 13:59 368阅读 0赞

select version();  
5.7.24

创建用户  
create user 命令  
create user ‘user’@‘localhost’ indentified by ‘password’

删除用户  
delete from user(\#mysql.user ) where user=‘name’  
drop user ‘name’@’%’

重命名用户  
rename user@host to newuser@host;  
(rename 也可用来重命名table)

指定数据表授权  
Grant privileges ON databasename.tablename TO ‘username’@‘host’  
Grant INSERT,DELETE,UPDATE,SELECT ON db.table TO ‘user’@‘localhost’;  
\+如果所有权利 , Grant all on db.table to ‘user’@‘localhost’  
\+如果数据库所有表 Grant all on db.\* to ‘user’@‘localhost’  
\+如果所有数据库 Grant all on *.* to ‘user’@‘localhost’  
phpmyadmin7.2 远程登录会遇到权限不足问题，这时候Grant命令就有用了。

取消权限授权  
revoke insert,delete on *.* from user@host  
revoke all on *.* from user@host

修改密码  
set password for ‘username’@‘localhost’ = PASSWORD(‘newpassword’)  
或当前用户  
set password = password(‘something’)  
update user set authentication\_string=password(‘some’) where user = ‘name’

查看用户权限  
show grants;  
show grants fro usr@host

远程访问  
1检查mysql访问端口,默认是3306 .  
配置文件 /etc/mysql/mysql.conf.d/mysqld.cnf  
\[mysqld\]字段下 port = 3306  
2 如果要在互联网访问,需要屏蔽本机监听  
\#bind-address = 127.0.0.1  
3 对需要访问的用户,开启主机限制  
在mysql的user表中,将需使用用户的host 为%  
update user set host = ’%’ where user = ’root’;

权限修改生效  
flush privileges;  
重启服务  
systemctl restart mysql

(此版本(5.7.24)授权表共有4个表：user、db、tables\_priv和columns\_priv。)