Introduction Securing database credentials is a critical aspect of protecting your data infrastructure. HashiCorp Vault offers a sophisticated solution for managing secrets and sensitive information, providing dynamic access controls to databases. This guide will demonstrate how to integrate HashiCorp Vault with a MySQL database to enhance security through dynamic credential management.
Prerequisites Before proceeding, ensure you have the following:
i) A MySQL database instance up and running. ii) A HashiCorp Vault instance installed and running. iii) Basic familiarity with MySQL and Vault operations. iv) Appropriate permissions to configure both Vault and MySQL.
Install and Configure HashiCorp Vault
root@06432e7f921c:~# apt update;apt install snap;snap install vault
Hit:1 http://ap-south-1.ec2.archive.ubuntu.com/ubuntu focal InRelease
Hit:2 http://ap-south-1.ec2.archive.ubuntu.com/ubuntu focal-updates InRelease
Hit:3 http://ap-south-1.ec2.archive.ubuntu.com/ubuntu focal-backports InRelease
Hit:4 http://security.ubuntu.com/ubuntu focal-security InRelease
Start Vault Server and Login to Vault
root@06432e7f921c:~# vault server -dev -dev-listen-address=0.0.0.0:8200 &
root@06432e7f921c:~# export VAULT_ADDR='http://0.0.0.0:8200'
root@06432e7f921c:~# vault login
Token (will be hidden):
Success! You are now authenticated. The token information displayed below
is already stored in the token helper. You do NOT need to run "vault login"
again. Future Vault requests will automatically use this token.
Key Value
--- -----
token hvs.sxxxxxxxxxxxxxxxxxxxxxxxx
token_accessor 2xxxxxxxxxxxxxxxxxxxxxxx
token_duration ∞
token_renewable false
token_policies ["root"]
identity_policies []
policies ["root"]
Install MariaDB Database
root@06432e7f922c:/home/cloud_user# apt install mariadb-server mariadb-client
Setting up libconfig-inifiles-perl (3.000002-1) ...
Setting up libcgi-pm-perl (4.46-1) ...
Setting up libhtml-template-perl (2.97-1) ...
Setting up libsnappy1v5:amd64 (1.1.8-1build1) ...
Setting up socat (1.7.3.3-2) ...
Setting up mariadb-server-core-10.3 (1:10.3.39-0ubuntu0.20.04.2) ...
Setting up galera-3 (25.3.29-1) ...
Setting up mariadb-client-core-10.3 (1:10.3.39-0ubuntu0.20.04.2) ...
Setting up libfcgi-perl (0.79-1) ...
Setting up libterm-readkey-perl (2.38-1build1) ...
Setting up libdbi-perl:amd64 (1.643-1ubuntu0.1) ...
Setting up libcgi-fast-perl (1:2.15-1) ...
Setting up mariadb-client-10.3 (1:10.3.39-0ubuntu0.20.04.2) ...
Setting up libdbd-mysql-perl:amd64 (4.050-3ubuntu0.2) ...
Setting up mariadb-server-10.3 (1:10.3.39-0ubuntu0.20.04.2) ...
Created symlink /etc/systemd/system/mysql.service → /lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/mysqld.service → /lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/multi-user.target.wants/mariadb.service → /lib/systemd/system/mariadb.service.
Setting up mariadb-client (1:10.3.39-0ubuntu0.20.04.2) ...
Setting up mariadb-server (1:10.3.39-0ubuntu0.20.04.2) ...
Processing triggers for systemd (245.4-4ubuntu3.23) ...
Processing triggers for man-db (2.9.1-1) ...
Processing triggers for libc-bin (2.31-0ubuntu9.16) ...
root@06432e7f922c:/home/cloud_user# systemctl start mariadb
root@06432e7f922c:/home/cloud_user# systemctl status mariadb
● mariadb.service - MariaDB 10.3.39 database server
Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
Active: active (running) since Mon 2024-06-03 17:35:59 UTC; 4min 24s ago
Docs: man:mysqld(8)
https://mariadb.com/kb/en/library/systemd/
Main PID: 34046 (mysqld)
Status: "Taking your SQL requests now..."
Tasks: 31 (limit: 2299)
Memory: 63.1M
CGroup: /system.slice/mariadb.service
└─34046 /usr/sbin/mysqld
Jun 03 17:35:59 06432e7f922c.mylabserver.com systemd[1]: Starting MariaDB 10.3.39 database server...
Jun 03 17:35:59 06432e7f922c.mylabserver.com systemd[1]: Started MariaDB 10.3.39 database server.
Jun 03 17:35:59 06432e7f922c.mylabserver.com /etc/mysql/debian-start[34081]: Upgrading MySQL tables if necessary.
Jun 03 17:35:59 06432e7f922c.mylabserver.com /etc/mysql/debian-start[34084]: Looking for 'mysql' as: /usr/bin/mysql
Jun 03 17:35:59 06432e7f922c.mylabserver.com /etc/mysql/debian-start[34084]: Looking for 'mysqlcheck' as: /usr/bin/mysqlcheck
Jun 03 17:35:59 06432e7f922c.mylabserver.com /etc/mysql/debian-start[34084]: This installation of MariaDB is already upgraded to 10.3.39-MariaDB.
Jun 03 17:35:59 06432e7f922c.mylabserver.com /etc/mysql/debian-start[34084]: There is no need to run mysql_upgrade again for 10.3.39-MariaDB.
Jun 03 17:35:59 06432e7f922c.mylabserver.com /etc/mysql/debian-start[34084]: You can use --force if you still want to run mysql_upgrade
Jun 03 17:35:59 06432e7f922c.mylabserver.com /etc/mysql/debian-start[34092]: Checking for insecure root accounts.
Jun 03 17:35:59 06432e7f922c.mylabserver.com /etc/mysql/debian-start[34097]: Triggering myisam-recover for all MyISAM tables and aria-recover for all Aria tables
Install Mysql
root@06432e7f922c:/home/cloud_user# mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
Enter current password for root (enter for none):
root@06432e7f922c:/home/cloud_user# mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
Enter current password for root (enter for none):
By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n] Y
... Success!
Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] Y
... Success!
By default, MariaDB comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n] Y
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n] Y
... Success!
Cleaning up...
All done! If you've completed all of the above steps, your MariaDB
installation should now be secure.
Thanks for using MariaDB!
Testing the connection
root@06432e7f922c:/home/cloud_user# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 44
Server version: 10.3.39-MariaDB-0ubuntu0.20.04.2 Ubuntu 20.04
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)]>
root@06432e7f922c:/home/cloud_user# cat /etc/mysql/mariadb.conf.d/50-server.cnf | grep bind
bind-address = 0.0.0.0
root@06432e7f922c:/home/cloud_user# systemctl restart mariadb
root@06432e7f922c:/home/cloud_user# systemctl status mariadb
● mariadb.service - MariaDB 10.3.39 database server
Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
Active: active (running) since Mon 2024-06-03 17:56:26 UTC; 6s ago
Docs: man:mysqld(8)
https://mariadb.com/kb/en/library/systemd/
Process: 34815 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
Process: 34816 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 34818 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= || VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -eq 0 ] && systemctl set-e>
Process: 34897 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 34899 ExecStartPost=/etc/mysql/debian-start (code=exited, status=0/SUCCESS)
Main PID: 34866 (mysqld)
Status: "Taking your SQL requests now..."
Tasks: 31 (limit: 2299)
Memory: 62.9M
CGroup: /system.slice/mariadb.service
└─34866 /usr/sbin/mysqld
Jun 03 17:56:26 06432e7f922c.mylabserver.com systemd[1]: Starting MariaDB 10.3.39 database server...
Jun 03 17:56:26 06432e7f922c.mylabserver.com systemd[1]: Started MariaDB 10.3.39 database server.
Jun 03 17:56:26 06432e7f922c.mylabserver.com /etc/mysql/debian-start[34901]: Upgrading MySQL tables if necessary.
Jun 03 17:56:26 06432e7f922c.mylabserver.com /etc/mysql/debian-start[34904]: Looking for 'mysql' as: /usr/bin/mysql
Jun 03 17:56:26 06432e7f922c.mylabserver.com /etc/mysql/debian-start[34904]: Looking for 'mysqlcheck' as: /usr/bin/mysqlcheck
Jun 03 17:56:26 06432e7f922c.mylabserver.com /etc/mysql/debian-start[34904]: This installation of MariaDB is already upgraded to 10.3.39-MariaDB.
Jun 03 17:56:26 06432e7f922c.mylabserver.com /etc/mysql/debian-start[34904]: There is no need to run mysql_upgrade again for 10.3.39-MariaDB.
Jun 03 17:56:26 06432e7f922c.mylabserver.com /etc/mysql/debian-start[34904]: You can use --force if you still want to run mysql_upgrade
root@06432e7f922c:/home/cloud_user# netstat -pant | grep 3306
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 34866/mysqld
Create User, Database and grant permission
root@06432e7f922c:/home/cloud_user# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 36
Server version: 10.3.39-MariaDB-0ubuntu0.20.04.2 Ubuntu 20.04
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 database book_info;
Query OK, 1 row affected (0.000 sec)
MariaDB [(none)]> create user 'vault'@'%' identified by '<your-password>';
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> grant all privileges on book_info.* to 'vault'@'%' with grant option;
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> grant create user on *.* to 'vault'@'%';
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.000 sec)
Create Table in Book_info Database and put some data
MariaDB [(none)]> use book_info
Database changed
MariaDB [book_info]> CREATE TABLE book_info (
-> id INT AUTO_INCREMENT PRIMARY KEY,
-> bookname VARCHAR(255) NOT NULL,
-> authorname VARCHAR(255) NOT NULL
-> );
Query OK, 0 rows affected (0.078 sec)
MariaDB [book_info]> INSERT INTO book_info (bookname, authorname) VALUES
-> ('To Kill a Mockingbird', 'Harper Lee'),
-> ('1984', 'George Orwell'),
-> ('Pride and Prejudice', 'Jane Austen'),
-> ('The Great Gatsby', 'F. Scott Fitzgerald'),
-> ('Moby Dick', 'Herman Melville');
Query OK, 5 rows affected (0.016 sec)
Records: 5 Duplicates: 0 Warnings: 0
MariaDB [book_info]> SELECT * FROM book_info;
+----+-----------------------+---------------------+
| id | bookname | authorname |
+----+-----------------------+---------------------+
| 1 | To Kill a Mockingbird | Harper Lee |
| 2 | 1984 | George Orwell |
| 3 | Pride and Prejudice | Jane Austen |
| 4 | The Great Gatsby | F. Scott Fitzgerald |
| 5 | Moby Dick | Herman Melville |
+----+-----------------------+---------------------+
5 rows in set (0.000 sec)
Enable Vault for Database
root@06432e7f921c:~# vault secrets enable database
2024-06-03T18:17:42.947Z [INFO] secrets.database.database_b9224b4f: initializing database rotation queue
2024-06-03T18:17:42.949Z [INFO] core: successful mount: namespace="" path=database/ type=database version="v1.16.2+builtin.vault"
Success! Enabled the database secrets engine at: database/
root@06432e7f921c:~# 2024-06-03T18:17:42.965Z [INFO] secrets.database.database_b9224b4f: populating role rotation queue
2024-06-03T18:17:42.965Z [INFO] secrets.database.database_b9224b4f: starting periodic ticker
Create Role in Vault
root@06432e7f921c:~# vault write database/roles/my-role db_name=book_info creation_statements="CREATE USER '{{name}}'@'%' IDENTIFIED BY '{{password}}'; GRANT SELECT O
N book_info.book_info TO '{{name}}'@'%';" default_ttl="1h" max_ttl="24h"
Success! Data written to: database/roles/my-role
Store Credential in vault and attach the role
root@06432e7f921c:~# vault write database/config/book_info plugin_name=mysql-database-plugin connection_url="{{username}}:{{password}}@tcp(172.31.115.254:3306)/" allowed_roles="my-role" username="vault" password='<passowd>'
Success! Data written to: database/config/book_info
Try to Generate and read the password
root@06432e7f921c:~# vault read database/creds/my-role
Key Value
--- -----
lease_id database/creds/my-role/qmJjwAzsLOuCitMCHT7G1cJW
lease_duration 1h
lease_renewable true
password jJiYrzYY8W5Rf-oRq7Bx
username v-root-my-role-RVu8N2m0O61mxty3O
** Create Policy file and apply**
root@06432e7f921c:~# cat databasepolicy.hcl
path "database/creds/book_info" {
capabilities = ["read"]
}
root@06432e7f921c:~# vault policy write dbpolicy databasepolicy.hcl
Success! Uploaded policy: dbpolicy
root@06432e7f921c:~# vault token create -policy=dbpolicy
Key Value
--- -----
token hvs.CAESILo8RAJSNiiJAigxRihFXMc9huhYL1zYdkgBl-Pgqo7XGh4KHGh2cy51cHRGY0xhWTBkN2tEMkZ3Y2R5VWw3S0k
token_accessor HOT77qOXRXUBM9PrLVAcmRCW
token_duration 768h
token_renewable true
token_policies ["dbpolicy" "default"]
identity_policies []
policies ["dbpolicy" "default"]
Access DB From Client
root@06432e7f923c:/home/cloud_user# mysql -u v-root-my-role-r4yGJElGGzndCcb6N -pmZSQLnhLpy4r-BWcn5-r -h 172.31.115.254
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 4
MariaDB [(none)]>
We are able to login to Database using Vault Creds