mysql创建用户并控制其对数据库、表、列的权限

创建一个用户realabc，步骤为进入mysql库，向user表中插数据，本地登陆，密码abc；flush privileges 使配置生效

mysql> use mysql;
mysql> insert into user (Host,User,Password) values ("localhost","realabc",PASSWORD("abc"));
mysql> flush privileges;

其实直接使用grant命令mysql也会帮我们创建用户

grant all privileges on *.* to 'yyy'@'localhost' identified by '123';

接下来想让realabc用户来插入、更新discuzx数据库里的pre_abc、pre_def表，那么肯定是用insert,update,当然还有select，要不数据都没法看到。先查看当前用户的权根：

mysql> show grants for realabc@localhost;
 +----------------------------------------------------------------------------------------------------------------+
 | Grants for realabc@localhost                                                                                   |
 +----------------------------------------------------------------------------------------------------------------+
 | GRANT USAGE ON *.* TO 'realabc'@'localhost' IDENTIFIED BY PASSWORD '*0D3CED9BEC10A777AEC23CCC353A8C08A633045E' |
 +----------------------------------------------------------------------------------------------------------------+
 1 row in set (0.00 sec)

接下来分两步输命令，提升权根

GRANT SELECT, INSERT, UPDATE ON `discuzx`.`pre_abc` TO 'realabc'@'localhost' ;
GRANT SELECT, INSERT, UPDATE ON `discuzx`.`pre_def` TO 'realabc'@'localhost' ;

如果权限加多了，可以使用revoke来回收权限，如

revoke select ON database1.* from 'yyy'@'localhost' ;

revoke ALL ON *.* from 'yyy'@'localhost' ;

如果为某数据库提升所有权限，使用

grant all on wordpress.* to wordpress@localhost;

这样查看权根，就会发现提升了，进phpmyadmin里就可以插数据了。

进了phpmyadmin发现还有一个information_schema，不想让phpmyadmin显示

找到phpmyadmin/libraries/config.default.php

在242行处加入information_schema

$cfg['Servers'][$i]['hide_db'] = 'information_schema';

Update: 2017-06-27
上述都是通过 grant 创建用户，在 mysql 5.7 会报 warning

+---------+------+------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                            |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------+

使用指导的方式创建 reporting 库和对应权限

CREATE DATABASE `reporting` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin */
create reporting@'%' identified by '';                                          
grant all privileges on reporting.* to reporting@'%';

:)EOT