如何更好的管理MySQL权限

下面跟着笔者一起来了解下如何更好的管理MySQL权限，相信大家看完肯定会受益匪浅，文字在精不在多，希望如何更好的管理MySQL权限这篇短内容是你想要的。 

1# 查看权限
比如，我们想要看看MySQL的root用户，拥有什么权限：

(root@localhost)[(none)]> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

(root@localhost)[(none)]> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*A0F874BC7F54EE086FCE60A37CE7887D8B31086B' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

一条show grants解决了问题，并且连grant语句都给我们了。这样就是说，我们稍微修修改改就可用重造出另一个和root一样的超级用户了。

 或者使用：
show grants for 'xxx'@'xxxxx';

其中第一条：

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*A0F874BC7F54EE086FCE60A37CE7887D8B31086B' WITH GRANT OPTION 

不光光grant  了  .  上的ALL PRIVILEGES 给'root'@'localhost' ，甚至还有验证密码和 WITH 信息。实际上这条语句是可用拿来创建这个用户的。这也是一个另类的创建用户的方法。

查看他人的权限：

(root@localhost)[(none)]> show grants for test1
    -> ;
+------------------------------------------------------------------------------------------------------+
| Grants for test1@%                                                                                   |
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test1'@'%' IDENTIFIED BY PASSWORD '*CFA887C680E792C2DCF622D56FB809E3F8BE63CC' |
+------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

这里看到用户'test1'@'%'只有一条权限，这条权限也是默认的创建用户语句。

2# 授予权限：
用户必然是需要使用数据库的。所以如果用户只有usage这个没用的权限的话，这个用户就不需要存在了。
语法：

(root@localhost)[(none)]> help grant
Name: 'GRANT'
Description:
Syntax:
GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user_specification [, user_specification] ...
    [REQUIRE {NONE | tsl_option [[AND] tsl_option] ...}]
    [WITH {GRANT OPTION | resource_option} ...]

GRANT PROXY ON user_specification                                  #这个代理也是语句也是单独存在
    TO user_specification [, user_specification] ...
    [WITH GRANT OPTION]

object_type: {                                                                  #对象类型
    TABLE
  | FUNCTION
  | PROCEDURE
}

priv_level: {                                                               #权限的等级分类
    *
  | *.*
  | db_name.*
  | db_name.tbl_name
  | tbl_name
  | db_name.routine_name
}

user_specification:                                     #用户
    user [ auth_option ]

auth_option: {                                            #验证信息
    IDENTIFIED BY 'auth_string'
  | IDENTIFIED BY PASSWORD 'hash_string'
  | IDENTIFIED WITH auth_plugin
  | IDENTIFIED WITH auth_plugin AS 'hash_string'
}

tsl_option: {                                        #SSL类型
    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
}

用户权限列表，见官方文档：https://dev.mysql.com/doc/refman/5.6/en/grant.html

用户test1当前是没有任何权限的。假设我们需要让他访问mysql.user表

(test1@localhost)[(none)]> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
(test1@localhost)[mysql]> select count(*) from user;
+----------+
| count(*) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)

(test1@localhost)[mysql]>

建立一个新数据库，建立一个新表，对这个表进行访问以及控制:

(root@localhost)[mysql]> create database sample;
Query OK, 1 row affected (0.00 sec)

(root@localhost)[mysql]> use sample;
Database changed
(root@localhost)[sample]> show tables;
Empty set (0.00 sec)

(root@localhost)[sample]> create table smp (id int,name char(20));
Query OK, 0 rows affected (0.07 sec)

(root@localhost)[sample]> grant all privileges on sample.smp to test1;
Query OK, 0 rows affected (0.00 sec)

(root@localhost)[sample]> select User,Table_name,Table_priv from mysql.tables_priv;
+-------+------------+----------------------------------------------------------------------------------------------+
| User  | Table_name | Table_priv                                                                                   |
+-------+------------+----------------------------------------------------------------------------------------------+
| test1 | user       | Select                                                                                       |
| test1 | smp        | Select,Insert,Update,Delete,Create,Drop,References,Index,Alter,Create View,Show view,Trigger |
+-------+------------+----------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

(root@localhost)[sample]>

更多的怎删改和删除表

(test1@localhost)[sample]> insert into smp values (1,'abc');
Query OK, 1 row affected (0.00 sec)

(test1@localhost)[sample]> select * from smp;
+------+------+
| id   | name |
+------+------+
|    1 | abc  |
+------+------+
1 row in set (0.00 sec)

(test1@localhost)[sample]> delete from smp;
Query OK, 1 row affected (0.00 sec)

(test1@localhost)[sample]> select * from smp;
Empty set (0.00 sec)

(test1@localhost)[sample]> drep table smp;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'drep table smp' at line 1
(test1@localhost)[sample]> drop table smp;
Query OK, 0 rows affected (0.00 sec)

drop table 是DDL, 这个时候table已经删除了，再看看权限：

(root@localhost)[sample]>  select User,Table_name,Table_priv from mysql.tables_priv;
+-------+------------+----------------------------------------------------------------------------------------------+
| User  | Table_name | Table_priv                                                                                   |
+-------+------------+----------------------------------------------------------------------------------------------+
| test1 | user       | Select                                                                                       |
| test1 | smp        | Select,Insert,Update,Delete,Create,Drop,References,Index,Alter,Create View,Show view,Trigger |
+-------+------------+----------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

权限依然存在。说明删除表是不会删除用户所拥有的对象权限的。

试试看建回来：

(test1@localhost)[sample]>  create table smp (id int,name char(20));
Query OK, 0 rows affected (0.00 sec)

(test1@localhost)[sample]> create table smp1 (id int,name char(20));
ERROR 1142 (42000): CREATE command denied to user 'test1'@'localhost' for table 'smp1'
(test1@localhost)[sample]>

成功建回来。那么是否可用在这个数据库中建立另外一张表呢？不行。

对 sample. 层面授予权限。

(root@localhost)[sample]> grant create on sample.* to test1;
Query OK, 0 rows affected (0.00 sec)

(root@localhost)[sample]> select User,Table_name,Table_priv from mysql.tables_priv;
+-------+------------+----------------------------------------------------------------------------------------------+
| User  | Table_name | Table_priv                                                                                   |
+-------+------------+----------------------------------------------------------------------------------------------+
| test1 | user       | Select                                                                                       |
| test1 | smp        | Select,Insert,Update,Delete,Create,Drop,References,Index,Alter,Create View,Show view,Trigger |
+-------+------------+----------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

(root@localhost)[sample]> show grants for test1;
+------------------------------------------------------------------------------------------------------+
| Grants for test1@%                                                                                   |
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test1'@'%' IDENTIFIED BY PASSWORD '*CFA887C680E792C2DCF622D56FB809E3F8BE63CC' |
| GRANT CREATE ON `sample`.* TO 'test1'@'%'                                                            |
| GRANT ALL PRIVILEGES ON `sample`.`smp` TO 'test1'@'%'                                                |
| GRANT SELECT ON `mysql`.`user` TO 'test1'@'%'                                                        |
+------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

(root@localhost)[sample]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

这个时候，test1用户有了CREATE ON sample. 。尝试在数据库中创建表对象。

(test1@localhost)[sample]>  create table smp1 (id int,name char(20));
ERROR 1142 (42000): CREATE command denied to user 'test1'@'localhost' for table 'smp1'

失败！很奇怪，已经有了权限，还是失败。尝试重新登陆

(test1@localhost)[sample]> exit
Bye
[mysql@mysql01 ~]$ mysql -utest1 -S /data/mysqldata/3306/mysql.sock -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 25
Server version: 5.6.31-log Source distribution

Copyright (c) 2000, 2016, 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.

