Hi,
We have a row level security implemented that has been working for 10+ years.
We have moved from on prem Cognos and ldap to Cognos on Cloud, with Azure AD.
The method used is parameter map for the security code column in the organization table combined with a session parameter for the user, with the security code for the user, added in a filter for the table. Combining these results in [Organization_table].[SecurityCodeColumn] in {SessionParameter}.
The issue is that Cognos isn't able to save/fetch this session parameter (from Azure AD) when running schedules. This leads all schedules to fail due to corrupt sql, as it isn't able to pass the session parameter value into the query.
We are now looking into solving the row level security in another manner, so that users can use schedules again.
By using the username-session parameter, which Cognos is able to utilize in schedules, we were thinking of using this value to filter another table, with the columns username and security code. Meaning the user security table will only return one row per run. Now my challenge it to combine the result of the security table into the organization table. How can this be done without joining the two tables? As the security table only will return a single row, I need the value in the security column to be added to the filter of the organization table.
Any advice?