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‘@‘%‘;