Guest account within MASTER

Question

Post reply

Guest account within MASTER

David.Poole

SSC Guru

Points: 76035
More actions
June 5, 2003 at 7:49 am

#80175

I've been looking at the public role within the master database.
There seem to be a number of stored procedures that I would not like to grant permissions to such a user. Xp_DirTree being one of them.
I would like to revoke permissions on some of these stored procedures but I could do with some clarification as to what to leave alone and what to remove.
I notice that quite a few of the stored procedures have checks in them to make sure that it is the dbo that is making changes.
sp_dboption lets any user interogate the database options even if it doesn't let them alter any.
sp_who reveals who is logged on to what, sp_helpdb lists all my databases even if my user does not have access to them.
I would be interested to hear what approach you take with this.
LinkedIn Profile

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Hi David,

I haven't touched any permissions in master yet, and depending on how this thread goes on, I'll see if I'll do so.

However, I think if you remove permissions from public in master or temp your server is likely to become unusable. People will not be able to use sp_help, other system stored procedures and system functions. They will not be able to login. People need to 'pass through' master to have their logins validated and everyone needs permission to tempdb in case the optimizer needs to create a working table.

However, on SQL7 I have taken a closer look at model and found permissions granted for public. I thought it was a good idea to revoke these permissions so that I don't have to do this manually every time after creating a new database. I can't remember what problems I got, but I ran into massive problems creating a new db. So I decided to restore permissions in model to their original state and leave my hands off this ever since!

But I'm interested to read opinions from people who roll out their apps to customers

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

BTW, I think you must have a guest user in master and temdb. You should not change its permissions in those databases.

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

Another BTW, ,

maybe you want to check out Haunting flaws in Microsoft SQL Server on http://www.appsecinc.com . I think it's a powerpoint file.

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

Here is the full link

http://www.appsecinc.com/techdocs/presentations.html

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

David.Poole SSC Guru Points: 76035 More actions · Answer 5

Thanks for that, I particularly like the closing statement "...and pray!".

It mentions three or four extended stored procs to block.

I notice xp_dirtree isn't documented anywhere. Come to think of it there are a hell of a lot of stored procs that simply aren't documented.

I am tempted to create a role in the master database called copypublic and copy the permissions from public into copypublic.

I can then revoke the permissions on various procs until something breaks safe in the knowledge that at least I have a definitive list of permissions somewhere.

LinkedIn Profile

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

Hi David,

quote:
Thanks for that, I particularly like the closing statement "...and pray!".

oooh I believe they meant 'plug and PLAY

quote:
I am tempted to create a role in the master database called copypublic and copy the permissions from public into copypublic.
I can then revoke the permissions on various procs until something breaks safe in the knowledge that at least I have a definitive list of permissions somewhere.

Good luck for this.

First tell your family that you're gonna be a little late today

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 7

I've actually done some research on this. Guess I need to put it together in an article. If you can wait a day or so, I'll go find my write-ups. There's one view and one stored procedure, if I remember right, that's absolutely essential. The rest can be undone, but you may break something.

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

K. Brian Kelley
@kbriankelley

David.Poole SSC Guru Points: 76035 More actions · Answer 8

quote:
I've actually done some research on this. Guess I need to put it together in an article. If you can wait a day or so, I'll go find my write-ups. There's one view and one stored procedure, if I remember right, that's absolutely essential. The rest can be undone, but you may break something.

Brian, if you lived locally I would buy you a beer!

LinkedIn Profile

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 9

First, Guest account has no any permission to any objects but it is in public role. You can't remove guest account from maste and tempdb. If you revoke permission from public role, everyone will not be able to access the object unless you grant the access specially to the particular user. i would like to keep public role unchange, instead, target those user who do not need access to those SPs/XPs and deny their access to those SPs/XPs etc.

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 10

What Microsoft did for OpenHack 4 is basically drop all the permissions the public role had. They left two, but I can't remember them off the top of my head. Also, I did some research on what happens if you, say, drop the permissions to sysdatabases (don't plan on linking from Access). That's what I'll put together.

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

K. Brian Kelley
@kbriankelley

Frank Kalis SSC Guru Points: 111183 More actions · Answer 11

Hi Brian,

quote:
What Microsoft did for OpenHack 4...

what is OpenHack 4???

Looking forward for your article!

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 12

OpenHack 4 was a competition sponsored by eWeek. They invited Microsoft and Oracle to lock down an application (Microsoft had to re-write it since it was in JSP, so they converted it to .NET) and then had an open invitation hack fest. The only break was on the Oracle side, but I believe it was an issue in the application code (provided to the competitors) itself. Even so, it was a relatively minor break.

Microsoft made heavy use of non-standard things like revoking public access to almost all tables and stored procedures. In addition, they made very heavy use of IPSec policies between the various servers.

The permissions which public maintained:

SELECT on spt_values

EXECUTE on sp_MShasdbaccess

Keep in mind that altering permissions results in a non-standard config, meaning Microsoft may require you to put permissions back the way they were before supporting you.

Here are some of the tables where I saw difficulties if you dropped the public roles access:

syscharsets

sysdatabases *

* This one is a biggie for linking with Access and the like. You can get away with not having access to syscharsets, but sysdatabases can be a show stopper to creating an ODBC connection.

As far as system stored procedures, there are probably too many to enumerate. Consider that when MS Access goes to get a list of tables, it use sp_tables, etc.

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

K. Brian Kelley
@kbriankelley

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 13

BTW, here are the OpenHack 4 write-ups:

http://www.eweek.com/category2/1,3960,600431,00.asp

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

K. Brian Kelley
@kbriankelley

Frank Kalis SSC Guru Points: 111183 More actions · Answer 14

Thanks Brian,

that doesn't seem to be a very realistic every day scenario they've put up when clashing. More like a marketing and prestige event (as always when MS and Oracle meet ).

I could imagine that by changing default installation of SQL Server you're running into problems with MS support or third-party vendors (as you've mentioned).

However, as I am sitting behind a firewall with some kind of content management observation, I am not able to open openhack.com. I must wait for my admin...

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]