Queries to find encrypted values in Bamboo database

Still need help?

The Atlassian Community is here for you.

Ask the community


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.

List of Plan Repositories
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
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
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
select * from CREDENTIALS c 
List encrypted variables in deployment plans
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
SELECT environment_id,
       name,
       description
FROM   deployment_environment
WHERE  xml_definition_data LIKE '%BAMSCRT%'; 




Last modified on May 31, 2022

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.