mysql安全机制
1、mysql安全机制
1>mysql权限表
mysql.user Global level
用户字段
权限字段
安全字段
资源控制字段
mysql.db、mysql.host Database level
用户字段
权限字段
mysql.tables_priv Table level
mysql.columns_priv Column level
mysql.procs_priv
2>MySQL用户管理
(1)登录和退出MySQL
mysql -h192.168.129.128 -P 3306 -u root -p123 mysql -e ‘select user,host from user‘ -h 指定主机名 -P MySQL服务器端口 -u 指定用户名 -p 指定登录密码 此处mysql为指定登录的数据库 -e 接SQL语句
(2)创建用户
方法一:CREATE USER语句创建
mysql> create user ‘localhost‘ identified by ‘123456‘;
方法二: INSERT语句创建
mysql> insert into mysql.user(user,host,password) values -> (‘user2‘,‘localhost‘,password(‘123456‘)); Query OK, 1 row affected, 3 warnings (0.00 sec) mysql> flush privileges; //flush privileges刷新MySQL的系统权限相关表 Query OK, 0 rows affected (0.00 sec)
方法三: GRANT语句创建
mysql> grant select on *.* to ‘localhost‘ identified by ‘123456‘; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)
(3)删除用户
方法一:DROP USER语句删除
mysql> drop user ‘localhost‘; Query OK, 0 rows affected (0.00 sec)
方法二:DELETE语句删除
mysql> delete from mysql.user where user=‘user2‘ and host=‘localhost‘; Query OK, 1 row affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)
(4)修改用户密码
root修改自己密码:
方法一:
[ ~]# mysqladmin -uroot -p123456 password ‘new_password‘ //123456为旧密码
方法二:
mysql> update mysql.user set password=password(‘111111‘) -> where user=‘root‘ and host=‘localhost‘; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)
方法三:
mysql> set password=password(‘123456‘); Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)
root修改其他用户密码:
方法一:
mysql> set password for ‘localhost‘=password(‘111111‘); //111111为新密码 Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)
方法二:
mysql> update mysql.user set password=password(‘123456‘) -> where user=‘user3‘ and host=‘localhost‘; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)
普通用户修改自己密码:
方法一:
mysql> set password=password(‘new_password‘);
方法二:
mysqladmin -uuser -p123 password ‘new_password‘ //123为旧密码
3>mysql权限管理
权限应用的顺序:
user (Y|N) ==> db ==> tables_priv ==> columns_priv
语法格式:
语法格式: grant 权限列表 on 库名.表名 to 用户名@‘客户端主机‘ [identified by ‘密码‘ with option参数]; ==权限列表 all 所有权限(不包括授权权限) select,update ==数据库.表名 *.* 所有库下的所有表 Global level web.* web 库下的所有表 Database level web.stu_info web库下的stu_info表 Table level SELECT (col1), INSERT (col1,col2) ON mydb.mytbl Column level ==客户端主机 % 所有主机 192.168.2.% 192.168.2.0网段的所有主机 192.168.2.168 指定主机 localhost 指定主机
with_option参数
GRANT OPTION: | 授权选项 |
MAX_QUERIES_PER_HOUR: | 定义每小时允许执行的查询数 |
MAX_UPDATES_PER_HOUR: | 定义每小时允许执行的更新数 |
MAX_CONNECTIONS_PER_HOUR: | 定义每小时可以建立的连接数 |
MAX_USER_CONNECTIONS: | 定义单个用户同时可以建立的连接数 |
Grant示例:
mysql> grant all on *.* to ‘localhost‘ identified by ‘localhost‘; Query OK, 0 rows affected (0.00 sec) mysql> grant all on *.* to ‘localhost‘ identified by ‘localhost‘ with grant option; Query OK, 0 rows affected (0.00 sec) mysql> grant all on bss.* to ‘localhost‘ identified by ‘localhost‘; Query OK, 0 rows affected (0.00 sec) mysql> grant all on bss.user to ‘localhost‘ identified by ‘localhost‘; Query OK, 0 rows affected (0.00 sec)
回收权限REVOKE
查看权限
mysql> show grants\G mysql> show grants for ‘localhost‘\G
回收权限REVOKE
语法:
REVOKE 权限列表 ON 数据库名 FROM 用户名@‘客户端主机’ 示例: REVOKE DELETE ON *.* FROM ’%’; //回收部分权限 REVOKE ALL PRIVILEGES ON *.* FROM ’%’; //回收所有权限 # REVOKE ALL PRIVILEGES,GRANT OPTION ON *.* FROM ‘USER2‘@‘%‘;