August 12, 2008 at 9:29 am
My favorite line in the article is "...they don't get the job unless I trust them." I know I take that in a different way than the author meant, but it is a great reflection of my security philosophy. I'm not a lazy programmer. I just firmly believe in a level of trust that is beyond what many "security" people believe in.
I'm the only developer/DBA in my "shop." I gave it careful consideration and decided I should have all the privileges I needed to do my job. 🙂 I trust myself a great deal.
The rest of security is a matter of figuring out permissions to give to my users. At present (this is expected to change in the future and so I appreciate learning other approaches), the only apps I have are desktop apps and the only users I have are employees of our agency. The employees of our agency generally do not have tools that let them directly access the databases. They only access the data through the front-ends that I provide.
We use windows authentications. Domain users are assigned to windows groups and those groups are assigned as server logins and then database users. The groups are assigned to a small set of roles that I create for each database. The roles are usually along the lines of RegularUser, ReadOnlyUser, PowerUser. The permissions are assigned to the roles for tables, stored procs, functions, etc.
I DO assign rights directly to the tables via the roles as makes sense for the application. I've yet to read an argument (and I've read and considered and re-considered them all again and again) that has convinced me that my strategy *for my environment* is an unacceptably insecure one. They are our employees. If we didn't trust them, we wouldn't hire them. That doesn't make me foolish. I know employees can go bad and/or make mistakes. (But hey, so could that junior DBA that was talked about in the article.) My users do not get administrative privileges. They just get access to the data that they need to do their jobs.
I don't use any of the built-in rolls. I prefer to define everything myself for a small set of user-defined rolls for each database.
August 12, 2008 at 9:38 am
Nice article...
August 12, 2008 at 9:38 am
Note I said "for the web"... I'm the fella here who has the happy job of monitoring the ever increasing volume of SQL Injection attacks that hit our sites every passing day. 🙁
In general I'd work on the first principal that an account being used by a website shouldn't be allowed to do anything at all, and then relax it from there, if it's really necessary. But again - the first principal ought to be: "I'll need a lot of convincing that it's really necessary".
However - I do like the line I just read (regarding desktop apps) that said "if my company hired them then they must be reasonably trustworthy." That shifts the blame where it really belongs... The HR / Personnel Department!
August 12, 2008 at 9:48 am
Note I said "for the web"
Exactly! I think I will be tasked with creating a web app in the not too distant future. At that point, the game will change, and I will be using a very different approach to security. I appreciate learning about "web security" because that is a different world than I currently live in. I wouldn't trust a web user as clearly as I could see her.
August 12, 2008 at 10:41 am
Steve Jones - Editor (8/12/2008)
I'd agree with Andy. The issue I have with datareader is that it automatically gives rights to all tables. So if I add a table to store anything, meta information about your database, performance, perhaps at the request of someone to store something else, everyone in that role gets rights.It means you're providing automatic access, and you might not want to. You should explicitly grant a role access if you want it, not have security setup to do the grants for you. That's the mindset that gets people into trouble.
I would respectfully disagree about datareader (though I'm wary of datawriter). While you should only use it when you know you want to person/group you are giving it to to genuinely be able to read all tables within a database including those that do not exist, there are definitely occasions when this is appropriate.
One example is if you have a developer that can be trusted with all information in the database (or if the information is not particularly confidential to begin with), they would have full rights on the development server and then datareader on the production version. Having datareader makes it easier for them to troubleshoot if there is an error or a question. This is especially relevant if the application was a rapid development project and may need to be put into in house use with less than complete testing. Of course, you could readily argue that this should not happen, but there are times when it is unavoidable.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
August 12, 2008 at 7:27 pm
On SQL Server 2000 I was vehemently against both db_datareader and db_datawriter. Granting db_datareader is a violation of the Principle of Least Privilege and thus should be avoided. Complete access to metadata "tables" as in SQL Server 2000 meant an information disclosure vulnerability. This is what blind SQL injection seeks to take advantage of. There were automated tools a few years ago to perform blind injection. Those tools have only improved now.
On SQL Server 2005, with the ability to assign SELECT against a schema, meaning all tables and views within that schema can be queried against, there's really no reason for it any longer.
I know this is a hard stance to take, especially when you're trying to get developers access so they can get to work. However, the old cliche is true. It's always easier to come out strict and relax as the situation demands than the other way around.
K. Brian Kelley
@kbriankelley
August 12, 2008 at 7:30 pm
Andy Warren (8/12/2008)
Ross, I tried multiple ID's and the only way it worked (for me) was to have separate machines, one logged in as powerless me, other as SA me. Trying to switch back and forth just annoyed me. Part of being a DBA is never working without a net - thinking before executing and having a fall back plan.Not saying I recommend this approach, but its worked for me better than the multiple ID approach.
The way we handle multiple IDs is pretty simple. The non-privileged is used to log on to the workstation. Email, etc., are retrieved/used via this account. When a DBA starts up SSMS or Profiler or another tool, they do so using RunAs. Therefore, if a zero-day comes through email, SQL Servers don't get compromised due to a logged on user having elevated privileges. Of course, the reason we do this is to reduce the # of systems. We've discussed the idea of getting multiple systems, but management hasn't bought off on it. One thing we have under consideration are VMs on an ESX server dedicated to providing administrative workstations for infrastructure personnel.
K. Brian Kelley
@kbriankelley
August 12, 2008 at 7:36 pm
JJ B (8/12/2008)
I DO assign rights directly to the tables via the roles as makes sense for the application. I've yet to read an argument (and I've read and considered and re-considered them all again and again) that has convinced me that my strategy *for my environment* is an unacceptably insecure one. They are our employees. If we didn't trust them, we wouldn't hire them. That doesn't make me foolish. I know employees can go bad and/or make mistakes. (But hey, so could that junior DBA that was talked about in the article.) My users do not get administrative privileges. They just get access to the data that they need to do their jobs.
In smaller organizations where you have the opportunity to know the people, this can certainly work. However, there's always the possibility in a small shop where the boss' "Chosen One" has to be granted access to the application and knows enough to be dangerous. In they go with the database tools (which they were allowed to install because they are the One) and BAM! Table deleted, restore from backup. As a result, I strive to drive developers to use stored procedures and ownership chaining wherever possible. I understand where you're coming from, but I've been burned by the "Chosen One" syndrome on the web development side. Thankfully, I had the presence of mind to diligently and vigorously use source control. I never want it to happen on one of my databases.
K. Brian Kelley
@kbriankelley
August 12, 2008 at 7:40 pm
david.gerrard (8/12/2008)
Note I said "for the web"... I'm the fella here who has the happy job of monitoring the ever increasing volume of SQL Injection attacks that hit our sites every passing day. 🙁In general I'd work on the first principal that an account being used by a website shouldn't be allowed to do anything at all, and then relax it from there, if it's really necessary. But again - the first principal ought to be: "I'll need a lot of convincing that it's really necessary".
However - I do like the line I just read (regarding desktop apps) that said "if my company hired them then they must be reasonably trustworthy." That shifts the blame where it really belongs... The HR / Personnel Department!
I'll put on my auditor cap here. If you have the ability to lock it down and don't, you're not protecting the org's assets. A person may be reasonably trustworthy when hired. However, life circumstances can change everything. Just look at members of the military or the government who have been turned to give up secrets and the reason they did so wasn't greed. A movie that illustrates this is The Sentinel where Montrose is turned after his family is threatened (though, granted, the relationship was established years earlier, the same tactic would have been successful without a previous relationship).
K. Brian Kelley
@kbriankelley
August 12, 2008 at 10:17 pm
One of the things I haven't seen mentioned here is how to use schema's in addition to roles for granting privileges.
I know that in most systems being converted from 2000 to 2005, all objects are owned by dbo and exist in the dbo role. For those systems, I would recommend creating the schemas and building synonyms within each schema and assigning appropriate rights to the schemas to the roles and/or specific users or groups.
For new development, start with your schemas and build up from there.
This way, you can easily grant the appropriate level of access and not have to use db_datareader at all.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
August 13, 2008 at 2:22 am
(I'm probably teaching my granny to suck eggs here, but...)
This is the fount of all knowledge with regard to Web Application security:
http://www.owasp.org/index.php/Main_Page
... plus it's really handy if you're having insomnia problems.
August 13, 2008 at 4:39 am
Jeffrey, the schema idea is interesting, it's just felt like it was worth doing to me, all too often users need to cross schemas to get work done. Have you tried this in production and if so, talk more about the advantages?
August 13, 2008 at 6:46 am
...We also implement our own reader/app (writer/exec-on-schema) / ReleaseManager db-groups and only add members to those roles...
Indeed, the days of using dbo-schema for user objects are over. 😎
I advise to use a user defined schema to host the objects of a certain data domain.
I also advise to always properly qualify all objects you use (don't rely on a schema probing cascade).
However we do not use the schema concept to differentiate e.g. read only stored procedures and sp's with the potential to update data.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 13, 2008 at 9:01 am
In smaller organizations where you have the opportunity to know the people, this can certainly work. However, there's always the possibility in a small shop where the boss' "Chosen One" has to be granted access to the application and knows enough to be dangerous. In they go with the database tools (which they were allowed to install because they are the One) and BAM! Table deleted, restore from backup.
This argument just doesn't make sense to me. My users do not have permissions to delete tables. So, even if they were allowed to install say Management Studio or MS Access, they couldn't delete any tables with their accounts.
I'm the only "one" in my organization. However, if there were a time in the future where there was another "one" (a special power user or another developer) and that person needed the level of rights that would allow them to actually do something like delete a table, then that person would need a higher level of permission as part of her/his job. If the boss/agency trusts someone to do the job, I'm not going stand in the way of the agency getting business done. (That's how security people get bad reps and stop the world from turning.) If I have to restore from backup, then I restore from backup. That's what backups are for. Think of it as job "security".
August 13, 2008 at 9:14 am
david.gerrard (8/13/2008)
(I'm probably teaching my granny to suck eggs here, but...)
I didn't know about that site. Looks like it could be a good resource for me. Thanks for showing this chicken where the seed is.
Viewing 15 posts - 16 through 30 (of 45 total)
You must be logged in to reply to this topic. Login to reply