Prevent NAV Developers changing objects in live and test

alvi99

Hi All NAV specialists :-),

Regarding database setup: An optimal setup in my perspective would be if the Customer has a Development Database which is data updated once every 6 month or more frequently, a Test Database which is updated whenever the customer or developer (with customers approval) wants it and a Live Database. All three databases should be 100 % objects synchronized, unless when something is being tested.

In this type of setup many consultancies have one NAV super user at their Customers, they can use to log on to all three databases. Too often I experience NAV Developers doing Development in the test database and sometimes even in the live database!! They often forget to update the Development database and sometimes, if they push objects back to the Development database, they accidentally overwrite changes made by another Developer. The whole development process becomes uncertain and messy - every time you go to a customer to make changes you have to spend time comparing the objects in the three databases. When confronted the consultant are always very sorry and “will never do it again” and it only happened because he/she was extremely busy. But it does happen again!

Removing their Super rights in test and live has been tried, but this causes frustration if they need to debug, datafix, just look in the code etc. What I really would like is the possibility to prevent SAVING objects in Test and Live even if they have a developer license and super NAV rights. I am thinking there might be a setup on the SQL server somewhere? Any ideas are very welcome!

I thought about suggesting Microsoft to add a check box under company information where you can indicate it, if the database is a live database. If this check box is marked and a Developer tries to save an object in the database, an error is shown saying that he needs to remove the checkmark in order to save the object. If he chooses to remove the check mark (which I am confident he will not because he remembers that he should not save an object in live) then it will be logged in the change log and the customer responsible can check the change log every once in a while. What do you think about this idea? This approach will off course apply to both live and test since the test database always is a copy of live and the only thing changed is the system indicator.

Br,
Alvi

deV.ch

You can easily implement a SQL Trigger to prevent changes in the object table. But that means every change will lead to an error, even fob imports.
So in order to update a database with new objects you would first need to "unlock" the database by eighter dropping the trigger or if you do a smart implementation, you could create a trigger that that can be enabled by nav. (make a setup table and read the lock state in the trigger, if its locked then send an error)

Easy to implement, should take you not more then 2 hours to create such a solution.

Here is an example

CREATE Trigger LockObjectChangeTrigger ON dbo.Object AFTER UPDATE, INSERT, DELETE AS  
BEGIN  
    SET NOCOUNT ON;
	
	-- Check if locked down mode, if its in lockdown mode then show error
	IF EXISTS(SELECT * FROM dbo.DB_Lockdown WHERE LockDownMode = 1)	
	BEGIN
		ROLLBACK TRANSACTION;
		DECLARE @error as varchar(max);
		SET @error = CONCAT(char(13),char(10),char(13),char(10),char(13),char(10),char(13),char(10),'This NAV Database is in Lock Down Mode.',char(13),char(10),'You can not change any objects.',char(13),char(10),char(13),char(10));
		RAISERROR (@error, 16, 1);
		RETURN 
	END	  	
END

"DB_Lockdown" would be replaced with the NAV Table you create, and LockDownMode would be a boolean field in that setup table.

pdj

Couldn't you just add DENY permission to the developers for the Object table?

alvi99

deV.ch - thanks for your suggested solution! I am not sure the solution would work for us - if a Developer needs to go to a setup table in NAV and remove a check mark every time he imports objects (which off course happens all the time) then he would probable just leave it unmarked. Is it not possible to implement a SQL Trigger that only prevent saving objects?

alvi99

pdj: The Developers still needs to be able to access the Object Designer in test and live e.g. to do datafixes, just look in the code, debugging (not sure if access is needed here), etc...

So I guess that would not work :-(

pdj

I only meant DENY for the INSERT & UPDATE & DELETE statements. Not for the SELECT statement.
Now I come to think of it, then I doubt it will work unless you are using the Enhanced Security Model, which I doubt many sane NAV installations does :-k

alvi99

Ok I see :-) Regarding "Now I come to think of it, then I doubt it will work unless you are using the Enhanced Security Model" - can you put some more words on that. If it works without using the Enhanced Security Model it would be a good solution...

pdj

Standard security model doesn’t assign permissions directly to AD users or AD groups in the SQL Server security system. It is granted via the application role, which has access to everything, and then the NAV client uses its own layer of security configuration of roles and permissions. The enhanced security model tries to keep the permissions assigned in NAV synchronized with the permissions granted in SQL, and let SQL do the actual check of permissions.

There are several articles about standard vs. enhanced security model available:
http://msdn.microsoft.com/en-us/library/dd568725.aspx
http://dynamicsuser.net/forums/t/19622.aspx (scroll down to Dean McRrae’s reply)

DENY normally overrules a GRANT, but I don’t know if it also works with this mix of personal and application role permissions. I would suggest trying it with one of the developers and test it. If it works as expected, then you could add the DENY permissions to the Developer group.

Please let us know how it turns out :-)

alvi99

Thanks for input Peter, I will get back :-)

Yashojit_Pandhare

Can we not just use the Lock / Unlock objects functionality of NAV. :!:

alvi99

Yashojit: I don't think so. If you thought about locking all objects in live and test this will cause other problems e.g. the debugger cannot debug a locked object, and I think it will be to easy for a developer to just unlock the object and change it..

Johannes_Nielsen

I know the situation. (MS, pleeeeeease give us version control, thank you very much)

But I'm surprised that its the developers whos the problem, I would hate working in any database without access to my various NAV utilities.

But I feel like it's wrong to start taping the cockpit controls down to protect it from the pilots...

Maybe provide a course in versioning?
Once you rely on versioning, you cannot imagine doing anything without it.

example.

You change something in Dev (or even Live) database. The object goes from xx1.5 to xx1.6.
You transfer the fob to Live (or Dev if you working in Live).

If the existing object is xx1.5 and yours in xx1.6 - then everybody did their versioning =D>
If the existing object is <> xx1.5(or date/time diff) - then someone broke the versioning. ](*,) = They should stop doing what they're doing.