Building a Security Philosophy

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Andy Warren » Building a Security Philosophy

46 posts, Page 1 of 5

1 2 3 4 5 »»»

Building a Security Philosophy

Rate Topic

Display Mode

Topic Options

Author

Message

Andy Warren

Posted Tuesday, August 12, 2008 12:04 AM

SSCertifiable

Group: Moderators
Last Login: Thursday, May 09, 2013 12:38 PM
Points: 6,462, Visits: 1,384

Comments posted to this topic are about the item Building a Security Philosophy

Andy
SQLShare - Learn One New Thing Each Day
SQLAndy - My Professional Blog
Connect with me on LinkedIn
Follow me on Twitter

Post #550754

Lian Pretorius

Posted Tuesday, August 12, 2008 12:32 AM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 3:49 AM
Points: 1,139, Visits: 1,107

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

Post #550762

ALZDBA

Posted Tuesday, August 12, 2008 1:39 AM

SSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 2:17 AM
Points: 6,862, Visits: 8,049

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

     Jul 13

Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't 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" BigGrin

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me Hehe

Post #550786

david.gerrard-604066

Posted Tuesday, August 12, 2008 3:34 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, June 15, 2012 3:48 AM
Points: 17, 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?

Post #550826

SuperDBA-207096

Posted Tuesday, August 12, 2008 4:49 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, January 02, 2013 12:15 PM
Points: 1,443, Visits: 711

Andy,
Well done! Security is often one of the most overlooked aspects of developing an application, until there is a problem.

Mark

Post #550855

ALZDBA

Posted Tuesday, August 12, 2008 5:00 AM

SSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 2:17 AM
Points: 6,862, Visits: 8,049

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

     Jul 13

Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere w00t

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me Hehe

Post #550862

Ross McMicken

Posted Tuesday, August 12, 2008 6:44 AM

Old Hand

Group: General Forum Members
Last Login: Today @ 8:30 AM
Points: 346, Visits: 1,819

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.

Post #550921

Steve Jones - SSC Editor

Posted Tuesday, August 12, 2008 6:46 AM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 3:30 PM
Points: 31,436, Visits: 13,751

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

Post #550925

Jack Corbett

Posted Tuesday, August 12, 2008 6:55 AM

SSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871

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

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

Post #550933

Johann Steenkamp-328619

Posted Tuesday, August 12, 2008 7:15 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Monday, March 04, 2013 12:19 AM
Points: 1,482, Visits: 122

Thanks Andy, exactly what I was looking for

Post #550957

« Prev Topic | Next Topic »

46 posts, Page 1 of 5

1 2 3 4 5 »»»

Permissions