How to identify which repositories are using a Shared Credential
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
The steps outlined on this article are provided AS-IS. This means we've had reports of them working for some customers — under certain circumstances — yet are not officially supported, nor can we guarantee they'll work for your specific scenario.
You may follow through and validate them on your own non-prod environments prior to production or fall back to supported alternatives if they don't work out.
We also invite you to reach out to our Community for matters that fall beyond Atlassian's scope of support!
Summary
This article provides an SQL query to identify if a shared credential is being used in any repositories.
Environment
All supported versions of Bamboo.
Solution
Please note that the queries below have been tested against a Postgres 10 version. If you use a different DBMS, such as MySQL, MSSQL Server, or Oracle, the SQL below might need adjustments to work properly with your database. For example, depending on the DBMS, you might need to switch from false to 0.
Identify shared credentials and their IDs
As the first step, run the query below to identify the list of shared credentials and their respective IDs:
SELECT
c.name,
c.credentials_ID
FROM
credentials cTake note of the ID of the credential you'd like to check.
Check if a given shared credential is being used in any repository
With the ID of the shared credential in hands, you can now check if there are any global, plan, or environment repositories that use it, by executing the following query. Please replace CREDENTIAL_ID on the second to last line with the ID you've just retrieved.
SELECT
r.vcs_location_ID AS REPOSITORY_ID,
r.name AS REPOSITORY_NAME,
r.is_global AS GLOBAL_LINKED,
b.title AS PLAN_NAME,
de.name AS ENVIRONMENT_NAME
FROM
vcs_location r
LEFT JOIN plan_vcs_location p ON p.vcs_location_ID = r.vcs_location_ID
LEFT JOIN build b ON p.plan_ID = b.build_ID
LEFT JOIN deployment_env_vcs_location e ON e.vcs_location_ID = r.vcs_location_ID
LEFT JOIN deployment_environment de ON de.environment_id = e.environment_id
WHERE
r.xml_definition_data LIKE '%<string>SHARED_CREDENTIALS</string>%'
AND r.xml_definition_data LIKE '%<string>CREDENTIAL_ID</string>%'
AND r.marked_for_deletion = 'false'As an example, if a credential's ID is 917506, the last second to last line would be:
AND r.xml_definition_data LIKE '%<string>917506</string>%'