Mysql安全机制

在mysql下mysql库中有6个权限表

mysql.user 

用户字段，权限字段，安全字段，资源控制字段

mysql.db 、 mysql.host

用户字段，权限字段

mysql.tables_priv，mysql.columms_priv，mysql.procs_priv

一、用户管理

(1)创建用户的三种方法

1.create user user1@'localhost' identified  by '123456';
2.insert into mysql.user(user,host,password,ssl_cipher,x509_issuer,x509_subject) values('user2','localhost',password('123456'),'','','');
3.grant select on *.* to user3@'localhost' identified by '123' //授select权所有库和所有表给user3，密码123
flush privileges

(2)删除用户

1.drop user user1@'localhost'
2.delete from mysql.user where user='user1' and host='localhost';

(3)root用户修改自己密码

1.mysqladmin -uroot -proot password '123'
2.update mydql.user set password=password('new_password') where user='root' and host='localhost';
3.set password=password('new_password')
flush privileges //刷新授权表

(4)root用户修改其他用户密码

1.set password for user3@'localhost' =password('new_password');
flush privileges;
2.updatae mysql.user set password=password('new_password') where user='user3' and host='localhost';
flush privileges;
3.grant select on *.* to user3@'localhost' identified by 'pwd';
flush privileges;

(5)普通用户修改自己密码

set password=password('new_password');

(6)丢失root用户密码

vim /etc/my.cnf
skip-grant-tables//将这句话的注释去掉
service mysqld restart
mysql -uroot //然后就可以跳过权限表进入mysql
update mysql.user set password=password('new_password') where user='user3' and host='localhost';
flush privileges;
\q   //退出mysql
vim /etc/my.cnf
#skip-grant-tables //将这句话再重新注释

二、权限管理

语法格式：grant 权限列表 on 库名.表名 to 用户名@'客户端' [identified by 'password' with grant option]

其中：with_option参数如下

grant option: 授权选项
max_queries_per_hour:定义每小时允许执行的查询数
max_updates_per_hou:定义每小时允许执行的更新数
max_connections_per-hour:定义每小时可以建立的连接数
max-user_connections:定义单个用户同是可以建立的连接数

授权示例

grant all on *.* to admin1@'%' identified by 'password';
grant all on *.* to admin2@'%' identified by 'pw' with grant option;
grant all on *.* bbs.* to admin3@'%' identified by 'pw';
grant all on bbs.user to admin4@'%' identified by 'pw';
grant select(col1),insert(col2,col3) on bbs.user to admin5@'%' identified by 'pw';

flush privileges

查看权限

show grants for admin@'%' \G;

回收权限 revoke 权限列表 on 数据库名 from 用户名@'客户端主机'

1.revoke delete on *.* from admin@'%';//回收部分权限
2.revoke all privileges on *.* from admin@'%';
revoke grant on *.* from admin@'%'; //回收全部权限(包括授权)
flush privileges;    //刷新授权