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

发表评论

电子邮件地址不会被公开。 必填项已用*标注