Debtor visibility to specific users

[Atronics]

Using membership of SY_USerGroups for staff and CN_Groups for debtors, I want to limit the debtors visible to a staff member. I.e. If user is a member of SY "Group1" then he should see only debtors of CN "Group1". Some users will be members of more than one SY group and should be able to see multiple CN group members. Any pointers would be greatly appreciated.

[Mike.Sheen]

You could do this via plugin which would have to do an awful lot of work to try and inject filters wherever a reference to the debtor table is made - this would be hard and error-prone and very brittle.

I did find a way to do this using a new feature of SQL Server 2016 - Row Level Security.

With your requirements in mind, I managed to get filtering based on user completely transparent to the software, thus making it a robust solution.

It did require me creating a SQL Login and SQL User for each Jiwa staff member, and configuring each staff member to use that SQL Login - but that's a small burden considering what you get as a result.

Attached is a SQL Script which shows how this is done on demo data, for users Ann and Anne - read the comments and follow the steps carefully and you should end up with it all working.

[Mike.Sheen]

I wasn't content with the burden of creating SQL logins / users for each Jiwa staff member, so I've written a plugin to eliminate that need.

The way it works is quite simple - when the user logs into Jiwa, it writes to a table (SY_ProcessID) the process ID (@@SPID) and the staff ID, so then the predicate function knows which staff member to apply the filter to.

The SQL Script is attached as a document to the plugin. I also modified the predicate to let users who are members of the "Admin" user group to see all debtors.

Remember - this only works on SQL 2016 or later, or the current SQL Azure database (SQL Azure (RTM) - 12.0.2000.8).

Plugin Row Level Security.xml

Sample Plugin

(33.84 KiB) Downloaded 765 times

[Atronics]

Thanks, Mike. I will give this a try... after installing SQL 2016

[Ernst]

Hiya Mike,

Great piece of work, am having a go at using it for a customer. 30 users, and about 5 or 6 branches.
Am hooking it up to the DB category1 name and the user group name.
Worked well on test, opening debtor maintenance but when I do a search in debtor maintenance. The whole thing just hangs.

Removing this bit
OR @@SPID IN (SELECT dbo.SY_ProcessID.SPID
FROM dbo.SY_UserGroups
JOIN dbo.SY_UserGroupMemberships ON dbo.SY_UserGroupMemberships.SY_UserGroups_RecID = dbo.SY_UserGroups.RecID
JOIN dbo.HR_Staff ON dbo.HR_Staff.StaffID = dbo.SY_UserGroupMemberships.HR_Staff_StaffID
JOIN dbo.SY_ProcessID ON SY_ProcessID.HR_Staff_StaffID = dbo.HR_Staff.StaffID
WHERE dbo.SY_UserGroups.Name = 'Admin')

Debtor maintenance and search worked? We would like to have some key staff that can easily see all branches.
Any ideas?

[Ernst]

OK this seems to work better. Ive added the or dbo.SY_UserGroups.Name = 'Admin', and removed the OR at the end.. Teamwork...

Code: Select all

alter FUNCTION ufn_JIWA_SecurityPredicateDebtors (@DebtorID CHAR(20))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS ufn_JIWA_SecurityPredicateDebtors_result
FROM dbo.DB_Main
JOIN dbo.DB_Category1 ON dbo.DB_Category1.Category1ID = dbo.DB_Main.Category1ID
JOIN dbo.SY_UserGroups ON dbo.SY_UserGroups.Name = dbo.DB_Category1.Description or dbo.SY_UserGroups.Name = 'Admin'
JOIN dbo.SY_UserGroupMemberships ON dbo.SY_UserGroupMemberships.SY_UserGroups_RecID = dbo.SY_UserGroups.RecID
JOIN dbo.HR_Staff ON dbo.HR_Staff.StaffID = dbo.SY_UserGroupMemberships.HR_Staff_StaffID
JOIN dbo.SY_ProcessID ON SY_ProcessID.HR_Staff_StaffID = dbo.HR_Staff.StaffID
WHERE dbo.SY_ProcessID.SPID = @@SPID
AND DB_Main.DebtorID = @DebtorID
GO

[Ernst]

OK Working Well, Even Inventory sales function is being limited.
One thing...(isnt there always one)

The reports still pick up the full debtors list. e.g sales by COGS by Debtor.

That does not make sense to me as it still has to read db_main via the so_vinv, so it should block it??

[Ernst]

OK, so every time I run a report,it runs under the admin SPID,why would it do that, and not run under the usernames SPID?

[Mike.Sheen]

OK, so every time I run a report,it runs under the admin SPID,why would it do that, and not run under the usernames SPID?

Reports have a different SPID - they do run under a different set of credentials, so a different SQL connection... I think this is an oversight in the design and instead of SPID we should be using ClientProcessID (aka hostprocess), or we should also insert into that table tracking SPID's with staff a SPID for the report connection also.

It shouldn't take too much effort to get it working correctly with reports also.

[Ernst]

"It shouldn't take too much effort to get it working correctly with reports also."

OK I like that bit, is it something I could do, or would it need JIWA to change the way it runs reports?