SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


"Public" roles


"Public" roles

Author
Message
faissoilm
faissoilm
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 5
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
Steve Jones
Steve Jones
SSC Guru
SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)

Group: Administrators
Points: 224978 Visits: 19638
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?

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
faissoilm
faissoilm
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 5
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
Steve Jones
SSC Guru
SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)

Group: Administrators
Points: 224978 Visits: 19638
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
faissoilm
faissoilm
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 5
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
Lowell
SSC Guru
SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)

Group: General Forum Members
Points: 123604 Visits: 41464
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
faissoilm
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 5
AWESOME gonna test it now

Guys thanks for the help!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search