MySQL vault密码存储浅尝

原创

保持热爱奔赴山海

修改于 2024-01-19 16:48:45

2390

修改于 2024-01-19 16:48:45

文章被收录于专栏：数据库相关

官方文档 https://developer.hashicorp.com/vault/docs/secrets/databases/mysql-maria

注意：这里是一个DEMO用法，如果上生产还需要再深入官方文档（开启ssl等）!

实验

会话1：

前台启动（开发模式，纯内存运行，重启数据丢失！）
$ vault server -dev
==> Vault server configuration:

             Api Address: http://127.0.0.1:8200
                     Cgo: disabled
         Cluster Address: https://127.0.0.1:8201

会话2：

1 设置会话级别参数

export VAULT_ADDR='http://127.0.0.1:8200'

2 启用数据库插件

vault secrets enable database            
返回结果：
Success! Enabled the database secrets engine at: database/

3 注册一个数据库实例到vault，名称为 my-mysql-database

【注意这里用到一个高权限的账号，需要能创建账号、删除账号、调整授权】
vault write database/config/my-mysql-database \
    plugin_name=mysql-database-plugin \
    connection_url="{{username}}:{{password}}@tcp(192.168.31.181:3306)/" \
    allowed_roles="my-role" \
    username="dts" \
    password="dts"

返回结果：
Success! Data written to: database/config/my-mysql-database

4 注册一个role到vault，role名为my-role，对应的MySQL实例为 my-mysql-database

vault write database/roles/my-role \
    db_name=my-mysql-database \
    creation_statements="CREATE USER '{{name}}'@'%' IDENTIFIED BY '{{password}}';GRANT SELECT ON *.* TO '{{name}}'@'%';" \
    default_ttl="1h" \
    max_ttl="24h"

返回结果：
Success! Data written to: database/roles/my-role

5 调用valut，执行创建账号并授权的操作

返回结果类似：
Key                Value
---                -----
lease_id           database/creds/my-role/2OOZCQTE9FleB3WP98fbTvWJ
lease_duration     1h
lease_renewable    true
password           4RfiogKJLq-PwbDkqR-Z
username           v-root-my-role-V4C4koYsq3LIJNfa5

这一步操作，实际上也就是会在数据库里面创建一个账号并做授权。可以到数据库看下账号情况，类似如下：

[(none)]> show grants for 'v-root-my-role-V4C4koYsq3LIJNfa5'@'%';
+---------------------------------------------------------------+
| Grants for v-root-my-role-V4C4koYsq3LIJNfa5@%                 |
+---------------------------------------------------------------+
| GRANT SELECT ON *.* TO `v-root-my-role-V4C4koYsq3LIJNfa5`@`%` |
+---------------------------------------------------------------+
1 row in set (0.00 sec)

6 连接测试

$ mysql -uv-root-my-role-V4C4koYsq3LIJNfa5 -p'4RfiogKJLq-PwbDkqR-Z'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 24851

其它常用的几个命令

查看所有密钥
	vault list sys/leases/lookup/database/creds/my-role

	类似结果如下：
	Keys
	----
	1V66bUzexm8kk4BxczzN3LYI
	73eHHmgdI6KqJCkqH19PaN8O
	AzPTSpP5JPFSOjkXnp9Hi60O
	EZXp2RJJbTZdyf73Qry0uJ92
	EtdiX3tD0uU3xrEd9r6jZRTG
	LuRtNOvF5U3N7Yi8TPr8btnU
	Vy5NMM1K8aTawN3PD3F3ob9N
	twLooVuf59eUChPMnd8yPLkH


查看全部的LEASE_ID
	vault list sys/leases/lookup/database/creds/my-role

查看最早的一个LEASE_ID
	LEASE_ID=$(vault list -format=json sys/leases/lookup/database/creds/my-role | jq -r ".[0]")
	echo ${LEASE_ID}

续订：
	vault lease renew database/creds/my-role/${LEASE_ID}

撤销租约：
	vault lease revoke database/creds/my-role/${LEASE_ID}

列出现有租约：
	vault list sys/leases/lookup/database/creds/my-role

撤销租约而不等待其到期
	（vault list可能看到多条记录）
	vault lease revoke database/creds/my-role/HjpLVqzbvKBK59WPmNdFS1qP  # 注意：不管LEASE_ID是否存在，valut server都会返回处理成功


如果我们把全部租约都撤销掉，则之前的账号登录就会失败，如下：
	$ mysql -uv-root-my-role-dTvsVXnDp5fJUUl6S -p'Vpn-Y5f5YMFMEc446TmU'
	mysql: [Warning] Using a password on the command line interface can be insecure.
	ERROR 1045 (28000): Access denied for user 'v-root-my-role-dTvsVXnDp5fJUUl6S'@'localhost' (using password: YES)

关于 default_ttl 和 max_ttl 的演示

vault write database/roles/my-role \
    db_name=my-mysql-database \
    creation_statements="CREATE USER '{{name}}'@'%' IDENTIFIED BY '{{password}}';GRANT SELECT ON *.* TO '{{name}}'@'%';" \
    default_ttl="5m" \
    max_ttl="10m"

说明：
default_ttl 指的是默认的ttl时间，如果期间没有续订，则5分钟后自动账号被吊销。 
max_ttl 指的是最大的ttl时间，即使续订，总的生命周期也只有10分钟。

vault read database/creds/my-role                                                                       
Key                Value
---                -----
lease_id           database/creds/my-role/M1C5geg7ZWw6jbRCOC6lrnB9
lease_duration     5m
lease_renewable    true
password           mYuV8OrfvmsjWWIv-CvT
username           v-root-my-role-OwaNauJ534actITvM


可以看下面的测试结果：
$ while true; do mysql -uv-root-my-role-OwaNauJ534actITvM  -p'mYuV8OrfvmsjWWIv-CvT' -e 'select now();select sleep(60)'; done
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------------+
| now()               |
+---------------------+
| 2024-01-18 18:24:45 |
+---------------------+
+-----------+
| sleep(60) |
+-----------+
|         0 |
+-----------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------------+
| now()               |
+---------------------+
| 2024-01-18 18:25:46 |
+---------------------+
+-----------+
| sleep(60) |
+-----------+
|         0 |
+-----------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------------+
| now()               |
+---------------------+
| 2024-01-18 18:26:46 |
+---------------------+
+-----------+
| sleep(60) |
+-----------+
|         0 |
+-----------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------------+
| now()               |
+---------------------+
| 2024-01-18 18:27:46 |
+---------------------+
+-----------+
| sleep(60) |
+-----------+
|         0 |
+-----------+


可以看到，我们没有续订的情况下，大约在5分钟后，这个账号密码就被回收掉了。

此外，如果开启了MySQL的general_log， 可以看到有如下的权限回收的操作：
2024-01-18T18:28:38.190768+08:00    24896 Query START TRANSACTION
2024-01-18T18:28:38.191071+08:00    24896 Query REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'v-root-my-role-OwaNauJ534actITvM'@'%'
2024-01-18T18:28:38.194481+08:00    24896 Query DROP USER 'v-root-my-role-OwaNauJ534actITvM'@'%'                                                                                                  
2024-01-18T18:28:38.199703+08:00    24896 Query COMMIT
2024-01-18T18:28:46.043495+08:00    25015 Quit  


此外，创建账号的时候，数据库日志记录类似如下：
2024-01-18T18:23:38.181555+08:00    24896 Query START TRANSACTION
2024-01-18T18:23:38.181983+08:00    24896 Prepare   CREATE USER 'v-root-my-role-OwaNauJ534actITvM'@'%' IDENTIFIED BY <secret>
2024-01-18T18:23:38.182124+08:00    24896 Execute   CREATE USER 'v-root-my-role-OwaNauJ534actITvM'@'%' IDENTIFIED BY <secret>
2024-01-18T18:23:38.186132+08:00    24896 Close stmt    
2024-01-18T18:23:38.186244+08:00    24896 Prepare   GRANT SELECT ON *.* TO 'v-root-my-role-OwaNauJ534actITvM'@'%'
2024-01-18T18:23:38.186442+08:00    24896 Execute   GRANT SELECT ON *.* TO 'v-root-my-role-OwaNauJ534actITvM'@'%'
2024-01-18T18:23:38.188468+08:00    24896 Close stmt    
2024-01-18T18:23:38.188527+08:00    24896 Query COMMIT

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

hashicorp-vault