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 7:24 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 7, 2014 10:47 AM
Points: 1,248, Visits: 1,196
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...
Post #550971
Posted Tuesday, August 12, 2008 7:35 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, September 18, 2014 7:56 AM
Points: 6,723, Visits: 8,462
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


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 #550988
Posted Tuesday, August 12, 2008 7:58 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Monday, September 15, 2014 9:16 AM
Points: 6,784, Visits: 1,895
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.


Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #551015
Posted Tuesday, August 12, 2008 8:00 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Monday, September 15, 2014 9:16 AM
Points: 6,784, Visits: 1,895
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.


Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #551016
Posted Tuesday, August 12, 2008 8:09 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, September 15, 2014 8:29 AM
Points: 356, Visits: 1,965
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.
Post #551026
Posted Tuesday, August 12, 2008 9:29 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 19, 2014 3:35 PM
Points: 266, Visits: 2,595
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.
Post #551119
Posted Tuesday, August 12, 2008 9:38 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 3:19 AM
Points: 5,371, Visits: 1,391
Nice article...


Post #551137
Posted Tuesday, August 12, 2008 9:38 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 15, 2012 3:48 AM
Points: 17, Visits: 63
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!
Post #551138
Posted Tuesday, August 12, 2008 9:48 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 19, 2014 3:35 PM
Points: 266, Visits: 2,595
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.
Post #551144
Posted Tuesday, August 12, 2008 10:41 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, April 4, 2014 4:40 PM
Points: 751, Visits: 917
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/
Post #551225
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse