Flexible collections data backup/restore

[r-tome]

trafficstars

Type of change

- [ ] Bug fix
- [ ] New feature development
- [ ] Tech debt (refactoring, code cleanup, dependency upgrades, etc)
- [ ] Build/deploy pipeline (DevOps)
- [ ] Other

Objective

Create a stored procedure to run before the data migrations to backup all the Organization data. Create a second stored procedure to be ran after the migration if there is the need to restore all configurations.

Code changes

• New tables script:

  • [dbo].[FCBackupAccessAllGroups]
    • Table to store the GroupIds with AccessAll = 1
  • [dbo].[FCBackupAccessAllOrganizationUsers]
    • Table to store the OrganizationUserIds with AccessAll = 1
  • [dbo].[FCBackupOrganizationUserManagers]
    • Table to store the OrganizationUsers that were previously Manager or had Edit/Delete Assigned Collections permissions
  • [dbo].[FCBackupCollectionUserColumns]
    • Table to store the CollectionUser ReadOnly and HidePasswords values

• Backup sproc (OrganizationId as parameter):

  • Select all Organization GroupIds that have AccessAll = 1 and save them to [dbo].[FCBackupAccessAllGroups]
  • Select all OrganizationUserIds that have AccessAll = 1 and save them to [dbo].[FCBackupAccessAllOrganizationUsers]
  • Select all OrganizationUserIds that are Type = 3 (Manager) or Type = 4 (Custom) and 'Edit/Delete assigned permissions' as the only enabled permissions and save them to [dbo].[FCBackupOrganizationUserManagers]
  • Select all CollectionUser Ids, ReadOnly and HidePasswords values

• Restore sproc (OrganizationId as parameter):

  • Update all Group with AccessAll = 1 that have the GroupId present on the table [dbo].[FCBackupAccessAllGroups]
  • Update all OrganizationUsers with AccessAll = 1 that have the OrganizationUserId present on the table [dbo].[FCBackupAccessAllOrganizationUsers]
  • Update all OrganizationUsers with the backed up Type present on the table [dbo].[FCBackupOrganizationUserManagers]
  • Update all CollectionUsers with the backed up ReadOnly and HidePasswords values and set Manage = 0

Before you submit

• Please check for formatting errors (dotnet format --verify-no-changes) (required)
• If making database changes - make sure you also update Entity Framework queries and/or migrations
• Please add unit tests where it makes sense to do so (encouraged but not required)
• If this change requires a documentation update - notify the documentation team
• If this change has particular deployment requirements - notify the DevOps team

[bitwarden-bot]

Checkmarx One – Scan Summary & Details – 9b642a29-c696-4822-9656-85919e68d9d6

No New Or Fixed Issues Found

[codecov[bot]]

Codecov Report

All modified and coverable lines are covered by tests :white_check_mark:

Project coverage is 36.19%. Comparing base (9d59e4d) to head (60cdb32). Report is 15 commits behind head on main.

Additional details and impacted files

@@            Coverage Diff             @@
##             main    #3752      +/-   ##
==========================================
- Coverage   36.41%   36.19%   -0.23%     
==========================================
  Files        1158     1158              
  Lines       56074    56105      +31     
  Branches     5381     5385       +4     
==========================================
- Hits        20422    20305     -117     
- Misses      34708    34854     +146     
- Partials      944      946       +2     

:umbrella: View full report in Codecov by Sentry.
:loudspeaker: Have feedback on the report? Share it here.