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

运维人员常用的方法：

mysql> grant all  on xiaohu.* to 'qiqi@localhost' identified by '123.com';

                     库.表

Query OK, 0 rows affected (0.01 sec)


grant语法

grant all privileges on dbname.* to user@localhost identified by 'password'


比如创建boy的用户，对qiqi库具备所有权限，允许从localhost主机登录管理数据库，密码是123.com

mysql> grant all   privileges on qiqi.* to boy@localhost identified by '123.com';

Query OK, 0 rows affected (0.00 sec)


mysql> flush privileges; 刷新权限

Query OK, 0 rows affected (0.00 sec)


mysql> select user,host from mysql.user;

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

| user           | host            |

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

| qiqi@localhost | %               |

| root           | 127.0.0.1       |

|                | localhost       |

| boy            | localhost       |

| root           | localhost       |

|                | master.test.com |

| root           | master.test.com |

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

7 rows in set (0.00 sec)



mysql> show grants for boy@localhost; 查看权限

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

| Grants for boy@localhost                                                                                   |

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

| GRANT USAGE ON *.* TO 'boy'@'localhost' IDENTIFIED BY PASSWORD '*AC241830FFDDC8943AB31CBD47D758E79F7953EA' |

| GRANT ALL PRIVILEGES ON `qiqi`.* TO 'boy'@'localhost'    对qiqi库有创建权限                                                  |

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

2 rows in set (0.00 sec)



MYSQL用户可以授权的权限：


上面提到了boy有all的权限 怎么收回？

revoke 收回权限 语法

revoke instrt on *.* from 'boy'@'localhost';


mysql> revoke insert on qiqi.* 注意一定要看看给哪个库收权限  from 'boy'@'localhost';

Query OK, 0 rows affected (0.00 sec)


mysql> show grants for boy@localhost;

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

| Grants for boy@localhost                                                                                                                                                                                                    |

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

| GRANT USAGE ON *.* TO 'boy'@'localhost' IDENTIFIED BY PASSWORD '*AC241830FFDDC8943AB31CBD47D758E79F7953EA'                                                                                                                  |

| 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 `qiqi`.* TO 'boy'@'localhost' |

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

上面是GRANT 的权限

2 rows in set (0.00 sec)



企业生产环境是如何授权的？

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

如web连接用户尽量采用最小化原则，如select（查询）insert（插入）update（修改）delete（删除）还需要create，drop的权限

生成数据表后一定要权限收回

revoke  insert on 　*.*  from  ‘boy‘@localhost’