三、删除MySQL系统多余账号
语法：drop user “user”@“主机域” <----注意引号，可以是单或者双引号，但是不能不加。
四、创建MySQL用户及赋予用户权限
1、通过help查看grant命令帮助
1）通过在mysql中输入“help 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的命令帮助，可以很容易的找到创建用户并授权的例子！
2）运维人员比较常用的创建用户的方法是，使用grant命令在创建用户的同时进行权限授权。具体授权例子为：
grant all on db1.* to 'jeffrey'@'localhost' identified by 'mypass';
3）上述grant命令帮助里还提供了一个先用create命令创建用户，然后再用grant授权的方法，即创建用户和授权权限分开进行，例：
create user 'jeffrey'@'localhost' identified by 'mypass'; grant all on db1.* to 'jeffrey'@'localhost'; 以上俩条命令相当于下面一条命令： grant all on db1.* to 'jeffrey'@'loaclhost' identified by 'mypass';
2、通过grant命令创建用户并授权
1）grant命令简单语法如下：
grant all privilege on dbname.* username@'localhost' identified by 'passwd';
2）列表说明如下：
| grant | all privilege | on dbname.* | to username@localhost | identified by 'passwd' |
| 授权命令 | 对应权限 | 目标：库和表 | 用户名和客户端主机 | 用户密码 |
说明：上述命令是授权localhost主机上通过用户username管理dbname数据库的所有权限，密码为passwd。其中username，dbname，passwd可根据业务的情况修改。
3）操作案例1：创建oldboy用户，对test库具备所有权限，允许从localhost主机登陆管理数据库，密码是oldboy123。
mysql> select user,host from mysql.user; +------+-----------------------+ | user | host | +------+-----------------------+ | root | 127.0.0.1 | | root | ::1 | | | localhost | | root | localhost | | | localhost.localdomain | | root | localhost.localdomain | +------+-----------------------+ 6 rows in set (0.00 sec) mysql> grant all privileges on test.* to oldboy@'localhost' identified by 'oldboy123'; Query OK, 0 rows affected (0.28 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> select user,host from mysql.user; +--------+-----------------------+ | user | host | +--------+-----------------------+ | root | 127.0.0.1 | | root | ::1 | | | localhost | | oldboy | localhost | | root | localhost | | | localhost.localdomain | | root | localhost.localdomain | +--------+-----------------------+ 7 rows in set (0.00 sec)
查看授权用户oldboy具体权限
mysql> show grants for oldboy@localhost; +---------------------------------------------------------------------------------------------------------------+ | Grants for oldboy@localhost | +---------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'oldboy'@'localhost' IDENTIFIED BY PASSWORD '*FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515' | | GRANT ALL PRIVILEGES ON `test`.* TO 'oldboy'@'localhost' | +---------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
3、create和grant配合法
1）首先创建用户username及密码passwd，授权主机localhost
create user 'username'@'localhost' identified by 'passwd';
2）然后授权localhost主机上通过用户username管理dbname数据库的所有权限，无需密码。
grant all on dbname.* to 'username'@'loaclhost';
3）操作案例2：创建oldgirl用户，对test库具备所有权限，允许从localhost主机登陆管理数据库，密码是oldgirl123.
4）实战演示
查看当前数据库用户情况，然后执行对应命令创建用户如下：
mysql> create user oldgirl@localhost identified by 'oldgirl123'; Query OK, 0 rows affected (0.00 sec) mysql> show grants for oldgirl@localhost; +----------------------------------------------------------------------------------------------------------------+ | Grants for oldgirl@localhost | +----------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'oldgirl'@'localhost' IDENTIFIED BY PASSWORD '*2CADADD54086D5EB4C9F10E0430084D7F179885C' | +----------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> grant all on test.* to 'oldgirl'@'localhost'; Query OK, 0 rows affected (0.00 sec) mysql> show grants for oldgirl@localhost; +----------------------------------------------------------------------------------------------------------------+ | Grants for oldgirl@localhost | +----------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'oldgirl'@'localhost' IDENTIFIED BY PASSWORD '*2CADADD54086D5EB4C9F10E0430084D7F179885C' | | GRANT ALL PRIVILEGES ON `test`.* TO 'oldgirl'@'localhost' | +----------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
4、授权局域网内主机远程连接数据库：
根据grant命令语法，我们知道test@‘localhost’位置为授权访问数据库的主机，localhost可以用域名，IP地址或IP段来替代，因此，要授权局域网内主机可以通过如下发放实现：
a.百分号匹配法
mysql> create user test@'10.0.0.%' identified by 'test'; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> quit Bye [root@localhost ~]# mysql -utest -ptest -h 10.0.0.7 Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 8 Server version: 5.5.32 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>
b.子网掩码匹配法
mysql> create user test1@'10.0.0.0/255.255.255.0' identified by 'test1'; Query OK, 0 rows affected (0.00 sec) mysql> select user,host from mysql.user; +---------+------------------------+ | user | host | +---------+------------------------+ | test | 10.0.0.% | | test1 | 10.0.0.0/255.255.255.0 | | root | 127.0.0.1 | | root | ::1 | | | localhost | | oldboy | localhost | | oldgirl | localhost | | root | localhost | | | localhost.localdomain | | root | localhost.localdomain | +---------+------------------------+ 10 rows in set (0.00 sec) mysql> quit Bye [root@localhost ~]# mysql -utest1 -ptest1 -h 10.0.0.7 Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 10 Server version: 5.5.32 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>
通过mysql客户端连接异地数据库服务：
1）本地mysql -uroot -p‘oldboy123’ 连接数据库相当于 mysql -uroot -p‘oldboy123’ -h localhost
2）要远程连接10.0.0.7的数据库，命令为mysql -utest -p‘test’ -h 10.0.0.7，如果要能成功连接，还需要在10.0.0.7的数据库服务器上通过如下命令授权：
grant all on *.* to test@‘10.0.0.%’ identified by ‘test’；
3）通过php服务器连接mysql服务器的代码写法为；
<?php //$link_id=mysql_connect('主机名','用户','密码')； $link_id=mysql_connect('10.0.0.7','test','test') or mysql_error(); if($link_id){ echo "musql successful by oldboy!"; }else{ echo mysql_error(); } ?>
5、MySQL用户可以授权的权限有哪些
1）先看看前面授权过的oldboy的权限
mysql> show grants for oldboy@localhost;
+---------------------------------------------------------------------------------------------- -----------------+
| Grants for oldboy@localhost |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'oldboy'@'localhost' IDENTIFIED BY PASSWORD '*FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515' |
| GRANT ALL PRIVILEGES ON `test`.* TO 'oldboy'@'localhost' |
+---------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
2）取消oldboy的只读权限（SELECT）看看。
mysql> REVOKE INSERT ON test.* FROM 'oldboy'@'localhost'; Query OK, 0 rows affected (0.00 sec) mysql> show grants for oldboy@localhost; +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for oldboy@localhost | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'oldboy'@'localhost' IDENTIFIED BY PASSWORD '*FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515' | | 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 'oldboy'@'localhost' | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
提示：此时我们再查看oldboy用户权限，ALL PRIVILEGES 权限已经被细分了，但是没有select权限了。
因为，我们可以得出结论，ALL PRIVILEGES包括权限为：
[root@localhost ~]# mysql -uroot -pdubin -e "show grants for oldboy@localhost;"|grep -i grant |tail -1 |tr ',' ' ' >all.txt [root@localhost ~]# cat all.txt -n 1 SELECT 2 INSERT 3 UPDATE 4 DELETE 5 CREATE 6 DROP 7 REFERENCES 8 INDEX 9 ALTER 10 CREATE TEMPORARY TABLES 11 LOCK TABLES 12 EXECUTE 13 CREATE VIEW 14 SHOW VIEW 15 CREATE ROUTINE 16 ALTER ROUTINE 17 EVENT 18 TRIGGER
因此可以说，ALL PRIVILEGES的权限包括：
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 'oldboy'@'localhost'
即在授权时，可以授权用户最小的满足业务需求的权限，而不是一味的授权“ALL PRIVILEGES”。
6、企业生产环境如何授权用户权限？
1）博客，CMS等产品的数据库授权：
对于web连接用户授权尽量采用最小化原则，很多开源软件都是web界面安装，因此，在安装期间除了select，insert，update，delete4个权限外，还需要create，drop等比较危险的权限。
mysql> grant select,insert,update,delete,create,drop on blog.* to 'blog'@'10.0.0.%' identified by 'oldboy';
Query OK, 0 rows affected (0.00 sec)
常规情况下授权select，insert，update，delete4个权限即可，有的开源软件，例如discuzbbs，还需要create，drop等比较危险的权限
2）生成数据库表后，要收回create、drop授权：
mysql> grant select,insert,update,delete,create,drop on blog.* to 'blog'@'10.0.0.%' identified by 'oldboy'; Query OK, 0 rows affected (0.00 sec) mysql> revoke create on blog.* from 'blog'@'10.0.0.%'; Query OK, 0 rows affected (0.00 sec) mysql> show grants for blog@'10.0.0.%'; +------------------------------------------------------------------------------------------------------------+ | Grants for blog@10.0.0.% | +------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'blog'@'10.0.0.%' IDENTIFIED BY PASSWORD '*7495041D24E489A0096DCFA036B166446FDDD992' | | GRANT SELECT, INSERT, UPDATE, DELETE, DROP ON `blog`.* TO 'blog'@'10.0.0.%' | +------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)