创建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’;
表格说明：
| grant |
all privileges |
on dbname.* |
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用户的权限有哪些
权限表
| 权限 |
权限级别 |
权限说明 |
| CREATE |
数据库、表或索引 |
创建数据库、表或索引权限 |
| DROP |
数据库或表 |
删除数据库或表权限 |
| GRANT OPTION |
数据库、表或保存的程序 |
赋予权限选项 |
| REFERENCES |
数据库或表 |
|
| ALTER |
表 |
更改表，比如添加字段、索引等 |
| DELETE |
表 |
删除数据权限 |
| INDEX |
表 |
索引权限 |
| INSERT |
表 |
插入权限 |
| SELECT |
表 |
查询权限 |
| UPDATE |
表 |
更新权限 |
| CREATE VIEW |
视图 |
创建视图权限 |
| SHOW VIEW |
视图 |
查看视图权限 |
| ALTER ROUTINE |
存储过程 |
更改存储过程权限 |
| CREATE ROUTINE |
存储过程 |
创建存储过程权限 |
| EXECUTE |
存储过程 |
执行存储过程权限 |
| FILE |
服务器主机上的文件访问 |
文件访问权限 |
| CREATE TEMPORARY TABLES |
服务器管理 |
创建临时表权限 |
| LOCK TABLES |
服务器管理 |
锁表权限 |
| CREATE USER |
服务器管理 |
创建用户权限 |
| PROCESS |
服务器管理 |
查看进程权限 |
| RELOAD |
服务器管理 |
执行flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, flush-threads, refresh, reload等命令的权限 |
| REPLICATION CLIENT |
服务器管理 |
复制权限 |
| REPLICATION SLAVE |
服务器管理 |
复制权限 |
| SHOW DATABASES |
服务器管理 |
查看数据库权限 |
| SHUTDOWN |
服务器管理 |
关闭数据库权限 |
| SUPER |
服务器管理 |
执行kill线程权限 |
MYSQL的权限如何分布，就是针对表可以设置什么权限，针对列可以设置什么权限等等，这个可以从官方文档中的一个表来说明：
| 权限分布 |
可能的设置的权限 |
| 表权限 |
'Select', 'Insert', 'Update', 'Delete', 'Create', 'Drop', 'Grant', 'References', 'Index', 'Alter' |
| 列权限 |
'Select', 'Insert', 'Update', 'References' |
| 过程权限 |
'Execute', 'Alter Routine', 'Grant' |
企业生产环境环境如何授权用户权限
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）主从高级授权策略
第一种使用简单方法
| 写库 |
|||
| solin |
Ysolin456 |
3306 |
192.168.119.224 |
| 读库 |
|||
| solin |
Ysolin456 |
3306 |
192.168.119.225 |
第二种配置方法
| 写库 |
|||
| solin_w |
Ysolin456 |
3306 |
192.168.119.224 |
| 读库 |
|||
| solin_r |
Ysolin789 |
3306 |
192.168.119.225 |
主库授权的命令
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参数对超级用户无效。
