Queries to find encrypted values in Bamboo database
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
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.
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%';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%'; 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%'
;select * from CREDENTIALS c 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%'; SELECT environment_id,
name,
description
FROM deployment_environment
WHERE xml_definition_data LIKE '%BAMSCRT%';