Mysql用户权限管理

1.创建用户

create user rep@’10.0.0.%’ identified by ‘123’;

2.查看用户

select user,host from mysql.user;

3.删除用户

drop user rep@’10.0.0.%’;

4.修改密码

①命令行修改 mysqladmin -uroot -p123 password ‘123456’

②授权修改 grant all on . to root@’%’ identified by ‘123456’;

③更新数据修改 update mysql.user set password=password(‘123456′) where user=’root’ and host=’localhost’; flush privileges; 刷新授权表

5.忘记密码时修改密码

①停止数据库 systemctl stop mysql

②跳过授权表和网络启动数据库 mysqld_safe –skip-grant-tables –skip-networking &

③连接数据库 mysql

④update修改密码 update mysql.user set password=password(‘123456′) where user=’root’ and host=’localhost’; flush privileges; 刷新授权表

⑤启动数据库并测试
mysqladmin -p123456 shutdown
systemctl start mysql
mysql -uroot -p123456

6.权限管理

grant 权限 on 库.表 to 用户@’主机域’ identified by ‘密码’; #授权

revoke drop on 库.表 from 用户@’主机域’; #回收权限

所有权限：INSERT,SELECT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE

单列授权(只能查看用户表)：grant select(user) on mysql.user to 用户@’主机域’ identified by ‘密码’； 其他同理

查看用户权限：show grants for root@’localhost’;