How to Protect Data Saved In MySQL or PostgreSQL Using CryptDB

Posted By : Aftab Alam | 29-Nov-2018

mysql

1. Introduction:- MySQL/PostgreSQL database stores data in plaintext which is the human-readable format. There are some scenarios in which data can be theft from the MySQL database. So some of them are listed below.
i. If root/super user of MySQL/PostgreSQL database gets access to MySQL/PostgreSQL database, One can read data from the database.
ii. If a hacker/cracker /competitor anyhow gets penetrated, by finding out vulnerabilities and, loop and holes of technologies, to MySQL database, One can read data from the database.

CryptDB is a system that provides more security by storing data onto MySQL/PostgreSQL Database in an encrypted format and makes queries over the same. It uses the collection of efficient SQL-aware encryption schemes. It can also chain encryption keys to user password which makes data more reliable and secure to be stored in MySQL database and data, stored in the database, can be only be decrypted by/for a particular user. If anyhow MySQL server is compromised, Only logged user data can be decrypted and rest data, belonging to users who are not logged in, would be in the encrypted format and can’t be decrypted by hacker/cracker.

1.2. How does it work:- CryptDB is installed as a service in hosting environment. It sits in between application and MySQL database server. Application server communicates with it by passing plain SQL queries and cryptDB rewrites these queries, using SQL-aware encryption schemes, in the encrypted format and MySQL Database server executes these encrypted queries to store encrypted data.

1.3. What is It Not:- It might be illusion what exactly cryptDB does
i. It doesn’t store data.
ii. It doesn’t query on database for data.
iii. It is not the library which needs to be used by the particular programming language.

1.4. What is It:- What does it exactly offer
i. It is a service which is running in hosting environment and gets connected to independently running MySQL Server.
ii. It receives plain-text SQL queries from the application server and rewrites, using some encryption algorithm, these queries and passes these queries to the MySQL database
server.

2. Requirements:- Listed below are the prerequisites to install CryptDB on Ubuntu 14.04 or 16.04

i. ruby 1.9.3p484 version and command to install ruby on ubuntu is "sudo apt-get install git ruby"
ii. gcc 4.7 version and command to install gcc on ubuntu is "sudo apt-get install gcc-4.7"
iii. bison 2.x version and command to install bison on ubuntu "sudo apt-get install bison"
iv. mysql 5.6 and lower version and command to install mysql 5.6 on ubuntu is "sudo apt-get install mysql-server-5.6"

3. Architecture Diagram:-

4. Clone Repository:- Run following command to clone CryptDB onto your local system.

i. git clone https://github.com/agribu/cryptdb.git

5. Command(s):- Some commands which are used by CryptDB from installation to bootstrap are listing below and their functioning is also explained.

5.1. CryptDB Installation:- Following command can be used to install CryptDB by executing on the terminal
sudo ./scripts/install.rb .(dot pointing to current working directory)

5.2. Bootstrap CryptDB Server:- Following command can be used to bootstrap CryptDB by executing on the terminal
sudo $EDBDIR/bins/proxy-bin/bin/mysql-proxy --plugins=proxy --event-threads=4 --max-open-files=1024 --proxy-lua-script=$EDBDIR/mysqlproxy/wrapper.lua --proxy-address=127.0.0.1:3308 --proxy-backend-addresses=localhost:3306

In the above command, --proxy-address=127.0.0.1:3308 is the address and port of CryptDB Server which will be used by application server and --proxy-backend-addresses=localhost:3306 is the address and port of MySQL-Database Server to which CryptDB would be get connected.

5.3. Connect to CryptDB through terminal:- Listed below command can be used to connect CryptDB with MySQL Database.
mysql -h127.0.0.1 -P3308 -uroot -pletmein

6. References:- Listed below out are some references.

i. https://www.youtube.com/watch?v=xsaXMUelOEA

ii. https://css.csail.mit.edu/cryptdb/
iii. https://github.com/agribu/cryptdb

7. Conclusion:- CryptDB provides more security by storing encrypted data in MySQL/PostgreSQL and making queries over encrypted data. If any unauthorized person gets access to our encrypted data in MySQL/PostgreSQL, One won't be able to make any sense from encrypted data.