Ape Creation Call for Papers | Authorized users can't query the table?


foreword

πŸ“« About the author: June Blizzard Fei Lihua, focusing on Java research, working as a back-end intermediate engineer in a technology company, good at back-end architecture design

πŸ”₯ Sanlian Support: If this article is not bad, please ❀️ Follow, πŸ‘ Like, πŸ‘‰ Favorite Sanlian, and support the blogger~

1 Background overview

1.1 Authorized users cannot query the table?

It's like this. A few days ago, a colleague of mine needed to create a user and authorize several business tables to be used by colleagues in the partner group. Script creation, providing authorization. When it was used by a partner, the response from the partner group was that the query table could not be found, and a whole oolong was given. Since it was a project that was temporarily accepted in the middle of the road, I didn't know what my predecessor was messing with. After checking the logs, it was found that the MySQL audit was set up. Well, let's talk about it in a shallow way today, as a growth.

1.2 What is MySQL auditing? What specification to follow?

MySQL Enterprise Edition includes MySQL Enterprise Audit, implemented using a server plug-in. MySQL Enterprise Audit uses the open MySQL Audit API to enable standard, policy-based monitoring and logging of connection and query activity performed on specific MySQL servers. Designed to meet Oracle Audit Specifications, MySQL Enterprise Audit provides an out-of-the-box, easy-to-use auditing and compliance solution for applications subject to internal and external regulatory guidelines. Once installed, the audit plugin enables the MySQL server to generate log files containing audit records of server activity. Log content includes when clients connect and disconnect, and what they do while connecting, such as the databases and tables they access.

For the specification of Oracle audit, you can refer to the official white paper document.

Audit overview: https://docs.oracle.com/cd/E26926_01/html/E25889/auditov-1.html#scrolltoc

Audit White Paper: https://www.oracle.com/cn/a/tech/docs/technical-resources/bwp-security-audit-vault.pdf

1.3 What problems does MySQL audit solve?

// to do

2 Local experimental environment

For MySQL Professional Edition (MySQL Enterprise Edition) audit, please refer to the official solution and operation steps: MySQL Enterprise Edition Audit , let's talk about MySQL Community (GPL) How is the community version self-built test environment audit set up? This article is an experiment under the GNU/Linux (CentOS 7 x86_64) environment.

MySQL auditing provides several ways to talk about it, and lists them one by one according to the steps of their own experimental operations, and provides them with reference for everyone. Comments are welcome, and I am very grateful. also hope MySQL Advanced Skill Tree Advanced can have this practice.

Three ways of MySQL audit:

  • General Query Log
  • BinLog + init_connect
  • Audit plugin

Let's talk about this general_log first.

2.1 general_log (not recommended)

By default, MySQL does not enable General Query Log; after enabling General Query Log, MySQL records all SQL statements that arrive at MySQL Server.
about MySQL logs , the official has given a clear explanation and description. From the literal meaning, there are usually the following logs:

log typename] log information
Error logerror logProblems starting, running, or stopping mysqld
General query logGeneral query logEstablished client connections and statements received from clients
Binary logbinary logStatements that change data (also used for replication)
Relay logrelay logData changes received from the replication source server
Slow query logslow query logQuery that takes longer than long_query_time to execute for a few seconds
DDL log (metadata log)DDL log (metadata log)Metadata operations performed by DDL statements

2.2.1 View general_log settings

Execute the SQL command to see that the default general_log is OFF, and the SQL keyword statements and attribute values ​​are case-insensitive (this can be set).

mysql> show variables like 'general_log';

2.2.2 View log files

After the General Query Log is opened, all SQL accesses will be recorded in the log file pointed to by general_log_file. Regarding the value of the global variable general_log_file and the location of the log file, you can execute the following command to get it.

mysql> show variables like 'general_log_file';


By default, the log file will be placed under your MySQL file, and the naming rule is machine name.log. For example, my mysql log is placed in /usr/local/mysql-8.0.18-macos10.14-x86_64/data/ (Usually, we call /usr/local/mysql-8.0.18-macos10.14-x86_64/ MySQ_HOME) directory, the machine name is Macbook, then the log file here is Macbook.log.

First, we can take a look at what is stored in this file.

Aion.Liu $ tail -200f /usr/local/mysql-8.0.18-macos10.14-x86_64/data/Macbook.log
tail: /usr/local/mysql-8.0.18-macos10.14-x86_64/data/Macbook.log: No such file or directory

No such file or directory This prompts us that there is no such file or directory? In fact, this also verifies the accuracy of our first step, which is turned off by default.

2.2.3 Manually open General Query Log

When executing mysql> set global general_log = on;, we set the global variable general_log to the open state, and check again that the general_log is open and is in the ON state.

When we opened it, we followed the steps of 2.2.2 to check the contents of the log file again. The surprise came, and there was text in it (I intercepted the part below).

Aion.Liu $ tail -200f /usr/local/mysql-8.0.18-macos10.14-x86_64/data/Macbook.log
										alert_notification.is_default,
										alert_notification.disable_resolve_message,
										alert_notification.send_reminder,
										alert_notification.frequency
										FROM alert_notification
	  							 WHERE alert_notification.org_id = 1 AND ((alert_notification.is_default = '1'))
2022-08-30T14:19:49.933094Z	 3678 Prepare	SELECT
										alert_notification.id,
										alert_notification.uid,
										alert_notification.uid
										FROM alert_notification
	  							 WHERE alert_notification.org_id = 1 AND alert_notification.id = 1
2022-08-30T14:20:35.933719Z	 3608 Close stmt
2022-08-30T14:20:35.933977Z	 3678 Prepare	SELECT `id`, `org_id`, `version`, `name`, `type`, `access`, `url`, `password`, `user`, `database`, `basic_auth`, `basic_auth_user`, `basic_auth_password`, `with_credentials`, `is_default`, `json_data`, `secure_json_data`, `read_only`, `uid`, `created`, `updated` FROM `data_source` WHERE `id`=? AND `org_id`=? LIMIT 1
2022-08-30T14:20:35.934139Z	 3678 Execute	SELECT `id`, `org_id`, `version`, `name`, `type`, `access`, `url`, `password`, `user`, `database`, `basic_auth`, `basic_auth_user`, `basic_auth_password`, `with_credentials`, `is_default`, `json_data`, `secure_json_data`, `read_only`, `uid`, `created`, `updated` FROM `data_source` WHERE `id`=4 AND `org_id`=1 LIMIT 1
2022-08-30T14:20:35.934361Z	 3678 Close stmt
2022-08-30T14:20:35.935332Z	 3608 Prepare	SELECT
										alert_notification.id,
										alert_notification.uid,
										alert_notification.org_id,
										alert_notification.name,
										alert_notification.type,
										alert_notification.created,
										alert_notification.updated,
										alert_notification.settings,
										alert_notification.secure_settings,
										alert_notification.is_default,
										alert_notification.disable_resolve_message,
										alert_notification.send_reminder,
										alert_notification.frequency
										FROM alert_notification
	  							 WHERE alert_notification.org_id = ? AND ((alert_notification.is_default = ?))
2022-08-30T14:20:35.935422Z	 3608 Execute	SELECT
										alert_notification.id,
										alert_notification.uid,
										alert_notification.org_id,
										alert_notification.name,
										alert_notification.type,
										alert_notification.created,
										alert_notification.updated,
										alert_notification.settings,
										alert_notification.secure_settings,
										alert_notification.is_default,
										alert_notification.disable_resolve_message,
										alert_notification.send_reminder,
										alert_notification.frequency										
										......

Check the size of the current log file, which is 324K. Here, after we open the log, the log will be written to the log file in real time.

Aion.Liu $ du -h /usr/local/mysql-8.0.18-macos10.14-x86_64/data/Macbook.log
324K	/usr/local/mysql-8.0.18-macos10.14-x86_64/data/Macbook.log

2.2.4 Experiment

1) We arbitrarily execute some statements in the command line tool window. Here I will query the number of table records in table1.

2) At this time, let's see how the contents of the log file are recorded

It's easy to understand, the query type and the content of the executed statement have been recorded in the log.

3) Use the client tool to execute SQL commands to query the two column data of the abc table in my local experimental database study.

4) At this time, we query the log files in the past.

Is it easy to understand, the operations performed on the server at this time will be recorded in the set log file Macbook.log.

5) We turn off the global log settings and try
Execute the command as follows

mysql> set global general_log = off;
Query OK, 0 rows affected (0.00 sec)

View log file contents

tail -200f /usr/local/mysql-8.0.18-macos10.14-x86_64/data/Macbook.log


Since I have enabled scheduling events and some triggers here, the SQL statement has been executed, so a lot of log information will be printed. When we close again, the log will no longer be printed, and the last line tells us that the log has been closed. Will all operations be recorded in the log? If you are interested, you can try it locally. At this point, there is no need for me to say more about the // to do left behind.

3 Summary

When the General Query Log is turned on, MySQL will record the log as long as the user performs the operation, regardless of right or wrong. In this case, the log volume will be very large, which will affect the efficiency of the database. Therefore, we generally do not recommend enabling the open function. In some cases, it may be temporarily opened for a period of time for troubleshooting.

Tags: Database MySQL Java

Posted by shareaweb on Wed, 31 Aug 2022 23:59:19 +0530