mysql 數據庫 安全

我們開始之前的一些基本信息： (Some basic information before we get started:)

Source: Center for Internet Security’s (CIS) Oracle MySQL Community Server 5.7

來源： 互聯網安全中心(CIS)Oracle MySQL Community Server 5.7

Operating system: Windows 10

作業系統： Windows 10

Where to execute: command line

執行位置：命令行

mysql -u USERNAME -p

Target application: Oracle MySQL Community Server 5.7

目標應用程序： Oracle MySQL Community Server 5.7

信息系統的審核和記錄 (Auditing and logging for information systems)

Logs play a crucial role for security when there is a suspected cyberattack. A manual review of logs is painstaking for security personnel, and they must use log review tools to extract information and analyze it. Logs should use a WORM (write once read many) storage technology and encryption to avoid corruption and losing log data. Also, logs should have a standardized format for ease of maintenance, access and comparison.

當懷疑有網絡攻擊時，日志對于安全起著至關重要的作用。 手動查看日志對于安全人員來說是艱辛的，他們必須使用日志查看工具來提取信息并進行分析。 日志應使用WORM(一次寫入多次讀取)存儲技術和加密功能，以避免損壞和丟失日志數據。 此外，日志應具有標準化的格式，以便于維護，訪問和比較。

確保“ log_error”不為空 (Ensure “log_error” is not empty)

command:

命令：

SHOW variables LIKE ‘log_error’;

Error logs contains data on events when mysqld starts or stops. It also shows when a table needs to be assessed or repaired. It must generate a “value”. The reason for enabling error logging is it helps increase the ability to detect malicious attempts against MySQL and other vital messages.

錯誤日志包含有關mysqld啟動或停止時事件的數據。 它還顯示何時需要評估或修復表。 它必須產生一個“值”。 啟用錯誤日志記錄的原因是它有助于提高檢測針對MySQL和其他重要消息的惡意嘗試的能力。

確保日志文件存儲在非系統分區上 (Ensure log files are stored on a non-system partition)

command:

命令：

SELECT @@global.log_bin_basename;

Log files of MySQL can be stored anywhere in the filesystem and set using the MySQL configuration. Also, it is a best practice is to ensure that the logs in the filesystem are not cluttered with other logs such as application logs. You must ensure that the value returned does not indicate that it is in the root “(‘/’)”, “/var”, or “/usr”. The reason for this is that partitioning will decrease the likelihood of denial of service if the available disk space to the operating system is depleted.

MySQL的日志文件可以存儲在文件系統中的任何位置，并可以使用MySQL配置進行設置。 另外，最佳做法是確保文件系統中的日志不會與其他日志(例如應用程序日志)雜亂無章。 您必須確保返回的值不表示它位于根目錄“('/')”，“ / var”或“ / usr”中。 這樣做的原因是，如果操作系統的可用磁盤空間已用完，分區將減少拒絕服務的可能性。

確保“ log_error_verbosity”未設置為“ 1” (Ensure “log_error_verbosity” is not set to “1”)

command:

命令：

SHOW GLOBAL VARIABLES LIKE ‘log_error_verbosity’;

This check provides additional information to what functionalities the MySQL log has or has enabled on error messages. A value of 1 enables the logging of error messages. A value of 2 enables both the logging of error and warning messages. A value of 3 enables logging of error, warning, and note messages. This helps detect malicious behavior by logging communication errors and aborted connections.

此項檢查提供了有關MySQL日志對錯誤消息具有或已啟用的功能的更多信息。 值1啟用錯誤消息的記錄。 值2啟用錯誤和警告消息的記錄。 值3允許記錄錯誤，警告和注釋消息。 這有助于通過記錄通信錯誤和中止連接來檢測惡意行為。

確保啟用審核日志記錄 (Ensure audit logging is enabled)

Enabling audit logging is crucial for production environment for interactive user sessions and application sessions. With audit logging, it helps identify who changed what and when. It can also help to identify what an attacker has done and can even be used as evidence in investigations.

啟用審核日志記錄對于交互式用戶會話和應用程序會話的生產環境至關重要。 使用審核日志記錄，它可以幫助確定誰更改了內容和時間。 它還可以幫助識別攻擊者的所作所為，甚至可以用作調查中的證據。

command:

命令：

SELECT NAME FROM performance_schema.setup_instruments WHERE NAME LIKE ‘%/alog/%’;

command:

命令：

SET GLOBAL general_log = ‘ON’ ;

command: CREATE USER ‘user1’@’localhost’ IDENTIFIED BY PASSWORD ‘not-so-secret’;

命令： CREATE USER'user1'@'localhost'由密碼'not-so-secret'標識；

The log’s path in Windows 10 can be found by using Services application, looking to see if MySQL is running, and right-click properties.

可以使用服務應用程序找到Windows 10中的日志路徑，以查看MySQL是否正在運行，然后右鍵單擊屬性。

The log in the author’s system was located in: C:\ProgramData\MySQL\MySQL Server 5.7\Data\DJ-JASON-CLARK.log

作者系統中的日志位于：C：\ ProgramData \ MySQL \ MySQL Server 5.7 \ Data \ DJ-JASON-CLARK.log

信息系統認證 (Authentication for information system)

Authentication makes sure the credentials provided by the user or machine are matched with the database of authorized users in a local operating system or in an authentication server. Authentication is then followed by authorization, which is granted by an administrator to users or machines. An authentication that is commonly used in both private and public networks is password-based authentication.

身份驗證可確保用戶或計算機提供的憑據與本地操作系統或身份驗證服務器中的授權用戶數據庫匹配。 身份驗證之后是授權，授權由管理員授予用戶或計算機。 專用網絡和公用網絡中都普遍使用的身份驗證是基于密碼的身份驗證。

確保密碼未存儲在全局配置中 (Ensure passwords are not stored in the global configuration)

The [client] section of a MySQL configuration file allows the creation of a user and password to be set. The check is important because allowing a user and password in the configuration file impacts the confidentiality of the user’s password negatively.

MySQL配置文件的[client]部分允許設置用戶和密碼的創建。 該檢查很重要，因為在配置文件中允許用戶和密碼會負面影響用戶密碼的機密性。

To audit, open MySQL configuration file and examine the [client] section — it must not have any password stored. No password was set in the author’s system (see figure below). If a password was set in the configuration file, use mysql_config_editor to store passwords in the encrypted form in .mylogin.cnf.

要進行審計，請打開MySQL配置文件并檢查[client]部分-不得存儲任何密碼。 在作者的系統中未設置密碼(請參見下圖)。 如果在配置文件中設置了密碼，請使用mysql_config_editor將密碼以加密形式存儲在.mylogin.cnf中。

確保“ sql_mode”包含“ NO_AUTO_CREATE_USER” (Ensure ‘sql_mode’ contains ‘NO_AUTO_CREATE_USER’)

The “no_auto_create_user” is an option to prevent the auto creation of user when authentication information is not provided.

“ no_auto_create_user”是用于在未提供身份驗證信息時阻止自動創建用戶的選項。

command:

命令：

SELECT @@global.sql_mode;

command:

命令：

SELECT @@session.sql_mode;

確保為所有MySQL帳戶設置了密碼 (Ensure passwords are set for all MySQL accounts)

A user can create a blank password. Having a blank password is risky as anyone can just assume the user’s identity, enter the user’s loginID and connect to the server. This bypasses authentication, which is bad.

用戶可以創建一個空白密碼。 擁有空白密碼是有風險的，因為任何人都只能假設用戶的身份，輸入用戶的loginID并連接到服務器。 這樣會繞過身份驗證，這很不好。

command:

命令：

SELECT User,host FROM mysql.user WHERE authentication_string=’’;

確保“ default_password_lifetime”小于或等于“ 90” (Ensure ‘default_password_lifetime’ is less than or equal to ‘90’)

Changing the password lifetime to 90 days decreases the time available for the attacker to compromise the password, and thus decreases the likelihood of getting attacked.

將密碼有效期更改為90天可以減少攻擊者破解密碼的時間，從而減少遭受攻擊的可能性。

command:

命令：

SHOW VARIABLES LIKE ‘default_password_lifetime’;

command:

命令：

SET GLOBAL default_password_lifetime=90;

確保密碼復雜度到位 (Ensure password complexity is in place)

Password complexity adds security strength to authentications and includes adding or increasing length, case, numbers and special characters. The more complex the password, the harder for attackers to use brute force to obtain the password. Weak passwords are easily obtained in a password dictionary.

密碼復雜性增加了身份驗證的安全性，包括增加或增加長度，大小寫，數字和特殊字符。 密碼越復雜，攻擊者就越難使用蠻力來獲取密碼。 在密碼字典中很容易獲得弱密碼。

command:

命令：

SHOW VARIABLES LIKE ‘validate_password%’;

確保沒有用戶使用通配符主機名 (Ensure no users have wildcard hostnames)

Users with wildcard hostnames (%) are granted permission to any location. It is best to avoid creating wildcard hostnames. Instead, create users and give them specific locations from which a given user may connect to and interact with the database.

具有通配符主機名(％)的用戶被授予對任何位置的權限。 最好避免創建通配符主機名。 而是，創建用戶并為他們提供特定位置，給定用戶可以從該位置連接到數據庫并與數據庫進行交互。

command:

命令：

SELECT user, host FROM mysql.user WHERE host = ‘%’;

確保不存在匿名帳戶 (Ensure no anonymous accounts exist)

Users can have an anonymous (empty or blank) username. These anonymous usernames have no passwords and any other user can use that anonymous username to connect to the MySQL server. Removal of these anonymous accounts ensures only identified and trusted users can access the MySQL server.

用戶可以具有匿名(空或空白)用戶名。 這些匿名用戶名沒有密碼，任何其他用戶都可以使用該匿名用戶名連接到MySQL服務器。 刪除這些匿名帳戶可確保只有已識別且受信任的用戶才能訪問MySQL服務器。

command:

命令：

SELECT user,host FROM mysql.user WHERE user = ‘’;

與MySQL服務器的網絡連接 (Network connection to MySQL server)

The network connection plays an important role for communication between the user and the MySQL server. Insecure network connections are very vulnerable to attacks. The following are checks for network connection security.

網絡連接對于用戶與MySQL服務器之間的通信起著重要作用。 不安全的網絡連接非常容易受到攻擊。 以下是檢查網絡連接安全性的方法。

確保將“ have_ssl”設置為“ YES” (Ensure ‘have_ssl’ is set to ‘YES’)

To avoid malicious attackers peeking inside your system, it is best to use SLL/TLS for all network traffic when using untrusted networks.

為避免惡意攻擊者偷窺系統內部，使用不受信任的網絡時，最好對所有網絡流量使用SLL / TLS。

command:

命令：

WHERE variable_name = ‘have_ssl’;

確保所有遠程用戶的“ ssl_type”設置為“ ANY”，“ X509”或“ SPECIFIED” (Ensure ‘ssl_type’ is set to ‘ANY’, ‘X509’, or ‘SPECIFIED’ for all remote users)

SSL/TLS should be configured per user. This further prevents eavesdropping of malicious attackers.

應該為每個用戶配置SSL / TLS。 這進一步防止了對惡意攻擊者的竊聽。

command:

命令：

SELECT user, host, ssl_type FROM mysql.user WHERE NOT HOST IN (‘::1’, ‘127.0.0.1’, ‘localhost’);

復寫 (Replication)

Checking for replication status lets you monitor performance and security vulnerabilities. Microsoft SQL Server Management Studio has the following tools to monitor replication:

通過檢查復制狀態，可以監視性能和安全漏洞。 Microsoft SQL Server Management Studio具有以下工具來監視復制：

1. view snapshot agent status,
   查看快照代理狀態，
2. view log reader agent status, and
   查看日志閱讀器代理狀態，以及
3. view synchronization status.
   查看同步狀態。

確保復制流量安全 (Ensure replication traffic is secured)

Replication traffic between servers must be secured. During replication transfers, passwords could leak.

復寫 服務器之間的流量必須得到保護。 在復制傳輸期間，密碼可能會泄漏。

To audit, check if they’re using: a private network, a VPN, SSL/TLS or a SSH Tunnel. Hopefully the author’s system is using a private network. Correct if otherwise, and secure by using the private network, a VPN, SSL/TLS or a SSH Tunnel.

要審核，請檢查它們是否在使用：專用網絡，VPN，SSL / TLS或SSH隧道。 希望作者的系統正在使用專用網絡。 否則請更正，并通過使用專用網絡，VPN，SSL / TLS或SSH隧道來確保安全。

確保將'MASTER_SSL_VERIFY_SERVER_CERT'設置為'YES'或'1' (Ensure ‘MASTER_SSL_VERIFY_SERVER_CERT’ Is Set to ‘YES’ or ‘1’)

