テーブルの暗号化
openssl rand -hex 32 >> keyfile vi keyfile
先頭に任意の数字を追加する。
e8682f7b803eb20d6bb963434aa62bd94bbe760e8371ac2599012581556ecd59↓
1;e8682f7b803eb20d6bb963434aa62bd94bbe760e8371ac2599012581556ecd59
openssl rand -hex 128 > keyfile.key openssl enc -aes-256-cbc -md sha1 -pass file:keyfile.key -in keyfile -out keyfile.enc
設定ファイルを編集する。
vi /etc/mysql/mariadb.conf.d/50-server.cnf
[mariadb]plugin_load_add = file_key_management
...
loose_file_key_management_filename = ANYPATH/keyfile.enc
loose_file_key_management_filekey = FILE:ANYPATH/keyfile.key
loose_file_key_management_encryption_algorithm = AES_CBC
mysql
show variables like '%enc%';
+------------------------------------------+---------+
| Variable_name | Value |
+------------------------------------------+---------+
| aria_encrypt_tables | OFF |
| encrypt_binlog | OFF |
| encrypt_tmp_disk_tables | OFF |
| encrypt_tmp_files | OFF |
| file_key_management_encryption_algorithm | aes_cbc |
| innodb_buf_dump_status_frequency | 0 |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 5000 |
| innodb_default_encryption_key_id | 1 |
| innodb_defragment_frequency | 40 |
| innodb_encrypt_log | OFF |
| innodb_encrypt_tables | OFF |
| innodb_encrypt_temporary_tables | OFF |
| innodb_encryption_rotate_key_age | 1 |
| innodb_encryption_rotation_iops | 100 |
| innodb_encryption_threads | 0 |
| innodb_purge_rseg_truncate_frequency | 128 |
| innodb_thread_concurrency | 0 |
| thread_concurrency | 10 |
+------------------------------------------+---------+
SSLで暗号化
現在状況を確認する。
mysql show variables like '%ssl%';
+---------------------+-------------+
| Variable_name | Value |
+---------------------+-------------+
| have_openssl | NO |
| have_ssl | DISABLED |
| ssl_ca | |
| ssl_capath | |
| ssl_cert | |
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_key | |
| version_ssl_library | YaSSL 2.4.4 |
+---------------------+-------------+
SSLを有効にする。
vi /etc/mysql/mariadb.conf.d/50-server.cnf
ssl = onservice mysql restart mysql show variables like '%ssl%';
+---------------------+-------------+
| Variable_name | Value |
+---------------------+-------------+
| have_openssl | NO |
| have_ssl | YES |
| ssl_ca | |
| ssl_capath | |
| ssl_cert | |
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_key | |
| version_ssl_library | YaSSL 2.4.4 |
+---------------------+-------------+
暗号キーの作成
openssl genrsa 2048 > ca-key.pem
Generating RSA private key, 2048 bit long modulus (2 primes)
.............................................................................+++++
...................................................................................................................+++++
e is 65537 (0x010001)
openssl req -new -x509 -nodes -days 365000 -key ca-key.pem -out ca-cert.pem
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [AU]:XX
State or Province Name (full name) [Some-State]:XX
Locality Name (eg, city) []:XX
Organization Name (eg, company) [Internet Widgits Pty Ltd]:XX
Organizational Unit Name (eg, section) []:IXX
Common Name (e.g. server FQDN or YOUR name) []:XX
Email Address []:XX
openssl req -newkey rsa:2048 -days 365 -nodes -keyout server-key.pem -out server-req.pem
Ignoring -days; not generating a certificate
Generating a RSA private key
........................................................................................................................................+++++
.+++++
writing new private key to 'server-key.pem'
-----
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [AU]:XX
State or Province Name (full name) [Some-State]:XX
Locality Name (eg, city) []:XX
Organization Name (eg, company) [Internet Widgits Pty Ltd]:XX
Organizational Unit Name (eg, section) []:IT
Common Name (e.g. server FQDN or YOUR name) []:XX
Email Address []:XXPlease enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:XX
An optional company name []:
openssl rsa -in server-key.pem -out server-key.pem
openssl x509 -req -in server-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem
Signature ok
subject=C = XX, ST = XX, L = XX, O = XX, OU = XX, CN = XX, emailAddress = XX
Getting CA Private Key
vi /etc/mysql/mariadb.conf.d/50-server.cnf
ssl-ca=ANYPATH/ca-cert.pem
ssl-cert=ANYPATH//server-cert.pem
ssl-key=ANYPATH/server-key.pem
mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 72
Server version: 10.3.22-MariaDB-0+deb10u1 Debian 10
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
SHOW VARIABLES LIKE '%ssl%';
+---------------------+--------------------------------------+
| Variable_name | Value |
+---------------------+--------------------------------------+
| have_openssl | NO |
| have_ssl | YES |
| ssl_ca | ANYPATH/ca-cert.pem |
| ssl_capath | |
| ssl_cert | ANYPATH/server-cert.pem |
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_key | ANYPATH/server-key.pem |
| version_ssl_library | YaSSL 2.4.4 |
+---------------------+--------------------------------------+
コメント