June 18, 2003 at 10:20 am
My company is going through a large security crackdown, and making drastic changes to the way business is done within all databases environments. This is long overdue in my mind. However, I have to overcome serious political and system control issues in order to institute some of the standards our security department is calling for. Since many of the developers have been managing their own environments it is a major undertaking to eliminate some of their access while still granting them the rights they need to do their job. I was hoping to find someone who has implemented a very good methodology in regards to allowing lead developers to have access to schedule batch jobs and dts packages, without granting them dbo rights or syadmin access. My issue is that I do not want to have to manage these people's scheduled jobs, but once I removed them from the sysadmin server role they could no longer access their jobs or dts packages. Is there an easy way to do this? Or a more complex way that someone may have designed?
Any advice or ideas would be appreciated.
June 18, 2003 at 10:36 am
Have not played with this much, but I think for the jobs you can make them owners of the jobs. By doing this they should then be able to manage those jobs that they own. Not sure about the DTS packages. I assume if they are the owners of the packages they will also be able to manage their packages.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
June 18, 2003 at 11:10 am
If you are really cracking down, then they should not be able to schedule jobs or alter packages. You are introducing places where change is not managed. Someone should approve a package change just like an object change.
That being said, remove guest from msdb and ensure that their accounts are being used. You can grant grant them msdb access (use a role) and experiement with the permissions to the sysdtsxxx and sysjobxxx tables.
Steve Jones
June 18, 2003 at 11:23 am
Yes, I agree that they should not be able to schedule jobs or packages, which is the way I have done it at every company in the past, hence the reason why I am not sure how to bend some of these rules without opening the doors too wide.
In this case though I have over 45 production servers, many maintained by different devs, so there is no way I can realistically review every batch job they write, or dts package. Also we have development around the clock, so if it meant that I had to review every job and put it into production myself, I would likely never sleep. So, I am trying to come up with a hybrid methodology that works.
I have already removed the guest account, which was the first step of limiting their access. baby steps.
Thanks for your suggestions.
June 23, 2003 at 6:13 am
not be able to schedule jobs or alter packages?
this is where you dba-types are totally clueless - how is a developer going to develop when they can't create dts pkgs and schedule jobs that run existing dts pkgs? not possible for any real db dev or testing of data...
if you have to have someone approve these tasks and you have more than a handful of developers (we have 32+), you're going to be a very busy dba - and it will serve(r) you right!
do it right - put sql server on the developer machines - then create a policy that says - if you don't back up your own work, it's your ass....and get your config manager involved so that everyone's work is automatically backed up into source safe each night...
and require everyone to put all dev work in the same base directory on their local machines.
now, once this is setup you can give them access to run pkgs and jobs between local and network servers without giving away the farm on the network server!
in addition to improving security, taking these steps will get everyone involved actively in insuring their individual piece of the dev pie is managed properly - what's good for the one is good for the many.
if you've got 45 production servers and you've just now removed the guest account - that means those before you (and probly many of those still there) did a really, really bad job managing the entire environment - you need to get the security people involved - find out who was responsible before (think witch hunt), knock some heads, and start off-loading some of the work and responsibility to the idiots that came before you.
hope this helps,
e.
June 23, 2003 at 9:38 am
quote:
this is where you dba-types are totally clueless - how is a developer going to develop when they can't create dts pkgs and schedule jobs that run existing dts pkgs? not possible for any real db dev or testing of data...
A couple of things here. First, Steve is advocating the removal of guest permissions in msdb for good reason. Keep in mind every user that has access to a SQL Server can access a database using guest. Guest kicks in if you don't have a mapped user account in the database. That means the person in HR who only needs read access to a particular database has the privs of the guest account, hence his recommendation. That's also why he said start adding permissions back, in order to assist developers and make sure they have the rights they need.
Scheduling is a completely different story. Scheduling is handled by SQL Server Agent and any scheduled jobs should be maintained by the DBA. Here's the problem: if the DBA doesn't have tight control and someone schedules a job or DTS package that really slams the SQL Server, the first question that's going to be asked is, "Why do they have the access to do that?" When management then finds out the developer in question also has sysadmin rights (meaning they can change the configuration of the server), the DBA is in a lot of hot water.
quote:
do it right - put sql server on the developer machines -
This is a huge problem from a patch/security hotfix management perspective. This is what brought a lot of comapnies to their knees during SQL Slammer. Yes, most have production servers that were unpatched, but what made the firefight even worse was there were developer workstations that were also unpatched. As a result, it was much like Nimda... firefighting one block at a time and constantly having to checked cleared blocks to make sure a reinfection didn't happen. And saying, "Developer, make sure your SQL Server is patched!" isn't going to cut it. Because if the developer doesn't, it isn't just the developer who's going to be affected. As SQL Slammer showed, it could be the entire organization.
With all that said, there has to be a happy medium, a compromise of some sort if resources are that short on the DBA side. Travlin, are there any DBA-caliber developers who can take a larger role, after having been trained to follow the standard procedures your organization defines, for their respective projects. It's not the optimal solution (that would be enough people), but it may help you meet the needs your organization has set.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
June 23, 2003 at 1:19 pm
ok,
first let me respond by saying that - if an organization has 45 production servers - then they also need the infrastructure to keep all workstations logged and patched automatically (IT dept's problem - but very doable)...
secondly, in many environments (especially large corporate retail) developers must write, test, and deploy automated jobs every day, all day - it's just a fact of life and there's no way around it - and that's exactly why i say put sql server on the developer's boxes that need to run the automated jobs - that way you can setup rights from those boxes to the servers that allow jobs to be run, but that's it....this is how you will implement the last piece of the security puzzle without clamping down on production (and that is what it's all about - production)
lastly, there's no happy medium to security anymore - you get your IT dept to put automated norton or mcaffee on every box, install a policy on every box that dis-allows installation of 3rd-part software, and you automate patches and updates once a day or once a week - then you implement a strict policy about the workstations - you can do all of this and still provide an open dev network w/sql server and all of it's features..!
and you will find that the IT dept will be very open to creating policies on the dev boxes.
good luck!
June 23, 2003 at 10:56 pm
I would love to agree with you, but the fact of the matter is that patch management is still a major issue, even with tools like those provided by Microsoft, Shavlik, and St. Bernard (among others) available. Take SQL Slammer, for instance. Getting different companies to agree to support us when we were talking about going to SP 2 with the hotfix or even to SP3 was a major chore. We weren't the only ones facing this issue. There was a general call on NTBugtraq on the issue. This problem is discussed a lot every time a new serious vulnerability comes out, indicating that patch management has no easy answers right now.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
June 25, 2003 at 5:58 am
ok, bkelley - ya jus shot yerself in the foot, my friend - i'm reading your last post and i'm hearing that when the slammer came along you hadn't even had sp2 on some of your boxes - that is why the slammer slammed so bad.
it's shops like yours that didn't keep up w/patches that were the brunt of the problem....! (the slammer came out 6 months after sp2 - that's not lack of patch management, that's just downright laziness and lack of responsibility to your organization)
so, if you read my post thoroughly, you'll see that i advocate a very strict policy regarding patch management!
life-cycle management of patches on the os and all server products on workstations and servers is the key (along w/the right firewall setup and stringent virus management)
sorry to rant, but you got me started on patch management and all the baggage that goes with it...i get militant when i hear about cases like these - patch management is not a 'gray area' - you either do it or not and if you fail to it's no one's fault but your own....
to get back on track - the main issue here is developers needing access to all the functions of sql server - you still need a solution, and i think the best way is going to be w/sql server on the workstation(s). worst case, put it on one or two workstations that have no external drives and are disconnected from the network.
the vendors of rdbms's designed scripting hooks into things like jobs and dts so that developers (not just dba's) can get work done quickly and efficiently - if they can't use the latest tools and techniques available with these systems, you as a dba are doing your corporation a disservice - when one person in the chain loses efficiency, the bottom line suffers...
find a solution that keeps your developers in the loop!
good luck travlin, and be careful who you take advice from!
eric.
June 25, 2003 at 6:37 am
quote:
it's shops like yours that didn't keep up w/patches that were the brunt of the problem....! (the slammer came out 6 months after sp2 - that's not lack of patch management, that's just downright laziness and lack of responsibility to your organization)
Laziness? I think not. If you're supporting major applications that cost millions of dollars to implement with hefty maintenance annual fees and the vendor keeps saying, "We've not tested our product on that service pack. If you go to it, you won't be supported," then you know what's it like to be caught between a rock and a hard-place. Which is where we were. Ever call a software vendor three times on the same day and get three different answers on what service pack they support? To make matters, you only talked to two different people (which means someone gave you two different answers).
Now, try asking about a hotfix. Keep in mind SP2 didn't fix the problem. SP2 with MS02-061 fixed it, and that's only if you didn't accidentally overwrite the netlib files with the hotfix that came out to repair a memory leak... a hotfix that shipped with the version of the netlib files prior to MS02-061. And yes, MS02-61 had been out 6 months before SQL Slammer, Litchfield provided exploit code to show the issue, and still a lot of major vendors wouldn't talk about going off of SP1. That's the real world and it has nothing to do with laziness. You go forward and that hefty 10% you're paying in maintenance on that product that's so expensive, well, it means didly squat as soon as they found out you applied a service pack. You act with due diligence and ask the question a lot over six months to the vendor and their position hasn't budged. Now then, please explain to me how that is laziness on our parts?
Did we get infected? No. We did not. Our perimeter security held out just fine and we didn't get infected from a VPN connection or anything of that sort because the only ones authorized to have such connections patched their personal systems. But the DBAs all felt we dodged a major bullet. I think the vendors got a wake-up call, at least temporarily. We'll see what happens when the next major SP comes out.
And as far as patched go, if you want a rant, look at Microsoft's recent history on how stable their patches have made systems. Just look at the past 18 months and see which patches, when originally released, broke servers or worse yet, blue-screened them so you couldn't even get to the point where you could fix them again. That means a system rebuild. Think management is therefore gun-shy about pushing out a patch "as soon as it is out?" They had better be. It's not as simple as you try to make it out to be. Microsoft themselves have described the issue as broke.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
Edited by - bkelley on 06/25/2003 07:16:47 AM
K. Brian Kelley
@kbriankelley
June 25, 2003 at 7:12 am
Article where Microsoft's chief security strategist characterizes patch management as broke:
http://www.informationweek.com/story/showArticle.jhtml?articleID=10300045
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
June 25, 2003 at 10:21 am
I still diasgree with allowing developers to schedule and run packages. In the real world, developers are not conservative by nature, not giving to performing good tests.
Therefore having them schedule jobs or alter packages on production servers can and has been a problem in all environments I've worked in. There are exceptions where developers prove they can be trusted, but overall they cannot. Exceptions are just that, exceptions.
Here's the main reason. A DBA (administrative) should be conservative by nature. They should be wary of changes to their environment. So they may not be able to "review" every package and task, but they can be in charge of scheduling them. If your packages on 45 servers change daily, then you have other development/QA/testing/Bus analysis issues. Packages should be tested and "certified" that they will work. You can provide some amount of information to the DBA, but even if you cannot, he schedules it and then HE is aware that a change occurred and if some problems happen, then the DBA knows where to look.
Same thing for altering packages. If you're a developer and you say Package A is complete and ready to run. Then it gets setup in production to run. You SHOULD NOT be allowed to make a change to this without letting people know. Maintaining a production environment that is stable and runs well requires that change be managed. This has been proven over and over and over again in all types of environments, not just computing. When unmanaged changes occur, problems usually follow.
I don't have a problem with SQL on a developer's laptop. There are issues with patch management, but this must be borne by IT and the developer. You have to patch your machines.
We have issues with developers running packages on their machine against production data. Two issues here. One, the developers should not have production access,. Two, sometimes there's no way to duplicate the data in a test environment, especially if you have a workstation. There needs to be some balance.
It is not always laziness in managing patches. I manage 50 SQL Servers and we have different revs of patches. Why? Reasons BKelley mentioned. Vendors don't always ceritify patches. I had to remove SP4 from a SQL7 machine recently and roll back to SP3 because the vendor wouldn't support our version of their software on SP4. It ran fine, but there was a software issue unreleated to SQL and they wouldn't support the client until the server was rolled back. Maybe it's valid, maybe not. Point is I had no choice. And it isn't easy to upgrade. Costs major $$. Patch management is a huge issue, especially for us with 1000 servers and 8000 workstations. Things fall through the cracks all the time.
Steve Jones
June 25, 2003 at 10:48 am
the statement made that 'developers can't be trusted' is irresponsible - and is an example of what is wrong with a lot of the cost of doing business in the software industry lately...
when industry is chock-full of prima-dona attitudes about who-did-what and the spirit of teamwork gets blown away with statements such as these - we end up raising the cost of doing business with our idiotic technical rivalries - more controls, less camaraderie, less of the important thing - getting the friggin jobs out the door to pay the bills
gee, i wonder why IT salaries are plummeting?
don't you realize that if it weren't for developers f'ing things up dba's would be out of a job? 🙂
when i suggested developers get a local version of sql to develop jobs and dts, it should be plain to see that they are then going to have to run those jobs from their local machines into the connected server(s) - and if you've got the policies setup correctly on the db objects of the production boxes, it shouldn't be that big of a deal....if there's concern about some developers, create a simplified certification level that a developer has to be at before he/she can run dts or jobs...
so, you want a full-time job reviewing dts packages? jobs?
if you have a developer that screws up production data more that a couple of times because they're cutting corners or something, lock that person out...it's all about policy control on an org-by-org basis
1000 servers and 8000 workstations? wow! that's an 8-1 ratio - you must work for donald trump or an ISP!
good luck!
eric.
June 25, 2003 at 11:05 am
I don't think any of us are saying developers can't be trusted. I've been a developer, a DBA, a system admin, and now I basically do all three plus architecture. So I appreciate the developer's viewpoint, as I think most of the others who have responded do as well.
However, we are saying apply the principle of least privilege. Only give people the rights they need to do the job. We're also saying have clearly defined roles and segregate environments as much as possible... two key security concepts. It's the same reason I discuss in a forthcoming article about removing the BUILTIN\Administrators group... you gotta trust the admins because of the power they wield.
However, that doesn't mean you neglect to put in security controls just because they can override them. If you've got good auditing in place, you'll catch them doing the override. It's an after-the-fact catch, but that's how it has to be.
"I am only one; but still I am one. I cannot do everything, but still I can do something. I will not refuse to do the something I can do." - Helen Keller
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
June 26, 2003 at 8:23 am
Quote: if you have a developer that screws up production data more that a couple of times because they're cutting corners or something, lock that person out...
Why risk the chance that they'd screw up production data in the first place? That would scare me a little.
I don't think anyone wants to review every package/job that comes your way, but at least, as bkelly says, if you are in control of scheduling or making changes then you are aware of what is running in your system and how they might impact upon your system
Growing old is mandatory, growing up is optional
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply