A. Pre Configure
1. Create database
create database secretdb;
use secretdb;
MariaDB [teguhth]> create database secretdb;
Query OK, 1 row affected (0.000 sec)
MariaDB [teguhth]>
MariaDB [teguhth]> use secretdb;
Database changed
MariaDB [secretdb]>
2. create table sample barang;
create table barang(
KODE_BARANG char(6) not null ,
NAMA_BARANG varchar(25),
SATUAN_BARANG varchar(20),
STOK_BARANG decimal(4),
primary key (KODE_BARANG));
insert into barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-01','RICE COOKER','BUAH',20);
insert into barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-02','LEMARI ES','UNIT',8);
insert into barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-03','TELEVISI','UNIT',30);
insert into barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-04','RADIO/TAPE','BUAH',35);
insert into barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-05','KOMPUTER','UNIT',28);
insert into barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-06','KIPAS ANGIN','BUAH',38);
select * from barang;
MariaDB [secretdb]> create table barang(
-> KODE_BARANG char(6) not null ,
-> NAMA_BARANG varchar(25),
-> SATUAN_BARANG varchar(20),
-> STOK_BARANG decimal(4),
-> primary key (KODE_BARANG));
Query OK, 0 rows affected (0.003 sec)
MariaDB [secretdb]>
MariaDB [secretdb]> select * from barang;
+-------------+-------------+---------------+-------------+
| KODE_BARANG | NAMA_BARANG | SATUAN_BARANG | STOK_BARANG |
+-------------+-------------+---------------+-------------+
| ELK-01 | RICE COOKER | BUAH | 20 |
| ELK-02 | LEMARI ES | UNIT | 8 |
| ELK-03 | TELEVISI | UNIT | 30 |
| ELK-04 | RADIO/TAPE | BUAH | 35 |
| ELK-05 | KOMPUTER | UNIT | 28 |
| ELK-06 | KIPAS ANGIN | BUAH | 38 |
+-------------+-------------+---------------+-------------+
6 rows in set (0.000 sec)
MariaDB [secretdb]>
3. show table
| barang | CREATE TABLE `barang` (
`KODE_BARANG` char(6) NOT NULL,
`NAMA_BARANG` varchar(25) DEFAULT NULL,
`SATUAN_BARANG` varchar(20) DEFAULT NULL,
`STOK_BARANG` decimal(4,0) DEFAULT NULL,
PRIMARY KEY (`KODE_BARANG`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
4. change table barang to encripted table
MariaDB [secretdb]> alter table secretdb.barang encrypted=yes;
ERROR 1478 (HY000): Table storage engine 'InnoDB' does not support the create option 'ENCRYPTION_KEY_ID'
MariaDB [secretdb]>
B. Configure Encripted
1. check directory MariaDB / Setting Up
[root@teguhth ~]# find / -name *.ibd 2>/dev/null
/var/lib/mysql/mysql/innodb_table_stats.ibd
/var/lib/mysql/mysql/innodb_index_stats.ibd
/var/lib/mysql/mysql/transaction_registry.ibd
/var/lib/mysql/mysql/gtid_slave_pos.ibd
/var/lib/mysql/teguhth/barang.ibd
/var/lib/mysql/teguhth/suplier.ibd
/var/lib/mysql/teguhth/customer.ibd
/var/lib/mysql/teguhth/pasok.ibd
/var/lib/mysql/teguhth/pembelian.ibd
/var/lib/mysql/secretdb/barang.ibd
[root@teguhth ~]#
2. Generate Keys
mkdir -p /etc/mysql/encryption
cd /etc/mysql/encryption
echo "1;"$(openssl rand -hex 32) > keys
echo "2;"$(openssl rand -hex 32) >> keys
echo "3;"$(openssl rand -hex 32) >> keys
echo "4;"$(openssl rand -hex 32) >> keys
[root@teguhth ~]# mkdir -p /etc/mysql/encryption
[root@teguhth ~]# cd /etc/mysql/encryption
[root@teguhth encryption]#
[root@teguhth encryption]# echo "1;"$(openssl rand -hex 32) > keys
[root@teguhth encryption]# echo "2;"$(openssl rand -hex 32) >> keys
[root@teguhth encryption]# echo "3;"$(openssl rand -hex 32) >> keys
[root@teguhth encryption]# echo "4;"$(openssl rand -hex 32) >> keys
[root@teguhth encryption]# cat keys
1;cd2535351acabde5260eda3a12447c111bc37869b7d005611392c60dde1bed3f
2;8e52508870025fef4070e78e3902ad2decd2f3cd1a6e72cc7f763552e59b5e3d
3;9e002ba2220776303996711b7dd1f613b0e99a650598005d3e92ce67ebdf7aef
4;dc5df44cc36c7cac6f23b906b49c32b3dc4866d3f63b2b77e850e3997c2cdf4c
[root@teguhth encryption]#
3. encrypt these keys with a long random password
openssl rand -hex 128 > password_file
openssl enc -aes-256-cbc -md sha1 -pass file:password_file -in keys -out keys.enc
[root@teguhth encryption]# pwd
/etc/mysql/encryption
[root@teguhth encryption]# openssl rand -hex 128 > password_file
[root@teguhth encryption]# openssl enc -aes-256-cbc -md sha1 -pass file:password_file -in keys -out keys.enc
[root@teguhth encryption]#
4. Updating MariaDB Configuration
[root@teguhth encryption]# vi /etc/my.cnf.d/encryption.cnf
[root@teguhth encryption]# cat /etc/my.cnf.d/encryption.cnf
[mariadb]
## File Key Management
plugin_load_add = file_key_management
file_key_management_filename = /etc/mysql/encryption/keys.enc
file_key_management_filekey = FILE:/etc/mysql/encryption/password_file
file_key_management_encryption_algorithm = aes_cbc
## 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
[root@teguhth encryption]#
5. change ownership and permission of these sensitive files so no other user can read it.
cd /etc/mysql/
chown -R mysql:root ./encryption
chmod 500 /etc/mysql/encryption/
cd /etc/mysql/encryption/
chmod 400 keys.enc password_file
[root@teguhth encryption]# cd /etc/mysql/
[root@teguhth mysql]# chown -R mysql:root ./encryption
[root@teguhth mysql]# chmod 500 /etc/mysql/encryption/
[root@teguhth mysql]#
[root@teguhth my.cnf.d]# cd /etc/mysql/encryption/
[root@teguhth encryption]# pwd
/etc/mysql/encryption
[root@teguhth encryption]# ls
keys keys.enc password_file
[root@teguhth encryption]# chmod 400 keys.enc password_file
[root@teguhth encryption]#
6. change permission encryption.cnf
chmod 644 /etc/my.cnf.d/encryption.cnf
[root@teguhth mysql]# chmod 644 /etc/my.cnf.d/encryption.cnf
[root@teguhth mysql]#
7. restart mariadb
[root@teguhth mysql]# systemctl restart mariadb
[root@teguhth mysql]#
8. change table barang to encripted table
[root@teguhth ~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.5.20-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)]> use secretdb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [secretdb]> alter table secretdb.barang encrypted=yes;
Query OK, 0 rows affected (0.007 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [secretdb]>
C. check after encripted
MariaDB [secretdb]> desc barang;
+---------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| KODE_BARANG | char(6) | NO | PRI | NULL | |
| NAMA_BARANG | varchar(25) | YES | | NULL | |
| SATUAN_BARANG | varchar(20) | YES | | NULL | |
| STOK_BARANG | decimal(4,0) | YES | | NULL | |
+---------------+--------------+------+-----+---------+-------+
4 rows in set (0.003 sec)
MariaDB [secretdb]> show create table barang;
| barang | CREATE TABLE `barang` (
`KODE_BARANG` char(6) NOT NULL,
`NAMA_BARANG` varchar(25) DEFAULT NULL,
`SATUAN_BARANG` varchar(20) DEFAULT NULL,
`STOK_BARANG` decimal(4,0) DEFAULT NULL,
PRIMARY KEY (`KODE_BARANG`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci `encrypted`=yes |
MariaDB [secretdb]> select * from secretdb.barang;
+-------------+-------------+---------------+-------------+
| KODE_BARANG | NAMA_BARANG | SATUAN_BARANG | STOK_BARANG |
+-------------+-------------+---------------+-------------+
| ELK-01 | RICE COOKER | BUAH | 20 |
| ELK-02 | LEMARI ES | UNIT | 8 |
| ELK-03 | TELEVISI | UNIT | 30 |
| ELK-04 | RADIO/TAPE | BUAH | 35 |
| ELK-05 | KOMPUTER | UNIT | 28 |
| ELK-06 | KIPAS ANGIN | BUAH | 38 |
+-------------+-------------+---------------+-------------+
6 rows in set (0.001 sec)
MariaDB [secretdb]>
D. Check other query
SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID,@@hostname,@@version FROM information_schema.INNODB_TABLESPACES_ENCRYPTION;
MariaDB [secretdb]> SELECT @hostname, NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID
-> FROM information_schema.INNODB_TABLESPACES_ENCRYPTION;
+-----------+----------------------------+-------------------+----------------+
| @hostname | NAME | ENCRYPTION_SCHEME | CURRENT_KEY_ID |
+-----------+----------------------------+-------------------+----------------+
| NULL | innodb_system | 1 | 1 |
| NULL | mysql/innodb_table_stats | 1 | 1 |
| NULL | mysql/innodb_index_stats | 1 | 1 |
| NULL | mysql/transaction_registry | 1 | 1 |
| NULL | mysql/gtid_slave_pos | 1 | 1 |
| NULL | teguhth/barang | 1 | 1 |
| NULL | teguhth/suplier | 1 | 1 |
| NULL | teguhth/customer | 1 | 1 |
| NULL | teguhth/pasok | 1 | 1 |
| NULL | teguhth/pembelian | 1 | 1 |
| NULL | secretdb/barang | 1 | 1 |
+-----------+----------------------------+-------------------+----------------+
11 rows in set (0.000 sec)
MariaDB [secretdb]>
-> FROM information_schema.INNODB_TABLESPACES_ENCRYPTION;
+-----------+----------------------------+-------------------+----------------+
| @hostname | NAME | ENCRYPTION_SCHEME | CURRENT_KEY_ID |
+-----------+----------------------------+-------------------+----------------+
| NULL | innodb_system | 1 | 1 |
| NULL | mysql/innodb_table_stats | 1 | 1 |
| NULL | mysql/innodb_index_stats | 1 | 1 |
| NULL | mysql/transaction_registry | 1 | 1 |
| NULL | mysql/gtid_slave_pos | 1 | 1 |
| NULL | teguhth/barang | 1 | 1 |
| NULL | teguhth/suplier | 1 | 1 |
| NULL | teguhth/customer | 1 | 1 |
| NULL | teguhth/pasok | 1 | 1 |
| NULL | teguhth/pembelian | 1 | 1 |
| NULL | secretdb/barang | 1 | 1 |
+-----------+----------------------------+-------------------+----------------+
11 rows in set (0.000 sec)
MariaDB [secretdb]>
https://webdock.io/en/docs/how-guides/security-guides/how-to-enable-encryption-mariadb#:~:text=In%20order%20to%20enable%20encryption,and%20a%20semicolon%20%E2%80%9C%3B%E2%80%9D.&text=Now%2C%20encrypt%20these%20keys%20with%20a%20long%20random%20password.
No comments:
Post a Comment