SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Building a Security Philosophy


Building a Security Philosophy

Author
Message
Andy Warren
Andy Warren
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: Moderators
Points: 11723 Visits: 2730
Comments posted to this topic are about the item Building a Security Philosophy

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Skandalis
Skandalis
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1393 Visits: 1301
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
ALZDBA
ALZDBA
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12250 Visits: 8926
I'm quit with you Andy. Smooooth

With one exception ... The DWH bulk loaders Wink. 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


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


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
david.gerrard-604066
david.gerrard-604066
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 63
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?
SuperDBA-207096
SuperDBA-207096
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1955 Visits: 711
Andy,
Well done! Security is often one of the most overlooked aspects of developing an application, until there is a problem.

Mark
ALZDBA
ALZDBA
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12250 Visits: 8926
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


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


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Ross McMicken
Ross McMicken
Mr or Mrs. 500
Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)

Group: General Forum Members
Points: 560 Visits: 2237
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.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: Administrators
Points: 63081 Visits: 19113
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Jack Corbett
  Jack Corbett
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18638 Visits: 14899
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Johann Steenkamp-328619
Johann Steenkamp-328619
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1492 Visits: 139
Thanks Andy, exactly what I was looking for
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search