.::: Create User MariaDB MySQL using SSL / Certificate :::.

 

Mengaktifkan TLS/SSL pada MariaDB 10.6 dan jika sudah nanti akan di test dengan user 'adminssl' password 'adminssl' 

Berikut adalah panduan mengaktifkan TLS/SSL di MariaDB 10.6 dan menguji koneksi dengan user adminssl:

1. Buat Direktori & Sertifikat SSL

mkdir -p /etc/mysql/ssl

cd /etc/mysql/ssl

2. Create Certificate 
 # Buat CA (Certificate Authority)
openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -days 3650 -key ca-key.pem -out ca-cert.pem -subj "/CN=MyMariaDB-CA"

# Buat sertifikat server
openssl req -newkey rsa:2048 -days 3650 -nodes -keyout server-key.pem -out server-req.pem -subj "/CN=MariaDB-Server"
openssl x509 -req -in server-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

# Buat sertifikat klien
openssl req -newkey rsa:2048 -days 3650 -nodes -keyout client-key.pem -out client-req.pem -subj "/CN=adminssl"
openssl x509 -req -in client-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 02 -out client-cert.pem

# Atur kepemilikan & izin
chown mysql:mysql *.pem
chmod 600 *.pem

ini apa 
openssl req -newkey rsa:2048 -nodes -keyout client-pm-key.pem -out client-req.pem -subj "/CN=adminssl"

log 

[root@teguhth mysql]#  mkdir -p /etc/mysql/ssl
[root@teguhth mysql]# cd /etc/mysql/ssl
[root@teguhth ssl]# openssl genrsa 2048 > ca-key.pem
Generating RSA private key, 2048 bit long modulus (2 primes)
.....+++++
........................................+++++
e is 65537 (0x010001)
[root@teguhth ssl]#
[root@teguhth ssl]# openssl req -new -x509 -days 3650 -key ca-key.pem -out ca-cert.pem -subj "/CN=MyMariaDB-CA"
[root@teguhth ssl]#
[root@teguhth ssl]# openssl req -newkey rsa:2048 -days 3650 -nodes -keyout server-key.pem -out server-req.pem -subj "/CN=MariaDB-Server"
Ignoring -days; not generating a certificate
Generating a RSA private key
..........................................................................................+++++
...................................................................+++++
writing new private key to 'server-key.pem'
-----
[root@teguhth ssl]#
[root@teguhth ssl]# 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=CN = MariaDB-Server
Getting CA Private Key
[root@teguhth ssl]#
[root@teguhth ssl]#

[root@teguhth ssl]# openssl req -newkey rsa:2048 -days 3650 -nodes -keyout client-key.pem -out client-req.pem -subj "/CN=adminssl"
Ignoring -days; not generating a certificate
Generating a RSA private key
.............................................................................................................+++++
.....................+++++
writing new private key to 'client-key.pem'
-----
[root@teguhth ssl]# openssl x509 -req -in client-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 02 -out client-cert.pem
Signature ok
subject=CN = adminssl
Getting CA Private Key
[root@teguhth ssl]#

[root@teguhth ssl]# chown mysql:mysql *.pem
[root@teguhth ssl]# sudo chmod 600 *.pem
[root@teguhth ssl]#
[root@teguhth ssl]# pwd
/etc/mysql/ssl
[root@teguhth ssl]#

