"Public" roles

Question

Post reply

"Public" roles

faissoilm

SSC Rookie

Points: 32
More actions
December 11, 2013 at 7:05 am

#302242

Hi guys,
A bit new in SQL, and I was just wondering if someone could tell me what roles exactly got "public" with those two lines :
INFORMATION_SCHEMA TABLE_PRIVILEGES public dbo Grant Select (All)
INFORMATION_SCHEMA TABLES public dbo Grant Select (All)
The way I understand it, is that anyone can "select" any table of the database? Should taht role be revoked as it is a security issue?
Thanks for your help

Viewing 7 posts - 1 through 6 (of 6 total)

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

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 1

Your question doesn't quite make sense and I'm not sure what I'm looking at in the lines with INFORMATION_SCHEMA.

Is there some code you ran or a dialog you see this in?

faissoilm SSC Rookie Points: 32 More actions · Answer 2

hey John

thanks for the answer and sorry, i will try tobe more clear

I ran sp_helprotect to have information about user permissions for an object, and in the result I can see that "SELECT" is granted to Public on "TABLES". So I just want to understand what rights it gives to "Public" which is for my understanding "all user"

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 3

Rights granted to public are available for all users. This is usually a bad idea since it prevents you from setting limited permissions.

The information_schema tables are meta data tables. The Information_Schema.tables table has all the table names in it. There are select rights here to view the names of the tables, but not to actually view the data in them.

faissoilm SSC Rookie Points: 32 More actions · Answer 4

Damn John Thanks !!

So to make sure I get the point here , does it mean that for "SYS", if "public" has "select" granted, then all users are able to see the data ?

Lowell SSC Guru Points: 323504 More actions · Answer 5

this can be a little misleading, especially when it comes ot using sp_helpprotect.

that procedure lists the objects that the public role,(in this specific example) can see,

if you look at the list,the OWNER column specifically, it's all information_schema and sys, and lots of objects which belong to those schemas; that is PERFECTLY NORMAL.

an end user has access to the view sys.tables for example, but he can see rows INSIDE that view he actually has access to...so if you create a new table, or for any existing tables, a person that exists only in the public role does not know that those tables exist.

the public role gives end users the ability to find data that they have access to...and the data in the sys views are filtered by end user permissions.

test this for yourself: create a user, without any other roles, so it only has the default public permissions.

CREATE USER TestUser WITHOUT LOGIN;

EXECUTE AS User='TestUser'

select * from sys.tables --i only see some spt_* tables, but I KNOW there are other tables there!

REVERT; --change back into my original role

DROP USER TestUser

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

faissoilm SSC Rookie Points: 32 More actions · Answer 6

AWESOME gonna test it now

Guys thanks for the help!