Enkripsi Database Mysql, Postgresql, dan Oracle

Data security is the most important thing in order to create a good system. Database encryption is the process of converting data in a database into a form that cannot be read by unauthorized persons. The purpose of database encryption is to improve the security and privacy of the data in the database. With an encrypted database, the contents of the data cannot be arbitrarily viewed by unauthorized people. This is important to protect sensitive data such as user identification numbers that cannot be viewed by just anyone.

Encryption is done on a column-level table, so people who want to see the contents of the database columns must first decrypt with a secret key, so people who don't have the key can't decrypt the columns.

In this article, we will discuss how to encrypt databases in MySql, Postgresql, and Oracle databases, these three databases are often used by developers, including in neuronworks. Each has its own way of encrypting databases, how to encrypt databases in the three databases themselves, including:

1. Enkripsi MySql

Encryption in MySql does not support native encryption, i.e. encryption is done directly on the table. Instead, Mysql uses encryption functions like the table below.

Link Source : https://dev.mysql.com/doc/refman/8.0/en/encryption-functions.html?

Here we use the AES_ENCRYPT() function for encryption and AES_DECRYPT for decryption. Encryption is done at the time of data insertion, while decryption is done at the time of selecting data. The fields to be encrypted must be of the Varbinary or Blob data type. Here is an example of the syntax for using encryption and decryption:

Example of encryption at the time of database insert:

INSERT INTO user (user_name, no_ktp)?

VALUES (‘arya’, AES_ENCRYPT(‘{KEY}’, ‘{VALUE dari no_ktp}’));

Example of encryption at select database:

SELECT user_name,? AES_DECRYPT(‘{KEY}’, no_ktp) FROM user ?
WHERE user_name = ‘arya’;

The advantages of using MySql decryption encryption are:

Encryption can help improve the security of data in MySQL databases by converting the original data into a form that cannot be read by unauthorized persons;
Encryption can be used to protect the privacy of sensitive data;
Minimize the risk of data loss. Even if the data is stolen or lost, it cannot be used by unauthorized people because it must be decrypted first;?
The use of encryption can give database users confidence that their data is safe and well protected.

The disadvantages of using MySql decryption encryption are:

All code in the program should be changed to add an encrypt/decrypt function at the time of query to db;?==
The key must be managed on the application side, so it must ensure that the key storage is secure;
Stay selective when displaying query results to the screen because the query results are already in a decrypted state; ?
If the query results want to be sent to another system, they must be encrypted again manually if they want to be secure; ?
The fields to be encrypted must be of type VARBINARY or BLOB data to support AES_ENCRYPT.

2. Enkripsi Postgresql

Unlike MySQL, encryption is done at the column level only,? PostrgreSQL uses pgsodium for encryption at the column level and table level. Encryption at the table level, only done once when the table is created. However, pgsodium itself is only supported in PostrgreSQL v.14, while versions below that are not yet supported.

The way to encrypt with pgsodium is as follows

Make sure you have installed the pgsodium extension in your postgre database by

SELECT * FROM pg_available_extensions; — (atau)
SHOW shared_preload_libraires;

Create a postgre table as usual with secret columns.
Apply encryption to the secret field by:

SECURITY LABEL FOR pgsodium ON COLUMN kolom_secret IS ‘ENCRYPT WITH KEY ID {KEY}’;?

After that, when inserting data as usual, secret columns will be automatically encrypted. Meanwhile, the way to decrypt it at the time of select is to add a decypted_ prefix to the secret column as follows:

SELECT name, decrypted_noktp AS notkp FROM private.decrypted_customer

Advantages of postgresql decryption encryption:

Direct encryption is done at the table level
There is no need to use the encrypt function in the INSERT statement, so there is no need to change the code in the application
All data types support encryption

Disadvantages of postgresql decryption encryption:

If the KEY is discovered, then we need to update all the data in the column to change the KEY;?
Need a change from the code side to decrypt columns from the table side?
Need to migrate the database for the existing database to be encrypted

3. Enkripsi Oracle

Oracle Database provides a variety of options for encrypting data in a database. One of the encryption options available is Transparent Data Encryption (TDE), which provides encryption at the column, table, or tablespace level automatically without the need to change the application or program code. TDE uses strong encryption algorithms such as AES and Triple DES. However, TDE Encryption cannot be implemented on the foreign-key field. To be able to encrypt TDE you need an Oracle Wallet to create a key.

Oracle Wallet is a security mechanism used in Oracle Database to store and manage private keys, and public keys used in data encryption. Oracle Wallet provides a secure security environment for storing sensitive security information. To be able to encrypt, you must first store-key to Oracle Wallet-key, as a form of authentication to access data, whether it can be encrypted / decrypted.

For encryption, it is enough to add 'ENCYPT' to the secret column when creating a table such as:

CREATE TABLE employee (?
     first_name VARCHAR2(128),?
     last_name VARCHAR2(128),?
     empID NUMBER,?
     salary NUMBER(6) ENCRYPT);

So every data that is inserted is automatically encrypted, and at the time of the automatic selection is decrypted by the system for the authorized person.