Link security policies to compliance frameworks on policy change

What does this MR do and why?

This MR adds logic to link the compliance frameworks with security_orchestration_policy_configuration whenever a policy is updated/created. ComplianceManagement::ComplianceFramework::SecurityPolicy stores the mapping of security_orchestration_policy_configuration and compliance_frameworks along with the policy_index to distinguish between which policy has policy_scope

Addresses #428491 (closed)

Database query

BEGIN
  SELECT
      "compliance_framework_security_policies"."id" 
  FROM
      "compliance_framework_security_policies" 
  WHERE
      "compliance_framework_security_policies"."policy_configuration_id" = 39 
  ORDER BY
      "compliance_framework_security_policies"."id" ASC,
      "compliance_framework_security_policies"."updated_at" ASC LIMIT 1;

  SELECT
      "compliance_framework_security_policies"."id" 
  FROM
      "compliance_framework_security_policies" 
  WHERE
      "compliance_framework_security_policies"."policy_configuration_id" = 39 
      AND "compliance_framework_security_policies"."id" >= 4 
  ORDER BY
      "compliance_framework_security_policies"."id" ASC,
      "compliance_framework_security_policies"."updated_at" ASC LIMIT 1 OFFSET 1000;

  DELETE 
  FROM
      "compliance_framework_security_policies" 
  WHERE
      "compliance_framework_security_policies"."policy_configuration_id" = 39 
      AND "compliance_framework_security_policies"."id" >= 4;

  INSERT 
  INTO
      "compliance_framework_security_policies"
      ("framework_id","policy_configuration_id","policy_index","created_at","updated_at") 
  VALUES
      (2, 39, 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
      (3, 39, 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
      (2, 39, 1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP) ON CONFLICT  DO NOTHING RETURNING "id";
COMMIT 

How to set up and validate locally

1. Create a new top level group and create compliance framework from [Settings -> General -> Compliance Framework] and make note of its ID

2. Create new Policy for the the first top level group [Secure -> Policies -> Create new]

3. Choose YAML-mode in the Policy Editor

4. Add policy_scope:

   ...
   policy_scope:
     compliance_frameworks:
     - id:<ID of the other group's compliance framework>
   ...

5. Verify that a new row is created in compliance_framework_security_policies table for the configuration_id and framework_id

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

• I have evaluated the MR acceptance checklist for this MR.