mysql安全机制

第一节:mysql 安全机制

1.1 mysql 权限表

  user 权限表:记录允许连接到服务器的用户帐号信息,里面的权限是全局级的。

  db 权限表:记录各个帐号在各个数据库上的操作权限。

  table_priv 权限表:记录数据表级的操作权限。

  columns_priv 权限表:记录数据列级的操作权限。

  host 权限表:配合 db 权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受 GRANT 和 REVOKE 语句的影响。

1.2 mysql 用户管理

(1)登录和退出

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语句

quit 退出

 

(2)创建用户

  方法一:CREATE USER 语句创建

mysql> create user user1@'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 user3@'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 user1@'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 修改自己密码:

  方法一:

[root@centos6 ~]# mysqladmin -uroot -p123456 password 'new_password'

  方法二:

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 user3@'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'

1.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 示例:

mysql> grant all on *.* to user1@'localhost' identified by 'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on *.* to user2@'localhost' identified by 'localhost' with grant option;
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on bss.* to user3@'localhost' identified by 'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on bss.user to user4@'localhost' identified by 'localhost';
Query OK, 0 rows affected (0.00 sec)

  查看权限:

mysql> show grants\G
mysql> show grants for user1@'localhost'\G

  回收权限 REVOKE:

  语法:REVOKE 权限列表 ON 数据库名 FROM 用户名@‘客户端主机’

#示例:
REVOKE DELETE ON *.* FROM USER1@’%’;           #回收部分权限
REVOKE ALL PRIVILEGES ON *.* FROM USER2@’%’;    #回收所有权限
# REVOKE ALL PRIVILEGES,GRANT OPTION ON *.* FROM 'USER2'@'%';

 

posted @ 2020-05-03 20:43  我听过  阅读(438)  评论(0编辑  收藏  举报