MySQL DBA 高级运维学习之路-创建mysql用户及授权的多种方法实战

妖狐艹你老母 2022-01-12 17:27 129阅读 0赞

# 1.先了解一下grant命令的用法 #

mysql> help grant;
    ……省略部分……
    CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
    GRANT ALL ON db1.* TO 'jeffrey'@'localhost';
    GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost';
    GRANT USAGE ON *.* TO 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;
    ……省略部分……

比较常用的授权方法是使用grant命令在创建用户的同时进行权限授权语法如下：

GRANT ALL ON db1.* TO 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';

上述grant命令帮助里还提供了一个先用create命令创建用户，然后再用grant授权的方法(即创建用户和授权权限分开进行)，语法如下所示：

CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
    GRANT ALL ON db1.* TO 'jeffrey'@'localhost';

# 2.通过grant命令创建用户并授权 #

## 2.1 方法一：一起创建账户并授权 ##

(1)语法如下:privileges(特权的意思)也可以不写

grant all privileges on dbname.* to 'username'@'localhost' identified by 'mypass';

(2)列表说明:

![MySQL DBA 高级运维学习之路-创建mysql用户及授权的多种方法实战][MySQL DBA _-_mysql]

(3)案例:创建账户zhangsan，对test库的所有表具备所有权限，并且允许从localhost主机登录管理数据库，密码是zhangsan123。

实战操作：

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

然后查看账户zhangsan的具体权限

mysql> show grants for 'zhangsan'@'localhost';
     Grants for zhangsan@localhost   
    GRANT USAGE ON *.* TO 'zhangsan'@'localhost' IDENTIFIED BY PASSWORD '*7E72D61D7B957897AA8ECED9A9397B649BE3B546' |
     GRANT ALL PRIVILEGES ON `test`.* TO 'zhangsan'@'localhost'  
    2 rows in set (0.00 sec)

## 2.2方法二：create和grant配合法 ##

(1)语法如下

CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
    GRANT ALL ON db1.* TO 'jeffrey'@'localhost';

(2)案例:创建账户lisi，对test库的所有表具备所有权限，并且允许从localhost主机登录管理数据库，密码是lisi123。

首先创建账户username，创建lisi账户后查看一下权限，默认是USAGE，及连接的权限，因为此时还没有权限。

mysql> create user 'lisi'@'localhost' identified by 'lisi123';
    Query OK, 0 rows affected (0.01 sec)
    mysql> show grants for 'lisi'@'localhost';
    Grants for lisi@localhost   GRANT USAGE ON *.* TO 'lisi'@'localhost' IDENTIFIED BY PASSWORD '*686008E0BFD16925072B84AA099EB5BC8375C35B' 
    1 row in set (0.00 sec)

对test库的所有表具备所有权限，并且允许从localhost主机登录管理数据库

mysql> grant all on test.* to 'lisi'@'localhost';
    Query OK, 0 rows affected (0.00 sec)
    mysql> show grants for 'lisi'@'localhost';
    Grants for lisi@localhost   
    GRANT USAGE ON *.* TO 'lisi'@'localhost' IDENTIFIED BY PASSWORD '*686008E0BFD16925072B84AA099EB5BC8375C35B' 
    GRANT ALL PRIVILEGES ON `test`.* TO 'lisi'@'localhost'

提示：可以看到默认权限是usage即连接数据库的权限，后面又增加了ALL对数据库执行的所有权限。

## 2.3 授权局域网内主机远程连接数据库 ##

根据grant命令的语法我们知道，test@localhost位置为授权访问数据库的主机，localhost可以用域名、IP地址或者网段来替代，因此要授权局域网内主机可以通过如下方法来实现。

(1)百分号匹配法

system@ceshi 01:5945->grant all privileges on test.* to 'linzhongniao'@'192.168.1.%' identified by 'linzhongniao123';
    Query OK, 0 rows affected (0.01 sec)
    system@ceshi 01:5950->show grants for 'linzhongniao'@'192.168.1.%';
    Grants for linzhongniao@192.168.1.%   
    GRANT USAGE ON *.* TO 'linzhongniao'@'192.168.1.%' IDENTIFIED BY PASSWORD '*E2190B1F46FD9E171DD25B61138EA7F4F4D82B8C' 
    GRANT ALL PRIVILEGES ON `test`.* TO 'linzhongniao'@'192.168.1.%'  
    2 rows in set (0.00 sec)
    system@ceshi 02:0023->flush privileges;
    Query OK, 0 rows affected (0.00 sec)

(2)子网掩码配置法

system@ceshi 02:3013->grant all privileges on test.* to 'mm'@'192.168.1.0/255.255.255.0' identified by 'mm123';
    Query OK, 0 rows affected (0.01 sec)
    system@ceshi 02:3127->flush privileges;
    Query OK, 0 rows affected (0.00 sec)

通过客户端远程连接数据库：

1、本地连接数据库`mysql –uroot –plinzhongniao666`相当于`mysql –uroot –plinzhongniao666 –h localhost`

2、要远程连接ip为192.168.1.108主机的数据库命令为 `mysql -umm -pmm123 -h 192.168.1.108`

3、通过php服务器连接mysql服务器的代码写法为

<?php
            //$link_id=mysql_connect('主机名','用户','密码')；
            $link_id=mysql_connect（'192.168.1.108','nn','nn123'）;
            if($link_id){
                    echo "mysql successful by nn";
            }else{
                echo mysql_error();
            }
    ?>

# 3.MySQL用户可以授权的权限有哪些? #

通过实验获得ALL PRIVILEGES包括哪些权限，取消权限的命令是revoke

## 3.1先查看有哪些账户 ##

system@ceshi 03:3751->select user,host from mysql.user;
     +--------+---------------------------+
    | user   | host  |
     +--------+---------------------------+
    | linzhongniao| 192.168.1.%   |
    | mm| 192.168.1.0/255.255.255.0 |
    | nn | localhost |
    | system | localhost |
     +--------+---------------------------+

## 3.2查看账户nn的权限 ##

现在查看nn账户的权限还是拥有所有权限(ALL PRIVILEGES)，给所有权限是很不安全的因此要给账户指定的权限来防止数据库数据的缺失。

mysql> show grants for 'nn'@'localhost'\G
     *************************** 1. row ***************************
    Grants for nn@localhost: GRANT USAGE ON *.* TO 'nn'@'localhost' IDENTIFIED BY PASSWORD '*C326C4F2C1C1CC2A59758F1E2A36FA4EE025D28B'
     *************************** 2. row ***************************
    Grants for nn@localhost: GRANT ALL PRIVILEGES ON `test`.* TO 'nn'@'localhost'
    2 rows in set (0.00 sec)

## 3.3取消账户nn的插入数据的权限(INSERT) ##

取消权限的命令是revoke，revoke在sql语言介绍那节已经提到过了，意思是取消授权。

(1)先看一下帮助，帮助里面提供了语法，

system@ceshi 03:4001->help revoke
    
    ……省略………
    REVOKE INSERT ON *.* FROM 'jeffrey'@'localhost';
    ……省略……

(2)取消账户的只读权限可以将账户nn的所有权限(ALL PRIVILEGES)细分。

mysql> REVOKE INSERT ON test.* FROM 'nn'@'localhost';
    Query OK, 0 rows affected (0.00 sec)
    mysql> flush privileges;
    Query OK, 0 rows affected (0.00 sec)

(3)再查看一下用户nn的权限就已经被细分了

mysql> show grants for 'nn'@'localhost'\G
     *************************** 1. row ***************************
    Grants for nn@localhost: GRANT USAGE ON *.* TO 'nn'@'localhost' IDENTIFIED BY PASSWORD '*C326C4F2C1C1CC2A59758F1E2A36FA4EE025D28B'
     *************************** 2. row ***************************
    Grants for nn@localhost: GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `test`.* TO 'nn'@'localhost'
    2 rows in set (0.00 sec)

此时nn用户的权限，ALL PRIVILEGES权限已经被细分了。按照下面的步骤我们可以更加清楚的知道ALL PRIVILEGES的权限包括哪些内容。

(1)我们用-e参数不登录mysql数据库直接查看用户nn有哪些权限

[root@linzhongniao ~]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e "show grants for 'nn'@'localhost'\G"|grep -i "grant"|tail -1
    Grants for nn@localhost: GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `test`.* TO 'nn'@'localhost'

(2)查看用户nn有哪些权限之后，将内容追加到txt文件中。

[root@linzhongniao ~]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e "show grants for 'nn'@'localhost'\G"|grep -i "grant"|tail -1|sed -nr 's#^.*GRANT (.*) `test`.*$#\1#gp'|tr ',' '\n'>>all.txt

(3)下面内容就是用户nn所具有的所有权限

没有插入INSERT权限刚才取消授权了

[root@linzhongniao ~]# cat all.txt 
    SELECT
     UPDATE
     DELETE
     CREATE
     DROP
     REFERENCES
     INDEX
     ALTER
     CREATE TEMPORARY TABLES
     LOCK TABLES
     EXECUTE
     CREATE VIEW
     SHOW VIEW
     CREATE ROUTINE
     ALTER ROUTINE
     EVENT
     TRIGGER ON

注意：在授权时可以授权用户最小的满足业务的权限，而不是一味的授权“ALL PRIVILEGES”

总结：所有权限(all privileges)包括上面的所有权限，所有给账户授权的时候可以指定要授权的权限。命令如下所示：多个权限之间用逗号隔开

grant SELECT,UPDATE,DELETE,CREATE on dbname.* to 'username'@'localhost' identified by 'mypass';

# 4.企业环境授权用户权限 #

(1)博客，CMS等产品的数据库授权

对于web连接用户授权尽量采用最小化规则，很多开源软件都是web界面安装，因此在安装期间除了select、insert、update和delete4个权限外，还需要create和drop等比较危险的权限。

system@ceshi 04:5606->grant select,insert,update,delete,create,drop on blog.* to 'blog'@'192.168.1.%' identified by '1b23456';
    Query OK, 0 rows affected (0.00 sec)
    system@ceshi 04:5907->flush privileges;
    Query OK, 0 rows affected (0.00 sec)

常规情况下授权select、insert、update、delete4个权限即可，有的开源软件，列如discuzbbs，还需要create,drop等比较危险的权限。

(2)生成数据库表之后，要收回create、drop授权

system@ceshi 04:5925->help revoke
    REVOKE ALL PRIVILEGES, GRANT OPTION
    FROM user [, user] ...
    REVOKE INSERT ON *.* FROM 'jeffrey'@'localhost';
    2    rows in set (0.01 sec)
    system@ceshi 05:1327->REVOKE CREATE,DROP ON blog.* FROM 'blog'@'192.168.1.%';
    Query OK, 0 rows affected (0.00 sec)
    
    system@ceshi 05:1452->flush privileges;
    Query OK, 0 rows affected (0.00 sec)
    system@ceshi 05:1543->show grants for 'blog'@'192.168.1.%'\G;
     *************************** 1. row ***************************
    Grants for blog@192.168.1.%: GRANT USAGE ON *.* TO 'blog'@'192.168.1.%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'
     *************************** 2. row ***************************
    Grants for blog@192.168.1.%: GRANT SELECT, INSERT, UPDATE, DELETE ON `blog`.* TO 'blog'@'192.168.1.%'
    2 rows in set (0.00 sec)

转载于:https://blog.51cto.com/10642812/2065696

[MySQL DBA _-_mysql]: https://i.imgur.com/qVuIZW8.png