vault database secret engine 之MySQL篇
下面演示的是动态角色。
1 启用数据库插件
代码语言：txt复制vault secrets enable database2 注册一个数据库实例到vault，名称为 my-mysql-database 【注意这里用到一个高权限的账号，需要能创建账号、删除账号、调整授权】
代码语言：txt复制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="123456"3 可选：配置了根用户后，我们强烈建议轮换用户密码，使得 Vault 以外的用户无法使用该账号：
代码语言：txt复制$ vault write -force database/rotate-root/my-databaseTIPS： 实际上就是vault连接到MySQL去修改了上面vault连接数据库账号的密码的操作，新密码只有vault知道，因此官方文档上强烈推荐在数据库中为 Vault 创建一个专户用户来管理其他数据库用户
4 注册一个role到vault，role名为my-role，对应的MySQL实例为 my-mysql-database
代码语言：txt复制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"5 编写策略文件并创建一个低权限的token
策略文件内容如下， cat mysql_db_read_policy.hcl
代码语言：txt复制path "database/creds/my-role" {
capabilities = ["read"]
}将策略提交到vault
代码语言：txt复制$ vault policy write mysql_db_read_policy ./mysql_db_read_policy.hcl
Success! Uploaded policy: mysql_db_read_policy创建一个普通的token并关联刚才创建的策略
代码语言：txt复制$ vault token create -policy="mysql_db_read_policy"
Key Value
--- -----
token hvs.CAESIGybFqnBorYi2BgNFNTSy5qXvRIivHHLC46ABd-sgJ3_Gh4KHGh2cy5RYUNnb1JkOTdzc3FMMVljUFlJc09XTGI
token_accessor epvT4nBq2iyMP2Rizf6LaTjK
token_duration 768h
token_renewable true
token_policies ["mysql_db_read_policy" "default"]
identity_policies []
policies ["mysql_db_read_policy" "default"]6 使用低权限的token登录vault，过读取搭配角色名的 /creds 端点来创建一个新的凭据
代码语言：txt复制$ vault login hvs.CAESIGybFqnBorYi2BgNFNTSy5qXvRIivHHLC46ABd-sgJ3_Gh4KHGh2cy5RYUNnb1JkOTdzc3FMMVljUFlJc09XTGI
$ vault read database/creds/my-role
返回结果类似：
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这一步操作，实际上也就是会在数据库里面创建一个账号并做授权。
下面是general_log摘录的部分内容：
代码语言：txt复制2024-06-02T11:29:13.370920 08:00 34381 Prepare CREATE USER 'v-root-my-role-0S6ARovFDezGO2RY7'@'%' IDENTIFIED BY <secret>
2024-06-02T11:29:13.371254 08:00 34381 Execute CREATE USER 'v-root-my-role-0S6ARovFDezGO2RY7'@'%' IDENTIFIED BY <secret>
2024-06-02T11:29:13.412660 08:00 34381 Close stmt
2024-06-02T11:29:13.413367 08:00 34381 Prepare GRANT SELECT ON *.* TO 'v-root-my-role-0S6ARovFDezGO2RY7'@'%'
2024-06-02T11:29:13.413608 08:00 34381 Execute GRANT SELECT ON *.* TO 'v-root-my-role-0S6ARovFDezGO2RY7'@'%'
2024-06-02T11:29:13.416956 08:00 34381 Close stmt
2024-06-02T11:29:13.417062 08:00 34381 Query COMMIT可以到数据库看下账号情况，类似如下：
代码语言：txt复制[(none)]> show grants for 'v-root-my-role-V4C4koYsq3LIJNfa5'@'%';
---------------------------------------------------------------
| Grants for v-root-my-role-V4C4koYsq3LIJNfa5@% |
---------------------------------------------------------------
| GRANT SELECT ON *.* TO `v-root-my-role-0S6ARovFDezGO2RY7`@`%` |
---------------------------------------------------------------
1 row in set (0.00 sec)关于租约的简单补充
代码语言：txt复制查看所有密钥
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}
撤销my-role的全部租约：
vault lease revoke database/creds/my-role
列出现有租约：
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 的演示
代码语言：txt复制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"
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 |
-----------
可以看到，在10分钟后，这个账号密码就被回收掉了。
此外，如果开启了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
