April 30, 2008 at 3:49 pm
Lynn (4/30/2008)
Creating a new database would involve going back and making changes to 100+ servers which cannot be done at this time. This particular db exists on all servers which is why I am looking for an alternate way to restrict access to the tables while giving full access to everything else.
I'd try Brian's DDL trigger idea then. Of course you should test the bejeebers out of it. I cannot imagine what kind of mayhem a bad DDL trigger script slipstreamed into 100 sites could cause.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 1, 2008 at 7:18 am
I've got the DDL trigger protecting schema changes but what about data changes? Aside of creating an instead of trigger on each table, how else can I restrict the developers to read only access?
May 1, 2008 at 8:20 am
Personally, I don't allow developers to have write access to anything in production... violates all sorts of merchant agreements and PCI standards not to mention a total lack of any kind of configuration control. I know... not the answer that you wanted... but, taking control and establishing the proper limits on developers and other individuals would be the right thing to do.
--Jeff Moden
May 1, 2008 at 9:08 am
I have to agree with Jeff here. You *could* use DML triggers to achieve the same effect for data, but I really think that you would be buying yourself a world of problems in terms of maintenance and reliability. I strongly recommend that you either:
1) Take away DBO rights from the developers and then just grant them only the rights that they need (and should have),
or 2) Put the data in a separate database that they only have read-access to and use cross-database views to make this transparent to the developers and other db objects.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 1, 2008 at 10:26 am
Just to clarify, this issue only applies to Dev,QA, and UAT environments, not production.
Option 2 is my backup plan. I would have like to go with option 1 if I had a way
to set up security for the developers nt group as follows:
1) take away dbo
2) explicitly give them create, alter, drop objects in db
3) Deny insert, update, delete on config tables (225 of them)
4) Deny alter, drop on config tables (DDL triggers can do that)
What do you think?
May 1, 2008 at 12:34 pm
Just to clarify, the issue only applies to my dev, qa and uat environments, not production.
Option 2 is my backup plan. However, I was interested in implementing option 1 if only I
could figure out a way to set up the NT group as follows:
Take away DBO
Give create, alter, drop objects in db
Give insert, update, delete from objects in db
Deny alter, and drop for replicated tables (DDL trigger does this)
Deny insert, update, delete for replicated tables.
Give read only access to replicated tables.
May 1, 2008 at 1:04 pm
Lynn (5/1/2008)
Just to clarify, the issue only applies to my dev, qa and uat environments, not production.Option 2 is my backup plan. However, I was interested in implementing option 1 if only I
could figure out a way to set up the NT group as follows:
Take away DBO
Give create, alter, drop objects in db
Give insert, update, delete from objects in db
Deny alter, and drop for replicated tables (DDL trigger does this)
Deny insert, update, delete for replicated tables.
Give read only access to replicated tables.
The problem is - you really don't want an unlocked QA or UAT area either, since you want those to be as close to production as you can possibly get them. How reliable is your user testing if devs have the ability to go in and "tweak" the config at will with no regards for change control? For that matter, how much can you really trust a "bad performance" test, if the server config has been trifled with. In either case - you're setting yourself up for a surprise at the moment when you decided to go live with something (which doesn't work the same as it did in test....).
I'm all for giving devs their own playground on devs, but the other pieces need some amount of reliability, or else both your testing and your QA processes themselves become suspect....
And - I'm speaking from experience (I'm a dev who spent a lot of time in shops where the testing areas were dodgy because everyone had their hand in the pot.... lots of nasty surprises at first.)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 1, 2008 at 1:04 pm
Same thing applies, Lynn... let the developers do what they want in Dev... keep them out of QA, UAT, and Prod.
--Jeff Moden
May 1, 2008 at 3:05 pm
I agree with locking down these environments. Thanks for the advice guys.
June 4, 2008 at 2:10 pm
Hi Brian,
Can you please suggest or help,
I have sql user he needs to create SSIS packages on production server, I created a SQL login for him and gave DBcreator access on the server level. when he created SSIS package he is getting access denied.
Can you please advise what specific permission I should give to the sql acct.
Thank you!
June 12, 2008 at 9:34 am
hello
no 1 said about creating login with limited access, instead of triggers?
is triggers better than a restricted login
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply