Invalid list of folders returned by the stored proc GetFoldersByPermissions

[vrittis]

Description of bug

The list of folders returned by the stored procedure GetFoldersByPermissions is incorrect

Steps to reproduce

1. Create two folders in resource management for the current portal
2. Prevent all permissions for one folder for everyone
3. Go to a urlcontrol control (eg in the HTML module) and you cannot access either folders

Current behavior

The folders are all hidden from the current user, even though at least one folder should be visible

Expected behavior

Folders without negative permissions should be visible

Error information

The problem comes from the following line in the stored procedure: AND FP.PermissionID NOT IN (SELECT DISTINCT PermissionID FROM dbo.[FolderPermission] WHERE allowaccess=0 AND (userid=@UserId OR roleid=-1 OR roleid IN (SELECT roleid FROM dbo.[UserRoles] WHERE UserID=@UserId))) It should be AND FP.PermissionID NOT IN (SELECT DISTINCT PermissionID FROM dbo.[FolderPermission] WHERE FolderID = FP.FolderID AND allowaccess=0 AND (userid=@UserId OR roleid=-1 OR roleid IN (SELECT roleid FROM dbo.[UserRoles] WHERE UserID=@UserId)))

Affected version

• [X] 09.08.01 and upper

Affected browser

All browsers are affected

[yog-it]

@vrittis if you run the 9.10.3 SQL provider script in your SQL console (non-production) does it fix this issue? https://github.com/dnnsoftware/Dnn.Platform/blob/develop/DNN%20Platform/Website/Providers/DataProviders/SqlDataProvider/09.10.03.SqlDataProvider

[stale[bot]]

We have detected this issue has not had any activity during the last 90 days. That could mean this issue is no longer relevant and/or nobody has found the necessary time to address the issue. We are trying to keep the list of open issues limited to those issues that are relevant to the majority and to close the ones that have become 'stale' (inactive). If no further activity is detected within the next 14 days, the issue will be closed automatically. If new comments are are posted and/or a solution (pull request) is submitted for review that references this issue, the issue will not be closed. Closed issues can be reopened at any time in the future. Please remember those participating in this open source project are volunteers trying to help others and creating a better DNN Platform for all. Thank you for your continued involvement and contributions!

[stale[bot]]

We have detected this issue has not had any activity during the last 90 days. That could mean this issue is no longer relevant and/or nobody has found the necessary time to address the issue. We are trying to keep the list of open issues limited to those issues that are relevant to the majority and to close the ones that have become 'stale' (inactive). If no further activity is detected within the next 14 days, the issue will be closed automatically. If new comments are are posted and/or a solution (pull request) is submitted for review that references this issue, the issue will not be closed. Closed issues can be reopened at any time in the future. Please remember those participating in this open source project are volunteers trying to help others and creating a better DNN Platform for all. Thank you for your continued involvement and contributions!