Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Building a Security Philosophy Expand / Collapse
Author
Message
Posted Tuesday, August 12, 2008 12:04 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 2:51 PM
Points: 6,790, Visits: 1,902
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
Post #550754
Posted Tuesday, August 12, 2008 12:32 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, October 16, 2014 8:09 PM
Points: 1,255, Visits: 1,199
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
Posted Tuesday, August 12, 2008 1:39 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:08 AM
Points: 6,731, Visits: 8,483
I'm quit with you Andy.

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


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

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #550786
Posted Tuesday, August 12, 2008 3:34 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Tuesday, August 12, 2008 4:49 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, January 2, 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
Posted Tuesday, August 12, 2008 5:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:08 AM
Points: 6,731, Visits: 8,483
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


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

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #550862
Posted Tuesday, August 12, 2008 6:44 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 10:26 AM
Points: 357, Visits: 1,972
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
Posted Tuesday, August 12, 2008 6:46 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 12:34 PM
Points: 31,181, Visits: 15,626
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
Posted Tuesday, August 12, 2008 6:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:11 AM
Points: 10,282, Visits: 13,264
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
Posted Tuesday, August 12, 2008 7:15 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Today @ 7:15 AM
Points: 1,482, Visits: 134
Thanks Andy, exactly what I was looking for
Post #550957
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse