Debtor visibility to specific users

[Mike.Sheen]

"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?

I don't think we'd need Jiwa to be changed at all - the SQL predicate function (ufn_JIWA_SecurityPredicateDebtors_result) would have to change, though.

A slight alteration to what I described in the approach, however - When a report is run a new connection is established by Crystal Reports to the SQL Server - so each report potentially has a different SPID.

But the report processes have the same hostprocess (Client Process ID) to the Jiwa application - so you can find the SPID of the reports by linking them via the sys.sysprocesses.hostprocess value.

So the good news is all you should need to change is the ufn_JIWA_SecurityPredicateDebtors_result function to consider sys.sysprocesses.SPID's with the same sys.sysprocesses.hostprocess as @@SPID).

Without dedicating some time to this I can't be absolutely sure this will work - and you might encounter issues with permissions to sys.sysprocesses - it depends on your environment.

[Ernst]

Had a look could be make more work and slowness tracing back to original SPID, wouldnt it be easier passing the host process ID, with Login,

And just use that for verification, Since the JIWA and the reports would all have the same process id?

[Ernst]

Message when linking to sys.sysprocesses view inside function...

Cannot schema bind table valued function 'dbo.ufn_JIWA_SecurityPredicateDebtors' because it references system object 'sys.sysprocesses'.

[Ernst]

Hi Mike,

OK have got it working for JIWA and reports now. Using the method below. Can you Pleeeease review my method, and let me know if you see any problems... VB rules.....

Adjust Table.. else you wont be able to log in.. which is a pain...
CREATE TABLE SY_ProcessID
Use SPID Char(10) NOT NULL,

PLugin
"DELETE FROM SY_ProcessID WHERE SPID = HOST_ID()";
INSERT INTO SY_ProcessID(RecID, HR_Staff_StaffID, SPID, DateTimeLoggedIn) VALUES (NewID(), @HR_Staff_StaffID, HOST_ID(), GETDATE())";

create FUNCTION [dbo].[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 -- and (user_name() = 'JiwaUser' or dbo.DB_Category1.Description = right(user_name(),4) )
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 = HOST_ID()
AND DB_Main.DebtorID = @DebtorID

Ive made UserGroups with same Number as Branch(Debtor Category1)

So you can add the user group to the staff record. As some reps have more than one branch customers.

While Branch staff would just have the one Usergroup/ Branch.

Anybody in Admin group will see all debtors.

Seems to be working so far.

[Mike.Sheen]

Can you Pleeeease review my method, and let me know if you see any problems...

Please provide the complete plugin. I don't know what you've changed, and the only way to be sure is to have a complete plugin (with the SQL script attached as a document and any deviation to the initial instructions in the description).

[Ernst]

My Golly, your a hard man to please, do I get Topic solved points for that?

[Mike.Sheen]

My Golly, your a hard man to please, do I get Topic solved points for that?

If you're going to aske me to check your work, you need to provide it!

[Ernst]

OK, I'll put that together. I'm really just following your suggestion of using the host process, and also hopefully providing this function in a format, that works in the real environment for those others who may need it..

[Ernst]

Hi Mike,

OK here is the full plugin we are using with adjusted documentation. Works with debtor maintenance and crystal reports.
One problem we have encountered is per below. When the restricted customer wants to look at inventory, The debtor Price tab causes this error. And now the user cannot open any inventory items
in inventory maintenance if a non allowed debtor has a price on it.

InvError.JPG (61.11 KiB) Viewed 3327 times

[Ernst]

Any ideas on how we could get inventory maintenance to work. Can JIWA be configured to open the Inventory maintenance screen, and not show the debtor records, that cannot be read.

Ive tried hiding the sell price tab in permissions, but it still comes up with that message.

Thanks