May 27, 2015 at 8:01 am
Kyrilluk (5/27/2015)
Eric M Russell (5/27/2015)
Kyrilluk (5/27/2015)
Eric M Russell (5/26/2015)
Kyrilluk (5/26/2015)
It would be nice to have this scientific method to all the blab about security as well. Like "I shouldn't give the sysadmin password to the developers because of security risk". How do you quantify this security risk? Using what research? Usually DBA use "good practice" and other B.S. to cover their own ignorance but then are very keen to ask others to give "proof" of what they say.Are you talking about granting developers sysadmin membership in development or production?
There is plenty of evidence out there suggesting that granting non-DBA users sysadmin access to production results in data loss and security breaches.
Does this evidence is based on scientific methods or on anecdotal evidence?
So, we're talking about wehther or not developers can accidentally delete data or drop objects in production, because they've been granted dbo or sysadmin permissions. Not only has this phenomenon been extensively documented in the field, it can be reproduced in a lab environment.
But then we can also reproduce in a lab environement the same kind of mistake being done by DBAs. Doesn't prove anything.
Kyrilluk, the scientific method doesn't answer subjective questions like:
"Should I give the sysadmin password to the developers?"
However, it can answer objective quetions like:
"Can a user with sysadmin access harm the database?"
"Does the developer need sysadmin access to perform their daily tasks?"
"Does the DBA need sysadmin access to perform their daily tasks?"
This can be proven objectively, and then used to support a subjective administrative decisions.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 27, 2015 at 8:06 am
Kyrilluk (5/27/2015)
Eric M Russell (5/27/2015)
Jeff Moden (5/27/2015)
Kyrilluk (5/27/2015)
Eric M Russell (5/26/2015)
Kyrilluk (5/26/2015)
It would be nice to have this scientific method to all the blab about security as well. Like "I shouldn't give the sysadmin password to the developers because of security risk". How do you quantify this security risk? Using what research? Usually DBA use "good practice" and other B.S. to cover their own ignorance but then are very keen to ask others to give "proof" of what they say.Are you talking about granting developers sysadmin membership in development or production?
There is plenty of evidence out there suggesting that granting non-DBA users sysadmin access to production results in data loss and security breaches.
Does this evidence is based on scientific methods or on anecdotal evidence?
Strictly scientific. The explosions are the result of the experiments. 😀
I once worked with a developer who ask for some assistance with a schema comparison tool and requested help from the DBA. He said he attempted to sync objects in development from production but kept getting error messages. He said it was probably because he wasn't sysadmin in production and thought that would fix the problem. Obviously the DBA wasn't about to do that for him, so I offered to come by his desk and take a look at what he was doing.
It turns out that he had the source and destination connections switched around. Had this guy been a member of sysadmin and the first attempt "worked", he probably would have gone on to sync every production database from development while the DBA sat eating lunch.
So, that's why we give developers least required privilege in production.
No apologies.
I have no doubt that some developers have done dumb things. And I don't have doubt as well, to have encountered this as well, where DBA have done stupid things as well. But that's not scientific evidence. That's just prejudice or gut feeling.
If you "need" sysadmin access to perform your job functions, then you are not just a "Developer", you're actually a "Developer / DBA". Regardless of what your employer calls you, if you routinely perform administrative operations on a database, then you are a DBA.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 27, 2015 at 8:10 am
GilaMonster (5/27/2015)
True, however:
How many developers does a company have?
How many DBAs does a company have?
Assuming the same rate of production-related mistakes (which should be checked and verified by a scientific study, not in a lab but with a sample of real-world companies), which is, on average, going to produce more production-related mistakes, just the DBA team having production sysadmin access (9 people last time I worked at a corporate) or the DBAs and all of the developers (over 100 last time I worked at a corporate).
Now, this is just an estimate, not a real result...
But you can study this. Not in a lab, but in the real world. Set up questionnaires on how many production-related mistakes or unauthorised changes (dropped tables, deleted or changed data, schema changes, etc) have been made in the last X months, how many people have sysadmin access, what the job titles are of the people who have sysadmin access, etc. Conduct the survey, analyse the result, adjust for how likely people are to under- or over-report figures. Analyse and conclude.
Perfectly science computer science research. I'd be surprised if it hasn't already been done. If I still had access to a university library, I'd ask the librarians for help finding it. In fact, just a quick google search turns up a paper from 1990
No, it's not specific to SQL Server and DBAs, but it's in the same area. There's very likely a lot more and a lot more recent ones.
The methodology in that paper is exactly the methodology you would use if you wanted to do a study specifically on SQL Server and DBA/developer permissions.
I agree that the more access you give to people the more likely something bad is going bad to happen. What I am after is the quantification of this risk in a company, using the position/experience/etc.. of the people in charge. In other words, if you have 9 DBA with sysadmin, to give a 10th person sysadmin is going to increase the risk by how much? Does this risk is mitigated by the title of the person (DBA, BI Dev, Software Dev) or by the numbers of years of experiences in the industry (junior, mid career, senior, expert).
Believe it or not, I have known developers that know more about DBA that some DBA themself. There are dumb people everywhere, not just among developers. And once aware of the risk a certain access can provide, my gut feeling(I evidently don't have any scientific evidence to back this up) let me to believe that experience is a better predictor at risk managing that simple role (being DBA, dev, etc).
May 27, 2015 at 8:25 am
Kyrilluk (5/27/2015)
I agree that the more access you give to people the more likely something bad is going bad to happen. What I am after is the quantification of this risk in a company, using the position/experience/etc.. of the people in charge.
Which falls under Risk Management, as I've previously mentioned. Is study-able, probably has been studied already several times.
Does this risk is mitigated by the title of the person (DBA, BI Dev, Software Dev) or by the numbers of years of experiences in the industry (junior, mid career, senior, expert).
Probably neither. It'll be mitigated by the person's experience in doing database-related work, in the number of times they've cleaned up after accidents, whether they're the ones who will be working late is something happens and how many people will be screaming at them if there's a database-related problem
my experience (I evidently don't have any scientific evidence to back this up), experience is a better predictor at risk managing that simple role (being DBA, dev, etc).
Not just experience. Experience specific to the area. I don't expect the person doing web development, who's main worries are around source code merges, layout, compatibility with browsers, response time, UI, etc to have the same concern about unauthorised database changes as the person looking after the database.
Similarly I don't expect the DBA to have the same concern about source control branch merges and untracked changes as the web developer. Because they have different experiences, different goals, different responsibilities, different attitudes.
I wouldn't want the person who mostly works with the database doing merges of code from source control, no more than I want the person doing web development handling the schema changes for a database. I don't expect the electrician to also have a very good idea of the intricacies of plumbing, no matter how good an electrician he is.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 27, 2015 at 9:02 am
Kyr
With code first Entity Framework, who would be the person making schema changes, the Web Developer, DBA or someone else?
May 27, 2015 at 9:38 am
robert.sterbal 56890 (5/27/2015)
Is it good security to lose customers?
You seem to have two unrelated questions mushed together here. Good security technically has nothing to do with customer retention. It's how the brand manages that security for their customers, and gets around it when needed, that causes the potential issue of losing the customer.
Your experience was with customer support, probably first tier. They couldn't provide you with what you needed and I bet they didn't escalate to tier 2 or 3 to see if a supervisor could come up with a work around. That is their problem, and their processes. But these people weren't managing security. In fact, I'd be surprised if they even had access to the information you needed. In companies that have a clue, the front line employees aren't able to see that information without a mask.
But your point remains. It was not good customer service that you received, and you got a bad impression of their processes and security regulations. Hence, their "good security" lost them a customer.
May 27, 2015 at 10:21 am
Emph. mine
meilenb (5/24/2015)
...Everyone knows that CO2, CH4 and N2O ...
1. It's hard to prove that Everyone knows anything. Statements like "everyone knows" and "we all agree" are antithetical to the point that the article was attempting to convey.
2. Wrong Forum.
-- Itzik Ben-Gan 2001
May 27, 2015 at 10:35 am
Kyrilluk (5/26/2015)
It would be nice to have this scientific method to all the blab about security as well. Like "I shouldn't give the sysadmin password to the developers because of security risk". How do you quantify this security risk? Using what research? Usually DBA use "good practice" and other B.S. to cover their own ignorance but then are very keen to ask others to give "proof" of what they say.
Oh, I don't know. Maybe sometimes just saying "good practice" ought to be enough. Of course, prison times for data exposure might not be levied against the development team.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 27, 2015 at 10:43 am
Eric M Russell (5/27/2015)
Kyrilluk (5/27/2015)
Eric M Russell (5/26/2015)
Kyrilluk (5/26/2015)
It would be nice to have this scientific method to all the blab about security as well. Like "I shouldn't give the sysadmin password to the developers because of security risk". How do you quantify this security risk? Using what research? Usually DBA use "good practice" and other B.S. to cover their own ignorance but then are very keen to ask others to give "proof" of what they say.Are you talking about granting developers sysadmin membership in development or production?
There is plenty of evidence out there suggesting that granting non-DBA users sysadmin access to production results in data loss and security breaches.
Does this evidence is based on scientific methods or on anecdotal evidence?
So, we're talking about wehther or not developers can accidentally delete data or drop objects in production, because they've been granted dbo or sysadmin permissions. Not only has this phenomenon been extensively documented in the field, it can be reproduced in a lab environment.
Since I've seen many a DBA screw up a production environment, I don't see what makes developers special in either regard. However, exposure is an extremely well documented concept and you must minimize exposure. That's before we get to legal requirements around data. You minimize the exposure. It doesn't eliminate the risk, but it does reduce it. There really are reasons why a business, not the DBA team, wants to not have developers in production. It's actually a silly argument.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 27, 2015 at 10:57 am
Depending on the organization, I'm not opposed to allowing Developers read-only access to production, especially if they are expected to wear the Data Analyst hat on occasion. Even more advanced things like querying system views, viewing execution plans, and event tracing can be allowed on a piecemeal basis to non-DBA users by granting them "ALTER TRACE" and "VIEW SERVER STATE" permission. The same goes for 3rd party tools.
However, I totally don't undertand why a Developer or an application would need SYSADMIN access to production, unless they are also wearing the DBA hat.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 27, 2015 at 11:13 am
Eric M Russell (5/27/2015)
Depending on the organization, I'm not opposed to allowing Developers read-only access to production, especially if they are expected to wear the Data Analyst hat on occasion. Even more advanced things like querying system views, viewing execution plans, and event tracing can be allowed on a piecemeal basis to non-DBA users by granting them "ALTER TRACE" and "VIEW SERVER STATE" permission. The same goes for 3rd party tools.However, I totally don't undertand why a Developer or an application would need SYSADMIN access to production, unless they are also wearing the DBA hat.
Yep to all this.
For what it's worth, I managed systems without having admin privileges on the box. I was only sysadmin on the SQL instance. Turns out, I didn't need that security either. You really can frequently figure out how to get your job done without zero security.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 27, 2015 at 11:17 am
It's not contradictory. It is reality
May 27, 2015 at 11:19 am
Grant Fritchey (5/27/2015)
You really can frequently figure out how to get your job done without zero security.
+1
😎
BTW, think this experiment thread is becoming a proof that digression is somewhat of a threat:-P
May 27, 2015 at 11:20 am
meilenb (5/27/2015)
It's not contradictory. It is reality
This appears random. To what statement were you making this comment?
May 27, 2015 at 11:29 am
My 2 cents on the security and privileges: for the production data, developers with administrative privileges are more dangerous than high heel wing walking on a fighter jet, cannot recall a single "incident" when a developer or a manager wasn't involved.
😎
Viewing 15 posts - 61 through 75 (of 168 total)
You must be logged in to reply to this topic. Login to reply