Development - Production Data Access

Hello All,
I have been searching for a published document for Best Practices
concerning access levels based on roles. Should developers have more
than (if at all) select level access to production data? If I
understand (from multiple postings) that it is best to have:
1. Development (developers have extensive access levels)
2. Test (developers have restriced access levels)
and
3. Production (developers have none or select level access)
Our environment and budget only allows for items 1 and 3.
If any body could point me to a document from a 'reputable' source, I
would greatly appreciate it.

TIA
BillI don't have a reputable source for you, only some more opinions.

Practices obviously vary from place to place and will depend partly on
the size and complexity of your dev operation, your toolset, and on how
much support your developers need to do. If your developers have to
support systems in production then they may need some extra level of
access to the production environment.

One thing I would not want to compromise on: do not test only in a dev
environment. That's because it's important to have a separate a
deployment process for testing that mirrors the way you will deploy
changes to production. In my opinion that's the best way to ensure that
you only release to production exactly what is tested. That doesn't
necessarily mean you need physically separate servers - whether that's
necessary depends on what components are under test. In the case of SQL
Server it does mean you ought to at least have separate instances for
development and testing.

--
David Portas
SQL Server MVP
--|||Bill Willyerd (bwillyerd@.dshs.wa.gov) writes:
> I have been searching for a published document for Best Practices
> concerning access levels based on roles. Should developers have more
> than (if at all) select level access to production data? If I
> understand (from multiple postings) that it is best to have:
> 1. Development (developers have extensive access levels)
> 2. Test (developers have restriced access levels)
> and
> 3. Production (developers have none or select level access)
> Our environment and budget only allows for items 1 and 3.
> If any body could point me to a document from a 'reputable' source, I
> would greatly appreciate it.

I think it's difficult to come with a best practice here, because it
is likely to business-dependent.

If developers have full access to the production database, this means
that they address critical issues directly, and don't have to spend
half a day to get some sort of access.

On the the other hand, this also means that developers are able to
all sorts of silly stuff in production, and also get access to data
that is sensitive.

So here is obviously a trade-off. The more availability you need, the
more in security you need to sacrifice - or invest in procedures so
that when a developer needs to debug in production, he can get access
easily by some sort of approval procedure.

I fully agree with David's view that you need a test environment
separate from development. I'll chime in here and add that the
process of transferring code from different environments should
be performed through version control, and the source-countrol system
is the master for all code to test and production environments. (As
well as to development environment to some extent as well.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Bill Willyerd" <bwillyerd@.dshs.wa.gov> wrote in message
news:1123690516.767246.211860@.o13g2000cwo.googlegr oups.com...
> Hello All,
> I have been searching for a published document for Best Practices
> concerning access levels based on roles. Should developers have more
> than (if at all) select level access to production data? If I
> understand (from multiple postings) that it is best to have:
> 1. Development (developers have extensive access levels)
> 2. Test (developers have restriced access levels)
> and
> 3. Production (developers have none or select level access)
> Our environment and budget only allows for items 1 and 3.
> If any body could point me to a document from a 'reputable' source, I
> would greatly appreciate it.
> TIA
> Bill

In addition to David and Erlands' comments, you might want to consider
Sarbanes-Oxley compliance. As a general comment, SOX compliance requires a
separation of duties (and therefore permissions) between development and
production. As a result, it's often not even an option to allow to
developers change access in the production environment.

But as I understand it, what you have to do to comply with SOX is negotiated
with your external auditors, and it depends heavily on your internal
environment. So you may want to investigate what (if any) legal obligations
you have to consider, and what the precise implementation details are for
your situation. For what it's worth, in my environment developers have no
change access to UAT or production (db_datareader only), so all code and
scripts are deployed via an Operations team - this is great for SOX
purposes, but obviously it adds both cost and time.

Simon