Deny object changes in NAV

Waldo

I blogged about it here, but I came accross this forum, so why not just post it here as well :-).

Internally at iFacto, we have been working quite heavily on our infrastructure. And if I say "heavily" .. I mean unbefreakinglievable heavily. In a way: we threw everything out .. and we placed a lot in. But we only did this after going very deep in pre-defining about everything we could predefine: user security, SQL security, How to set up and maintain development databases, QA databases, Internal stuff, what do we back up, how much, .. . You know, the usual. And probably you also know that doing that as an IT partner, isn't that easy to do: you have to foresee IT for yourself, and for your customer's IT (at least - for its developents..), for products .. Releasemanagement, product management and sh<bleep>t … .

Anyway … it would take me too long to explain everything (although, I'm willing to expand on this if you want .. just drop me a comment). It's just .. by doing this, we created a lot of scripts, tips and tricks .. which I'm going to share over the few months of blogging..

When we were thinking about how to deny the object changes for released databases, we were thinking about how it was usually done: write some kind of trigger on the object table, and go from there.. . Or what I also heard: make a database "Read Only" in SQL Server.

But there is a much cleaner solution for doing something like this. With setting simple SQL Server Permissions, you can "Deny" object changes simply by setting GRANTing and DENYing the right permissions on the Object Table.

In our case, after setting up our new domain, we only grant permissions (all kinds of permissions) to User Groups (which is going to bite us in our *ss with NAV2013 (it seems) .. but anyway..). In this case, all users that have access to the database, are part of the windows group "RC-NAV-TECHNICAL".

Only thing we have to do, is to execute this "script" to the database that we don't want users to change objects:

GRANT CONTROL ON [dbo].[Object] TO [IFACTO\RC-NAV-TECHNICAL] 
GRANT SELECT ON [dbo].[Object] TO [IFACTO\RC-NAV-TECHNICAL] 
GRANT VIEW CHANGE TRACKING ON [dbo].[Object] TO [IFACTO\RC-NAV-TECHNICAL] 
GRANT VIEW DEFINITION ON [dbo].[Object] TO [IFACTO\RC-NAV-TECHNICAL] 
DENY ALTER ON [dbo].[Object] TO [IFACTO\RC-NAV-TECHNICAL] 
DENY DELETE ON [dbo].[Object] TO [IFACTO\RC-NAV-TECHNICAL] 
DENY INSERT ON [dbo].[Object] TO [IFACTO\RC-NAV-TECHNICAL] 
DENY REFERENCES ON [dbo].[Object] TO [IFACTO\RC-NAV-TECHNICAL] 
DENY TAKE OWNERSHIP ON [dbo].[Object] TO [IFACTO\RC-NAV-TECHNICAL] 
DENY UPDATE ON [dbo].[Object] TO [IFACTO\RC-NAV-TECHNICAL]

If you're not that familiar with T-SQL, you can use your SQL Server Management Studio to set up the permissions. Just select your database, go to security, double click the user (or group), select securables, click "Search", select "Specific Objects" and click OK. Graphically:



Next, select "Tables" from the Object Types, and try to find the Object-table:



Now, it's all about DENY-ing and GRANT-ing the different permissions you have available for your Object Table. In my opinion, this is the right setup:



When you generate a script from this, you'll see you get the same as I showed above.

So, what happens if I do try to change an object?

Well, as you would expect, you get this SQL Server Permission Error:



Same for insert or deleting an object.

I particularly like this way of denying permissions, not only because it makes most sense, but also: I'm still able to work with data in the database (post invoices and such - this won't be possible in a "Read Only" database), and I don't need complex triggers to figure out why something is denied or not.

Are there no buts?

Sure. You can only do this on the entire Object table. You have to keep that in mind when you're implementing this. For example, users won't be able to update a menu suite.

Probably most of you figured this already out. We actually never cared until our infrastructural change .. and I couldn't find a decent solution online .. so I thought: "let's share!" ;-).

Enjoy!

mdPartnerNL

Thanks for sharing. Really interested to see more of this.

Waldo

you're welcome .. and I have more