[root@teguhth certs]# pwd
/home/user/certs
[root@teguhth certs]# ls
ca-cert.pem  client-cert.pem  client-key.pem
[root@teguhth certs]#
[root@teguhth certs]# cat ca-cert.pem
-----BEGIN CERTIFICATE-----
MIIDDzCCAfegAwIBAgIUXoaTGzZa9vv4vK1C6tueMGoquBQwDQYJKoZIhvcNAQEL
BQAwFzEVMBMGA1UEAwwMTXlNYXJpYURCLUNBMB4XDTI1MDcyMjAzMjEwNVoXDTM1
MDcyMDAzMjEwNVowFzEVMBMGA1UEAwwMTXlNYXJpYURCLUNBMIIBIjANBgkqhkiG
9w0BAQEFAAOCAQ8AMIIBCgKCAQEArm8YKxZt/v05a2yUKvHzfqyrH8UOhxfA5XTs
+0zOCwIWc0tvyoIc8xwDSPsIsA6hSgq28cv/Bp8IXBKFBRPhNkBPB3kYqlwRBdGm
nQHMISbg++Hmh61v4wDYkaVBNzmqUGuqR8fld068S28hcD4epcVzdbJhtb7dmD6W
XdAM9xp0kVxUU07l1s1/wehYnI62YV0HocDci27TurNAodlVLdVcCzl1xEIBIVBk
kYyPSM3zm0GtlwBu0ujwPDhBqKvJaL7wCThyDzz04yt9Vbaqxs4/2/+e0UYlw9Zf
oGE04lKt3q9jAuErbzasHBboVVTDsaCPbN/U6FT7nZIOHHIzMQIDAQABo1MwUTAd
BgNVHQ4EFgQUyf10S5BMfurPrRVA4tzw1pjhkv0wHwYDVR0jBBgwFoAUyf10S5BM
furPrRVA4tzw1pjhkv0wDwYDVR0TAQH/BAUwAwEB/zANBgkqhkiG9w0BAQsFAAOC
AQEAQ198+g3WcUCDBxP71iwEnkeQxNckJtx2Xuf2Yt/VuPqUDm+wlLuxd5Ccdn4t
h1HNE3PONkS12Q6dJkjOm6+Gr8n6IcwQAM1tP2Un04DdansZIJjgncORvsrmAMCe
n+50h77oKGcXNEcW5+tb3KQ4JVm4D7OJ1lNbeLIhVhjXcfd8xLW660CiEetHyG57
sYRbYidLuCxcUdFoxKR15owVy4eIcWsgTIYwvEAyEPaLQUEbTA1mT1uKw5v5y0oO
qtqrIYbJCf5rIBETJfmkKuzL6oSiMTboHJg3oSg9tnJViLabZ8Hf0MeV59xV0vxW
v1x6Y5N6WsXBXZzoSZxcDnPBQQ==
-----END CERTIFICATE-----
[root@teguhth certs]#
[root@teguhth certs]# cat client-cert.pem
-----BEGIN CERTIFICATE-----
MIIC5zCCAc+gAwIBAgIBAjANBgkqhkiG9w0BAQsFADAXMRUwEwYDVQQDDAxNeU1h
cmlhREItQ0EwHhcNMjUwNzIyMDMyMTA2WhcNMzUwNzIwMDMyMTA2WjATMREwDwYD
VQQDDAhhZG1pbnNzbDCCASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEBAMOx
4JeNiQ6HL4mppn4sqylVXFI5MXdu+SSpA2qZ6EWdRuVIK0PrwS3d7NULukpF965c
5aSOC/Rta4T28Hivk2gX5zXh3u6fYntDyAFFQwYvy+J2JD1uIpU6w9AWwp0+Z/1K
oIicAaelcsiSXZz0x3ytGPU7D8yba29gVW/9TRzagwAaaj+LgS3E+jSyepbtq8Vt
g+02jMzoNU/6X/mIq1J/tzTl6/bVJRZene+J51+oZYgiwhBvqhcmd+vbfnxN/7vi
QD7gGAov/Xn0DNKsHg2NFpIfi4jT3v6/aISbZycTufezaetLvpSMVCthwxdocHdk
oFMaqHsAqTLanF8uk+8CAwEAAaNCMEAwHQYDVR0OBBYEFPN6mi2vta85JdlBIP4L
uDAIGwtpMB8GA1UdIwQYMBaAFMn9dEuQTH7qz60VQOLc8NaY4ZL9MA0GCSqGSIb3
DQEBCwUAA4IBAQBaqk6m9lum4767TAOM4oRxSJVrfQ5WOut7l85z+Cn0YiMVzJsq
4SF0H17nO00jcEW30JXCEk192/qltDXhh/L7x37iI9xMXrFu1J16zCUOFpLaXYI0
93rTK7/YL8wCYXzc1qqQODOpuqVaHdfQH+9wfJ6su8GaHeN3Sg+gsp8/m1BhINKL
8RHF625IiMqw+ZVVSZm8ik8OyUrql+/ctLcOAdc3iOdkAXJjvU2BvUf1OcwE+dMo
9h7/jgsq4Z4lm6PY4/mQe1klCflcjxCmYl7QF2vr2YOSxrBz5C59XsZQ84x2YsPP
VEcPYBnp2pXVFUEtRT5SkYaCGXM1xN3k2U32
-----END CERTIFICATE-----
[root@teguhth certs]#
[root@teguhth certs]# cat client-key.pem
-----BEGIN PRIVATE KEY-----
MIIEvQIBADANBgkqhkiG9w0BAQEFAASCBKcwggSjAgEAAoIBAQDDseCXjYkOhy+J
qaZ+LKspVVxSOTF3bvkkqQNqmehFnUblSCtD68Et3ezVC7pKRfeuXOWkjgv0bWuE
9vB4r5NoF+c14d7un2J7Q8gBRUMGL8vidiQ9biKVOsPQFsKdPmf9SqCInAGnpXLI
kl2c9Md8rRj1Ow/Mm2tvYFVv/U0c2oMAGmo/i4EtxPo0snqW7avFbYPtNozM6DVP
+l/5iKtSf7c05ev21SUWXp3viedfqGWIIsIQb6oXJnfr2358Tf+74kA+4BgKL/15
9AzSrB4NjRaSH4uI097+v2iEm2cnE7n3s2nrS76UjFQrYcMXaHB3ZKBTGqh7AKky
2pxfLpPvAgMBAAECggEASNxC6xpCw7p4bBBSkVqvNXn3IBP0Q2v5bflRcNCxxq16
lmZK2YR+zcV5Zo7xwrKPN4eV5rEOaWiN5hkMylpQjyqLJqgcmQqVn6BKaJKxlC2a
gwwKjSai0J3rBuBagDIahJdS8tnm0dTLSjsYiJYoadaG2tMtHDhnhTXASaxtGAbp
QgZgXlrs55gKKBIIytVz2mkMYWGj2PjihR9+0p8dCXJEiZ7uD7TQdJeHmxmyX0BG
tzijIv+LGCdobHlDSnxaNIV1h1Jou4cLOdOmjGTRJM5JED204vpvKAbH+GdpqVnu
tUai2UpfDjd8v+Ei8CbSzhclZ0PtxVi1LOL7yV42hQKBgQD+a6TSSiRcf52mBMWY
QoQk+tLDRZjU9l3gDPYbfcw24g2Y/H0sR/NWzJNARA5sE6NaHzVMtsiWIJq57oxk
WDimeiv83Fb/go8L62Q3vzdKcHgZOS+TBfZTJxsn79gMvxdLLN7kE9bYU/pHBMdI
iMlnoDMYRpMiH2A2S+sys/J3xQKBgQDE6OZE5O7q3b56ga8sjHH5vRus5lsGo6H7
LlEM/1KDrXo2ZOYrJaSUmJNtjClqrcddaCshI1dPOQVCmWKZE3lEvzia3bZx9JzG
mF4WeDgPFtEO14P8F5XnLWKAY5XC4kfAU1POMphB+AFHPn51JqLoCMhRDY8wRYQV
qDhWhfakIwKBgHpBN55lSju4hwSz3k9gByfN72EuHFaZXFrBX7GuLWdiEK6nBW81
09St0URcb6G3fhNcU7xdSN37JN6bppLBYdd9dY24Q4XcMujDYBD5rZPzn4JfXGEF
yGwEqqPyOJtHZ+YT1bc7YU3qbKWbwjc0o7NL48GSiDkpYo40xPxERAsRAoGBAJtR
UgDZy5K64fpjvLcY+PYSsnfJI9eyzpwARrJH2uA+v/2TzPIPuSf0yaVJP4oEFROa
jRqQc9frlDiaIGm6MJeHdbDCXZy5Y8hKezyiCyXry283k8YPHJDC/cVjx8d3ET2k
/J/SzMKMSwXVR45EsY7xDoDQfzeGPc+PZxDojkmdAoGATC75/A/pbfBs2aaaB875
9MvXC+d0oTkm72b5wN+en0nuHrMRG4bF6k5HlZd4sCTLZ/+CVIgeR1GvmHJVAuXL
7ry63103wvCdUfLw8zlXohZ5u1RwPx2kxo8PU/cLztVfGqLNLFTa8dUqKcR/ujhH
PndlYrVw5dH7gV8UeQBQ9U4=
-----END PRIVATE KEY-----
[root@teguhth certs]#

3. Konfigurasi MariaDB (my.cnf atau 50-server.cnf) & Restart mariadb
Edit /etc/my.cnf.d/server.cnf atau /etc/my.cnf:

[mysqld]
ssl-ca=/etc/mysql/ssl/ca-cert.pem
ssl-cert=/etc/mysql/ssl/server-cert.pem
ssl-key=/etc/mysql/ssl/server-key.pem

Anda juga bisa menambahkan ini untuk keamanan tambahan:

ssl-cipher=TLSv1.2

[root@teguhth ssl]# pwd
/etc/mysql/ssl
[root@teguhth ssl]# grep -i ssl /etc/my.cnf.d/server.cnf
ssl-ca=/etc/mysql/ssl/ca-cert.pem
ssl-cert=/etc/mysql/ssl/server-cert.pem
ssl-key=/etc/mysql/ssl/server-key.pem
ssl-cipher=TLSv1.2
[root@teguhth ssl]#

4. Cek Apakah TLS Aktif
Login ke MariaDB dan jalankan:

SHOW VARIABLES LIKE 'have_ssl';
SHOW VARIABLES LIKE '%ssl%';
SHOW STATUS LIKE 'Ssl_version';

[root@teguhth ssl]# mariadb
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.6.22-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>
MariaDB [(none)]> SHOW VARIABLES LIKE 'have_ssl';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_ssl      | YES   |
+---------------+-------+
1 row in set (0.001 sec)

