March 9, 2011 at 9:07 pm
Comments posted to this topic are about the item DBA Concerns
March 9, 2011 at 10:58 pm
Truth be told, as a consultant I relate best practices to customers every chance I get, and it seems that many prefer functionality and ease of administration to proper security practices. They start with bad habits and don't have the time or inclination (and sometimes the money to pay somebody like me) to fix these issues. Maybe I am not strong enough in my expressions of concern, but I think many just don't care enough. It is weird to see this attitude, but it is their environment so I can only say so much without risking annoying them. They say, "Yeah, we get it, its' just too much trouble" or time or money or something.
So I agree. A survey of SQL Server DBA's would likely have the same results. At least the ones I know...
Scary for them.
Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
March 10, 2011 at 2:29 am
Although I agree with the general direction of the ediitorial, database server security concerns all depend on the IT manager's policy on security. I once worked with a company where the IT manager gave SYSADMIN access to all of his developers on the production db servers. DBA's were not allowed to override this and revoke or restrict this access under any circumstances. When asked in a meeting why? the response was " I trust my developers implicitly, subject is closed." When suggested that more granular access be distributed on an individual need, per database, The response was "the subject is closed, next order of business.". Well, you can imagine what their turnover rate was there on DBA's. 😀
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
March 10, 2011 at 3:49 am
TravisDBA (3/10/2011)
I once worked with a company where the IT manager gave SYSADMIN access to all of his developers on the production db servers.
I kind of agree with that in production. But problems happen on production where a DBA (or developer who is doing the DBA work) needs to take a look, managing statistics and indexes and checking performance, and the "best" way to do that is with SYSADMIN (why? because very few things in BOL tell you the exact privileges you need to do quite a lot of work like that, and IT managers aren't always that proficient in managing SQL Server; it's a DBA/developer DBA's job).
Which brings me to a second point. Sometimes in groups of developers, the one architect will lock the others out of the development database and try to enforce that kind of security while refusing SYSADMIN; I work in a place like that, and it's impossible to do anything I need to do to be effective in my job because, again, nobody can get a clear picture of what specific flags need to be set for me to perform a series of tasks. Hands get thrown up, and it gets put into the "too hard" basket, and I'm left to my own devices rather than cave in and give SYSADMIN.
Which brings me to my third and final point; that the BOL documentation, and any documentation at all, for explaining how security functions and how to maintain it, is extremely lacking for SQL Server. When you throw roles/owners, logins/users, functions/procedures, and the whole slew of SQL and management commands into the mix, it's all daunting and unexplained.
The internet is full of half-written introductions to the security system that don't go into any depth at all, because few people outside of the development team really understand it, and those that do probably operate on an unconscious experience base, or on a long and arduous "turn everything off then turn things on until it works" policy.
March 10, 2011 at 6:12 am
drowlfs (3/10/2011)
TravisDBA (3/10/2011)
I once worked with a company where the IT manager gave SYSADMIN access to all of his developers on the production db servers.I kind of agree with that in production. But problems happen on production where a DBA (or developer who is doing the DBA work) needs to take a look, managing statistics and indexes and checking performance, and the "best" way to do that is with SYSADMIN (why? because very few things in BOL tell you the exact privileges you need to do quite a lot of work like that, and IT managers aren't always that proficient in managing SQL Server; it's a DBA/developer DBA's job).
Which brings me to a second point. Sometimes in groups of developers, the one architect will lock the others out of the development database and try to enforce that kind of security while refusing SYSADMIN; I work in a place like that, and it's impossible to do anything I need to do to be effective in my job because, again, nobody can get a clear picture of what specific flags need to be set for me to perform a series of tasks. Hands get thrown up, and it gets put into the "too hard" basket, and I'm left to my own devices rather than cave in and give SYSADMIN.
Which brings me to my third and final point; that the BOL documentation, and any documentation at all, for explaining how security functions and how to maintain it, is extremely lacking for SQL Server. When you throw roles/owners, logins/users, functions/procedures, and the whole slew of SQL and management commands into the mix, it's all daunting and unexplained.
The internet is full of half-written introductions to the security system that don't go into any depth at all, because few people outside of the development team really understand it, and those that do probably operate on an unconscious experience base, or on a long and arduous "turn everything off then turn things on until it works" policy.
I agree with this. I've got an email thread somewhere that runs into tens of messages between me and a contractor that was working on a database server of ours. Every time I'd give him permission to do something, he'd come back saying either it hadn't worked, or that now he needed to do something else and didn't have permission to do that. In the end I had to make a judgement call and decided to give him sysadmin permission for the time necessary for him to do his work.
March 10, 2011 at 7:59 am
drowlfs (3/10/2011)
TravisDBA (3/10/2011)
I once worked with a company where the IT manager gave SYSADMIN access to all of his developers on the production db servers.I kind of agree with that in production. But problems happen on production where a DBA (or developer who is doing the DBA work) needs to take a look, managing statistics and indexes and checking performance, and the "best" way to do that is with SYSADMIN (why? because very few things in BOL tell you the exact privileges you need to do quite a lot of work like that, and IT managers aren't always that proficient in managing SQL Server; it's a DBA/developer DBA's job).
I'll admit I'm somewhat of a control freak, but I am very leery of granting sysadmin to a developer on a server that hosts databases for multiple projects/developers. Why isn't the DBA sitting with the developer to help troubleshoot? The developer knows their application/database, but the DBA knows (or should know) more about performance/indexing/troubleshooting, as well as what actions may affect other databases on the server. Amazing what you can accomplish with teamwork & communication.
As for the DBA survey, I find it interesting that funding is a primary concern as well - we run into this all the time. There never seems to be funding to do things correctly the first time. Once there's an audit coming, we suddenly have money (but very little time) to go back and fix problems. It would be nice to have that funding spread throughout the year to just do it right the first time (as SQL DBAs, we do try to make sure it's done right the first time - our Oracle DBAs scramble through pre-audit fixes every year...WHY???). Part of that comes down to communication - communicating the standards and policies more clearly would help to get things done right the first time as well.
March 10, 2011 at 8:22 am
drowlfs (3/10/2011)
TravisDBA (3/10/2011)
I once worked with a company where the IT manager gave SYSADMIN access to all of his developers on the production db servers.I kind of agree with that in production.
WOW... where to start.... :w00t:
Where do you work?
What is the address to your web site?
I have a great intrest in your company now! 😛
If you are a publicly traded company, or having to follow any mandated security system or protocol, this would cause you to fail any and every audit.
You can't even shelve this for a re certification in most cases.
Widespread SYSADMIN access to the production database is an automatic Fail.
What does not fail you is creating an single account that has the access, securing it's password, and limiting it's use with a change control policy.
Unfortunately there are several very BAD and costly ways to give IT professionals the access they need to production to do thier jobs.
I have never seen an issue with security on any system that could not be solved by 5 minutes of research done by someone that knew what they where doing.
This would include my time with DARPA, the US Army, ACS, TSA, and US Customs.
I guess that is why IT Security Professional is an actual job description just like RDMS Administration. Like many things in the IT world, security is easy if you take the time to learn about it.
Just ask the Match.com DBA that lost the entire internal Sales and Adervtising database with there executives personal contacts to thier largest competetor. Seems that you can't even prosocute certain things if you purposely multi-home your internal applications Database server to the internet to make administration easier, then don't change the default SA password. This is considered negligence, lawyers call it leaving the front door open and putting out a for sale sign.
If you want to be robbed don't lock the door, open all your windows, leave on vacation. :smooooth: I'll be wating... 😉
March 10, 2011 at 8:28 am
If you are a publicly traded company, or having to follow any mandated security system or protocol, this would cause you to fail any and every audit.
You can't even shelve this for a re certification in most cases.
Widespread SYSADMIN access to the production database is an automatic Fail.
What does not fail you is creating an single account that has the access, securing it's password, and limiting it's use with a change control policy.
Unfortunately there are several very BAD and costly ways to give IT professionals the access they need to production to do thier jobs.
I have never seen an issue with security on any system that could not be solved by 5 minutes of research done by someone that knew what they where doing.
This would include my time with DARPA, the US Army, ACS, TSA, and US Customs.
I guess that is why IT Security Professional is an actual job description just like RDMS Administration. Like many things in the IT world, security is easy if you take the time to learn about it.
Just ask the Match.com DBA that lost the entire internal Sales and Adervtising database with there executives personal contacts to thier largest competetor. Seems that you can't even prosocute certain things if you purposely multi-home your internal applications Database server to the internet to make administration easier, then don't change the default SA password. This is considered negligence, lawyers call it leaving the front door open and putting out a for sale sign.
If you want to be robbed don't lock the door, open all your windows, leave on vacation. :smooooth: I'll be wating... 😉
I never said that I agree with a company's management doing this. Others did that. All I said was that some companies have this policy set by management, that override a DBA's security concerns, regardless of the DBA's objections. I have worked at one of those. 😀
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
March 10, 2011 at 8:54 am
SanDroid (3/10/2011)
drowlfs (3/10/2011)
TravisDBA (3/10/2011)
I once worked with a company where the IT manager gave SYSADMIN access to all of his developers on the production db servers.I kind of agree with that in production.
WOW... where to start.... :w00t:
I think he/she meant "development", since the rest of the post would make more sense. At least that's what I thought.
March 10, 2011 at 9:36 am
I am smack in the middle of this at my job. My manager is all about security/process/documentation, while other parts of management (and even the upper management) has a differing opinion. We've set policy but because we're in education, few people desire to follow it and do not have to if they squawk loud enough. I've been in a few heated meetings lately where I'm having to explain that all the "exceptions" aren't documented, and when we get pwned, my buttsky is on the line, as I doubt that whoever in management allowed the exception will not cop to it when the issue is front page news.
Very frustrating.
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
March 10, 2011 at 9:57 am
Duncan Pryde (3/10/2011)
I've got an email thread somewhere that runs into tens of messages between me and a contractor that was working on a database server of ours. Every time I'd give him permission to do something, he'd come back saying either it hadn't worked, or that now he needed to do something else and didn't have permission to do that. In the end I had to make a judgement call and decided to give him sysadmin permission for the time necessary for him to do his work.
Depending on the granularity of the access provided I may question the competency of that DBA. Either he doesn't know what SQL permissions he needs to accomplish what he's trying, he doesn't know what he's doing with the DB so he doesn't really know what he's trying to accomplish, or the access being granted is granular enough that if the first thing he needs to check doesn't return right he needs to request more access.
March 10, 2011 at 10:10 am
TravisDBA (3/10/2011)
Although I agree with the general direction of the ediitorial, database server security concerns all depend on the IT manager's policy on security. I once worked with a company where the IT manager gave SYSADMIN access to all of his developers on the production db servers. DBA's were not allowed to override this and revoke or restrict this access under any circumstances. When asked in a meeting why? the response was " I trust my developers implicitly, subject is closed." When suggested that more granular access be distributed on an individual need, per database, The response was "the subject is closed, next order of business.". Well, you can imagine what their turnover rate was there on DBA's. 😀
I think I worked there. Actually this was the response until a few developer "fixes" after deployments in production caused the clients to call the office. After that I was able to remove "sa" access from developers.
March 10, 2011 at 10:13 am
cfradenburg (3/10/2011)
Duncan Pryde (3/10/2011)
I've got an email thread somewhere that runs into tens of messages between me and a contractor that was working on a database server of ours. Every time I'd give him permission to do something, he'd come back saying either it hadn't worked, or that now he needed to do something else and didn't have permission to do that. In the end I had to make a judgement call and decided to give him sysadmin permission for the time necessary for him to do his work.Depending on the granularity of the access provided I may question the competency of that DBA. Either he doesn't know what SQL permissions he needs to accomplish what he's trying, he doesn't know what he's doing with the DB so he doesn't really know what he's trying to accomplish, or the access being granted is granular enough that if the first thing he needs to check doesn't return right he needs to request more access.
You may indeed question the competency - but in small organisations with multi-disciplinary developers (who are usually .net developer, sql developer, server administrator etc all rolled into one) it's not unusual. I'm no security expert myself, so for development environments (such as the one I mentioned) I couldn't justify the time to work out what he needed to accomplish and therefore what permissions might be required. In production it's a bit different, since only one or two people will ever have access to a production server, and usually only need to perform specific pre-defined tasks.
I've seen some pretty horrific things security-wise with a couple of companies I've dealt with in the past (not ones I'm dealing with now I'm happy to say) - but if they are very small there seems to be the idea that they can rely on "security through obscurity".
March 10, 2011 at 10:16 am
drowlfs (3/10/2011)
I kind of agree with that in production. But problems happen on production where a DBA (or developer who is doing the DBA work) needs to take a look, managing statistics and indexes and checking performance, and the "best" way to do that is with SYSADMIN (why? because very few things in BOL tell you the exact privileges you need to do quite a lot of work like that, and IT managers aren't always that proficient in managing SQL Server; it's a DBA/developer DBA's job).
Whoever does the management of production servers needs sysadmin. But it ought not to be blanketly all developers. Too often the developer mentality of "fix" things, even small things, causes instability. This also typically violates the SOX rules of separation of duties. I don't agree with developers having access to production by default. There are security issues, privacy issues, etc. If you are of any size, you ought to have separate people looking at things. Or automatically be restoring last night's backup to a QA type server to check on production issues.
Which brings me to a second point. Sometimes in groups of developers, the one architect will lock the others out of the development database and try to enforce that kind of security while refusing SYSADMIN; I work in a place like that, and it's impossible to do anything I need to do to be effective in my job because, again, nobody can get a clear picture of what specific flags need to be set for me to perform a series of tasks. Hands get thrown up, and it gets put into the "too hard" basket, and I'm left to my own devices rather than cave in and give SYSADMIN.
I have removed sysadmin in development, often because the developers do many changes without documenting them. By becoming a chokepoint, the architect or DBA can keep track of what's being changed.
Where this becomes an issue is when the DBA is a bottleneck. They have to keep up with requests from developers.
Which brings me to my third and final point; that the BOL documentation, and any documentation at all, for explaining how security functions and how to maintain it, is extremely lacking for SQL Server.
Completely agree. Most items have permissions thrown in as an afterthought. I am loathe to rely on BOL for documentation and typically need to verify things myself. In fact, I ran into this the other day: http://voiceofthedba.wordpress.com/2011/03/07/sql-server-truncate-table-permissions/
March 10, 2011 at 10:38 am
In our shop, developers have sysadmin on development, but only myself, the migration manager, and the ETL specialist have it on test and prod. Whenever I receive an authorized request for increased permissions, I document the change, and update the user's current permissions in my log. This way I can provide a complete report on demand of current user access levels on all databases and servers to management or our security officer.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply