August 12, 2008 at 12:04 am
Comments posted to this topic are about the item Building a Security Philosophy
August 12, 2008 at 12:32 am
Nice article Andy.
If I may ask, what do you have against the datareader role? Apart from denying users access to sensitive data, do you have any other reasons/examples of why not to use it?
Thanks,
Lian
August 12, 2008 at 1:39 am
I'm quit with you Andy. :smooooth:
With one exception ... The DWH bulk loaders ;). They are being granted "bulkadmin".
We also implement our own reader/app (writer/exec-on-schema) / ReleaseManager db-groups and only add members to those roles.
This eases db-migration (e.g. from prod to QA or Dev) because we create windows-ad-groups at server-db-level to host the appropriate windows (service) accounts.
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 12, 2008 at 3:34 am
I'd try not to give a website account any rights over the underlying data at all, and only Execute rights over stored procs to access the data.
Am I doing the right thing?
August 12, 2008 at 4:49 am
Andy,
Well done! Security is often one of the most overlooked aspects of developing an application, until there is a problem.
Mark
August 12, 2008 at 5:00 am
david.gerrard (8/12/2008)
I'd try not to give a website account any rights over the underlying data at all, and only Execute rights over stored procs to access the data.Am I doing the right thing?
Yes, you are !
Holding a "doors locked" attitude is always the best way, especialy with web stuff.
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 12, 2008 at 6:44 am
Do you give sysadmin rights to a DBA's regular LAN ID? We only grant admin rights to special ID's that aren't used for anything else. That prevents a bunch of mistakes, and provides the appropriate SOX cover as well. We make sure that no ID has admin rights on both a test/development box and a production box, which prevents anyone from accidentally running tests against a production database. We also have a separate acceptance environment that is set up hte same as a production box, with limited access for developers and support personnel. All actions on the aceptance environment are done by a DBA/Server admin just as would occur on a production box. This ensures that installs and upgrades to apps and databases will work. Nothing goes straight to a production box - all changes must go through acceptance first.
August 12, 2008 at 6:46 am
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.
August 12, 2008 at 6:55 am
I have to admit being lax about security as far as developers go. Of course part of this comes from the fact that I have always worked in small shops (Max 5 developers including myself) and we have all had some level of interaction with the SQL Servers so we took the easy way out and were all sysadmin. I'm not saying that this is the best way to do things, but it is how it was done.
For regular users I am with Andy in that I rarely grant direct table access and rarely use the fixed database roles. One area I did use the datareader role was for a specific linked server account we had setup. It was only used internally and used within stored procedures to access data across servers.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 12, 2008 at 7:15 am
Thanks Andy, exactly what I was looking for
August 12, 2008 at 7:24 am
We give datareader access on the read-only reporting db to some staff who need to run ad-hoc queries. None of our business logic is in the database (Views, stored proces, etc) - So execute rights is probably not applicable.
Setting up a roll to view specific tables (just over 1000 - 1 schema) can be a bit tedious, but after Steve's post it makes a bit more sense - Will investigate a bit further now...
August 12, 2008 at 7:35 am
Ross McMicken (8/12/2008)
Do you give sysadmin rights to a DBA's regular LAN ID? We only grant admin rights to special ID's that aren't used for anything else. That prevents a bunch of mistakes, and provides the appropriate SOX cover as well. We make sure that no ID has admin rights on both a test/development box and a production box, which prevents anyone from accidentally running tests against a production database. We also have a separate acceptance environment that is set up hte same as a production box, with limited access for developers and support personnel. All actions on the aceptance environment are done by a DBA/Server admin just as would occur on a production box. This ensures that installs and upgrades to apps and databases will work. Nothing goes straight to a production box - all changes must go through acceptance first.
FYI we have at least 3 windows userid per DBA/Sysadmin person.
1) regular ID (mail, ... and sysadmin for DEV)
(even for dev plans are to use a separate win. userid)
2) QA_admin windows user
3) Prod_admin windows user
All activity is logged for SOx related servers.
cfr my article: http://www.sqlservercentral.com/articles/Security/3203/
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 12, 2008 at 7:58 am
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.
August 12, 2008 at 8:00 am
David,
I like the approach, but be open to other options if it seems like that is what the company needs. For OLTP as far Im concerned it doesnt get any better than only using SP's for data access and only granting read only on the minimum tables needed to build dynamic queries.
August 12, 2008 at 8:09 am
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.
Our usual method for priovileged access is to use a Terminal Server session to do the work. We login to Citrix with the alternate ID and do whatever we need to do. That works well, and minimizes the risks as much as possible. There is a separate TS farm set up just for this sort of work.
Another option is remote desktop connections to tools on the server itself. We are on SQL 2000, so I don't know how that would work on a later version of SQL Server
I can understand the difficulties with two machines, or having to log in/out just to do a simple task. That was one of the drivers behind our TS and RDC options.
Viewing 15 posts - 1 through 15 (of 45 total)
You must be logged in to reply to this topic. Login to reply