Архив метки: MySQL

Шифрование в состоянии покоя в MariaDB

Неактивное шифрование предотвращает доступ злоумышленника к зашифрованным данным, хранящимся на диске, даже если у него есть доступ к системе. Базы данных с открытым исходным кодом MySQL и MariaDB теперь поддерживают функцию шифрования в состоянии покоя, которая отвечает требованиям нового законодательства ЕС о защите данных. Шифрование MySQL в состоянии покоя немного отличается от MariaDB, поскольку MySQL обеспечивает шифрование только для таблиц InnoDB. В то время как MariaDB также предоставляет возможность шифровать файлы, такие как журналы повторного выполнения, журналы медленных операций, журналы аудита, журналы ошибок и т. д. Однако оба они не могут зашифровать данные в ОЗУ и защитить их от вредоносного корня.

В этой статье мы научимся настраивать шифрование на уровне базы данных для MariaDB.

 

Начиная

Для шифрования данных в состоянии покоя требуется плагин шифрования вместе с управлением ключами. Плагин шифрования отвечает за управление ключом шифрования, а также за шифрование/дешифрование данных.

MariaDB предоставляет три решения для управления ключами шифрования, поэтому то, как ваши базы данных управляют ключом шифрования, зависит от используемого вами решения. В этом руководстве будет продемонстрировано шифрование на уровне базы данных с помощью решения MariaDB File Key Management. Однако этот плагин не поддерживает функцию ротации ключей.

Если вы используете сервер LAMP, файлы для добавления этого плагина находятся в каталоге «/opt/lamp». В противном случае изменения вносятся в папку «/etc/mysql/conf.d».

 

Создание ключей шифрования

Перед шифрованием базы данных с помощью плагина управления ключами файлов нам необходимо создать файлы, содержащие ключи шифрования. Мы создадим файл с двумя частями информации. Это ключ шифрования в шестнадцатеричном формате вместе с 32-битным идентификатором ключа.

Мы создадим новую папку «keys» в каталоге « /etc/mysql/» и воспользуемся утилитой OpenSSL для случайной генерации трех шестнадцатеричных строк и перенаправления вывода в новый файл в папке ключей. Введите следующие команды:

ubuntu@ubuntu:~$ sudo mkdir /etc/mysql/keys

ubuntu@ubuntu:~$ echo -n "1;"$openssl rand hex 32 > /etc/mysql/keys/enc_keys"

ubuntu@ubuntu:~$ echo -n "2;"$openssl rand hex 32 > /etc/mysql/keys/enc_keys"

ubuntu@ubuntu:~$ echo -n "3;"$openssl rand hex 32 > /etc/mysql/keys/enc_keys"

 

Где 1,2,3 — ключевые идентификаторы; мы включаем их, чтобы создать ссылку на ключи шифрования, используя переменную innodb_default_encryption_key_id в MariaDB. Выходной файл будет выглядеть так:

1;01495ba35e1c9602e14e40bd6de41bb8

2;3cffa4a5d288e90108394dbf639664f8

3;9953297ed1a58ae837486318840f5f1d

Шифрование ключевого файла

Мы можем легко установить системную переменную file_key_management_filename с соответствующим путем внутри плагина File Key Management. Но оставлять ключи в виде обычного текста небезопасно. Мы можем до некоторой степени снизить риск, назначив права доступа к файлам, но этого недостаточно.

Теперь мы зашифруем ранее созданные ключи, используя случайно сгенерированный пароль. Напротив, размер ключа может варьироваться от 128/192/256 бит.

ubuntu@ubuntu:~$ openssl rand -hex 192> /etc/mysql/keys/enc_paswd.key

 

Поэтому мы будем использовать OpenSSL ENC команду в терминале для шифрования enc_key.txt файл enc_key.enc, с помощью ключа шифрования , созданного выше. Кроме того, MariaDB поддерживает только режим CBC AES для шифрования ключей шифрования.

ubuntu@ubuntu:~$ openssl enc -aes-256-cbc -md sha1 -pass file:/etc/mysql/keys/enc_paswd.key -in /etc/mysql/keys/enc_key.txt -out /etc/mysql/keys/enc_key.enc && sudo rm /etc/mysql/keys/enc_key.txt

 

Мы также удаляем наш файл enc_keys.txt, поскольку он больше не нужен. Кроме того, мы всегда можем расшифровать наши данные в MariaDB, если наш файл паролей в безопасности.

 

Настройка плагина управления файловыми ключами

Теперь мы настроим MariaDB с помощью плагина File Key Management, добавив следующие переменные в файл конфигурации. Файлы конфигурации обычно находятся в ‘/etc/mysql’ и по умолчанию читают все файлы .cnf. Или вы можете создать новый файл конфигурации «mariadb_enc.cnf» в каталоге /etc/mysql/conf.d/.

Теперь ваш файл конфигурации может выглядеть совершенно иначе. Однако добавьте эти переменные шифрования в [sqld]. Если ключ зашифрован, плагину требуется настроить две системные переменные, то есть file_key_management_filename и file_key_management_filekey.

[sqld]



#File Key Management Plugin

plugin_load_add=file_key_management

file_key_management = ON file_key_management_encryption_algorithm=aes_cbc file_key_management_filename = /etc/mysql/keys/enc_keys.enc

file_key_management_filekey = /etc/mysql/keys/enc_paswd.key



# InnoDB/XtraDB Encryption Setup

innodb_default_encryption_key_id = 1

innodb_encrypt_tables = ON

innodb_encrypt_log = ON

innodb_encryption_threads = 4



# Aria Encryption Setup

aria_encrypt_tables = ON



# Temp & Log Encryption

encrypt-tmp-disk-tables = 1

encrypt-tmp-files = 1

encrypt_binlog = ON

 

Вы можете найти подробную информацию о каждой системной переменной на официальном сайте MariaDB.

 

Защита файла паролей

Мы изменим права доступа к каталогу MySQL, чтобы защитить пароль и другие конфиденциальные файлы. Право собственности на MariaDB будет изменено на текущего пользователя, которым в Ubuntu является mysql.

sudo chown -R mysql:root /etc/mysql/keys

sudo chmod 500 /etc/mysql/keys/

 

Теперь мы изменим пароль и права доступа к зашифрованным файлам на

sudo chown mysql:root /etc/mysql/keys/enc_paswd.key /etc/mysql/keys/enc_key.enc



sudo chmod 600 /etc/mysql/keys/enc_paswd.key /etc/mysql/keys/enc_key.enc

 

Теперь перезапустите службу базы данных.

sudo service mysql restart

Вывод

В этой статье мы узнали, как шифрование на уровне базы данных является актуальной задачей и как мы можем настроить шифрование в состоянии покоя в MariaDB. Единственным недостатком плагина File Key Management является то, что он не поддерживает ротацию ключей. Однако, помимо этого подключаемого модуля, существует множество других решений для шифрования управления ключами, например подключаемый модуль AWS Key Management и подключаемый модуль Eperi Key Management. Вы можете найти более подробную информацию об этих плагинах на официальном сайте MariaDB.



2021-02-25T04:45:11
MariaDB

MySQL Pivot: поворот строк в столбцы

Таблица базы данных может хранить различные типы данных, и иногда нам нужно преобразовать данные на уровне строк в данные на уровне столбцов. Эту проблему можно решить с помощью функции PIVOT(). Эта функция используется для поворота строк таблицы в значения столбцов. Но эта функция поддерживается очень немногими серверами баз данных, такими как Oracle или SQL Server. Если вы хотите выполнить ту же задачу в таблице базы данных MySQL, вам необходимо написать запрос SELECT, используя оператор CASE для поворота строк в столбцы. В статье показано, как выполнять задачу функции PIVOT() в связанных таблицах базы данных MySQL.

 

Предпосылка:

Вам необходимо создать базу данных и несколько связанных таблиц, в которых строки одной таблицы будут преобразованы в столбцы, такие как функция PIVOT(). Выполните следующие операторы SQL, чтобы создать базу данных с именем unidb и создать три таблицы с именами ‘students’, ‘courses’ и ‘result’. студенты и таблицы результатов будут связаны отношением «один ко многим», а таблицы курсов и результатов будут связаны здесь отношением «один ко многим». Запрос CREATE таблицы результатов содержит два ограничения внешнего ключа для полей, std_id и course_id.

CREATE DATABASE unidb;

USE unidb;



CREATE TABLE students (

id INT PRIMARY KEY,

name varchar(50) NOT NULL,

department VARCHAR(15) NOT NULL);



CREATE TABLE courses (

course_id VARCHAR(20) PRIMARY KEY,

name varchar(50) NOT NULL,

credit SMALLINT NOT NULL);



CREATE TABLE result(

std_id INT NOT NULL,

course_id VARCHAR(20) NOT NULL,

mark_type VARCHAR(20) NOT NULL,

marks SMALLINT NOT NULL,

FOREIGN KEY (std_id) REFERENCES students(id),

FOREIGN KEY (course_id) REFERENCES courses(course_id),

PRIMARY KEY (std_id, course_id, mark_type));

 

Вставьте некоторые записи в студентов, курсы и таблицы результатов. Значения должны быть вставлены в таблицы в соответствии с ограничениями, установленными во время создания таблицы.

INSERT INTO students VALUES

( '1937463', 'AndreyEx', 'CSE'),

( '1937464', 'Destroyer', 'CSE'),

( '1937465', 'Alex', 'CSE'),





INSERT INTO courses VALUES

( 'CSE-401', 'Объектно-ориентированное программирование', 3),

( 'CSE-403', 'Структура данных', 2),

( 'CSE-407', 'Программирование Unix', 2);



INSERT INTO result VALUES

( '1937463', 'CSE-401','Внутренний экзамен' ,15),

( '1937463', 'CSE-401','Промежуточный экзамен' ,20),

( '1937463', 'CSE-401','Итоговый экзамен', 35),

Здесь таблица результатов содержит несколько одинаковых значений для столбцов std_id , mark_type и course_id в каждой строке. Как преобразовать эти строки в столбцы этой таблицы для отображения данных в более организованном формате, показано в следующей части этой статьи.

 

Поверните строки в столбцы, используя оператор CASE:

Выполните следующую простую инструкцию с оператором SELECT, чтобы отобразить все записи таблицы результатов.

SELECT * FROM result;

 

На выходе показаны оценки четырех студентов за три типа экзаменов по трем курсам. Таким образом, значения std_id , course_id и mark_type повторяются несколько раз для разных студентов, курсов и типов экзаменов.

Вывод будет более читабельным, если запрос SELECT можно будет написать более эффективно с помощью оператора CASE. Следующий оператор SELECT с оператором CASE преобразует повторяющиеся значения строк в имена столбцов и отобразит содержимое таблиц в более понятном для пользователя формате.

SELECT result.std_id, result.course_id,

MAX(CASE WHEN result.mark_type = "Внутренний экзамен" THEN result.marks END) "Внутренний экзамен",

MAX(CASE WHEN result.mark_type = "Промежуточный экзамен" THEN result.marks END) "Промежуточный экзамен",

MAX(CASE WHEN result.mark_type = "Итоговый экзамен" THEN result.marks END) "Итоговый экзамен"

FROM result

GROUP BY result.std_id, result.course_id

ORDER BY result.std_id, result.course_id ASC;

 

Поверните строки в столбцы, используя CASE и SUM():

Если вы хотите подсчитать общее количество каждого курса каждого студента из таблицы, вы должны использовать агрегатную функцию SUM() group by std_id и course_id с оператором CASE. Следующий запрос создается путем изменения предыдущего запроса с помощью функции SUM() и предложения GROUP BY.

SELECT result.std_id,result.course_id,

MAX(CASE WHEN result.mark_type = "Внутренний экзамен" THEN result.marks END) "Внутренний экзамен",

MAX(CASE WHEN result.mark_type = "Промежуточный экзамен" THEN result.marks END) "Промежуточный экзамен",

MAX(CASE WHEN result.mark_type = "Итоговый экзамен" THEN result.marks END) "Итоговый экзамен",

SUM( result.marks) as Total

FROM result

GROUP BY result.std_id, result.course_id

ORDER BY result.std_id, result.course_id ASC;

В выходных данных отображается новый столбец «Total», в котором отображается сумма оценок по всем типам экзаменов каждого курса, полученных каждым конкретным студентом.

 

Поверните строки в столбцы в нескольких таблицах:

К таблице результатов применяются два предыдущих запроса.. Эта таблица связана с двумя другими таблицами. Это студенты и курсы. Если вы хотите отображать имя студента вместо идентификатора студента и имени курса вместо идентификатора курса, вам нужно написать запрос SELECT, используя три связанных таблицы, студентов , курсы и результат. Следующий запрос SELECT создается путем добавления трех имен таблиц после предложения FORM и задания соответствующих условий в предложении WHERE для извлечения данных из трех таблиц и создания более подходящего вывода, чем предыдущие запросы SELECT.

SELECT students.name as `Student Name` , courses.name as `Course Name`,

MAX(CASE WHEN result.mark_type = "Внутренний экзамен" THEN result.marks END) "Вн",

MAX(CASE WHEN result.mark_type = "Промежуточный экзамен" THEN result.marks END) "Пр",

MAX(CASE WHEN result.mark_type = "Итоговый экзамен" THEN result.marks END) "Финал",

SUM( result.marks) as Total

FROM students, courses, result

WHERE result.std_id = students.id and result.course_id= courses.course_id

GROUP BY result.std_id, result.course_id

ORDER BY result.std_id, result.course_id ASC;

 

Заключение:

В этой статье показано, как реализовать функциональность функции Pivot() без поддержки функции Pivot() в MySQL с использованием некоторых фиктивных данных. Мы надеемся, что читатели смогут преобразовать любые данные на уровне строк в данные на уровне столбцов с помощью запроса SELECT после прочтения этой статьи.



2021-01-22T09:48:45
MariaDB

Установка MariaDB на FreeBSD

MariaDB — это бесплатная производная от системы управления базами данных MySQL с открытым исходным кодом. Он был разработан из-за резервации базы пользователей MySQL, когда он был приобретен Oracle еще в 2009 году. По этой причине он вполне совместим с MySQL и только недавно немного отклонился с введением исходных движков, MyRocks, Aria и Columnstore., назвать несколько.

Это краткое руководство по установке сервера базы данных MariaDB 10.3 в системе FreeBSD 12. Следуя приведенным здесь инструкциям, вы сможете настроить базу данных на своем собственном сервере FreeBSD. Читать →

Ошибка входа в phpMyAdmin

После установки phpMyAdmin при входе в систему с root вы получаете следующую ошибку

Вход в систему root был заблокирован по соображениям безопасности.

Ошибка входа в phpMyAdmin

 

1698 - Access denied for user 'root'@'localhost'

mysqli_real_connect(): (HY000/1698): Access denied for user 'root'@'localhost'

 

Нам нужно самостоятельно создать пользователя-администратора.

1 Используйте Терминал для входа в mysql как root, если вы создали пароль root для mysql, если он пуст, используйте следующую команду

sudo mysql -p -u root

 

2 Создайте нового пользователя с новым паролем, здесь мы используем andreyex и testpassword в качестве примера

CREATE USER 'andreyex'@'localhost' IDENTIFIED BY 'testpassword';

 

Предоставить новому пользователю права суперпользователя

GRANT ALL PRIVILEGES ON *.* TO 'andreyex'@'localhost' WITH GRANT OPTION;

 

Теперь вы можете использовать новый логин для входа в phpMyAdmin.



2020-10-21T13:42:19
MariaDB

MySQL. Комментарии в глубину

Описание : в этой статье вы узнаете, как использовать комментарий MySQL для документирования оператора SQL или блока кода в MySQL.

Комментарии

Комментарии могут использоваться для документирования назначения оператора SQL или логики блока кода в хранимой процедуре. При разборе кода SQL, MySQL игнорирует часть комментариев. Он выполняет только часть SQL, за исключением исполняемого комментария, который мы обсудим в следующем разделе. Читать →

Как сопоставить значения NULL с другими значимыми значениями

Резюме: в этой статье вы научитесь отображать значения NULL в другие значимые значения.

Dr.EFCodd, который является создателем реляционной модели для базы данных, представил эту концепцию NULL в теории реляционных баз данных. Согласно Dr.EFCodd, NULL означает неизвестное значение или недостающая информацию.

MySQL также поддерживает NULL, которые представляют концепцию отсутствующей или неприменимой информации.

В таблице базы данных вы храните данные, которые содержат значения NULL. Когда вы предоставляете данные пользователям в форме отчетов, показывать NULL значения не имеет смысла.

Чтобы сделать отчеты более читабельными и понятными, необходимо отображать значения NULL в виде других значений, таких как неизвестные, отсутствующие или недоступные (нет данных). Для этого вы можете использовать функцию IF.

Синтаксис функции IF следующий:

IF(exp,exp_result1,exp_result2);

 

Если значение exp имеет TRUE (когда exp <> 0 и exp <> NULL), то функция IF возвращает значение из в противном случае exp_result1 возвращает значение exp_result2.

Возвращается значение функции IF может быть строка или число, в зависимости от выражений exp_result1 и exp_result2.

Давайте потренируемся с некоторыми примерами, чтобы лучше понять.

Мы будем работать с таблицей customers в примере базы данных.

Ниже приведены частичные данные в таблице customers, которая включает в себя customername state и country:

SELECT

customername, state, country

FROM

customers

ORDER BY country;

 

Значения состояния недоступны для некоторых клиентов. Вы можете использовать функцию IF для отображения значения NULL как N/A:

SELECT

customername, IF(state IS NULL, 'N/A', state) state, country

FROM

customers

ORDER BY country;

 

Помимо функции IF, MySQL предоставляет функцию IFNULL, которая позволяет NULL напрямую обрабатывать значения. Ниже приведен синтаксис функции IFNULL:

IFNULL(exp,exp_result);

 

 

Функция IFNULL возвращает значение выражения exp_result, если exp вычисляется в значение NULL, в противном случае, он возвращает значение выражения exp.

В следующем запросе функция IFNULL отображается NULL как неизвестная следующим образом:

SELECT customername,

IFNULL(state,"N/A")state,

country

FROM customers

ORDER BY country;

 

В этой статье вы узнали, как использовать функцию IF и IFNULL для отображения значений NULL на других более значимых значения для представления данных в читаемом виде.



2020-03-28T11:51:49
База данных MySQL