‘MASTER_SSL_VERIFY_SERVER_CERT’ checks whether the replica should verify the primary's certificate or not. The replica should verify the primary's certificate to authenticate the primary before continuing the connection.

'MASTER_SSL_VERIFY_SERVER_CERT'檢查副本是否應驗證主服務器的證書。 在繼續連接之前，副本服務器應驗證主服務器的證書以對主服務器進行身份驗證。

command:

命令：

SELECT ssl_verify_server_cert FROM mysql.slave_master_info;

確保將“ master_info_repository”設置為“ TABLE” (Ensure ‘master_info_repository’ is set to ‘TABLE’)

The ‘master_info_repository’ determines where the replica logs the primary's status and connection information. The password is stored in the primary info repository that is a plain text file. Storing the password in the TABLE master_info is a safer.

“ master_info_repository”確定副本在哪里記錄主數據庫的狀態和連接信息。 密碼存儲在主要信息存儲庫中，該存儲庫是純文本文件。 將密碼存儲在表master_info中較為安全。

command:

命令：

SHOW GLOBAL VARIABLES LIKE ‘master_info_repository’;

確保復制用戶的“ super_priv”未設置為“ Y” (Ensure ‘super_priv’ is not set to ‘Y’ for replication users)

The “SUPER” privilege (‘super_priv’) located in the “mysql.user” table has functions like “CHANGE”, “MASTER TO”, “KILL”, “mysqladmin kill”, “PURGE BINARY LOGS”, “SET GLOBAL”, “mysqladmin debug”, and other logging controls. Giving a user the “SUPER” privilege allows the user to view and terminate currently executing SQL statements, even for password management. If the attacker exploits and gains the “SUPER” privilege, they can disable, alter, or destroy logging data.

的 位于“ mysql.user”表中的“ SUPER”特權(“ super_priv”)具有“ CHANGE”，“ MASTER TO”，“ KILL”，“ mysqladmin kill”，“ PURGE BINARY LOGS”，“ SET GLOBAL”， “ mysqladmin調試”和其他日志記錄控件。 授予用戶“ SUPER”特權，使用戶可以查看和終止當前正在執行SQL語句，甚至用于密碼管理。 如果攻擊者利用并獲得“超級”特權，則他們可以禁用，更改或破壞日志記錄數據。

command:

命令：

SELECT user, host FROM mysql.user WHERE user=’repl’ and Super_priv = ‘Y’;

確保沒有復制用戶具有通配符主機名 (Ensure no replication users have wildcard hostnames)

MySQL allows you to grant permissions to wildcard hostnames. Wildcard hostnames should be avoided, and you should create or modify users and give them specific locations from which a given user may connect to and interact with the database.

MySQL允許您授予通配符主機名的權限。 應該避免使用通配符主機名，您應該創建或修改用戶，并為他們提供特定位置，給定用戶可以從該位置連接到數據庫并與數據庫進行交互。

結論 (Conclusion)

The following checks are made for a single work environment using MySQL as the information system on both the application-side and the user-side.

在使用MySQL作為應用程序端和用戶端的信息系統的單個工作環境中，進行了以下檢查。

The assessment is imperative to check for standard logging of MySQL and enabling additional logging functions (it also enables checking for authentication vulnerabilities). Network checks are important to prevent other users with malicious intent from peeking into your network. Always implement SSL/TLS to encrypt. Securing one-way transfer is necessary. Securing replication traffic adds a defensive layer.

必須進行評估，以檢查MySQL的標準日志記錄并啟用其他日志記錄功能(還可以檢查身份驗證漏洞)。 網絡檢查對于防止其他有惡意的用戶窺探您的網絡非常重要。 始終實施SSL / TLS進行加密。 確保單向傳輸是必要的。 保護復制流量會增加防御層。

The result of the assessment can inform you if the system is able to operate at a level of trust.

評估結果可以通知您系統是否能夠在信任級別上運行。

Thank you for reading my blog! You have now started the path to securing your MySQL database.=)

感謝您閱讀我的博客！ 現在，您已經開始保護MySQL數據庫的路徑。

翻譯自: https://www.freecodecamp.org/news/cjn-is-your-mysql-secured-7793e5444cf5/

mysql 數據庫 安全