Are database utilities, such as Crystal Designer, SQL client tools, and DB
Artisan a security risk that should be removed from the system, or is it
more effective to use the security provided by SQL Server? I think that
removal of the database utilities is unnecessary, but I have been asked to
develop software that finds the software and optionally removes it.
One reason why removal of the utilities has negligent value is that software
such as that can easily be used from other media and/or can be installed. I
know there are ways to limit use and installation of software but as far as
I know, there would be only partial security provided by doing that.
I need to learn about SQL Server security. If I am correct about what I say
above, then if I can get suggestions for what to read or read about, then
that would help.Preventing access to database utility software does not in itself provide
data security. One can write and execute a script like the one below using
only a text editor.
Set conn = CreateObject("ADODB.Connection")
conn.Open _
"Provider=SQLOLEDB;" & _
"Data Source=MyServer;" & _
"Integrated Security=SSPI;" & _
"Initial Catalog=MyDatabase;"
conn.Execute "DELETE FROM MyTable"
Of course, the user (Windows authenticated account in this example) must
have the appropriate SQL permissions in order to successfully execute such a
script and that is why attention to database security is important.
That said, it is often desirable to discourage ad-hoc SQL access to
production databases using database tools and utilities. Consider a
non-technical user with the database object permissions needed in order to
use an application. With a separate reporting tool, the user could easily
access the database from outside the application and cause blocking or other
performance problems with a poorly formed query. This is one reason why it
is common to create a separate database for end-user reporting and provide
users with the tools needed to do their job.
Applications sometimes use an application login or role to provide data
access under a security context other than the end user. This approach
provides users with the database permissions needed to use the app yet
prevents adhoc access from outside the context of the application unless the
user's own login has been granted the access and permissions.
> I need to learn about SQL Server security. If I am correct about what I
> say above, then if I can get suggestions for what to read or read about,
> then that would help.
For starters, check out 'Managing Security' topic in the Books Online. If
you don't have the doc installed, you can find it online at
http://msdn.microsoft.com/library/d...>
ity_05bt.asp
Hope this helps.
Dan Guzman
SQL Server MVP
"Sam Hobbs" <samuel@.social.rr.com_change_social_to_socal> wrote in message
news:e2EmgVP%23FHA.1332@.tk2msftngp13.phx.gbl...
> Are database utilities, such as Crystal Designer, SQL client tools, and DB
> Artisan a security risk that should be removed from the system, or is it
> more effective to use the security provided by SQL Server? I think that
> removal of the database utilities is unnecessary, but I have been asked to
> develop software that finds the software and optionally removes it.
> One reason why removal of the utilities has negligent value is that
> software such as that can easily be used from other media and/or can be
> installed. I know there are ways to limit use and installation of software
> but as far as I know, there would be only partial security provided by
> doing that.
> I need to learn about SQL Server security. If I am correct about what I
> say above, then if I can get suggestions for what to read or read about,
> then that would help.
>|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:u0%23ilqQ%23FHA.4092@.TK2MSFTNGP10.phx.gbl...
> Preventing access to database utility software does not in itself provide
> data security. One can write and execute a script like the one below
> using only a text editor.
> Set conn = CreateObject("ADODB.Connection")
> conn.Open _
> "Provider=SQLOLEDB;" & _
> "Data Source=MyServer;" & _
> "Integrated Security=SSPI;" & _
> "Initial Catalog=MyDatabase;"
> conn.Execute "DELETE FROM MyTable"
The script would not actually be executed by the text editor, but otherwise
this is an example of something that would be impractical to remove. The
actual programs would be WScript and CScript. If removal of possibly risky
tools is done to protect thd data, then WScript and CScript would have to be
removed also, which would make all scripts useless. HTML scripting
capability would also have to be removed.
> it is often desirable to discourage ad-hoc SQL access to production
> databases using database tools and utilities. Consider a non-technical
> user with the database object permissions needed in order to use an
> application. With a separate reporting tool, the user could easily access
> the database from outside the application and cause blocking or other
> performance problems with a poorly formed query. This is one reason why
> it is common to create a separate database for end-user reporting and
> provide users with the tools needed to do their job.
I think that perormance is not a typical security matter. Performance
considerations such as this make the problem much more complicated. In those
situations where elimination of possible performance problems require a
security solution different from other security solutions, I think that the
benefits would not justify the cost. I think that performance problems
should not be a primary duty of security and is especially outside the scope
of my project.
> Applications sometimes use an application login or role to provide data
> access under a security context other than the end user. This approach
> provides users with the database permissions needed to use the app yet
> prevents adhoc access from outside the context of the application unless
> the user's own login has been granted the access and permissions.
I do not know if this application's login provides a different security
context; I am not aware of that, but it is worth investigating.
If the application provides the ability to execute a macro during or
immediately following an application, is it possible that the security
context could be changed then? I realize that this might not work for this
particular application, so I am asking only if it might work.
Perhaps it is possible to write a program that changes the security context
then executes the application, but that would be impractical if it requires
a separate login. I need to learn about SQL Server's roles before deciding
to use a solution such as this.
SQL Server's roles seem to be the prefered solution and I will read the
article you specify before I ask any questions about roles.|||> The script would not actually be executed by the text editor, but
> otherwise this is an example of something that would be impractical to
> remove.
You are, of course, completely correct. I should have said "write using any
text editor and execute using standard OS componenets".
> I think that perormance is not a typical security matter. Performance
> considerations such as this make the problem much more complicated. In
> those situations where elimination of possible performance problems
> require a security solution different from other security solutions, I
> think that the benefits would not justify the cost. I think that
> performance problems should not be a primary duty of security and is
> especially outside the scope of my project.
I included issues related to authorized data access because I don't know the
scope of your security project and these considerations could affect your
security analysis and design. In the broader sense, security includes
taking reasonable precautions to ensure that data are accessible by
authorized users. Some companies also include DR under the security
umbrella.
> If the application provides the ability to execute a macro during or
> immediately following an application, is it possible that the security
> context could be changed then? I realize that this might not work for this
> particular application, so I am asking only if it might work.
> Perhaps it is possible to write a program that changes the security
> context then executes the application, but that would be impractical if it
> requires a separate login. I need to learn about SQL Server's roles before
> deciding to use a solution such as this.
>
I'm not sure I understand what you mean by 'macro'. If you are referring to
SQL script and the script is run by the app immediately after each database
connection, such an approach might allow you to enable an application role.
AFAIK, other 'macro' techniques would require separate accounts.
Hope this helps.
Dan Guzman
SQL Server MVP
"Sam Hobbs" <samuel@.social.rr.com_change_social_to_socal> wrote in message
news:O8AvMGo%23FHA.272@.TK2MSFTNGP09.phx.gbl...
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:u0%23ilqQ%23FHA.4092@.TK2MSFTNGP10.phx.gbl...
> The script would not actually be executed by the text editor, but
> otherwise this is an example of something that would be impractical to
> remove. The actual programs would be WScript and CScript. If removal of
> possibly risky tools is done to protect thd data, then WScript and CScript
> would have to be removed also, which would make all scripts useless. HTML
> scripting capability would also have to be removed.
>
> I think that perormance is not a typical security matter. Performance
> considerations such as this make the problem much more complicated. In
> those situations where elimination of possible performance problems
> require a security solution different from other security solutions, I
> think that the benefits would not justify the cost. I think that
> performance problems should not be a primary duty of security and is
> especially outside the scope of my project.
>
> I do not know if this application's login provides a different security
> context; I am not aware of that, but it is worth investigating.
> If the application provides the ability to execute a macro during or
> immediately following an application, is it possible that the security
> context could be changed then? I realize that this might not work for this
> particular application, so I am asking only if it might work.
> Perhaps it is possible to write a program that changes the security
> context then executes the application, but that would be impractical if it
> requires a separate login. I need to learn about SQL Server's roles before
> deciding to use a solution such as this.
> SQL Server's roles seem to be the prefered solution and I will read the
> article you specify before I ask any questions about roles.
>|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23E$%23Noz%23FHA.1676@.TK2MSFTNGP09.phx.gbl...
> I'm not sure I understand what you mean by 'macro'. If you are referring
> to SQL script and the script is run by the app immediately after each
> database connection, such an approach might allow you to enable an
> application role. AFAIK, other 'macro' techniques would require separate
> accounts.
What I meant by macro is that the application (optionally) calls something,
which can be a script, when it gets logged in; therefore the macro is
executed once per execution.
After thinking about it, I realized that that probably won't work, since the
applicaton probably accesses the database prior to the execution of the
login macro.
