创建MySQL用户及赋予用户权限

1、通过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;

……

2、运维人员比较常用的创建用户的方法是

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

3、先创建用户再授权

CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';

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

4、创建用户并授权

grant命令简单语法如下

grant all privileges on bdname.* to username@localhost identified by ‘passwd’;

表格说明：

授权用户solin对solin_utf8库所有权限：

mysql> grant all privileges on solin_utf8.* to 'solin'@'localhost' identified by'ubuntu';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

5、查看用户权限

mysql> show grants for solin@localhost;

+--------------------------------------------------------------------------------------------------------------+

| Grants for solin@localhost                                                                                   |

+--------------------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'solin'@'localhost' IDENTIFIED BY PASSWORD '*3CD53EE62F8F7439157DF288B55772A2CA36E60C' |

| GRANT ALL PRIVILEGES ON `solin_utf8`.* TO 'solin'@'localhost'                                                |

+--------------------------------------------------------------------------------------------------------------+

2 rows in set (0.00 sec)

6、create和grant配合法

①首先创建用户solin01及密码ubuntu，授权主机localhost

CREATE USER 'solin01'@'localhost' IDENTIFIED BY 'ubuntu';

②授权

mysql> create user solin01@localhost identified by 'ubuntu';

Query OK, 0 rows affected (0.00 sec)

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| solin_utf8         |

+--------------------+

4 rows in set (0.00 sec)

mysql> grant all on solin_utf8.* to 'solin01'@'localhost';

Query OK, 0 rows affected (0.00 sec)

7、授权局域网内主机远程连接

solin@localhost位置为授权访问数据库的主机，localhost可以用域名，IP地址或者IP段来替代。

①IP配置方式

[root@db-server ~]# mysql -uroot -pcentos  #登陆MySQL（-u用户，-p,密码）

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 3928

Server version: 5.1.73 Source distribution

Copyright (c) 2000, 2013, 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> create user test@'192.168.119.%' identified by 'test';      #创建新的数据库授权192.168.119.%段主机登陆

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;          #刷新

Query OK, 0 rows affected (0.00 sec)

mysql> \q

Bye

root@db-Client:~# mysql -utest -ptest -h 192.168.119.224    #远程连接数据库

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 3930

Server version: 5.1.73 Source distribution

Copyright (c) 2000, 2014, 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> \q             #退出数据库

Bye

②子网掩码配置方式

grant all on test@’192.168.119.224/255.255.255.0’ indentified by ‘test’;

flush privileges;

MySQL客户端连接异地数据库服务

本地：mysql -uroot -pcentos=mysql -uroot -pcentos -h localhost

远程：mysql -utest -ptest -h 192.168.119.224

通过php服务器连接mysql服务器的代码

<?php

      //$link_id=mysql_connect('主机名','用户','密码')；

      $link_id=mysql_connect('192.168.119.224','test''centos') or mysql_error();

      if($link_id){

           echo "mysql successful by oldboy !";

      }else{

           echo mysql_error();

      }

?>

8、MySQL用户的权限有哪些

权限表

MYSQL的权限如何分布，就是针对表可以设置什么权限，针对列可以设置什么权限等等，这个可以从官方文档中的一个表来说明：

企业生产环境环境如何授权用户权限

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

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

2、常规情况下授权select，insert，update，delete4个权限即可，

生成数据库表后，要收回create、drop权限

mysql> grant select,insert,update,delete,create,drop on test.* to 'solin'@'192.168.119.%' identified by'centos';

Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'solin'@'192.168.119.%';

+------------------------------------------------------------------------------------------------------------------+

| Grants for solin@192.168.119.%                                                                                   |

+------------------------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'solin'@'192.168.119.%' IDENTIFIED BY PASSWORD '*128977E278358FF80A246B5046F51043A2B1FCED' |

| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON `test`.* TO 'solin'@'192.168.119.%'                        |

+------------------------------------------------------------------------------------------------------------------+

2 rows in set (0.00 sec)

mysql> REVOKE create,drop ON test.* FROM 'solin'@'192.168.119.%';

Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'solin'@'192.168.119.%';

+------------------------------------------------------------------------------------------------------------------+

| Grants for solin@192.168.119.%                                                                                   |

+------------------------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'solin'@'192.168.119.%' IDENTIFIED BY PASSWORD '*128977E278358FF80A246B5046F51043A2B1FCED' |

| GRANT SELECT, INSERT, UPDATE, DELETE ON `test`.* TO 'solin'@'192.168.119.%'                                      |

+------------------------------------------------------------------------------------------------------------------+

2 rows in set (0.00 sec)

mysql> \q

Bye

（1）生产环境针对主库（写为主读为辅）用户的授权

普通环境：

本机:LNMP、LAMP环境数据库授权

GRANT all privileges ON ‘test.*’ TO ‘solin’@’localhost’ identified by ‘centos’;

应用服务器和数据库服务器不在一个主机上的授权

GRANT all privileges ON ‘test.*’ TO ‘solin’@’192.168.119.%’ identified by ’centos’;

严格的授权：重视安全，忽略了方便：

GRANT SELECT,INSERT,UPDATE,DELETE ON ‘test.*’ to ‘solin’@’192.168.119.%’ identified by ‘centos’;

（2）生产环境从库（只读）用户的授权：

CRANT SELECT ON ‘test.*’ TO ‘solin’@’192.168.119.%’ identified by ‘centos’;

（3）主从高级授权策略

第一种使用简单方法

第二种配置方法

主库授权的命令

GRANT SELECT,INSERT,UPDATE,DELETE ON ‘test.*’@’192.168.119.%’ identified by ‘centos’;

从库授权的命令

GRANT SELECT ON ‘test.*’ TO ‘solin’@’192.168.119.%’ identified by ‘centos’;

      当从库除了做select的授权外，还可以加read-only等只读参数，严格web用户写从库

（4）问题：

就是主从库的mysql库和表示同步的，无法针对同一个用户授权不同的权限，因为主从库授权后会自动同步到从库上，导致从库的授权只读失败

解决办法：

a.取消mysql库的同步

b.授权主库权限后，从库执行回收增删改权限

c.不在授权上控制增删改，而是用read-only参数，控制普通用户更新，注意，read-only参数对超级用户无效。