Queries to find encrypted values in Bamboo database
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
Bamboo encrypts the password variables (those that include keywords "secret" and "password"), SSH Keys and Shared credentials / repository credentials and saves them in the database using the system wide encryption feature:
The encrypted values that are stored in the database are prefixed with BAMSCRT. Below are the SQL queries to list all the encrypted fields in a Bamboo database:
Solution
These SQLs are tested in a PostgreSQL database.
List of Plan Repositories
1
2
3
4
5
6
7
8
9
select pln.FULL_KEY "Plan Full Key",
pln.DESCRIPTION "Description",
cvcs.NAME "Repository Name",
vcs.IS_GLOBAL "Linked Repository"
from BUILD pln
join PLAN_VCS_LOCATION pvl on pln.BUILD_ID = pvl.PLAN_ID
JOIN VCS_LOCATION cvcs on pvl.VCS_LOCATION_ID = cvcs.VCS_LOCATION_ID
join VCS_LOCATION vcs on pvl.VCS_LOCATION_ID = cvcs.VCS_LOCATION_ID
where vcs.PARENT_ID IS NULL and pln.BUILD_TYPE ='CHAIN' and vcs.XML_DEFINITION_DATA like '%BAMSCRT%';List of all the plans got Password related Variables
1
2
3
4
5
6
7
8
select VARIABLE_KEY, b.TITLE "PLAN TITLE", b.FULL_KEY, p.TITLE "PROJECT TITLE"
from VARIABLE_DEFINITION vd
join BUILD b on b.BUILD_ID =vd.PLAN_ID
join PROJECT p on p.PROJECT_ID = b.PROJECT_ID
where VARIABLE_KEY LIKE '%password%'
OR VARIABLE_KEY LIKE '%passphrase%'
OR VARIABLE_KEY LIKE '%secret%'
OR VARIABLE_KEY LIKE '%sshkey%'; List of all plans has encrypted values saved inside Tasks
1
2
3
4
5
6
7
8
select p.TITLE "Project" ,b.TITLE, b.FULL_KEY , b.PROJECT_ID, *
from BUILD_DEFINITION bd
join BUILD b on b.BUILD_ID = bd.BUILD_ID
join PROJECT p on p.PROJECT_ID = b.PROJECT_ID
where
bd.XML_DEFINITION_DATA like '%BAMSC%'
;List of all Shared Credentials
1
select * from CREDENTIALS c List encrypted variables in deployment plans
1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT dp.NAME "Project_name",
env.NAME " Deployment_Environment",
vd.variable_key,
vd.variable_value
FROM deployment_environment env
JOIN variable_definition vd
ON env.environment_id = vd.environment_id
JOIN deployment_project dp
ON dp.deployment_project_id = env.package_definition_id
WHERE vd.variable_key LIKE '%password%'
OR variable_key LIKE '%passphrase%'
OR variable_key LIKE '%secret%'
OR variable_key LIKE '%sshkey%'; List of Deployment task
1
2
3
4
5
SELECT environment_id,
name,
description
FROM deployment_environment
WHERE xml_definition_data LIKE '%BAMSCRT%'; Was this helpful?