MariaDB [(none)]> SHOW VARIABLES LIKE '%ssl%';
+---------------------+----------------------------------+
| Variable_name       | Value                            |
+---------------------+----------------------------------+
| have_openssl        | YES                              |
| have_ssl            | YES                              |
| ssl_ca              | /etc/mysql/ssl/ca-cert.pem       |
| ssl_capath          |                                  |
| ssl_cert            | /etc/mysql/ssl/server-cert.pem   |
| ssl_cipher          | TLSv1.2                          |
| ssl_crl             |                                  |
| ssl_crlpath         |                                  |
| ssl_key             | /etc/mysql/ssl/server-key.pem    |
| version_ssl_library | OpenSSL 1.1.1c FIPS  28 May 2019 |
+---------------------+----------------------------------+
10 rows in set (0.001 sec)

MariaDB [(none)]> SHOW STATUS LIKE 'Ssl_version';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Ssl_version   |       |
+---------------+-------+
1 row in set (0.001 sec)

MariaDB [(none)]>
 

5. Create User adminssl yang Wajib SSL

CREATE USER 'adminssl'@'%' IDENTIFIED BY 'adminssl' REQUIRE SSL;
GRANT ALL PRIVILEGES ON *.* TO 'adminssl'@'%';
FLUSH PRIVILEGES;

CREATE USER 'adminssl'@'localhost' IDENTIFIED BY 'adminssl' REQUIRE SSL;
GRANT ALL PRIVILEGES ON *.* TO 'adminssl'@'localhost';
FLUSH PRIVILEGES;

 

ALTER USER 'adminssl'@'%' REQUIRE X509;

MariaDB [(none)]> show create user 'adminssl'@'%';
+------------------------------------------------------------------------------------------------------------+
| CREATE USER for adminssl@%                                                                                 |
+------------------------------------------------------------------------------------------------------------+
| CREATE USER `adminssl`@`%` IDENTIFIED BY PASSWORD '*F42D6C8B86DB1A3D1C64D4300904894F54605932' REQUIRE X509 |
+------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [(none)]>


SELECT @@hostname,@@version,USER,HOST,ssl_type,PLUGIN  FROM mysql.user;

[root@teguhth ssl]# mariadb
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.6.22-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> CREATE USER 'adminssl'@'%' IDENTIFIED BY 'adminssl' REQUIRE SSL;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'adminssl'@'%';
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]>

6. Uji Koneksi dengan SSL dari Client
Copy file berikut ke komputer client (misal: /home/user/certs):

ca-cert.pem
client-cert.pem
client-key.pem

Jalankan koneksi:

cd /etc/mysql/ssl/
cp ca-cert.pem  client-cert.pem client-key.pem /home/user/certs/

7. test loging in Terminal 

mysql -u adminssl -padminssl \
  --ssl-ca=/home/user/certs/ca-cert.pem \
  --ssl-cert=/home/user/certs/client-cert.pem \
  --ssl-key=/home/user/certs/client-key.pem \
  -h 10.10.10.9
  
mysql -u adminssl -padminssl --ssl-ca=/home/user/certs/ca-cert.pem --ssl-cert=/home/user/certs/client-cert.pem --ssl-key=/home/user/certs/client-key.pem -h 10.10.10.9

[root@teguhth ssl]# mysql -u adminssl -padminssl --ssl-ca=/home/user/certs/ca-cert.pem --ssl-cert=/home/user/certs/client-cert.pem --ssl-key=/home/user/certs/client-key.pem -h 10.10.10.9
ERROR 2026 (HY000): TLS/SSL error: No such file or directory
[root@teguhth ssl]#
[root@teguhth ssl]# cp ca-cert.pem client-cert.pem client-key.pem /home/user/certs/
[root@teguhth ssl]# cd /home/user/certs/
[root@teguhth certs]#
[root@teguhth certs]# ls
ca-cert.pem  client-cert.pem  client-key.pem
[root@teguhth certs]#

[root@teguhth certs]# mysql -u adminssl -padminssl --ssl-ca=/home/user/certs/ca-cert.pem --ssl-cert=/home/user/certs/client-cert.pem --ssl-key=/home/user/certs/client-key.pem -h 10.10.10.9
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 10.6.22-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> select @@hostname,@@version;
+------------+-----------------+
| @@hostname | @@version       |
+------------+-----------------+
| teguhth    | 10.6.22-MariaDB |
+------------+-----------------+
1 row in set (0.000 sec)

MariaDB [(none)]>

8. test loging dengan Tool example heidi