I just finished reading several articles on the SQL Server Security Checklis
t
or Best Practices - and I could use some advise. Our ITS group is too small
to have a full time [application] DBA for SQL Servers so only Networking has
SA rights on the servers which in the past was not a problem. But now that
we're starting to develop/implement new Coldfusion and SQL Server Database
applications as we head toward switching from static HTML to a dynamic
database driven multiple server production environment that will use both a
staging and a development server. Since these are new technologies to this
environment and the production servers that only contain public information
(i.e. do not contain high risk data), Development has requested read-only
access to the production and staging servers to look at the data integrity,
check the rev. number on the code, and look at IIS, CF, and SQL logs and
configuration files, as needed. Development has also requested SA rights fo
r
their Sr. Developers only on the development server (i.e not the product
servers) to be used for testing replication, running diagnostics, reviewing
logs, create test applications users/roles to test permissions, test
configuration settings and permission issues (we are also implementing activ
e
directory) but access was denied to both requests. With one month left
before going live with the new production servers on IIS/CF/SQL, development
is concerned with the time delays with testing or replicating problems on th
e
development server and the inability to diagnose production problems with
outdated logs (vs real time monitors of IIS/CF).
The networking staff are competent at network issue but are not DBAs and doe
not have knowledge of how setting and permissions in SQL Server (not to
mention IIS or CF) will affect applications. How should SQL Server
roles/permission be split between Networking and Development on the
Development server in our small environment (note: our Sr Developers have 5
-
25 years of development experience including commercial DB engineering)?hi
there are no hard and fast rules to set a role to the users of the database.
it depends on the policy that u draft.
U need not have a full time DBA, eventhough its advised to have. Senior
developers with good admin skills can take up the role.
the person should have knowledge on taking backups and crash recovery
hope this answers the question
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"JA" wrote:
> I just finished reading several articles on the SQL Server Security Checkl
ist
> or Best Practices - and I could use some advise. Our ITS group is too sma
ll
> to have a full time [application] DBA for SQL Servers so only Networking has
> SA rights on the servers which in the past was not a problem. But now tha
t
> we're starting to develop/implement new Coldfusion and SQL Server Database
> applications as we head toward switching from static HTML to a dynamic
> database driven multiple server production environment that will use both
a
> staging and a development server. Since these are new technologies to th
is
> environment and the production servers that only contain public informatio
n
> (i.e. do not contain high risk data), Development has requested read-only
> access to the production and staging servers to look at the data integrity
,
> check the rev. number on the code, and look at IIS, CF, and SQL logs and
> configuration files, as needed. Development has also requested SA rights
for
> their Sr. Developers only on the development server (i.e not the product
> servers) to be used for testing replication, running diagnostics, reviewin
g
> logs, create test applications users/roles to test permissions, test
> configuration settings and permission issues (we are also implementing act
ive
> directory) but access was denied to both requests. With one month left
> before going live with the new production servers on IIS/CF/SQL, developme
nt
> is concerned with the time delays with testing or replicating problems on
the
> development server and the inability to diagnose production problems with
> outdated logs (vs real time monitors of IIS/CF).
> The networking staff are competent at network issue but are not DBAs and d
oe
> not have knowledge of how setting and permissions in SQL Server (not to
> mention IIS or CF) will affect applications. How should SQL Server
> roles/permission be split between Networking and Development on the
> Development server in our small environment (note: our Sr Developers have
5 -
> 25 years of development experience including commercial DB engineering)?|||It sounds like you're between a political rock and a hard place. The
important thing is to save in your CYA file a copy of the memo informing
management that what's going live cannot be adequately tested without
access, and since access has been denied, that they'll have to live with the
possibility of a meltdown. Once a meltdown occurs, you can be absolutely
sure that you will be given all the access you need (and probably more) in
order to fix the problem. (You might want to plan on an all-night debugging
fest, so be sure to stock up on Mountain Dew.)
Another solution would be to request equipment identical to the production
environment that is dedicated and accessible to the ITS group just for
testing. Of course someone will have to pay for the new equipment, and
you'll probably have to delay going live until the new equipment is
configured and testing is completed.
"JA" <JA@.discussions.microsoft.com> wrote in message
news:9E08BED2-6BEA-46B7-966E-64A6ADE1AD19@.microsoft.com...
> I just finished reading several articles on the SQL Server Security
Checklist
> or Best Practices - and I could use some advise. Our ITS group is too
small
> to have a full time [application] DBA for SQL Servers so only Networking
has
> SA rights on the servers which in the past was not a problem. But now
that
> we're starting to develop/implement new Coldfusion and SQL Server Database
> applications as we head toward switching from static HTML to a dynamic
> database driven multiple server production environment that will use both
a
> staging and a development server. Since these are new technologies to
this
> environment and the production servers that only contain public
information
> (i.e. do not contain high risk data), Development has requested read-only
> access to the production and staging servers to look at the data
integrity,
> check the rev. number on the code, and look at IIS, CF, and SQL logs and
> configuration files, as needed. Development has also requested SA rights
for
> their Sr. Developers only on the development server (i.e not the product
> servers) to be used for testing replication, running diagnostics,
reviewing
> logs, create test applications users/roles to test permissions, test
> configuration settings and permission issues (we are also implementing
active
> directory) but access was denied to both requests. With one month left
> before going live with the new production servers on IIS/CF/SQL,
development
> is concerned with the time delays with testing or replicating problems on
the
> development server and the inability to diagnose production problems with
> outdated logs (vs real time monitors of IIS/CF).
> The networking staff are competent at network issue but are not DBAs and
doe
> not have knowledge of how setting and permissions in SQL Server (not to
> mention IIS or CF) will affect applications. How should SQL Server
> roles/permission be split between Networking and Development on the
> Development server in our small environment (note: our Sr Developers have
5 -
> 25 years of development experience including commercial DB engineering)?|||SA rights on a development box does not sound unreasonable; in fact, I
recommend it, for many of the reasons you list above. Developers need
their own space to break stuff, so that they understand how NOT to
break it in production.
I work in a small shop as well, and currently I serve as both
production and development DBA; we're hiring a production DBA, but
until then I have to document every little thing I do to make sure that
there is a record for accountability depending on the hat I'm wearing.
I think it's OK for developers to have read-only access to
llimited-risk data in production, and they should have full control
over their development environment. And you can tell your bosses I
said so :)
Someone on the Interwebs believes in your cause.
Stu|||On Fri, 29 Jul 2005 22:56:02 -0700, JA wrote:
(snip)
>The networking staff are competent at network issue but are not DBAs and do
e
>not have knowledge of how setting and permissions in SQL Server (not to
>mention IIS or CF) will affect applications. How should SQL Server
>roles/permission be split between Networking and Development on the
>Development server in our small environment (note: our Sr Developers have 5
-
>25 years of development experience including commercial DB engineering)?
Hi JA,
I tend to agree with Stu: SA rights on the dev. server for experienced
DB developers and read rights to non-sensitive stuff on the prod server
sounds reasonable.
On the other hand, the networking staff has been assigned the
responsibility to keep the databases up and running and to protect data
from from unauthorized spreading (I'm sure that this is a terrible
sentence, but I don't know how to put it in correct English - I hope you
do understand what I'm trying to say).
And since it's THEIR responsibility, they are free to take whatever
measures THEY think are needed to accomplish that goals.
Many years ago, I had to do some major reshuffling on a SQL Server
implementation. The sa access I had was enough for about half the things
I needed to do (like shutting down the service, or creating a new file),
but many other tasks (like restarting the service, and deleting files no
longer needed) were only available for the network admins. Of course, I
requested access as network admin, to streamline the process. And of
course, I didn't get it. So I phoned the network admins each time I
needed them to do something:
"Hi, Hugo here - could you restart the SQL Server service please?"
"Hi, Hugo here - could you restart the SQL Server service please?"
"Hi, Hugo here - could you delete file XXX on server YYY please?"
"Hi, Hugo here - could you restart the SQL Server service please?"
"Hi, Hugo here - file AAA has to be moved from drive BBB to CCC."
"Hi, Hugo here - could you restart the SQL Server service please?"
"Hi, Hugo here - could you restart the SQL Server service please?"
"Hi, Hugo here - I requested a restart of the SQL Server service some 5
minutes ago, but doesn't seem to be running"
And so on.
Guess how long it took before I DID get some extra rights?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||When I worked help desk, we used to hate guys like you :)
Stu|||On 30 Jul 2005 16:38:27 -0700, Stu wrote:
>When I worked help desk, we used to hate guys like you :)
Hey! I was only trying to get my job done, you know!
(mumble, whine, mumble)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment