2nd REPOST (HELP!): Blocking access to system sprocs

I have posted two other times and Kalen Delaney and David Browne were kind
enough to respond. I thank them. Nonetheless, I am still without a
solution and need help. My first subject probably wasn't good enough and
perhaps I missed the attention of others in this group. This problem is
not as easy of an issue as it sounds. I think this is a serious security
hole in SQL Server and I hope Microsoft jumps into this thread. If you
disagree with me about how serious this is, please just be respectful that I
want a solution for my purposes nonetheless.
System: SQL 2005
Problem: I want to set up a user (from ASP.NET code) that has only execute
access on the database stored procs, but NOT access to system stored procs.
But, from my testing, even before I issue the issue any access or role
membership to a user, it seems that any users of a database can access
system stored procs (such as sp_stored_procedures, which exposes all the
user stored proc names that they can call with their execute access!).
Why is this a security problem, some ask? Because you never want to expose
your table names and proc names should a hacker somehow get your
username/pass. Also, even aside from knowing the company database
metadata, there are some system procs that do real damage, as mentioned in
this video:
http://www.learnsqlserver.com/VideoTutorials/Sql-Server-Video/352/Some-Dangerous-Stored-Procedures-xp-cmdshell-sp.aspx
Some argue that blocking system stored procs can ruin Management Studio
features for that user. True, but that is fine for an ASP.NET user that
will never be in Management Studio.
So why not just do DENY EXECUTE on sp_stored_produres TO [user or role]?
I've tried that and it doesn't work. You get an internal error about
permissions on server scoped catalog views or system stored procedures.
One not-so-good workaround? This issue has been discussed in only one other
forum I found. The solution was to create a same-named user in MASTER, and
then DENY execute on each individual system stored proc in master. That
sounds like a horribly tedious solution, and I'm not crazy about hand-typing
hundreds of system stored proc names. Besides, you would have to do this by
user. I tried doing it on a role, like a "db_executor" role I created, but
that doesn't work at all. See:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=407228&SiteID=1
I also found this Micrsoft article, but it didn't help because sys was not
recognized from inside my database, nor was system_objects:
http://msdn2.microsoft.com/en-us/library/ms178634.aspx
Pleeeeasssse help with a solution to blocking system stored procs, even if
you don't think it is that necessary for security. Thank you.
On Mar 7, 12:40 pm, "K. Abit" <sendtothegroupo...@.this-is-not-
real.com> wrote:
> I have posted two other times and Kalen Delaney and David Browne were kind
> enough to respond. I thank them. Nonetheless, I am still without a
> solution and need help. My first subject probably wasn't good enough and
> perhaps I missed the attention of others in this group. This problem is
> not as easy of an issue as it sounds. I think this is a serious security
> hole in SQL Server and I hope Microsoft jumps into this thread. If you
> disagree with me about how serious this is, please just be respectful that I
> want a solution for my purposes nonetheless.
> System: SQL 2005
> Problem: I want to set up a user (from ASP.NET code) that has only execute
> access on the database stored procs, but NOT access to system stored procs.
> But, from my testing, even before I issue the issue any access or role
> membership to a user, it seems that any users of a database can access
> system stored procs (such as sp_stored_procedures, which exposes all the
> user stored proc names that they can call with their execute access!).
> Why is this a security problem, some ask? Because you never want to expose
> your table names and proc names should a hacker somehow get your
> username/pass. Also, even aside from knowing the company database
> metadata, there are some system procs that do real damage, as mentioned in
> this video:http://www.learnsqlserver.com/VideoTutorials/Sql-Server-Video/352/Som...
> Some argue that blocking system stored procs can ruin Management Studio
> features for that user. True, but that is fine for an ASP.NET user that
> will never be in Management Studio.
> So why not just do DENY EXECUTE on sp_stored_produres TO [user or role]?
> I've tried that and it doesn't work. You get an internal error about
> permissions on server scoped catalog views or system stored procedures.
> One not-so-good workaround? This issue has been discussed in only one other
> forum I found. The solution was to create a same-named user in MASTER, and
> then DENY execute on each individual system stored proc in master. That
> sounds like a horribly tedious solution, and I'm not crazy about hand-typing
> hundreds of system stored proc names. Besides, you would have to do this by
> user. I tried doing it on a role, like a "db_executor" role I created, but
> that doesn't work at all. See:http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=407228&SiteID=1
> I also found this Micrsoft article, but it didn't help because sys was not
> recognized from inside my database, nor was system_objects:http://msdn2.microsoft.com/en-us/library/ms178634.aspx
> Pleeeeasssse help with a solution to blocking system stored procs, even if
> you don't think it is that necessary for security. Thank you.
This is easily done, I just confirmed that I can prevent a standard
SQL login from executing sp_help. Add the login to your user database
AND to the master database. In MASTER, go into the properties for
that login. Under "Securables", add "All objects belonging to the
schema" and choose the "sys" schema. Select the sprocs that you want
to prevent execution of, and check the "Deny" box.
I confirmed this by creating a login named "test", logged in with that
login, ran sp_help. I then denied access as described above, and now
get a permission denied error trying to run sp_help.
|||"Tracy McKibben" <tracy.mckibben@.gmail.com> wrote in message
news:1173294431.368031.226630@.s48g2000cws.googlegr oups.com...
> On Mar 7, 12:40 pm, "K. Abit" <sendtothegroupo...@.this-is-not-
> real.com> wrote:
> This is easily done, I just confirmed that I can prevent a standard
> SQL login from executing sp_help. Add the login to your user database
> AND to the master database. In MASTER, go into the properties for
> that login. Under "Securables", add "All objects belonging to the
> schema" and choose the "sys" schema. Select the sprocs that you want
> to prevent execution of, and check the "Deny" box.
> I confirmed this by creating a login named "test", logged in with that
> login, ran sp_help. I then denied access as described above, and now
> get a permission denied error trying to run sp_help.
>
Well, I agree that works, thanks, but that is the same concept as in the
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=407228&SiteID=1
article which I posted earlier, except yours is through the GUI. The
problem, still, is that I would have to do this for hundreds of stored
procedures, which is a LOT of clicking considering you have to select a proc
in one pane and then choose the deny in the other pane. Doing that would
take a long time for just one user.
I appreciate the reply, but I cannot believe Microsoft has not given us an
easier way to block access to all system stored procs. I suppose they're
waiting for some big company to have this exploit used on them, and then
they'll issue a service pack a year later.
|||On Mar 7, 9:40 pm, "K. Abit" <sendtothegroupo...@.this-is-not-real.com>
wrote:
> Well, I agree that works, thanks, but that is the same concept as in thehttp://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=407228&SiteID=1
> article which I posted earlier, except yours is through the GUI. The
> problem, still, is that I would have to do this for hundreds of stored
> procedures, which is a LOT of clicking considering you have to select a proc
> in one pane and then choose the deny in the other pane. Doing that would
> take a long time for just one user.
> I appreciate the reply, but I cannot believe Microsoft has not given us an
> easier way to block access to all system stored procs. I suppose they're
> waiting for some big company to have this exploit used on them, and then
> they'll issue a service pack a year later.
Rather than nitpick about what Microsoft did or did not do, can't you
simply write a script that loops through sysobjects and issues the
necessary DENY's against every stored proc? This query will get you
all of the system procs if you run it in master:
SELECT *
FROM dbo.sysobjects
WHERE xtype = 'P'
I'm betting there's another way to do this using schemas, but I
haven't worked with them enough to know.
|||"Tracy McKibben" <tracy.mckibben@.gmail.com> wrote in message
news:1173357526.661405.239300@.64g2000cwx.googlegro ups.com...
> On Mar 7, 9:40 pm, "K. Abit" <sendtothegroupo...@.this-is-not-real.com>
> wrote:
> Rather than nitpick about what Microsoft did or did not do, can't you
> simply write a script that loops through sysobjects and issues the
> necessary DENY's against every stored proc? This query will get you
> all of the system procs if you run it in master:
> SELECT *
> FROM dbo.sysobjects
> WHERE xtype = 'P'
> I'm betting there's another way to do this using schemas, but I
> haven't worked with them enough to know.
>
I think Microsoft deserves the criticism. This is a security less-than-best
practice that is not easy to work around. Their default setup here is NOT
good. I will try the 1200 DENY statements and see if that messes up
anything. Thanks.