Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

"Public" roles Expand / Collapse
Author
Message
Posted Wednesday, December 11, 2013 7:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 5:56 AM
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
Post #1521883
Posted Wednesday, December 11, 2013 7:14 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 8:20 PM
Points: 32,764, Visits: 14,928
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
Post #1521886
Posted Wednesday, December 11, 2013 7:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 5:56 AM
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"

Post #1521895
Posted Wednesday, December 11, 2013 7:32 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 8:20 PM
Points: 32,764, Visits: 14,928
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
Post #1521900
Posted Wednesday, December 11, 2013 7:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 5:56 AM
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 ?
Post #1521905
Posted Wednesday, December 11, 2013 7:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:29 AM
Points: 12,741, Visits: 31,050
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1521914
Posted Wednesday, December 11, 2013 7:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 5:56 AM
Points: 4, Visits: 5
AWESOME gonna test it now

Guys thanks for the help!
Post #1521917
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse