November 12, 2007 at 8:25 pm
We have a couple of options where I work to give the developers access to production or near production data. First, we have a review login set up on production. They can make a request to have this account actived. At that point we give them read permissions in the database(s) requested. Since it's only read access (we use the db_datareader role), they obviously can't update data or execute stored procs. They typically use this to troubleshoot productiion issues. Any changes (schema or data) needs to go thru the normal change control process. At the end of the day, a SQL job revokes access to all databases for this login.
The second thing we do is to have an environment where we restore selected production database backups nightly. We have a separate server for it but you could have a separate instance on a dev server. One step in the restore job is to add the developer group to the db_owner role for all of the databases. This gives them an environment that allows them to do pretty much anything with fairly up-to-date data. Typically they use this to test data changes before going to production. Although code changes can be tested here (we have separate servers for that), it's not really a test or quality testing environment since it's overwritten every day, it does allow them to test emergency code changes that need to go into production immediately. Between these two options, it keeps them pretty happy without giving them the keys to production.
November 13, 2007 at 4:05 am
I don't know. I think the best way to secure the Server against everyone is to unplug the darn thing and lock it in a closet.
Of course then I might be out of a job... But at least I can say I secured the Server! @=)
November 14, 2007 at 10:25 pm
Brandie Tarvin (11/13/2007)
I don't know. I think the best way to secure the Server against everyone is to unplug the darn thing and lock it in a closet.Of course then I might be out of a job... But at least I can say I secured the Server! @=)
Actually, from a security perspective, that's not the case. In security we have what we call the C-I-A Triad. C-I-A stands for Confidentiality, Integrity, and Availability. That last one gets you. 😉
K. Brian Kelley
@kbriankelley
November 15, 2007 at 4:00 am
Ahh, who needs access to data anyway! Right? @=)
I love the title of the book you co-authored. "How to Cheat at Securing SQL Server"... How much detention does a DBA get for cheating at security? @=)
November 15, 2007 at 8:21 am
Brandie Tarvin (11/15/2007)
Ahh, who needs access to data anyway! Right? @=)I love the title of the book you co-authored. "How to Cheat at Securing SQL Server"... How much detention does a DBA get for cheating at security? @=)
If it's done right, none. That's kind of the point of the book, not to cut corners on implementation, but to reduce down the amount of security info you have to digest in order to get a relatively secure implementation of SQL Server. Let's face it, Books Online is a great resource, but sometimes the topics aren't well connected and you spend a lot of time searching for the correct answer in it.
K. Brian Kelley
@kbriankelley
November 30, 2007 at 8:48 am
> use AD accounts
> use AD security groups
> turn off public / guest accounts and groups (minimize access if can't remove)
> use change mangement control and log the sql local accounts for services only (if you have to use local)
> snapshot --> dev / test env
> readonly access to the programmers / developers should be ok on the production
> dba ought monitor accesses, changes regularly
> bottom line if you are not a paranoid DBA that would make me paranoid
good luck
Cheers,
John Esraelo
November 30, 2007 at 10:14 am
Gotta love AD - who decided that a single point of failure was an improved security model?? Guess one password, access dozens of sytems. 😀
December 4, 2007 at 1:47 pm
so far we haven't secured SQL against anyone, but hopefully we will do it on some servers. we are also going to look at forcing people to use their local subscribers instead of the publishers
our latest plan is to change the passwords of the sql user id's for our java apps. since a lot of other apps use these as well we will force them to use new logins and passwords we create for each app.
for the devs and MS Access users we are going to do the following. delete current logins. create two windows groups for each server on the domain. one for read only and one for R/W. Create corresponding local groups on each server.
as people call the helpdesk add them to the R/O group for their local server. if they need R/W add them to the R/W group for that server.
we also want to control new apps connecting to SQL and hopefully this will do it as well
Viewing 8 posts - 31 through 37 (of 37 total)
You must be logged in to reply to this topic. Login to reply