Revoking public permissions

I am doing some security work on SQL server and am concerned about the permi
sions on the public role. i want to strip down the permisions given to the
public role in the system databases. What are the implications of runnign sc
ripts to revoke all to publ
ic for all statements and objects in all databases. I have read this causes
problems? What have other folk done regarding this and is there a definitive
list anywhere?
Thanks in advanceGemmill,
We have many databases with all rights to user tables, views, stored
procedures, etc. revoked for public. (You can do this by simply never
granting any rights to public as well.) Also, make sure you do not have
the guest account. (I have never tried to revoke public rights to the
system tables, so I don't know if you can do that. I guess I can go try
when I get back to my office.)
The main implication is that you will break rights that your people have
become accustomed to.
Russell Fields
"Gemmill" <anonymous@.discussions.microsoft.com> wrote in message
news:2657C8D7-F49D-47D1-99B6-04D470BE6D71@.microsoft.com...
> I am doing some security work on SQL server and am concerned about the
permisions on the public role. i want to strip down the permisions given to
the public role in the system databases. What are the implications of
runnign scripts to revoke all to public for all statements and objects in
all databases. I have read this causes problems? What have other folk done
regarding this and is there a definitive list anywhere?
> Thanks in advance|||Thanks for the reply russell. Iam fairly comforatable with removing permissi
ons as susggested from user tables. But am concerned it will cause problems
if i carry it out on the system databases. I would like to "lock down" publi
c permissions on the system
databases without seriously affecting system operation. What have you done i
n regards to this.
Thanks|||My recommendation would be to leave the system tables alone. If there
are any security holes that turn up that arise from granting
permissions to public, then they'll probably be addressed in a service
pack, if they haven't been already. If you try mucking around in the
systems tables yourself, you'll probably just end up breaking things.
-- Mary
MCW Technologies
http://www.mcwtech.com
On Fri, 27 Feb 2004 01:11:05 -0800, "gemmill"
<anonymous@.discussions.microsoft.com> wrote:

>Thanks for the reply russell. Iam fairly comforatable with removing permissions as
susggested from user tables. But am concerned it will cause problems if i carry it o
ut on the system databases. I would like to "lock down" public permissions on the sy
ste
m databases without seriously affecting system operation. What have you done in regards to
this.
>Thanks|||Best practices often tell you to remove PUBLIC role from all user objects an
d this should be done after a careful review of the permission required by y
our application.
Some experts also go as far as to recommend that you remove PUBLIC role perm
issions from system stored procedures and extended stored procedures to prev
ent attacks on your server. This should also be done, but only after carefu
l testing as removing PUBLI
C permissions to all stored procedures can often break some functionality in
Enterprise Manager. I do not know of a article/whitepaper that has studied
this and listed what exactly will be broken based on each system stored proc
edure.
Brian Kelley recently released a nice whitepaper on the PUBLIC role permissi
ons to system tables that you should read to see if it is any help.
SQL Server 2000: Permissions on System Tables Granted to Logins Due to the P
ublic Role
http://www.giac.org./practical/GSEC...Kelley_GSEC.pdf
Randy Dyess
www.Database-Security.Info|||Thanks for the reply's, much appreciated