Posts tagged #Authentication

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

CloudOpsKube

Tag: #Authentication

Enhancing MySQL Security: Implementing HashiCorp Vault for Secure Authentication