开源KMS之vault part5
原创
MSSQL的案例
https://lonegunmanb.github.io/essential-vault/6.%E6%9C%BA%E5%AF%86%E5%BC%95%E6%93%8E/6.3.Database_MSSQL.html
https://developer.hashicorp.com/vault/docs/secrets/databases/mssql
【推荐这篇，讲的比较好】https://developer.hashicorp.com/vault/tutorials/db-credentials/database-secrets-mssql
1 在mssql数据库创建账号和授权，再创建一个测试表写点数据
CREATE LOGIN vault_user WITH PASSWORD = 'Abcd1234';
GO
CREATE user vault_user for login vault_user;
GO
GRANT ALTER ANY LOGIN TO vault_user;
GRANT ALTER ANY USER TO vault_user;
GRANT ALTER ANY CONNECTION TO vault_user;
GRANT CONTROL ON SCHEMA::dbo TO vault_user;
EXEC sp_addrolemember db_accessadmin, vault_user;
GO
-- 创建一个库表，并写入些测试数据
create database myapp;
use myapp;
create table dbo.t1(a int);
insert into dbo.t1 select 333;
insert into dbo.t1 select 333;
insert into dbo.t1 select 333;2 启用database插件，并配置和vault的连接
$ vault secrets enable -path=database-new database # 注意，我这里的路径是自定义的，如果照抄官方文档会跑不起来
Success! Enabled the database secrets engine at: database-new/
$ vault write database-new/config/mssql-database \
plugin_name=mssql-database-plugin \
connection_url=sqlserver://{{username}}:{{password}}@192.168.31.181:2433 \
allowed_roles="readonly" \
username="vault_user" \
password="Abcd1234"
Success! Data written to: database-new/config/mssql-database3 编写授权配置文件，然后配置role映射
tee readonly.sql <<EOF
USE [myapp];
CREATE LOGIN [{{name}}] WITH PASSWORD = '{{password}}';
CREATE USER [{{name}}] FOR LOGIN [{{name}}];
EXEC sp_addrolemember db_datareader, [{{name}}];
EOF
$ vault write database-new/roles/readonly \
db_name=mssql-database \
creation_statements=@readonly.sql \
default_ttl="1h" \
max_ttl="24h"
Success! Data written to: database-new/roles/readonly要小心，如果没有设置 revocation_statement，Vault 会执行默认的吊销过程。在大型数据库中，这有可能引发连接超时。这种情况下请指定一个吊销语句。
4 编写策略文件并创建一个低权限的token
策略文件内容如下：
cat mssql_db_read_policy.hcl
path "database-new/creds/readonly" {
capabilities = ["read"]
}将策略提交到vault
$ vault policy write mssql_db_read_policy ./mssql_db_read_policy.hcl
Success! Uploaded policy: mssql_db_read_policy创建一个普通的token并关联刚才创建的策略
$ vault token create -policy="mssql_db_read_policy"
Key Value
--- -----
token hvs.CAESIGybFqnBorYi2BgNFNTSy5qXvRIivHHLC46ABd-sgJ3_Gh4KHGh2cy5RYUNnb1JkOTdzc3FMMVljUFlJc09XTGI
token_accessor epvT4nBq2iyMP2Rizf6LaTjK
token_duration 768h
token_renewable true
token_policies ["mssql_db_read_policy" "default"]
identity_policies []
policies ["mssql_db_read_policy" "default"]5 通过读取搭配角色名的 /creds 端点来创建一个新的凭据
使用上面的低权限的token登录
$ vault login hvs.CAESIGybFqnBorYi2BgNFNTSy5qXvRIivHHLC46ABd-sgJ3_Gh4KHGh2cy5RYUNnb1JkOTdzc3FMMVljUFlJc09XTGI
$ vault read database-new/creds/readonly
Key Value
--- -----
lease_id database-new/creds/readonly/UzH7OmbUu6BQda6BWIpk8m8l
lease_duration 1h
lease_renewable true
password -0gfi7jWZYYv94enppWW
username v-root-readonly-bpsFVblsxbhtE9S6pBRa-1717308782或者使用命令 vault read database-new/creds/readonly -format=json | jq -r .data 将账号密码解析成json格式（例如用在脚本里面）
在mssql的管理界面中，可以看到新添加的账号
可以使用这个账号密码登陆下mssql，实测是可以访问数据库的，并且可访问的库也是被限制在myapp下面的。
自定义密码复杂度
详见 https://developer.hashicorp.com/vault/tutorials/db-credentials/database-secrets-mssql
1、定义密码复杂度文件
tee password-policy.hcl <<EOF
length=20
rule "charset" {
charset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
min-chars = 1
}
rule "charset" {
charset = "abcdefghijklmnopqrstuvwxyz"
min-chars = 1
}
rule "charset" {
charset = "0123456789"
min-chars = 1
}
rule "charset" {
charset = "!@#$%^&*"
min-chars = 1
}
EOF2 创建一个名为 Vault 密码策略，其名称mssql与 中定义的密码策略规则相同password-policy.hcl。
$ vault write sys/policies/password/mssql-database policy=@password-policy.hcl
Success! Data written to: sys/policies/password/mssql-database3 根据密码策略生成密码mssql
$ vault read sys/policies/password/mssql-database/generate
Key Value
--- -----
password PIyWasC87V@zK%zqHNtB4 将上面创建的自定义密码策略应用到secret引擎，路径为mssql-database
$ vault write database-new/config/mssql-database password_policy="mssql-database"
Success! Data written to: database-new/config/mssql-database5 从附加了策略的数据库角色中读取凭据mssql
$ vault read database-new/creds/readonly
Key Value
--- -----
lease_id database-new/creds/readonly/vBS9z6j0cQh45DVvqiKo18IT
lease_duration 1h
lease_renewable true
password W@Moi&JR!ZAb4ZxeCTtX
username v-readonly-1717310424-SXt自定义用户名复杂度
详见 https://developer.hashicorp.com/vault/tutorials/db-credentials/database-secrets-mssql
这里演示下自定义用户名长度
1、定义用户名模板，下面的定义的含义：v-角色名称-unix时间戳-3个随机字符
vault write database-new/config/mssql-database username_template="v-{{.RoleName}}-{{unix_time}}-{{random 3}}"
Success! Data written to: database-new/config/mssql-database2、从数据库角色读取凭证readonly
$ vault read database-new/creds/readonly
Key Value
--- -----
lease_id database-new/creds/readonly/MxPpEndI5Shmwj6rYow6B3nD
lease_duration 1h
lease_renewable true
password -TEBYDUt9Xs0KXkO7YAy
username v-readonly-1717309989-W2O租约相关
查看租约
$ vault list sys/leases/lookup/database-new/creds/readonly
Keys
----
6KbQXfmRdt5ueVrLmeIWYYTa
UKDxCEIpdK1f2zuvkSsAhrvx
UzH7OmbUu6BQda6BWIpk8m8l
hEfaWgGUgEI9xXmXauXq5Kbb创建一个存储第一个租约 ID 的变量
$ LEASE_ID=$(vault list -format=json sys/leases/lookup/database-new/creds/readonly | jq -r ".[0]")
$ echo $LEASE_ID
6KbQXfmRdt5ueVrLmeIWYYTa通过传递租约 ID 来续订数据库凭证的租约
$ vault lease renew database-new/creds/readonly/$LEASE_ID
Key Value
--- -----
lease_id database-new/creds/readonly/6KbQXfmRdt5ueVrLmeIWYYTa
lease_duration 1h
lease_renewable true无需等待租约到期即可撤销租约
$ vault lease revoke database-new/creds/readonly/$LEASE_ID
All revocation operations queued successfully!
列出剩余的租约
$ vault list sys/leases/lookup/database-new/creds/readonly
Keys
----
UKDxCEIpdK1f2zuvkSsAhrvx
UzH7OmbUu6BQda6BWIpk8m8l
hEfaWgGUgEI9xXmXauXq5Kbb撤销与该路径相关的所有剩余租约database/creds/readonly
$ vault lease revoke -prefix database-new/creds/readonly # 前缀标志将所有有效租约与数据库/creds/readonly的路径前缀匹配
All revocation operations queued successfully!列出现有的租约
$ vault list sys/leases/lookup/database-new/creds/readonly
No value found at sys/leases/lookup/database-new/creds/readonlymssql profiler日志说明
这里打开profiler，可以看到创建凭据日志和撤销租约的日志。
创建凭据的日志：
核心操作包括34步：切库、创建login、创建user、调用sp_addrolemember增加授权。
撤销租约的日志：
核心操作包括3步：禁止login、删除user、删除login。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
腾讯云开发者
