Version 7.6

• 1: New Features
• 2: Preview Features (experimental)
• 3: Breaking Changes
• 4: Upgrade Notes
• 5: Change Log

1 - New Features

Document and Application Permissions

The document and permissions model has undergone significant changes. Both the user interface and underlying data model has changed.

The legacy screens for managing users, groups and their permissions were often very confusing to use. The new screens attempt to make it much more intuitive.

Terminology

• Explicit / Direct - This means a permission is specifically granted to the User/Group in question.
• Inherited / Effective - This means a permission is granted to the Group that a User/Group is a member of or is granted to an ancestor Group.

Groups of Groups

Previously in Stroom it was not possible for a Group to be a member of a Group. This has been changed so now a Group’s membership can include both Users and Groups. This allows a richer permissions structure to be created.

For example you can have a Basic Users group that has limited application permissions and a Super Users group that is a member of Basic Users so it inherits all the basic permissions and adds its own set of explicit permissions.

Users Screen

A Users screen has been added to list all Stroom Users (as distinct from Accounts . This screen is only available to users that hold the Manage Users application permission. It is accessible from:

Or the key bind g , g .

---

It lists all users and allows the user to jump to various other screens relating to that user.

---

It is also possible to jump to User screen for a specific user by clicking the hover icon.

Groups Screen

A Groups screen has been added for managing User Groups and their memberships. It is accessible from:

Or the key bind g , g .

---

This screen is split into two or three panes depending on whether you have selected a User or Group in the top pane. The top pane lists all users and groups in stroom, with the icon indicating the type. In this pane you can add/edit Groups.

If you have selected a User then you will see only two panes. The bottom pane will display all the Groups that the user is a direct member of, i.e. one that they have been explicitly added to. This pane can be used to add the selected User to another Group or to remove them from a Group that they are already a member of.

If you have selected a Group in the top pane then you will see three panes.

The bottom left pane will show Groups that the selected Group is a direct member of. This pane can be used to add the selected Group to another Group or to remove them from a Group that they are already a member of.

The bottom right pane will show all direct members of the selected Group. It can be used to add/remove members, be they Users or Groups.

Application Permissions Screen

This screen has been added to manages the application level permissions that are granted to Users/Groups. It replaces the previous modal dialog screen. It is accessible from:

Or the key bind g , a .

---

This screen is split into three panes.

• Top pane - Lists the Users and Groups, depending on the selection in the Permission Visibility drop-down. The Permission Visibility has the following values:

  • Show Explicit - Shows only those Users/Groups that have at least one application permission explicitly granted to them.
  • Show Effective - Shows only those Users/Groups that have at least one application permission explicitly granted to them or to a group that they are a member of (directly or indirectly).
  • Show All - Shows all Users/Groups. The Permissions column lists all the permissions granted to the User/Group, explicitly or otherwise. Permissions that are not explicitly granted are greyed out.

• Middle pane - Lists ALL application permissions whether granted or not along with a checkbox next to each one to indicate/control the granted state. This is a three state check box:

  • Empty - The permission is not granted to the User/Group.
  • Half Ticked - The permission has been granted to a Group that the selected User/Group is a member or (directly or otherwise).
  • Ticked - The permission has been explicitly granted to the selected User/Group. This pane allows the user to modify the explicit application permissions for the selected user. If a permission is inherited from the membership of a Group, the user may make grant the permission explicitly, but they cannot remove the inherited grant except by modifying the explicit grants of the ancestor group.

• Bottom pane - Provides the detail for the currently selected permission in the middle pane. If the currently selected User/Group holds the permission indirectly, it will details which of the ancestor groups have been granted that permission explicitly.

Document Permissions

Permission Names

The permission names have changed as follows:

• Use => Use - Can use a document without being able to view it, e.g. using an Index as part of a search process, but not being able to view the Index.
• Read => View - Can see the document in the explorer tree and open it to view its contents.
• Update => Edit - Can open and edit documents.
• Delete => Delete - Can delete documents.
• Owner => Owner - Can change the permissions of the document, e.g. granting access to other Users/Groups. A document can have more than one owner. Owner will automatically be granted to a user when they create a document.

A document now has only one permission granted per user (excluding the create permissions). Previously a User/Group could be granted multiple, e.g. Use and Read. Now a User/Group can have only one permission or no permission at all, so in the previous example they would now hold View (formally Read).

Document Permissions Screen

A new screen has been added for managing document permissions. It is accessible from:

---

This screen lists all the documents that the current user has View permission on. The button can be used to filter the list of documents for making batch changes to the permissions.

The button allows you to make batch changes to the filtered list of Users/Groups.

---

The Batch Change Permissions screen provides a number of different idempotent operations (i.e. can be repeated with no change in effect) that can be performed.

For example having filtered the list, you could do a Set permission change to grant View permission to user jbloggs. jbloggs will now have View permission on all documents in that filtered list, regardless of whether they previously had a lower or higher permission.

The options for making batch changes are as follows:

• Set permission - Set a specific User/Group permission.
• Add permission to create - Add permission to create documents in the selected folders.
• Remove permission to create - Remove permission to create documents in the selected folders.
• Add permission to create any document - Add permission to create documents in the selected folders.
• Remove permission to create any document - Remove permission to create documents in the selected folders.
• Add all permissions - Add all permissions from the specified document to the selection.
• Set all permissions - Set all permissions in the selection to be exactly the same as the specified document.
• Remove all permissions for all users [DANGEROUS] - Removes all permissions for all Users/Groups.

When you click OK Stroom will present a confirmation dialog telling you how many documents will be affected by the change, giving you the opportunity to cancel.

Document Permissions Sub-Tab

The previous Document Permissions modal dialog has been replaced with a sub-tab on the document screen. It can be accessed by:

• Directly opening the document and selecting the Permissions sub-tab.
• Clicking Permissions in the explorer tree context menu.
  Permissions
• Double clicking the Document in the Document Permissions Screen.

---

This screen works in a very similar way to the Application Permissions screen.

• Top pane - Lists the Users and Groups, depending on the selection in the Permission Visibility drop-down.

  The Permission Visibility has the following values:

  • Show Explicit - Shows only those Users/Groups that have at least one document permission explicitly granted to them.
  • Show Effective - Shows only those Users/Groups that have at least one document permission explicitly granted to them or to a group that they are a member of (directly or indirectly).
  • Show All - Shows all Users/Groups regardless of whether any permission is held or not.

  The Explicit Permission column shows the permission explicitly granted to the corresponding User/Group.

  The Effective Permission column shows the permission effectively granted to the corresponding User/Group, either via explicit grant or inherited from an ancestor Group. The effective permission is what counts when Stroom makes decisions about what a User/Group can do or see.

• Bottom pane - Shows the permission details for the selected row in the top pane. It will show which ancestor Groups have been explicitly granted any inherited permissions.

Folder Permissions Sub-Tab

Folders are a special kind of document so their Permission sub-tab is slightly different to that on the Document screen.

It can be accessed by:

• Directly opening the Folder and selecting the Permissions sub-tab.
• Clicking Permissions in the explorer tree context menu.
  Permissions
• Double clicking the Folder in the Document Permissions Screen.

---

The columns are the same as for the Permissions sub-tab of the Document screen except for the addition of:

The Explicit Create Document Types column shows the document create permissions explicitly granted to the corresponding User/Group. It shows each document type as an icon. The hover tooltip will show the type name.

The Effective Create Document Types column shows the document create permissions effectively granted to the corresponding User/Group, either via explicit grant or inherited from an ancestor Group.

User/Group Profile Screen

A new screen has been added to essentially show a user profile for a User/Group. The idea is that it will show everything you might need to know about a User/Group. It is likely that more information relating to a User/Group will be added to this screen in future versions.

A user can view their own profile regardless of permissions, but to view another User or a Group the current User must hold the Manage Users application permission.

The screen is accessible from a number of places:

• The Stroom menu
  User

  User Profile
• From a hover link on the various User/Group related tables.
• From the Actions context menu on the various User/Group related tables.

The following sub-tabs are available:

• Info - Basic information such as their identifiers, name and enabled state.

---

• User Groups - Lists the Groups that this User/Group is a member of with the ability to join/leave Groups (subject to having Manage Users permission).

---

• Application Permissions - Lists all application permissions with a check box indicating the grant state. Very similar to the main Application Permissions screen, without the User/Group list pane.
  • Empty - The permission is not granted to the User/Group. The whole row is also greyed out.
  • Half Ticked - The permission has been granted to a Group that the selected User/Group is a member or (directly or otherwise).
  • Ticked - The permission has been explicitly granted to the selected User/Group.

---

• Document Permissions - Lists all the documents that the user has visibility with the explicit and inherited permission on each. The Permission Visibility drop-down has the following values:
  • Show Explicit - Shows only those documents where the User/Group has a document permission explicitly granted to them.
  • Show Effective - Shows only those documents where the User/Group has a document permission explicitly granted or inherited from an ancestor Group.
  • Show All - Shows all documents that the logged in User can see, regardless of whether any permission is held or not.

---

• Dependencies - This list various dependencies on the User/Group, e.g. a that is configured to Run As this user. It is useful in cases where a User is leaving the organisation and administrator needs to see what Stroom content depends on that user. Currently the following things can appear in the Dependencies sub-tab:
  • Pipelines that Run As the User/Group.
  • Analytic Rules that Run As the User/Group.

---

• API Keys - This lists the API Keys held by the User/Group with the ability to create/delete API Keys. Manage API Keys application permission is required to see this sub-tab and to see the logged in User’s own API Keys. Manage Users application permission is required to see this sub-tab for another User/Group.

User/Group Enable State

It is now possible to change the enabled state of a Stroom User. This is as distinct from changing the enabled state of an Account.

This is mostly useful for cases where Stroom is configured to use an external Identity Provider and an administrator wants to create the Stroom User associated with an IDP user but does not want to allow them to log in yet.

A disabled user will be unable to log in and anything running as the User (e.g. a Pipeline processor filter) will fail.

User Deletion

User/Group deletion has been improved. Deletion of a User/Group will remove them from any Groups and delete any API Keys that they hold. Any documents that are solely owned by them will then be only accessible by an administrator.

It is not possible to delete a User/Group where dependencies exist on that User/Group, e.g. a Pipeline processor filter. The Dependencies sub-tab of the User Profile screen can be used to track down these dependencies prior to deletion.

Pipeline Run As User

The permissions that a Pipeline runs with are now controlled by setting a Run As User/Group on the processor filter. It is advised to use a Group for this as it mitigates against having to change processor filters when a User leaves the organisation.

Viewing Document Dependencies

Previously, the Dependencies and Dependants items in the explorer tree context menu were only available if the logged in User had Owner permission on the selected Document. Now the User only needs View permission to see the dependences/dependants.

User Account Creation

When a Stroom User Account is created it will now create the corresponding Stroom User record. Previously this was a two step process. This is only applicable when using the internal Identity Provider .

Analytic Email Notifications

Now when a failure occurs sending an email notification for an Analytic Rule , the error will be written to the configured error Feed .

2 - Preview Features (experimental)

There are no preview features in v7.6.

3 - Breaking Changes

There are no breaking changes in v7.6.

4 - Upgrade Notes

Java Version

Stroom v7.6 requires Java 21. This is the same java version as Stroom v7.5. Ensure the Stroom and Stroom-Proxy hosts are running the latest patch release of Java v21.

Configuration File Changes

Stroom’s config.yml

The following changes have been made to the configuration file.

Added Property Trees

The following cache configuration property trees have been added.

• appPermissionIdCache.*
• docTypeIdCache.*
• userAppPermissionsCache.*
• userInfoByUuidCache.*

Removed Property Trees

The following cache configuration property trees have been removed.

• userAppPermissionsCache.*
• userByDisplayNameCache.*

Stroom-Proxy’s config.yml

No changes have been made configuration file.

Database Migrations

When Stroom boots for the first time with a new version it will run any required database migrations to bring the database schema up to the correct version.

On boot, Stroom will ensure that the migrations are only run by a single node in the cluster. This will be the node that reaches that point in the boot process first. All other nodes will wait until that is complete before proceeding with the boot process.

It is recommended however to use a single node to execute the migration. To avoid Stroom starting up and beginning processing you can use the migrage command to just migrate the database and not fully boot Stroom. See migrage command for more details.

update analytics_schema_history set checksum = '-86554219' where version = '07.06.00.405';

update processor_schema_history set checksum = '-175036745' where version = '07.06.00.305';

This release migrates all the existing document and application permission grants into new tables (prefixed by permission_). The legacy tables doc_permission and app_permission have been left untouched to allow migrated permissions to be compared against the previous state. In some future version of Stroom these tables will be removed.

Migration Scripts

For information purposes only, the following are the database migrations that will be run when upgrading to 7.6.0 from the previous minor version.

Note, the legacy module will run first (if present) then the other module will run in no particular order.

Module stroom-activity

Script V07_06_00_200__activity_pre_migration_checks.java

Path: stroom-activity/stroom-activity-impl-db/src/main/java/stroom/activity/impl/db/migration/V07_06_00_200__activity_pre_migration_checks.java

It is not possible to display the content here. The file can be viewed on : GitHub

Script V07_06_00_205__activity_user_uuid.sql

Path: stroom-activity/stroom-activity-impl-db/src/main/resources/stroom/activity/impl/db/migration/V07_06_00_205__activity_user_uuid.sql

-- ------------------------------------------------------------------------
-- Copyright 2023 Crown Copyright
--
-- Licensed under the Apache License, Version 2.0 (the "License");
-- you may not use this file except in compliance with the License.
-- You may obtain a copy of the License at
--
--     http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
-- ------------------------------------------------------------------------

-- Stop NOTE level warnings about objects (not)? existing
SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0;

DROP PROCEDURE IF EXISTS V07_06_00_205__activity_user_uuid;

DELIMITER $$

CREATE PROCEDURE V07_06_00_205__activity_user_uuid ()
BEGIN
    DECLARE object_count integer;

    SELECT COUNT(1)
    INTO object_count
    FROM information_schema.columns
    WHERE table_schema = database()
    AND table_name = 'activity'
    AND column_name = 'user_uuid';

    IF object_count = 0 THEN
        ALTER TABLE activity ADD COLUMN user_uuid varchar(255) NOT NULL;

        SELECT COUNT(1)
        INTO object_count
        FROM information_schema.tables
        WHERE table_schema = database()
        AND table_name = 'stroom_user';

        IF object_count = 1 THEN
            SET @sql_str = CONCAT(
                'UPDATE activity a, stroom_user s ',
                'SET a.user_uuid = s.uuid ',
                'WHERE a.user_id = s.name');
            PREPARE stmt FROM @sql_str;
            EXECUTE stmt;
        END IF;

        ALTER TABLE activity DROP COLUMN user_id;

    END IF;
END $$

DELIMITER ;

CALL V07_06_00_205__activity_user_uuid;

DROP PROCEDURE IF EXISTS V07_06_00_205__activity_user_uuid;

SET SQL_NOTES=@OLD_SQL_NOTES;

-- vim: set shiftwidth=4 tabstop=4 expandtab:

Module stroom-analytics

Script V07_06_00_405__execution_schedule_run_as_user_uuid.sql

Path: stroom-analytics/stroom-analytics-impl-db/src/main/resources/stroom/analytics/impl/db/migration/V07_06_00_405__execution_schedule_run_as_user_uuid.sql

-- ------------------------------------------------------------------------
-- Copyright 2023 Crown Copyright
--
-- Licensed under the Apache License, Version 2.0 (the "License");
-- you may not use this file except in compliance with the License.
-- You may obtain a copy of the License at
--
--     http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
-- ------------------------------------------------------------------------

-- Stop NOTE level warnings about objects (not)? existing
SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0;

DROP PROCEDURE IF EXISTS V07_06_00_405__execution_schedule_run_as_user_uuid;

DELIMITER $$

CREATE PROCEDURE V07_06_00_405__execution_schedule_run_as_user_uuid ()
BEGIN
    DECLARE object_count integer;

    SELECT COUNT(1)
    INTO object_count
    FROM information_schema.columns
    WHERE table_schema = database()
    AND table_name = 'execution_schedule'
    AND column_name = 'run_as_user_uuid';

    IF object_count = 0 THEN
        ALTER TABLE execution_schedule ADD COLUMN run_as_user_uuid varchar(255) DEFAULT NULL;

        -- The now legacy doc_permission table may be removed at some later point
        -- in which case we don't have to do anything
        SELECT COUNT(1)
        INTO object_count
        FROM information_schema.tables
        WHERE table_schema = database()
        AND table_name = 'doc_permission';

        IF object_count = 1 THEN
            SET @sql_str = CONCAT(
                'UPDATE execution_schedule es ',
                'INNER JOIN ( ',
                '    SELECT DISTINCT ',
                '        dp.doc_uuid, ',
                '        FIRST_VALUE(dp.user_uuid) ',
                '            OVER (PARTITION BY dp.doc_uuid ORDER BY dp.id DESC) latest_owner_uuid ',
                '    FROM doc_permission dp ',
                '    WHERE dp.permission = "Owner" ',
                ') as dpv on dpv.doc_uuid = es.doc_uuid ',
                'SET es.run_as_user_uuid = dpv.latest_owner_uuid;');
            PREPARE stmt FROM @sql_str;
            EXECUTE stmt;
        END IF;

    END IF;
END $$

DELIMITER ;

CALL V07_06_00_405__execution_schedule_run_as_user_uuid;

DROP PROCEDURE IF EXISTS V07_06_00_405__execution_schedule_run_as_user_uuid;

SET SQL_NOTES=@OLD_SQL_NOTES;

-- vim: set shiftwidth=4 tabstop=4 expandtab:

Module stroom-annotation

Script V07_06_00_100__annotation_pre_migration_checks.java

Path: stroom-annotation/stroom-annotation-impl-db/src/main/java/stroom/annotation/impl/db/migration/V07_06_00_100__annotation_pre_migration_checks.java

It is not possible to display the content here. The file can be viewed on : GitHub

Script V07_06_00_105__annotation_uuid.sql

Path: stroom-annotation/stroom-annotation-impl-db/src/main/resources/stroom/annotation/impl/db/migration/V07_06_00_105__annotation_uuid.sql

-- ------------------------------------------------------------------------
-- Copyright 2023 Crown Copyright
--
-- Licensed under the Apache License, Version 2.0 (the "License");
-- you may not use this file except in compliance with the License.
-- You may obtain a copy of the License at
--
--     http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
-- ------------------------------------------------------------------------

-- Stop NOTE level warnings about objects (not)? existing
SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0;

DROP PROCEDURE IF EXISTS V07_06_00_105_annotation;

DELIMITER $$

CREATE PROCEDURE V07_06_00_105_annotation ()
BEGIN
    DECLARE object_count integer;

    SELECT COUNT(1)
    INTO object_count
    FROM information_schema.columns
    WHERE table_schema = database()
    AND table_name = 'annotation'
    AND column_name = 'uuid';

    IF object_count = 0 THEN
        ALTER TABLE `annotation`
        ADD COLUMN `uuid` varchar(255) NOT NULL;
        UPDATE `annotation` set `uuid` = MID(UUID(),1,36);
        CREATE UNIQUE INDEX `annotation_uuid` ON `annotation` (`uuid`);
    END IF;
END $$

DELIMITER ;

CALL V07_06_00_105_annotation;

DROP PROCEDURE IF EXISTS V07_06_00_105_annotation;

SET SQL_NOTES=@OLD_SQL_NOTES;

-- vim: set shiftwidth=4 tabstop=4 expandtab:

Script V07_06_00_110__annotation_entry.sql

Path: stroom-annotation/stroom-annotation-impl-db/src/main/resources/stroom/annotation/impl/db/migration/V07_06_00_110__annotation_entry.sql

-- ------------------------------------------------------------------------
-- Copyright 2023 Crown Copyright
--
-- Licensed under the Apache License, Version 2.0 (the "License");
-- you may not use this file except in compliance with the License.
-- You may obtain a copy of the License at
--
--     http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
-- ------------------------------------------------------------------------

-- Stop NOTE level warnings about objects (not)? existing
SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0;

DROP PROCEDURE IF EXISTS V07_06_00_110__annotation_entry;

DELIMITER $$

CREATE PROCEDURE V07_06_00_110__annotation_entry ()
BEGIN
    DECLARE object_count integer;

    SELECT COUNT(1)
    INTO object_count
    FROM information_schema.columns
    WHERE table_schema = database()
    AND table_name = 'annotation_entry'
    AND column_name = 'entry_user_uuid';

    IF object_count = 0 THEN
        ALTER TABLE annotation_entry ADD COLUMN entry_user_uuid varchar(255) DEFAULT NULL;
        ALTER TABLE annotation_entry ADD COLUMN entry_time_ms bigint NOT NULL;

        SELECT COUNT(1)
        INTO object_count
        FROM information_schema.tables
        WHERE table_schema = database()
        AND table_name = 'stroom_user';

        IF object_count = 1 THEN
            -- Change create user names to entry user uuids.
            SET @sql_str = CONCAT(
                'UPDATE annotation_entry a, stroom_user s ',
                'SET a.entry_user_uuid = s.uuid ',
                'WHERE a.create_user = s.name');
            PREPARE stmt FROM @sql_str;
            EXECUTE stmt;

            -- Move all create times to entry times.
            SET @sql_str = CONCAT(
                'UPDATE annotation_entry a ',
                'SET a.entry_time_ms = a.create_time_ms');
            PREPARE stmt FROM @sql_str;
            EXECUTE stmt;

            -- Change all assignment entries to reference user UUID instead of name.
            SET @sql_str = CONCAT(
                'UPDATE annotation_entry a, stroom_user s ',
                'SET a.data = s.uuid ',
                'WHERE a.type = "Assigned" AND a.data = s.name');
            PREPARE stmt FROM @sql_str;
            EXECUTE stmt;
        END IF;

        ALTER TABLE annotation_entry DROP COLUMN version;
        ALTER TABLE annotation_entry DROP COLUMN create_time_ms;
        ALTER TABLE annotation_entry DROP COLUMN create_user;
        ALTER TABLE annotation_entry DROP COLUMN update_time_ms;
        ALTER TABLE annotation_entry DROP COLUMN update_user;

    END IF;
END $$

DELIMITER ;

CALL V07_06_00_110__annotation_entry;

DROP PROCEDURE IF EXISTS V07_06_00_110__annotation_entry;

SET SQL_NOTES=@OLD_SQL_NOTES;

-- vim: set shiftwidth=4 tabstop=4 expandtab:

Module stroom-processor

Script V07_06_00_300__processor_filter_pre_migration_checks.java

Path: stroom-processor/stroom-processor-impl-db/src/main/java/stroom/processor/impl/db/migration/V07_06_00_300__processor_filter_pre_migration_checks.java

It is not possible to display the content here. The file can be viewed on : GitHub

Script V07_06_00_305__processor_filter_run_as_user_uuid.sql

Path: stroom-processor/stroom-processor-impl-db/src/main/resources/stroom/processor/impl/db/migration/V07_06_00_305__processor_filter_run_as_user_uuid.sql

-- ------------------------------------------------------------------------
-- Copyright 2023 Crown Copyright
--
-- Licensed under the Apache License, Version 2.0 (the "License");
-- you may not use this file except in compliance with the License.
-- You may obtain a copy of the License at
--
--     http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
-- ------------------------------------------------------------------------

-- Stop NOTE level warnings about objects (not)? existing
SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0;

DROP PROCEDURE IF EXISTS V07_06_00_305__processor_filter_run_as_user_uuid;

DELIMITER $$

CREATE PROCEDURE V07_06_00_305__processor_filter_run_as_user_uuid ()
BEGIN
    DECLARE object_count integer;

    SELECT COUNT(1)
    INTO object_count
    FROM information_schema.columns
    WHERE table_schema = database()
    AND table_name = 'processor_filter'
    AND column_name = 'run_as_user_uuid';

    IF object_count = 0 THEN
        ALTER TABLE processor_filter ADD COLUMN run_as_user_uuid varchar(255) DEFAULT NULL;

        SELECT COUNT(1)
        INTO object_count
        FROM information_schema.tables
        WHERE table_schema = database()
        AND table_name = 'doc_permission';

        IF object_count = 1 THEN
            SET @sql_str = CONCAT(
                'UPDATE processor_filter pf ',
                'INNER JOIN ( ',
                '    SELECT DISTINCT ',
                '        dp.doc_uuid, ',
                '        FIRST_VALUE(dp.user_uuid) '
                '            OVER (PARTITION BY dp.doc_uuid ORDER BY dp.id DESC) latest_owner_uuid ',
                '    FROM doc_permission dp ',
                '    WHERE dp.permission = "Owner" ',
                ') as dpv on dpv.doc_uuid = pf.uuid ',
                'SET pf.run_as_user_uuid = dpv.latest_owner_uuid; ');
            PREPARE stmt FROM @sql_str;
            EXECUTE stmt;
        END IF;

    END IF;
END $$

DELIMITER ;

CALL V07_06_00_305__processor_filter_run_as_user_uuid;

DROP PROCEDURE IF EXISTS V07_06_00_305__processor_filter_run_as_user_uuid;

SET SQL_NOTES=@OLD_SQL_NOTES;

-- vim: set shiftwidth=4 tabstop=4 expandtab:

Module stroom-security

Script V07_06_00_800__app_permission.sql

Path: stroom-security/stroom-security-impl-db/src/main/resources/stroom/security/impl/db/migration/V07_06_00_800__app_permission.sql

-- ------------------------------------------------------------------------
-- Copyright 2024 Crown Copyright
--
-- Licensed under the Apache License, Version 2.0 (the "License");
-- you may not use this file except in compliance with the License.
-- You may obtain a copy of the License at
--
--     http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
-- ------------------------------------------------------------------------

-- Stop NOTE level warnings about objects (not)? existing
SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0;

DROP TABLE IF EXISTS `permission_app`;
DROP TABLE IF EXISTS `permission_app_id`;

--
-- Create the application permission id table
--
CREATE TABLE IF NOT EXISTS `permission_app_id` (
  `id` tinyint UNSIGNED NOT NULL AUTO_INCREMENT,
  `permission` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `permission_app_id_permission_idx` (`permission`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

--
-- Add app permission names into the app permission id table.
--
INSERT INTO `permission_app_id` (`permission`)
SELECT DISTINCT(permission)
FROM app_permission;

--
-- Create the new application permission table.
--
CREATE TABLE IF NOT EXISTS `permission_app` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `user_uuid` varchar(255) NOT NULL,
  `permission_id` tinyint UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  KEY `permission_app_user_uuid` (`user_uuid`),
  UNIQUE KEY `permission_app_user_uuid_permission_id_idx` (`user_uuid`,`permission_id`),
  CONSTRAINT `permission_app_user_uuid` FOREIGN KEY (`user_uuid`) REFERENCES `stroom_user` (`uuid`),
  CONSTRAINT `permission_app_permission_id` FOREIGN KEY (`permission_id`) REFERENCES `permission_app_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

--
-- Copy permission values to the new table.
--
INSERT INTO permission_app (user_uuid, permission_id)
SELECT ap.user_uuid, pai.id
FROM app_permission ap
JOIN permission_app_id pai
ON (pai.permission = ap.permission);

SET SQL_NOTES=@OLD_SQL_NOTES;

-- vim: set shiftwidth=4 tabstop=4 expandtab:

Script V07_06_00_900__doc_permission.sql

Path: stroom-security/stroom-security-impl-db/src/main/resources/stroom/security/impl/db/migration/V07_06_00_900__doc_permission.sql

-- ------------------------------------------------------------------------
-- Copyright 2024 Crown Copyright
--
-- Licensed under the Apache License, Version 2.0 (the "License");
-- you may not use this file except in compliance with the License.
-- You may obtain a copy of the License at
--
--     http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
-- ------------------------------------------------------------------------

-- Stop NOTE level warnings about objects (not)? existing
SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0;

DROP TABLE IF EXISTS `permission_doc`;
DROP TABLE IF EXISTS `permission_doc_id`;
DROP TABLE IF EXISTS `permission_doc_create`;
DROP TABLE IF EXISTS `permission_doc_type_id`;

--
-- Create the permission id table
--
CREATE TABLE IF NOT EXISTS `permission_doc_id` (
  `id` tinyint UNSIGNED NOT NULL,
  `permission` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `permission_doc_id_permission_idx` (`permission`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

--
-- Add permission names into the id table.
--
INSERT INTO `permission_doc_id` (`id`, `permission`)
VALUES
(10, "Use"),
(20, "Read"),
(30, "Update"),
(40, "Delete"),
(50, "Owner");

--
-- Create the new permission table.
--
CREATE TABLE IF NOT EXISTS `permission_doc` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `user_uuid` varchar(255) NOT NULL,
  `doc_uuid` varchar(255) NOT NULL,
  `permission_id` tinyint UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  KEY `permission_doc_user_uuid` (`user_uuid`),
  KEY `permission_doc_doc_uuid` (`doc_uuid`),
  UNIQUE KEY `permission_doc_user_uuid_doc_uuid_idx` (`user_uuid`,`doc_uuid`),
  CONSTRAINT `permission_doc_user_uuid` FOREIGN KEY (`user_uuid`) REFERENCES `stroom_user` (`uuid`),
  CONSTRAINT `permission_doc_permission_id` FOREIGN KEY (`permission_id`) REFERENCES `permission_doc_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

--
-- Copy permission values to the new table.
--
INSERT INTO permission_doc (user_uuid, doc_uuid, permission_id)
SELECT dp.user_uuid, dp.doc_uuid, MAX(pdi.id)
FROM doc_permission dp
JOIN permission_doc_id pdi
ON (pdi.permission = dp.permission)
WHERE dp.permission IN ("Owner", "Delete", "Update", "Read", "Use")
GROUP BY dp.user_uuid, dp.doc_uuid;

--
-- Modify the permission names.
--
UPDATE `permission_doc_id`
SET `permission` = "View"
WHERE `permission` = "Read";

UPDATE `permission_doc_id`
SET `permission` = "Edit"
WHERE `permission` = "Update";

--
-- Create the document type id table
--
CREATE TABLE IF NOT EXISTS `permission_doc_type_id` (
  `id` tinyint UNSIGNED NOT NULL AUTO_INCREMENT,
  `type` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `permission_doc_type_id_type_idx` (`type`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

--
-- Add document type names into the doc type id table.
--
INSERT INTO `permission_doc_type_id` (`type`)
SELECT DISTINCT(SUBSTRING(permission, 10))
FROM doc_permission
WHERE permission LIKE "Create - %";

--
-- Create the new document create permission table.
--
CREATE TABLE IF NOT EXISTS `permission_doc_create` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `user_uuid` varchar(255) NOT NULL,
  `doc_uuid` varchar(255) NOT NULL,
  `doc_type_id` tinyint UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  KEY `permission_doc_create_user_uuid` (`user_uuid`),
  KEY `permission_doc_create_doc_uuid` (`doc_uuid`),
  KEY `permission_doc_create_doc_type_id` (`doc_type_id`),
  UNIQUE KEY `permission_doc_create_user_uuid_doc_uuid_doc_type_id_idx` (`user_uuid`,`doc_uuid`, `doc_type_id`),
  CONSTRAINT `permission_doc_create_user_uuid` FOREIGN KEY (`user_uuid`) REFERENCES `stroom_user` (`uuid`),
  CONSTRAINT `permission_doc_create_doc_type_id` FOREIGN KEY (`doc_type_id`) REFERENCES `permission_doc_type_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

--
-- Copy document create permission values to the new table.
--
INSERT INTO permission_doc_create (user_uuid, doc_uuid, doc_type_id)
SELECT dp.user_uuid, dp.doc_uuid, pdti.id
FROM doc_permission dp
JOIN permission_doc_type_id pdti
ON (pdti.type = SUBSTRING(dp.permission, 10))
WHERE dp.permission LIKE "Create - %";

SET SQL_NOTES=@OLD_SQL_NOTES;

-- vim: set shiftwidth=4 tabstop=4 expandtab:

Script V07_06_00_905__user_display_name.sql

Path: stroom-security/stroom-security-impl-db/src/main/resources/stroom/security/impl/db/migration/V07_06_00_905__user_display_name.sql

-- ------------------------------------------------------------------------
-- Copyright 2024 Crown Copyright
--
-- Licensed under the Apache License, Version 2.0 (the "License");
-- you may not use this file except in compliance with the License.
-- You may obtain a copy of the License at
--
--     http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
-- ------------------------------------------------------------------------

-- Stop NOTE level warnings about objects (not)? existing
SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0;

-- Idempotent
-- Ensure we always have a display_name value
UPDATE `stroom_user`
SET `display_name` = `name`
WHERE `display_name` IS NULL
OR LENGTH(`display_name`) = 0;

-- Idempotent
-- Now make the col non null so can rely on a value being there
ALTER TABLE `stroom_user` MODIFY `display_name` VARCHAR(255) NOT NULL;

SET SQL_NOTES=@OLD_SQL_NOTES;

-- vim: set shiftwidth=4 tabstop=4 expandtab:

Script V07_06_00_910__user_add_deleted_col.sql

Path: stroom-security/stroom-security-impl-db/src/main/resources/stroom/security/impl/db/migration/V07_06_00_910__user_add_deleted_col.sql

-- ------------------------------------------------------------------------
-- Copyright 2020 Crown Copyright
--
-- Licensed under the Apache License, Version 2.0 (the "License");
-- you may not use this file except in compliance with the License.
-- You may obtain a copy of the License at
--
--     http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
-- ------------------------------------------------------------------------

-- Stop NOTE level warnings about objects (not)? existing
SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0;

-- --------------------------------------------------

-- An archive of the last known values of name, display_name, full_name and is_group for a given
-- uuid. No constraint on name to allow for stroom_user records being deleted and re-used with
-- a different uuid.
CREATE TABLE IF NOT EXISTS `stroom_user_archive` (
  `id` int NOT NULL AUTO_INCREMENT,
  `uuid` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `display_name` varchar(255) NOT NULL,
  `full_name` varchar(255) DEFAULT NULL,
  `is_group` tinyint NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `stroom_user_archive_uuid_idx` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Idempotent
-- Populate the new table based on what we currently have in the stroom_user table
INSERT INTO stroom_user_archive (
    uuid,
    name,
    display_name,
    full_name,
    is_group)
SELECT
    su.uuid,
    su.name,
    su.display_name,
    su.full_name,
    su.is_group
FROM stroom_user su
ON DUPLICATE KEY UPDATE
    uuid = su.uuid,
    name = su.name,
    display_name = su.display_name,
    full_name = su.full_name,
    is_group = su.is_group;

SET SQL_NOTES=@OLD_SQL_NOTES;

-- vim: set tabstop=4 shiftwidth=4 expandtab:

Script V07_06_00_915__drop_foreign_keys_to_stroom_user.sql

Path: stroom-security/stroom-security-impl-db/src/main/resources/stroom/security/impl/db/migration/V07_06_00_915__drop_foreign_keys_to_stroom_user.sql

-- ------------------------------------------------------------------------
-- Copyright 2020 Crown Copyright
--
-- Licensed under the Apache License, Version 2.0 (the "License");
-- you may not use this file except in compliance with the License.
-- You may obtain a copy of the License at
--
--     http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
-- ------------------------------------------------------------------------

-- Stop NOTE level warnings about objects (not)? existing
SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0;

-- --------------------------------------------------

DELIMITER $$

-- --------------------------------------------------

DROP PROCEDURE IF EXISTS security_run_sql_v1 $$

-- DO NOT change this without reading the header!
CREATE PROCEDURE security_run_sql_v1 (
    p_sql_stmt varchar(1000)
)
BEGIN

    SET @sqlstmt = p_sql_stmt;

    SELECT CONCAT('Running sql: ', @sqlstmt);

    PREPARE stmt FROM @sqlstmt;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END $$

-- --------------------------------------------------

DROP PROCEDURE IF EXISTS security_drop_constraint_v1 $$

-- e.g. security_drop_constraint_v1('MY_TABLE', 'MY_FK', 'FOREIGN KEY');
--      security_drop_constraint_v1('MY_TABLE', 'MY_UNIQ_IDX', 'INDEX');
--      security_drop_constraint_v1('MY_TABLE', 'PRIMARY', 'INDEX');
-- DO NOT change this without reading the header!
CREATE PROCEDURE security_drop_constraint_v1 (
    p_table_name varchar(64),
    p_constraint_name varchar(64),
    p_constraint_type varchar(64) -- e.g. FOREIGN KEY | UNIQUE
)
BEGIN
    DECLARE object_count integer;

    SELECT COUNT(1)
    INTO object_count
    FROM information_schema.table_constraints
    WHERE table_schema = database()
    AND table_name = p_table_name
    AND constraint_name = p_constraint_name;

    IF object_count = 0 THEN
        SELECT CONCAT(
            'Constraint ',
            p_constraint_name,
            ' does not exist on table ',
            database(),
            '.',
            p_table_name);
    ELSE
        CALL security_run_sql_v1(CONCAT(
            'alter table ', database(), '.', p_table_name,
            ' drop ', p_constraint_type, ' ', p_constraint_name));
    END IF;
END $$

DELIMITER ;

-- --------------------------------------------------

CALL security_drop_constraint_v1(
    'app_permission',
    'app_permission_user_uuid',
    'FOREIGN KEY');

CALL security_drop_constraint_v1(
    'doc_permission',
    'doc_permission_fk_user_uuid',
    'FOREIGN KEY');

-- --------------------------------------------------

DROP PROCEDURE IF EXISTS security_run_sql_v1;

DROP PROCEDURE IF EXISTS security_drop_constraint_v1;

-- --------------------------------------------------

SET SQL_NOTES=@OLD_SQL_NOTES;

-- vim: set tabstop=4 shiftwidth=4 expandtab:

5 - Change Log

• Issue #4671 : Remove foreign key constraints from the legacy (app|doc)_permission tables to stroom_user to fix user deletion.

• Issue #4670 : Fix display of disabled users in multiple permission related screens.

• Issue #4659 : Fix refresh selection changes after adding/removing users to/from groups.

• Issue #4594 : Various changes to the permissions screens. Added a new User screen to show all a user’s permissions, api keys, and dependencies. Added links between the various permission and user screens. Improved the tables of some of the permissions screens.

• Fix java.lang.NoClassDeffoundError: jakarta/el/ELManager error when booting proxy.

• Fix error when creating a document as a user without Administrator or Manager Users.

• Issue #4588 : Fix the API allowing documents to be moved with only VIEW permission. The UI requires EDIT permission. The API is now in line with that.

• Fix the Copy As menu item for ancestor folders that the user does not have VIEW permission on. For these cases, the Copy As sub menu now only displays the Copy as name entry.

• Change the explorer context menu to include the entries for Dependencies and Dependants if the user has at least VIEW permission. Previously required OWNER.

• Issue #4586 : Fix error when changing filter on Document Permissions Report.

• Make account creation also create a stroom user. Make an update to an account also update the stroom user if the full name has changed.

• Fix bug in DB migration V07_06_00_100__annotation_pre_migration_checks.

• If you are upgrading from a previous v7.6 beta release you will need to run the following SQL. update analytics_schema_history set checksum = '-86554219' where version = '07.06.00.405'; and update processor_schema_history set checksum = '-175036745' where version = '07.06.00.305';.

• Issue #4550 : Fix datasource already in use issue.

• Uplift docker image JDK to eclipse-temurin:21.0.5_11-jdk-alpine.

• Issue #4580 : Auto add a permission user when an account is created.

• Issue #4582 : Show all users by default and not just ones with explicit permissions.

• Issue #4345 : Write analytic email notification failures to the analytic error feed.

• Issue #4379 : Improve Stroom permission model.

For a detailed list of all the changes in v7.6 see: v7.6 CHANGELOG