Summary

Automation for Jira 9.0.1 introduced the Secret Keys panel where administrators can manage secret keys used by certain Rule Actions ( JIRAAUTOSERVER-335 - Add support for secret storage Closed ).

There are some housekeeping scenarios where its necessary to search for automation rules that make use of Global Secret keys.

This KB article seeks to share database queries that will retrieve all global secret keys, and then run a query to search for automation rules that make use of such global secret keys.

Environment

Automation for Jira 9.0 and above

Solution

• Please note that the queries below were formed and tested in PSQL database. You will need to work with your DBA to be convert these to your appropriate database.

• The query below to get global secrets by picking up secrets from the AO_589059_RULE_SECRET table that are not in AO_589059_SECRET_PROJ_ASSOC. This means these secrets are not bound to specific projects only, and are accessible from any project (as long as the user has the appropriate permissions).

  (select rs."KEY" from "AO_589059_RULE_SECRET" rs where rs."ID" not in (select rs."ID" from "AO_589059_RULE_SECRET" rs 
  join "AO_589059_SECRET_PROJ_ASSOC" rspa on rs."ID"=rspa."RULE_SECRET_ID"));

• Secret keys are mentioned in the VALUE column of the AO_589059_RULE_CFG_COMPONENT rule config components table.
  Each of the global secret key match of the query above will then be used to search for hits in the AO_589059_RULE_CFG_COMPONENT rule config components table.

• Please note that all matches to secret key strings will show up, including messages that may coincidentally include the secret key string. If the VALUE starts with {"webhookUrl":{"key":" followed by the secret key, then this means the global secret key is in use here. 

  select rcfg."ID" as RuleID, rcfg."NAME" as RuleName, rcfgcomp."COMPONENT", rcfgcomp."TYPE" as ActionType, rcfgcomp."VALUE" from "AO_589059_RULE_CFG_COMPONENT" rcfgcomp 
  join "AO_589059_RULE_CONFIG" rcfg on rcfgcomp."RULE_CONFIG_ID" = rcfg."ID" 
  where rcfgcomp."VALUE" LIKE CONCAT('%','webhookUrl":{"key":"<replacewithSecretKeyName>"','%');

• A sample valid result is shown below. The secret key is GlobalKey1, which is used in an ACTION of the rules named globalkeytest and global2 with rule ID 2 and 3 respectively.

Sample results: