MySQL用户定义与其他软件用户定义不一样,其他软件确定一个用户的唯一标准是username,而在MySQL中需要两个字段才能确定一个用户(user + host)。

mysql> select user,host,password from mysql.user;
+------+-----------+-------------------------------------------+
| user | host      | password                                  |
+------+-----------+-------------------------------------------+
| root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root | db01      |                                           |
| root | 127.0.0.1 |                                           |
| root | ::1       |                                           |
|      | localhost |                                           |
|      | db01      |                                           |
+------+-----------+-------------------------------------------+
6 rows in set (0.00 sec)


用户定义:

user 主机范围
使用某个用户 从哪个(些)地址访问我的数据库


用户的功能:

  1. 用来登陆MySQL数据库
  2. 用来管理数据库对象(库,表)
     

权限:针对不同用户设置对不同对象管理能力
  select  update  delete  insert  create

权限范围:

*.* 全局范围
fxw.* 单库级别
fxw.table1 单表级别


授权语句:
grant  all  on wordpress.* to wordpress@'10.0.0.%' identified by '123456';
          权限     权限范围           用户                                                    密码
 

关于用户的操作:

  • 创建用户
    mysql> create user zabbix@'10.0.0.%' identified by '123456';
    Query OK, 0 rows affected (0.02 sec)
    
    mysql>  select user,host,password from mysql.user;
    +--------+-----------+-------------------------------------------+
    | user   | host      | password                                  |
    +--------+-----------+-------------------------------------------+
    | root   | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
    | root   | db01      |                                           |
    | root   | 127.0.0.1 |                                           |
    | root   | ::1       |                                           |
    |        | localhost |                                           |
    |        | db01      |                                           |
    | zabbix | 10.0.0.%  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
    +--------+-----------+-------------------------------------------+
    7 rows in set (0.01 sec)
    
    mysql> 

    但此时,用户并没有更多权限,只有对于普通表的查询。

    
    [root@db01 ~]# mysql -uzabbix -p123456 -h10.0.0.20   #只能采用10.0.0.1-10.0.0.254之间的网段登陆
    Warning: Using a password on the command line interface can be insecure.
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 6
    Server version: 5.6.36 Source distribution
    
    Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    mysql> select user,password,host from mysql.user;
    ERROR 1142 (42000): SELECT command denied to user 'zabbix'@'10.0.0.20' for table 'user'

     

  • 删除用户:
    从上面可见,有几个没有用户名或没有密码的用户存在,其实这些对于数据库来说都是有安全隐患的,需要手动删除。

    mysql> drop user root@'db01';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> drop user root@'127.0.0.1';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> drop user root@'::1';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> drop user ''@'localhost';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> drop user ''@'db01';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select user,password ,host from mysql.user;
    +--------+-------------------------------------------+-----------+
    | user   | password                                  | host      |
    +--------+-------------------------------------------+-----------+
    | root   | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | localhost |
    | zabbix | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 10.0.0.%  |
    +--------+-------------------------------------------+-----------+
    2 rows in set (0.00 sec)
    
    mysql> 

     

  • 用户授权:
    新创建的用户zabbix,在没有授权前,是无法进行创建数据库(testdb)或更多其他的操作。

    mysql> select user,password,host from mysql.user;
    ERROR 1142 (42000): SELECT command denied to user 'zabbix'@'10.0.0.20' for table 'user'
    mysql> create database testdb;
    ERROR 1044 (42000): Access denied for user 'zabbix'@'10.0.0.%' to database 'testdb'
    mysql> 

    授权后:
    root用户操作:

    mysql> grant   all     on  testdb.*     to   zabbix@'10.0.0.%'; 
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> 

    zabbix用户操作: 

    mysql> create database testdb;   
    Query OK, 1 row affected (0.02 sec)
    
    mysql> create database testdb1;   #只能创建testdb,其他名称不能
    ERROR 1044 (42000): Access denied for user 'zabbix'@'10.0.0.%' to database 'testdb1'
    mysql> 
    

    #all权限:SELECT,INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES,INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE

    -- 开发用户权限:

    grant SELECT,INSERT, UPDATE, DELETE, CREATE, DROP  on  testdb.* to zabbix@'10.0.0.%';

     

  • 创建用户并授权

    mysql> grant all on *.* to root@'10.0.0.%' identified by '123456';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select user,password ,host from mysql.user;
    +--------+-------------------------------------------+-----------+
    | user   | password                                  | host      |
    +--------+-------------------------------------------+-----------+
    | root   | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | localhost |
    | root   | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 10.0.0.%  |
    | zabbix | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 10.0.0.%  |
    +--------+-------------------------------------------+-----------+
    3 rows in set (0.00 sec)
    
    mysql> 

     

  • 查询用户权限

    mysql> show grants for root@'10.0.0.%';
    +---------------------------------------------------------------------------------------------------------------------+
    | Grants for root@10.0.0.%                                                                                            |
    +---------------------------------------------------------------------------------------------------------------------+
    | GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.0.0.%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
    +---------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> show grants for root@'localhost';
    +----------------------------------------------------------------------------------------------------------------------------------------+
    | Grants for root@localhost                                                                                                              |
    +----------------------------------------------------------------------------------------------------------------------------------------+
    | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' WITH GRANT OPTION |
    | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |
    +----------------------------------------------------------------------------------------------------------------------------------------+
    2 rows in set (0.00 sec)

    root@localhost用户 带有with grant option 表示有权给其他用户授权。
    root@10.0.0.%用户 没有with grant option 就不能给其他用户授权

     

  • 收回权限
    revoke create,drop  on testdb.*  from zabbix@'10.0.0.%'; #收回部分权限
    revoke all on testdb.* from zabbix@'10.0.0.%';  #收回所有权限

    mysql> show grants for zabbix@'10.0.0.%';
    +--------------------------------------------------------------------------------------------------------------+
    | Grants for zabbix@10.0.0.%                                                                                   |
    +--------------------------------------------------------------------------------------------------------------+
    | GRANT USAGE ON *.* TO 'zabbix'@'10.0.0.%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
    | GRANT ALL PRIVILEGES ON `testdb`.* TO 'zabbix'@'10.0.0.%'                                                    |
    +--------------------------------------------------------------------------------------------------------------+
    2 rows in set (0.00 sec)
    
    mysql> revoke all on testdb.* from zabbix@'10.0.0.%';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> show grants for zabbix@'10.0.0.%';
    +--------------------------------------------------------------------------------------------------------------+
    | Grants for zabbix@10.0.0.%                                                                                   |
    +--------------------------------------------------------------------------------------------------------------+
    | GRANT USAGE ON *.* TO 'zabbix'@'10.0.0.%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
    +--------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> 

     

  • 思考:
    grant select on  *.*    to   zabbix@'10.0.0.%' ;
    grant INSERT, UPDATE, DELETE, CREATE, DROP  on  testdb.* to zabbix@'10.0.0.%'; 
    grant update on testdb.t1 to zabbix@'10.0.0.%';
    问:zabbix@'10.0.0.%'对T1表到底有什么权限?
    结论,如果对某个用户在不同数据库级别都设置了权限,最终权限相叠加,及加起来的最大权限为准。

    建议:尽量不要多范围授权。

最后修改于 2019-08-02 14:17:34
如果觉得我的文章对你有用,请随意赞赏
扫一扫支付
上一篇