(test1@localhost)[(none)]>
(test1@localhost)[(none)]>
(test1@localhost)[(none)]> create table smp1 (id int,name char(20));
ERROR 1046 (3D000): No database selected
(test1@localhost)[(none)]> use sample;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
(test1@localhost)[sample]> create table smp1 (id int,name char(20));
Query OK, 0 rows affected (0.01 sec)

重新登陆后建立表对象成功。说明普通用户的权限需要在登陆的时候刷新。

另类方法查看MYSQL所有权限有哪些：

mysql> grant ALL PRIVILEGES ON *.* to test@'localhost' IDENTIFIED BY 'oldboy123' with grant option;
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                      |
+------------+---------------------------+
| mysql_user | %                         |
| root       | 127.0.0.1                 |
| rep        | 192.168.1.%               |
| alrin      | 192.168.1.0/24            |
| alrin      | 192.168.1.0/255.255.255.0 |
| root       | localhost                 |
| test       | localhost                 |
+------------+---------------------------+
7 rows in set (0.00 sec)
mysql> show grants for test@localhost
    -> ;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for test@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'test'@'localhost' IDENTIFIED BY PASSWORD '*FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> revoke insert on *.* from  'test'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for  'test'@'localhost';
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for test@localhost                                                                                                                                                                                                                                                                                                                                                                                                                   |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO 'test'@'localhost' IDENTIFIED BY PASSWORD '*FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515' WITH GRANT OPTION |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> exit
Bye

[root@mysql01 3307]# mysql -utest -poldboy123 -S /data/3307/mysql.sock -e "show grants for 'test'@'localhost'"| grep GRANT|tail -1| tr ',' '\n'  > mysql_all_privileges.txt
Warning: Using a password on the command line interface can be insecure.
[root@mysql01 3307]# mysql -utest -poldboy123 -S /data/3307/mysql.sock -e "show grants for 'test'@'localhost'"| grep GRANT|tail -1| tr ',' '\n'  > mysql_all_privileges.txt

[root@mysql01 3307]# cat mysql_all_privileges.txt 
GRANT SELECT
 UPDATE
 DELETE
 CREATE
 DROP
 RELOAD
 SHUTDOWN
 PROCESS
 FILE
 REFERENCES
 INDEX
 ALTER
 SHOW DATABASES
 SUPER
 CREATE TEMPORARY TABLES
 LOCK TABLES
 EXECUTE
 REPLICATION SLAVE
 REPLICATION CLIENT
 CREATE VIEW
 SHOW VIEW
 CREATE ROUTINE
 ALTER ROUTINE
 CREATE USER
 EVENT
 TRIGGER
 CREATE TABLESPACE ON *.* TO 'test'@'localhost' IDENTIFIED BY PASSWORD '*FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515' WITH GRANT OPTION
[root@mysql01 3307]# vi mysql_all_privileges.txt 
GRANT SELECT
 UPDATE
 DELETE
 INSERT
 CREATE
 DROP
 RELOAD
 SHUTDOWN
 PROCESS
 FILE
 REFERENCES
 INDEX
 ALTER
 SHOW DATABASES
 SUPER
 CREATE TEMPORARY TABLES
 LOCK TABLES
 EXECUTE
 REPLICATION SLAVE
 REPLICATION CLIENT
 CREATE VIEW
 SHOW VIEW
 CREATE ROUTINE
 ALTER ROUTINE
 CREATE USER
 EVENT
 TRIGGER
 CREATE TABLESPACE ON *.* TO 'test'@'localhost'  WITH GRANT OPTION
~
~
~
~
~
~
~
~
~
~
~
~
"mysql_all_privileges.txt" 28L, 370C written
[root@mysql01 3307]#
看完如何更好的管理MySQL权限这篇文章后，很多读者朋友肯定会想要了解更多的相关内容，如需获取更多的行业信息，可以关注我们的行业资讯栏目。