The Vault Databases backend supports a number of popular databases for which it can create and revoke user credentials.
As part of configuring a given database secret backend, you define roles to include user creation and revocation statements, known as creation_statements and revocation_statements respectively in the query language native to the target database.
Vault then executes these statements as the configured database user to create and revoke additional database user credentials.
This article provides some detail on query execution and provides techniques for composing and encapsulating complex user creation statements, with some practical examples.
Complex User Creation Statements
While the documentation for the Oracle secret backend provides a quick start with a simple user creation statement that functions for introductory purposes, users also often want to make use of more complex statements.
Consider this example SQL user creation statement for Oracle that contains procedural logic in the form of IF and LOOP statements to ensure that the user created does not already exist in particular roles:
DECLARE
roleexist INTEGER;
schemaexist INTEGER;
profileexist INTEGER;
cursor userSchema_cur is SELECT USERNAME FROM DBA_USERS a WHERE ACCOUNT_STATUS NOT LIKE '%LOCKED%' AND USERNAME IN ( SELECT DISTINCT OWNER FROM DBA_TABLES WHERE OWNER NOT LIKE '%ORACLE%' AND OWNER NOT LIKE '%SYS%' AND TABLESPACE_NAME IS NOT NULL ) ORDER BY USERNAME;
BEGIN
SELECT COUNT(*) INTO roleexist FROM dba_roles WHERE role='READONLY';
IF (roleexist=0) THEN
EXECUTE immediate 'create role READONLY not identified';
END IF;
select count(*) into schemaexist FROM DBA_USERS a WHERE ACCOUNT_STATUS NOT LIKE '%LOCKED%' AND USERNAME IN ( SELECT DISTINCT OWNER FROM DBA_TABLES WHERE OWNER NOT LIKE '%ORACLE%' AND OWNER NOT LIKE '%SYS%' AND TABLESPACE_NAME IS NOT NULL );
IF (schemaexist<>0) THEN
For aSchema in userSchema_cur
LOOP
FOR aTab IN (SELECT table_name FROM all_tables WHERE owner=aSchema.USERNAME)
LOOP
EXECUTE IMMEDIATE 'GRANT SELECT ON ' ||aSchema.USERNAME|| '.' || aTab.table_name || ' TO readonly';
END LOOP;
END LOOP;
END IF;
SELECT COUNT(*) INTO profileexist FROM dba_profiles WHERE profile='READONLY_PROFILE';
IF (profileexist=0) THEN
EXECUTE immediate('CREATE PROFILE READONLY_PROFILE LIMIT PASSWORD_LIFE_TIME 130 PASSWORD_GRACE_TIME 5');
END IF;
EXECUTE immediate ('create user ' || {{name}} || 'identified by obar PROFILE READONLY_PROFILE');
EXECUTE immediate ('grant READONLY to ' || {{name}});
EXECUTE immediate ('grant CONNECT to ' || {{name}});
END;This goes beyond the initial example from the documentation quick start in scope and is quite a cumbersome chunk of code to pass into a vault write command line.
Sure, it can be expressed in a more compact form, but for passing into Vault creation_statements via vault write, for example, this kind of complex statement should be first encapsulated as an element of a JSON array.
Encapsulate as JSON Array
Here, we’ve taken a more compact version of the above statement and added it as a single JSON array element:
["DECLARE\nroleexist INTEGER;\nschemaexist INTEGER;\nprofileexist INTEGER;\ncursor userSchema_cur is SELECT USERNAME FROM DBA_USERS a WHERE ACCOUNT_STATUS NOT LIKE '%LOCKED%' AND USERNAME IN ( SELECT DISTINCT OWNER FROM DBA_TABLES WHERE OWNER NOT LIKE '%ORACLE%' AND OWNER NOT LIKE '%SYS%' AND TABLESPACE_NAME IS NOT NULL ) ORDER BY USERNAME;\nBEGIN\nSELECT COUNT(*) INTO roleexist FROM dba_roles WHERE role='READONLY';\nIF (roleexist=0) THEN\nEXECUTE immediate 'create role READONLY not identified';\nEND IF;\nselect count(*) into schemaexist FROM DBA_USERS a WHERE ACCOUNT_STATUS NOT LIKE '%LOCKED%' AND USERNAME IN ( SELECT DISTINCT OWNER FROM DBA_TABLES WHERE OWNER NOT LIKE '%ORACLE%' AND OWNER NOT LIKE '%SYS%' AND TABLESPACE_NAME IS NOT NULL );\nIF (schemaexist<>0) THEN For aSchema in userSchema_cur\nLOOP\nFOR aTab IN (SELECT table_name FROM all_tables WHERE owner=aSchema.USERNAME)\nLOOP\nEXECUTE IMMEDIATE 'GRANT SELECT ON ' ||aSchema.USERNAME|| '.' || aTab.table_name || ' TO readonly';\nEND LOOP;\nEND LOOP;\nEND IF;\nSELECT COUNT(*) INTO profileexist FROM dba_profiles WHERE profile='READONLY_PROFILE';\nIF (profileexist=0) THEN\nEXECUTE immediate('CREATE PROFILE READONLY_PROFILE LIMIT PASSWORD_LIFE_TIME 130 PASSWORD_GRACE_TIME 5');\nEND IF;\nEXECUTE immediate ('create user {{name}} identified by {{password}} PROFILE READONLY_PROFILE');\nEXECUTE immediate ('grant READONLY to {{name}}');\nEXECUTE immediate ('grant CONNECT to {{name}}');\nEND;"]This is the preferred way to specify complex user creation_statements for any of the database backends which do not use JSON in their native query language when writing a Vault role.
Base64 Encode Your Statements
Your statement will pass through Vault more cleanly than in its raw state if you first wrap it in a JSON array element as above, but you’ll also want to first Base64 the statement in its final form and then pass it in as creation_statements with vault write or via the HTTP API.
Using the above statements as our example yields a Base64 encoded string like this:
WyJERUNMQVJFXG5yb2xlZXhpc3QgSU5URUdFUjtcbnNjaGVtYWV4aXN0IElOVEVHRVI7XG5wcm9maWxlZXhpc3QgSU5URUdFUjtcbmN1cnNvciB1c2VyU2NoZW1hX2N1ciBpcyBTRUxFQ1QgVVNFUk5BTUUgRlJPTSBEQkFfVVNFUlMgYSBXSEVSRSBBQ0NPVU5UX1NUQVRVUyBOT1QgTElLRSAnJUxPQ0tFRCUnIEFORCBVU0VSTkFNRSBJTiAoIFNFTEVDVCBESVNUSU5DVCBPV05FUiBGUk9NIERCQV9UQUJMRVMgV0hFUkUgT1dORVIgTk9UIExJS0UgJyVPUkFDTEUlJyBBTkQgT1dORVIgTk9UIExJS0UgJyVTWVMlJyBBTkQgVEFCTEVTUEFDRV9OQU1FIElTIE5PVCBOVUxMICkgT1JERVIgQlkgVVNFUk5BTUU7XG5CRUdJTlxuU0VMRUNUIENPVU5UKCopIElOVE8gcm9sZWV4aXN0IEZST00gZGJhX3JvbGVzIFdIRVJFIHJvbGU9J1JFQURPTkxZJztcbklGIChyb2xlZXhpc3Q9MCkgVEhFTlxuRVhFQ1VURSBpbW1lZGlhdGUgJ2NyZWF0ZSByb2xlIFJFQURPTkxZIG5vdCBpZGVudGlmaWVkJztcbkVORCBJRjtcbnNlbGVjdCBjb3VudCgqKSBpbnRvIHNjaGVtYWV4aXN0IEZST00gREJBX1VTRVJTIGEgV0hFUkUgQUNDT1VOVF9TVEFUVVMgTk9UIExJS0UgJyVMT0NLRUQlJyBBTkQgVVNFUk5BTUUgSU4gKCBTRUxFQ1QgRElTVElOQ1QgT1dORVIgRlJPTSBEQkFfVEFCTEVTIFdIRVJFIE9XTkVSIE5PVCBMSUtFICclT1JBQ0xFJScgQU5EIE9XTkVSIE5PVCBMSUtFICclU1lTJScgQU5EIFRBQkxFU1BBQ0VfTkFNRSBJUyBOT1QgTlVMTCApO1xuSUYgKHNjaGVtYWV4aXN0PD4wKSBUSEVOIEZvciBhU2NoZW1hIGluIHVzZXJTY2hlbWFfY3VyXG5MT09QXG5GT1IgYVRhYiBJTiAoU0VMRUNUIHRhYmxlX25hbWUgRlJPTSBhbGxfdGFibGVzIFdIRVJFIG93bmVyPWFTY2hlbWEuVVNFUk5BTUUpXG5MT09QXG5FWEVDVVRFIElNTUVESUFURSAnR1JBTlQgU0VMRUNUIE9OICcgfHxhU2NoZW1hLlVTRVJOQU1FfHwgJy4nIHx8IGFUYWIudGFibGVfbmFtZSB8fCAnIFRPIHJlYWRvbmx5JztcbkVORCBMT09QO1xuRU5EIExPT1A7XG5FTkQgSUY7XG5TRUxFQ1QgQ09VTlQoKikgSU5UTyBwcm9maWxlZXhpc3QgRlJPTSBkYmFfcHJvZmlsZXMgV0hFUkUgcHJvZmlsZT0nUkVBRE9OTFlfUFJPRklMRSc7XG5JRiAocHJvZmlsZWV4aXN0PTApIFRIRU5cbkVYRUNVVEUgaW1tZWRpYXRlKCdDUkVBVEUgUFJPRklMRSBSRUFET05MWV9QUk9GSUxFIExJTUlUIFBBU1NXT1JEX0xJRkVfVElNRSAxMzAgUEFTU1dPUkRfR1JBQ0VfVElNRSA1Jyk7XG5FTkQgSUY7XG5FWEVDVVRFIGltbWVkaWF0ZSAoJ2NyZWF0ZSB1c2VyIHt7bmFtZX19IGlkZW50aWZpZWQgYnkge3twYXNzd29yZH19IFBST0ZJTEUgUkVBRE9OTFlfUFJPRklMRScpO1xuRVhFQ1VURSBpbW1lZGlhdGUgKCdncmFudCBSRUFET05MWSB0byB7e25hbWV9fScpO1xuRVhFQ1VURSBpbW1lZGlhdGUgKCdncmFudCBDT05ORUNUIHRvIHt7bmFtZX19Jyk7XG5FTkQ7Il0=
We can now write our role, passing in the Base64 encoded user creation statements:
$ vault write database/roles/readonly \
db_name=oracle \
creation_statements="WyJERUNMQVJFXG5yb2xlZXhpc3QgSU5URUdFUjtcbnNjaGVtYWV4aXN0IElOVEVHRVI7XG5wcm9maWxlZXhpc3QgSU5URUdFUjtcbmN1cnNvciB1c2VyU2NoZW1hX2N1ciBpcyBTRUxFQ1QgVVNFUk5BTUUgRlJPTSBEQkFfVVNFUlMgYSBXSEVSRSBBQ0NPVU5UX1NUQVRVUyBOT1QgTElLRSAnJUxPQ0tFRCUnIEFORCBVU0VSTkFNRSBJTiAoIFNFTEVDVCBESVNUSU5DVCBPV05FUiBGUk9NIERCQV9UQUJMRVMgV0hFUkUgT1dORVIgTk9UIExJS0UgJyVPUkFDTEUlJyBBTkQgT1dORVIgTk9UIExJS0UgJyVTWVMlJyBBTkQgVEFCTEVTUEFDRV9OQU1FIElTIE5PVCBOVUxMICkgT1JERVIgQlkgVVNFUk5BTUU7XG5CRUdJTlxuU0VMRUNUIENPVU5UKCopIElOVE8gcm9sZWV4aXN0IEZST00gZGJhX3JvbGVzIFdIRVJFIHJvbGU9J1JFQURPTkxZJztcbklGIChyb2xlZXhpc3Q9MCkgVEhFTlxuRVhFQ1VURSBpbW1lZGlhdGUgJ2NyZWF0ZSByb2xlIFJFQURPTkxZIG5vdCBpZGVudGlmaWVkJztcbkVORCBJRjtcbnNlbGVjdCBjb3VudCgqKSBpbnRvIHNjaGVtYWV4aXN0IEZST00gREJBX1VTRVJTIGEgV0hFUkUgQUNDT1VOVF9TVEFUVVMgTk9UIExJS0UgJyVMT0NLRUQlJyBBTkQgVVNFUk5BTUUgSU4gKCBTRUxFQ1QgRElTVElOQ1QgT1dORVIgRlJPTSBEQkFfVEFCTEVTIFdIRVJFIE9XTkVSIE5PVCBMSUtFICclT1JBQ0xFJScgQU5EIE9XTkVSIE5PVCBMSUtFICclU1lTJScgQU5EIFRBQkxFU1BBQ0VfTkFNRSBJUyBOT1QgTlVMTCApO1xuSUYgKHNjaGVtYWV4aXN0PD4wKSBUSEVOIEZvciBhU2NoZW1hIGluIHVzZXJTY2hlbWFfY3VyXG5MT09QXG5GT1IgYVRhYiBJTiAoU0VMRUNUIHRhYmxlX25hbWUgRlJPTSBhbGxfdGFibGVzIFdIRVJFIG93bmVyPWFTY2hlbWEuVVNFUk5BTUUpXG5MT09QXG5FWEVDVVRFIElNTUVESUFURSAnR1JBTlQgU0VMRUNUIE9OICcgfHxhU2NoZW1hLlVTRVJOQU1FfHwgJy4nIHx8IGFUYWIudGFibGVfbmFtZSB8fCAnIFRPIHJlYWRvbmx5JztcbkVORCBMT09QO1xuRU5EIExPT1A7XG5FTkQgSUY7XG5TRUxFQ1QgQ09VTlQoKikgSU5UTyBwcm9maWxlZXhpc3QgRlJPTSBkYmFfcHJvZmlsZXMgV0hFUkUgcHJvZmlsZT0nUkVBRE9OTFlfUFJPRklMRSc7XG5JRiAocHJvZmlsZWV4aXN0PTApIFRIRU5cbkVYRUNVVEUgaW1tZWRpYXRlKCdDUkVBVEUgUFJPRklMRSBSRUFET05MWV9QUk9GSUxFIExJTUlUIFBBU1NXT1JEX0xJRkVfVElNRSAxMzAgUEFTU1dPUkRfR1JBQ0VfVElNRSA1Jyk7XG5FTkQgSUY7XG5FWEVDVVRFIGltbWVkaWF0ZSAoJ2NyZWF0ZSB1c2VyIHt7bmFtZX19IGlkZW50aWZpZWQgYnkge3twYXNzd29yZH19IFBST0ZJTEUgUkVBRE9OTFlfUFJPRklMRScpO1xuRVhFQ1VURSBpbW1lZGlhdGUgKCdncmFudCBSRUFET05MWSB0byB7e25hbWV9fScpO1xuRVhFQ1VURSBpbW1lZGlhdGUgKCdncmFudCBDT05ORUNUIHRvIHt7bmFtZX19Jyk7XG5FTkQ7Il0=" \
default_ttl="1h" \
max_ttl="24h"
Success! Data written to: database/roles/readonlyNote that while this example uses the Vault CLI, you should follow these concepts when writing roles via the Database Secret Backend HTTP APIs as well.