public role sys* table permissions

Question

public role sys* table permissions

jason_langston

Mr or Mrs. 500

Points: 553
More actions
February 15, 2002 at 8:13 am

#78208

I'm embarassed to ask, but ...
Does SQL Server default to allowing the public role SELECT permissions on system tables?!
If so is there a reason for this?
just seems like a big old security loophole to me. (A user can only view the tables you allow, but can easily do a SELECT from sysusers and syspermissions?!)
If this is so, it seems like security 101 should be 1. change sa password 2. DENY public on sys* tables.
Maybe that is security 101 and I just missed it? (entirely possible)
JasonL

JasonL

Viewing 12 posts - 1 through 11 (of 11 total)

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

Antares686 SSC Guru Points: 125444 More actions · Answer 1

For some, even some in the master database are granted selected access whether or not you gave them. Until you asked I did not even realize this and see that as a security hole myself. I will see if I can get a confirmation on why. Or if someelse knows can you post here.

Antares686 SSC Guru Points: 125444 More actions · Answer 2

Sorry I made an error in my statement. All databases have Public with select rights from the begining but the user can only access those of the DB they are in and master (whether or not granted directly to the user).

Andy Warren SSC Guru Points: 119902 More actions · Answer 3

Dont know if deny would be a good idea, rather than just removing the permissions.

Andy

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter

Antares686 SSC Guru Points: 125444 More actions · Answer 4

But deny would be taking the check box out, revoke is an X ad grant is a check. You could create a role with REVOKE on those tables and put everyone in them to be safe. But you cannot remove any user from public role. I have emailed Microsoft to see if I can get a valid reason why this is such, especially with the fact you can query and see other accoutns that have access to the server, find out a list of objects from master and any db's the have permit access on. As well, get details of the file structure of all DBs on the server. What happened to use Stored Procedures to protect tables (they are not following their own rulles).

jason_langston Mr or Mrs. 500 Points: 553 More actions · Answer 5

well, hey, thanks for the replies. I'm little less embarassed now.

I've been doing some digging on this but there doesn't seem to be a lot of info. I've seen in the past recommendations to remove the permissions for PUBLIC on certain extended and system stored procedures (or just remove those sp's on a production box) which we've done, but ... I was shocked to find that PUBLIC could SELECT system tables. I'm not that creative but the info in those tables could help me a long way if I was trying to hack into my database/network.

Since this seems to be a default setting I'd think there would be more information from MS to plug this up.

I'm just wondering before I go through and revoke that privilege all over the place if doing so might break something. (ie. PUBLIC has access to TEMP and MASTER and you can't change that so there must be a reason it's there).

JasonL

jason_langston Mr or Mrs. 500 Points: 553 More actions · Answer 6

Hey all -

there is a similar question here :

http://www.sqlsecurity.com/mb/view_message.asp?message_id=70

I'll keep my eye on that too and post any links info that turn up.

JasonL

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

quote:
But deny would be taking the check box out, revoke is an X ad grant is a check

Deny is the X. Revoke means no permissions are set. You can use Revoke to remove a deny.

As for the system tables, the public role does have select permissions by default, and I'm sure there's a good reason for it... It doesn't however, affect seem to affect the ability to execute stored procedures. This is something I just verified.

K. Brian Kelley

bkelley@sqlservercentral.com

http://www.sqlservercentral.com/columnists/bkelley/

K. Brian Kelley
@kbriankelley

Antares686 SSC Guru Points: 125444 More actions · Answer 8

D*$% Sorry about that, guess you really should only be doing 1 thing at a time to avoid mistakes.

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

You're suffering from that problem, too? I've got three different systems up at my cube (1 desktop and 2 laptops) and am active on all of them at the moment (usual day). Thank goodness for the 3-day weekend!

K. Brian Kelley

bkelley@sqlservercentral.com

http://www.sqlservercentral.com/columnists/bkelley/

K. Brian Kelley
@kbriankelley

Steve Jones - SSC Editor SSC Guru Points: 736210 More actions · Answer 10

It may be a security hole, but you also might need select permissions to GET your permissions. Not that it's agood design, but I wouldn't be surprised.

Steve Jones

steve@dkranch.net

Antares686 SSC Guru Points: 125444 More actions · Answer 11

Here is what I sent to Microsoft. Someone else may want to do something similar just to see if they will answer or make a change in this.

In SQL 7 and SQL 2000. I created a user with access to no tables on a database and set their defualt database to other than master and checked permit on the database. I was then able to query sysusers, sysobjects, and sysdatabases on the local granted DB and master (even thou I did not check permit for master). I can see other user names, what objects are in a DB and other database names and their file path on the server.

Why is this, and why create such a security loophole where a public user can get more details about other accounts with access and other details about the server? I consider this a security issue in a big way especially since you cannot drop users from that role. I can find no detail for this on your site concerning this as a feature or reason why this is the default.