mysql 安全_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';

grantselect(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; //刷新授权