mysql用户权限

 

一）用户权限管理

• 先查看用户名密码和IP是否允许连接
• 库级权限 mysql.db
• 表级权限 tables_priv 
• 字段权限 columns_priv
• 管理权限  procs_priv

1）用户授权（grant）

mysql> help grant
Name: 'GRANT'
Description:
Syntax:
GRANT
priv_type [(column_list)]
[, priv_type [(column_list)]] ...
ON [object_type] priv_level
TO user [auth_option] [, user [auth_option]] ...
[REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
[WITH {GRANT OPTION | resource_option} ...]

GRANT PROXY ON user
TO user [, user] ...
[WITH GRANT OPTION]

object_type: {
TABLE
| FUNCTION
| PROCEDURE
}

priv_level: {
*
| *.*
| db_name.*
| db_name.tbl_name
| tbl_name
| db_name.routine_name
}

user:
(see http://dev.mysql.com/doc/refman/5.7/en/account-names.html)

auth_option: {
IDENTIFIED BY 'auth_string'
| IDENTIFIED WITH auth_plugin
| IDENTIFIED WITH auth_plugin BY 'auth_string'
| IDENTIFIED WITH auth_plugin AS 'hash_string'
| IDENTIFIED BY PASSWORD 'hash_string'
}

tls_option: {
SSL
| X509
| CIPHER 'cipher'
| ISSUER 'issuer'
| SUBJECT 'subject'
}

resource_option: {
| MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count
}

案例1：

mysql> create database wp;
Query OK, 1 row affected (0.13 sec)

mysql> grant select,insert,update,delete,create view on wp.* to 'dev1'@'10.2.13.%' identified by 'Aa123';
Query OK, 0 rows affected, 1 warning (0.08 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> grant select on wp.report to 'dev2'@'10.2.13.%' identified by 'Aa123 ' with MAX_USER_CONNECTIONS 1; (授权只对某个表有查询权限）

案例2：

创建一个junior组，然后把tom和jim用户加到这各junior组，并对这个junior组进行授权（以下操作只适合5.7版本）

mysql> create user 'junior_dba'@'localhost' identified by '123456';
Query OK, 0 rows affected (5.05 sec)

mysql> create user 'tom'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.01 sec)

mysql> create user 'jim'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.04 sec)

mysql> grant proxy on 'junior_dba'@'localhost' to 'tom'@'localhost';
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> grant proxy on 'junior_dba'@'localhost' to 'jim'@'localhost';
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> grant select on *.* to 'junior_dba'@'localhost';
Query OK, 0 rows affected, 1 warning (0.00 s查看

mysql> show grants for 'tom'@'127.0.0.1';
+--------------------------------------------------------------+
| Grants for tom@127.0.0.1 |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'tom'@'127.0.0.1' |
| GRANT PROXY ON 'junior'@'127.0.0.1' TO 'tom'@'127.0.0.1'

 

测试：

[root@mysqlmaster01 ~]# mysql -u tom -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.20-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, 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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.01 sec)

[root@mysqlmaster01 ~]# mysql -u junior_dba -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.20-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, 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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)

（通过以上操作，发现tom和jim只能看到| information_schema 数据库，其他的都看不到，而junior_dba可以看到所有的）

 

案例3： 查看授权情况

mysql> show grants for 'dev'@'10.2.13.%';
+----------------------------------------------------------------------------------+
| Grants for dev@10.2.13.% |
+----------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'dev'@'10.2.13.%' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE VIEW ON `wp`.* TO 'dev'@'10.2.13.%' |
+----------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

2）回收用户权限（revoke）

revoke回收用户权限，但是不删除用户

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] ...

REVOKE PROXY ON user
FROM user [, user] ...

 案例1：

mysql> revoke insert on wp.* from 'dev'@'10.2.13.%';
Query OK, 0 rows affected (0.01 sec)

mysql> show grants for 'dev'@'10.2.13.%';
+--------------------------------------------------------------------------+
| Grants for dev@10.2.13.% |
+--------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'dev'@'10.2.13.%' |
| GRANT SELECT, UPDATE, DELETE, CREATE VIEW ON `wp`.* TO 'dev'@'10.2.13.%' |
+--------------------------------------------------------------------------+
2 rows in set (0.00 sec)