user access controls

[codelphi]

To preface this is my first post and I only do so to confirm my own suspicions based upon what I have found in my own searches for answers to my current problem.

Like most IT out there I am presented with older systems and software and expected to make it work without the ability to upgrade anything. We are currently using Goldmine Corporate Edition Ver 7.00.60526.

Our company has a policy that if a potential sales lead is off limits to other salespeople as long as the salesperson in question has maintained some form of contact with the lead in the last three months. The problem lies in the fact that the salespeople (potentially - until proven otherwise) have the ability to alter entries created by other salespeople. Removing the "edit tab folders" to the salespeople solves the issue but it also makes them unable to open the entries to view their content (unless I am mistaken and if I am please tell me). I have removed the "delete" option under contact record but upon testing it would appear that they are still able to delete entries in the history tabs so I'm not sure what this actually controls. I am hoping the section under user settings/access/access to others may hold the answer but none of the texts I have found seem to mention it's usage. (btw sorry if this seems to ramble but I'm typing this while also doing work). So if I am correct in assuming that this version is incapable of restricting access in this regard, Is there instead a built in function to allow for a running log of alterations of records? If this is also a no, is anyone aware of exactly how alterations are performed between Goldmine and SQL? So for instance if I was to edit an entry in the history tab and change the date that the entry occurred on, would it do this via a update string in SQL so that I could build a trigger to log any updates to conthist where createby <> lastuser?

 

[DJHunt]

I believe that you must do this in conjunction with the use of User Sales Groups, Record Ownership, and the GoldMine Curtaining capabilities. I would start by creating a user Sales Group for each salesperson. I would then add to that group all of the Users that are permitted to edit the GoldMine records owned by this Sales Group. I would then assign Ownership of the appropriate records to each User Group, and at the same time, I would define Curtaining options.

Do this properly, and you will achieve your goal.

DJ Hunt
Phone: (978)2-3333
WebSite:

http://www.DJHunt.US

- GoldMine Premium - The Definitive Guide
- One-on-One GoldMine Technical Support ( Fee Based )

http://www.DJHunt.US

 

[codelphi]

As I was waiting for responses, I continued to search for alternate means to fixing my issue. If I utilized a INSTEAD trigger into SQL would this in effect fix my issue and not break Goldmine in the process? My theory is as follows.


For Delete functionality

CREATE TRIGGER trgInsteadOfDelete ON [dbo].[Goldmine1].[CONTHIST] 
INSTEAD OF DELETE
AS
	declare @CREATEBY varchar(8);
	declare @LASTUSER varchar(8);
        declare @RECID varchar(15);
	
	select @CREATEBY=d.CREATEBY from deleted d;
	select @LASTUSER=d.LASTUSER from deleted d;
	select @RECID=d.RECID from deleted d;

	BEGIN
		if(@CREATEBY<>@LASTUSER)
		begin
                        INSERT into Employeeaudit(RECID,LASTUSER,ACTION,TIMESTAMP) values (@RECID,@LASTUSER,'DELETE',GETDATE())
			ROLLBACK;
		end
		 else
		 begin
			 COMMIT;
		 end
 	END
GO

For Update capability

CREATE TRIGGER trgInsteadOfUpdate ON [dbo].[Goldmine1].[CONTHIST] 
INSTEAD OF UPDATE
AS

        declare @ONDATE datetime; 
	declare @LASTUSER varchar(8);
        declare @RECID varchar(15);

        select @ONDATE=u.ONDATE from updated u;
	select @LASTUSER=d.LASTUSER from updated u;
	select @RECID=d.RECID from updated u;

	BEGIN
                if(@ONDATE<(SELECT GETDATE()-1)
		begin
                        INSERT into Employeeaudit(RECID,LASTUSER,ACTION,ALTEREDDATE,TIMESTAMP) values (@RECID,@LASTUSER,'UPDATE',@ONDATE,GETDATE())
 			ROLLBACK;
		 end
		 else
		 begin
			 COMMIT;
		 end
	 END
GO

For Insert capability

CREATE TRIGGER trgInsteadOfInsert ON [dbo].[Goldmine1].[CONTHIST] 
INSTEAD OF INSERT
AS

        declare @ONDATE datetime;
        declare @CREATEON datetime; 
	declare @LASTUSER varchar(8);
        declare @RECID varchar(15);

        select @ONDATE=u.ONDATE from inserted i;
        select @CREATEON=u.CREATEON from inserted i;
	select @LASTUSER=d.LASTUSER from inserted i;
	select @RECID=d.RECID from inserted i;

	BEGIN
                if(@ONDATE<@CREATEON)
		begin
                        INSERT into Employeeaudit(RECID,LASTUSER,ACTION,ALTEREDDATE,TIMESTAMP) values (@RECID,@LASTUSER,'INSERT',@ONDATE,GETDATE())
			ROLLBACK;
		end
		else
		begin
			COMMIT;
		end
	END
GO

 

[goner]

While your triggers may accomplish your goal, you'd be better served to utilize GoldMine's own security model with record ownership.

Also note that while your triggers will work for most history, record types (CONTHIST.SRECTYPE) of M are unusual in that they link to MAILBOX records. Unexpected rollbacks of those could cause weird results/errors.

Beyond that, if you have any SYNC happening (with Outlook, remote users, handheld devices, etc.) your rollbacks will not be respected unless you also go after the CONTTLOG/GMTLOG records associated...



Doug Castell
Castell Computers

http://www.castellcomputers.com

 

[codelphi]

Am I correct in my interpretation of the Goldmine admin manual that by utilizing record ownership it would thereby make entries hidden to other users thereby achieving the inability to edit the records?

To give further insight into my situation, this company has been using this software since 2006 where all notes are available to everyone to view. After three months of inactivity of a customer, another salesman is therefore able to step in and steal the account from the former account manager and by utilizing the other peoples past notes can tailor the sales pitch to avoid known subjects that might anger the customer. I have been tasked with maintaining this form of collaborative work/back stabbing your fellow man type of business model but ensuring the integrity of the data contained within.

Also I had attempted my trigger theory as a workaround until I could implement your suggestion and found that either my understanding of trigger implementation is flawed, or that the processes Goldmine uses to implement changes is misunderstood on my part. The Delete trigger does not work at all, and the update and insert triggers block everyone regardless of what qualifiers I put in.

 

[goner]

Ownership of a record with no curtaining will allow the record to be universally visible but not editable. If I understand your requirements, this would be your best bet.

I'd say a nightly SQL job that looks for records that haven't been touched in three months and updates their ownership would be a way to release those records into the wild. Maybe that job could also put 'AVAILABLE' in the sales person field. That way, the sales people could have something to look forward to in the morning -- the race to search out the newly available customers and pounce on them before their brethren do.

To facilitate that pounce, I'd probably implement a trigger (or lookup.ini) mechanism to update the owner field when the salesperson field is updated. (to avoid a 'double pounce' at 7:59am)

Doug Castell
Castell Computers

http://www.castellcomputers.com

 

[codelphi]

ok, I have tested DJHUNT's method and it appears sound for protection assigned accounts. Is there any way to do similar with any profiles under the [public] ownership but still allow people to add notes? Also is there any way to not allow the date to be changed to a prior date as a global setting?

 

[codelphi]

okay, I altered the delete trigger into an After Delete trigger to simply make a log of any CONTHIST entries that were deleted so I can audit them as they appear. But how do I capture the userid of the person requesting the delete being that it does not update the lastuser field because it is deleting the entry?

 

[DJHunt]

Why not prevent them from deleting the ContHist record altogether with the use of a SQL Trigger?

DJ Hunt
Phone: (978)2-3333
WebSite:

http://www.DJHunt.US

- GoldMine Premium - The Definitive Guide
- One-on-One GoldMine Technical Support ( Fee Based )

http://www.DJHunt.US

 

[codelphi]

Sorry for the gap between but the last couple of days have been hectic. So my intention was to allow the person who made the note to be able to delete it if they chose but nobody else. Also I wanted to identify anyone attempting to delete another users notes for the purpose of corrective action, (we have suspected this kind of issue but with no proof we can take no action).

 

[DJHunt]

I'm thinking that this needs to be done using a SQL Trigger as well. An OnUpdate Trigger should do it. Here's a similar Trigger for Detail Records.

Issue:

Hi Guys

I created this trigger for extended details and seems to work. The extended details tab is called 'Invoice' and the only GoldMine user that can change information on this tab is 'FINANCE'.

Thought this information would be handy for people wanting to do the same thing.

Resolution: ( Not Tested - Forum User Sukh )

CREATE TRIGGER deny_invoice_update
On Contsupp
FOR UPDATE
AS
BEGIN

Declare @contact varchar (20)
Declare @lastuser varchar (20)

SET @contact = (select contact from Inserted)
SET @lastuser = (select lastuser from Inserted)

if (@contact='invoice' and @lastuser<>'FINANCE')
BEGIN
RAISERROR ('You are not authorised to change information on this tab', 16,1)
rollback transaction
END
END

I hope that this sends you in the correct direction.

DJ Hunt
Phone: (978)2-3333
WebSite:

http://www.DJHunt.US

- GoldMine Premium - The Definitive Guide
- One-on-One GoldMine Technical Support ( Fee Based )

http://www.DJHunt.US