10.1 使用语法:
通过在 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;
10.2 第一种创建用户及授权方法：
创建用户
mysql> create user oldboy@'localhost' identified by 'oldboy';
Query OK, 0 rows affected (0.00 sec)
查看用户其权限
mysql> show grants for oldboy@'localhost';+---------------------------------------------------------------------------------------------------------------+
| Grants foroldboy@localhost|
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'oldboy'@'localhost'IDENTIFIED BY PASSWORD'*7495041D24E489A0096DCFA036B166446FDDD992' |
+---------------------------------------------------------------------------------------------------------------+
1 row in set (0.00sec)
授权用户权限
mysql> grant all on oldboy_gbk.* to oldboy@'localhost';
Query OK, 0 rows affected (0.04sec)
mysql> show grants for oldboy@'localhost';+---------------------------------------------------------------------------------------------------------------+
| Grants foroldboy@localhost|
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'oldboy'@'localhost'IDENTIFIED BY PASSWORD'*7495041D24E489A0096DCFA036B166446FDDD992' |
| GRANT ALL PRIVILEGES ON `oldboy_gbk`.* TO 'oldboy'@'localhost'
|
+---------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
10.3 第二种创建用户及授权方法：
mysql> grant all on oldboy_gbk.* to oldgirl@'localhost' identified by 'oldgirl';
Query OK, 0 rows affected (0.00sec)
列表说明:
grant all on dbname.*to username@’lcoalhost’ identified by ‘password’
授 权命令对应权限
目标：库和表 用户名和客户端主机 用户密码
mysql> show grants for oldgirl@'localhost';+----------------------------------------------------------------------------------------------------------------+
| Grants foroldgirl@localhost|
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'oldgirl'@'localhost'IDENTIFIED BY PASSWORD'*4FD27385BB43242FE02158144D4C211F75A03F76' |
| GRANT ALL PRIVILEGES ON `oldboy_gbk`.* TO 'oldgirl'@'localhost'
|
+----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
10.4 创建用户及授权哪个网段的主机可以连接 oldboy_gbk 库
提示：如果是 web 连接数据库的用户，尽量不要授权 all，而是 select，insert，update，delete
10.4.1 第一种方法：
mysql> grant all on oldboy_gbk.* to oldgirl@'172.16.1.%' identified by 'oldgirl';
Query OK, 0 rows affected (0.00sec)%表示 172.16.1.1-255 网段
10.4.2 第二种方法：
mysql> grant all on oldboy_gbk.* to oldgirl@'172.16.1.0/255.255.255.0' identified by 'oldgirl';
Query OK, 0 rows affected (0.00sec)
提示:不能这样写 oldgirl@’172.16.1.0/24’
10.5 关于 mysql 回收某个用户权限
语法格式：
REVOKE
priv_type [(column_list)]
[, priv_type [(column_list)]] ...
ON [object_type] priv_level
FROM user [, user] ...
REVOKE ALL PRIVILEGES, GRANT OPTION
FROM user [, user] ...
实例:查看 oldboy 用户回收权限前的权限
mysql> show grants for oldboy@'localhost';+---------------------------------------------------------------------------------------------------------------+
| Grants foroldboy@localhost|
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'oldboy'@'localhost'IDENTIFIED BY PASSWORD'*7495041D24E489A0096DCFA036B166446FDDD992' |
| GRANT ALL PRIVILEGES ON `oldboy_gbk`.* TO 'oldboy'@'localhost'
|
+---------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
查看回收 oldboy 用户的 insert 权限之后的权限
mysql> REVOKE INSERT ON oldboy_gbk.* FROM 'oldboy'@'localhost';
Query OK, 0 rows affected (0.00sec)
mysql>flush privileges;
Query OK, 0 rows affected (0.00sec)
mysql> show grants for 'oldboy'@'localhost';+---------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------+
| Grants foroldboy@localhost|
+---------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'oldboy'@'localhost'IDENTIFIED BY PASSWORD'*7495041D24E489A0096DCFA036B166446FDDD992'
|
|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 `oldboy_gbk`.*TO'oldboy'@'localhost' |
+---------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
10.6 企业生产环境如何授权用户权限(mysql 主库)
博客，CMS 等产品的数据库授权：
对于 web 连接用户授权尽量采用最小化原则，很多开源软件都是 web 界面安装，因此，在安装期间除了 select，insert，update，delete4 个权限外，还需要 create，drop 等比较危险的权限
mysql> grant insert,delete,update,select on blog.* to blog@'172.16.1.%' identified by 'blog';
Query OK, 0 rows affected (0.00sec)
mysql> show grants for blog@'172.16.1.%';+--------------------------------------------------------------------------------------------------------------+
| Grants for blog@172.16.1.%
|
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'blog'@'172.16.1.%'IDENTIFIED BY PASSWORD'*A5BA49C964C6DB89302E2EA293048E9224B33F34' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `blog`.* TO 'blog'@'172.16.1.%'
|
+--------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
常规情况下授权 select，insert，update，delete4 个权限即可，有的开源软件，例如 discuz bbs，
还需要 create，drop 等比较危险的权限，生成数据库表后，要收回 create、drop 权限
mysql> revoke drop,create on blog.* from blog@'172.16.1.%';
Query OK, 0 rows affected (0.00sec)
to your MySQL server versionfor the right syntax to use near 'from blog@'172.16.1.%'' at line 1mysql> show grants for blog@'172.16.1.%';+--------------------------------------------------------------------------------------------------------------+
| Grants for blog@172.16.1.%
|
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'blog'@'172.16.1.%'IDENTIFIED BY PASSWORD'*A5BA49C964C6DB89302E2EA293048E9224B33F34' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `blog`.* TO 'blog'@'172.16.1.%'
|
+--------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
如果觉得《mysql 创建用户权限_10.创建 MySQL 用户及赋予用户权限》对你有帮助，请点赞、收藏，并留下你的观点哦！
