...

In both techniques, the malicious user won't be able to decrypt data.

Encrypting Data At Rest

In the database, all the patient data is stored in data files. There is a chance that an attacker can retrieve sensitive data if they get direct access to such files. To avoid this, one can encrypt the data files using the keyring plugin provided by MySQL.

The keyring file is the main file that stores key using which the tablespace (where all table data files are stored) is encrypted. The keyring plugin is used for this purpose.

...

Expand

i) To encrypt a database table we run ALTER TABLE <TABLE_NAME> ENCRYPTION=”Y” ii)  Similarly, we’ll need to encrypt every table in the database. For this, we can generate a master-sql file, which will have the query to alter all the existing DB tables. iii) Follow the below procedure to create a master-query. Code Block language sql mysql> USE <database_name>; mysql> TEE encrypt_tables.sql; mysql> SELECT CONCAT("ALTER TABLE `", TABLE_NAME,"` ENCRYPTION=\"Y\" ;")     as encryption_query        FROM  INFORMATION_SCHEMA.TABLES        WHERE    TABLE_SCHEMA="<database_name>" AND          TABLE_TYPE="BASE TABLE"; mysql> NOTEE; mysql> EXIT; TEE: This command reads the standard input and writes it to the file as well as standard output. iv) Remove the table structure and the select query captured in the ‘encrypt_tables.sql’ file using the below command. Code Block language bash grep -i "ALTER" encrypt_tables.sql | sed '1d' | sed 's/|//g' > processed_encryption_tables.sql v) Again login into MySQL database and run the ‘source processed_encryption_table.sql’. Code Block language sql mysql> USE <database_name>; mysql> SOURCE processed_encryption_table.sql vi) Check if all tables are encrypted are not by noting ENCRYPTION="Y" flag under the CREATE_OPTIONS column. Code Block language sql SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE CREATE_OPTIONS LIKE '%ENCRYPTION%';

Encrypting Data In Transit

In this step, we create self-signed SSL certificates for MySQL server to communicate using SSL. The certificate file is shared with the application so that it can store certificates in its TrustStore file and application can trust the MySQL server.

...