MySQL权限系统(一).The MySQL Access Privilege System 概述

2019-09-27 17:44 来源:未知

 

纯属个人阅读,如有翻译错误,请指出

Preface

The primary function of the MySQL privilege system is to authenticate a user who connects from a given host and to associate that user with privileges on a database such as SELECTINSERT,UPDATE, and DELETE. Additional functionality includes the ability to have anonymous users and to grant privileges for MySQL-specific functions such as LOAD DATA INFILE and administrative operations.

 

 

    I supposed we are encountering a situation that there's an anonymous user has connected in our MySQL database with an account which has large privileges.The user is doing some query operations with bad performance.Which may subsequently lead to a high load of our database server.How to solve this issue efficiently and immediately?There's a little trick we can use below.

MySQL特权系统的主要功能是对从给定主机连接的用户进行身份验证,并将该用户与数据库的特权(如SELECT,INSERT,UPDATE和DELETE)相关联。 其他功能包括允许匿名用户和授予MySQL特定功能
(如LOAD DATA INFILE和管理操作)的权限。

 

 

Example

 

 

There are some things that you cannot do with the MySQL privilege system:

Create a test account.

  • You cannot explicitly specify that a given user should be denied access. That is, you cannot explicitly match a user and then refuse the connection.

  • You cannot specify that a user has privileges to create or drop tables in a database but not to create or drop the database itself.

  • A password applies globally to an account. You cannot associate a password with a specific object such as a database, table, or routine.

    在些事情你用MySQL的权限系统是处理不到的:

    •您不能明确指定应拒绝给定用户访问。 也就是说,您不能明确的匹配用户,然后拒绝连接。

    •您不能指定用户具有在数据库中创建或删除表的权限,但不能指定创建或删除数据库本身的权限。

    •全局适用于帐户的密码。 您不能将密码与特定对象(如数据库,表或例程)关联。

 1 (root@localhost mysql3306.sock)[(none)]>create user aaron8219@'192.168.1.%' identified by 'zlm';
 2 Query OK, 0 rows affected (0.00 sec)
 3 
 4 (root@localhost mysql3306.sock)[(none)]>select user,host from mysql.user;
 5 +---------------+-------------+
 6 | user          | host        |
 7 +---------------+-------------+
 8 | rpl_mgr       | %           |
 9 | aaron8219     | 192.168.1.% |
10 | repl          | 192.168.1.% |
11 | replica       | 192.168.1.% |
12 | zlm           | 192.168.1.% |
13 | mysql.session | localhost   |
14 | mysql.sys     | localhost   |
15 | root          | localhost   |
16 +---------------+-------------+
17 8 rows in set (0.00 sec)
18 
19 (root@localhost mysql3306.sock)[(none)]>grant all privileges on *.* to aaron8219@'192.168.1.%'; //Grant the supreme privileges to the user.
20 Query OK, 0 rows affected (0.00 sec)
21 
22 (root@localhost mysql3306.sock)[(none)]>show grants for aaron8219@'192.168.1.%';
23 +----------------------------------------------------------+
24 | Grants for aaron8219@192.168.1.%                         |
25 +----------------------------------------------------------+
26 | GRANT ALL PRIVILEGES ON *.* TO 'aaron8219'@'192.168.1.%' |
27 +----------------------------------------------------------+
28 1 row in set (0.00 sec)

 

 

The user interface to the MySQL privilege system consists of SQL statements such as CREATE USERGRANT, and REVOKE. See Section 14.7.1, “Account Management Statements”.

**Connect to database with the new account.**

Internally, the server stores privilege information in the grant tables of the mysql database (that is, in the database named mysql). The MySQL server reads the contents of these tables into memory when it starts and bases access-control decisions on the in-memory copies of the grant tables.

 1 [root@zlm2 09:25:29 ~]
 2 #mysql -uaaron8219 -pzlm -h192.168.1.101
 3 mysql: [Warning] Using a password on the command line interface can be insecure.
 4 Welcome to the MySQL monitor.  Commands end with ; or g.
 5 Your MySQL connection id is 4
 6 Server version: 5.7.21-log MySQL Community Server (GPL)
 7 
 8 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
 9 
10 Oracle is a registered trademark of Oracle Corporation and/or its
11 affiliates. Other names may be trademarks of their respective
12 owners.
13 
14 Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
15 
16 (aaron8219@192.168.1.101 3306)[(none)]>show databases; //The user "aaron8219" can see all the databases in the current MySQL instance.
17 +--------------------+
18 | Database           |
19 +--------------------+
20 | information_schema |
21 | mysql              |
22 | performance_schema |
23 | sys                |
24 | sysbench           |
25 | zlm                |
26 +--------------------+
27 6 rows in set (0.01 sec)
28 
29 (aaron8219@192.168.1.101 3306)[(none)]>create database aaron8219;
30 Query OK, 1 row affected (0.00 sec)
31 
32 (aaron8219@192.168.1.101 3306)[(none)]>use aaron8219;
33 Database changed
34 (aaron8219@192.168.1.101 3306)[aaron8219]>create table t1(
35     -> id int,
36     -> name char(10)
37     -> ) engine=innodb;
38 Query OK, 0 rows affected (0.02 sec)
在内部,服务器将权限信息存储在mysql数据库(即名为mysql的数据库)的授权表中。 MySQL服务器在启动时将这些表的内容读取到内存中,并基于对授权表的内存中副本的访问控制决策。

 

 

**Create another precise account which name is equal to the one above and with an intact ip address.**

The MySQL privilege system ensures that all users may perform only the operations permitted to them. As a user, when you connect to a MySQL server, your identity is determined by the host from which you connect and the user name you specify. When you issue requests after connecting, the system grants privileges according to your identity and what you want to do.

TAG标签:
版权声明:本文由澳门新浦金网站发布于数据库,转载请注明出处:MySQL权限系统(一).The MySQL Access Privilege System 概述