PUBLIC Role - What am I missing? 
Author Message
 PUBLIC Role - What am I missing?

Hello all.  I'm new to SQL security and wondering if the Public role
is as big of a pain as it appears.  Our goal is to allow the minimum
amount of access neccesary to read a single view in Access/EM and call
a single stored procedure.  It appears we can't do this without also
allowing:

 - Users to be able to enumerate databases (master.sysdatabases)
 - Look at SPROC text (syscomments)
 - Determine physical database structure (sysobjects)
 - Find out "stuff" about the system from master (sp_help*)

What am I missing?  I can get rid of Public's permissions one by one,
but this is tedious and there's the danger of breaking external tools.
 Are there scripts available that close down Public as much as
feasable?  Will these scripts break when a new version of SQL comes
out?

Thanks in advance for any help that can be provided.  Tell me it isn't
this bad!



Thu, 24 Mar 2005 09:52:50 GMT
 PUBLIC Role - What am I missing?

By default, when you create a new database, public has no permissions
on *any* objects, so there should be nothing to get rid of. You
shouldn't be messing around in the master database or with the system
objects unless you know what you are doing. All you need to do is to
create a custom database role and grant permissions to your single
view and your single stored procedure. All objects (table, view and
sproc) must have the same owner is all. And make sure nobody is
logging on as a sysadmin or db_owner.

-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446



Quote:
>Hello all.  I'm new to SQL security and wondering if the Public role
>is as big of a pain as it appears.  Our goal is to allow the minimum
>amount of access neccesary to read a single view in Access/EM and call
>a single stored procedure.  It appears we can't do this without also
>allowing:

> - Users to be able to enumerate databases (master.sysdatabases)
> - Look at SPROC text (syscomments)
> - Determine physical database structure (sysobjects)
> - Find out "stuff" about the system from master (sp_help*)

>What am I missing?  I can get rid of Public's permissions one by one,
>but this is tedious and there's the danger of breaking external tools.
> Are there scripts available that close down Public as much as
>feasable?  Will these scripts break when a new version of SQL comes
>out?

>Thanks in advance for any help that can be provided.  Tell me it isn't
>this bad!



Fri, 25 Mar 2005 03:04:32 GMT
 PUBLIC Role - What am I missing?
Hey there Bill - You are correct - but I wouldn't be worried... Let me
explain! By default, users with logins to the server can get into ANY
database which has a GUEST account. In the master database there is a guest
user - guest is a member of public and therefore has all rights directly
associated with their account (guest) as well as all rights of the roles in
which they are a member - meaning public.

It is true that public has rights to some of the system procedures (sp_help,
sp_helptext, sp_helpdb) but without direct access to the objects this
doesn't do them much good. They can see the names of the databases and the
names of the objects within the databases they have access BUT it doesn't
give them any rights to execute procedures or access any tables.

One thing you might be interested in is object obfuscation - meaning that
you can hide the procedural code of stored procedures, triggers and views.
See the "with encryption" option associated with each of these objects...

That should help. Outside of that - I wouldn't mess with the permissions in
master... It's really not that big of a deal - only the names of objects are
compromised - nothing else! And if databases should not be visible in any
way - then consider creating another instance of SQL Server and separate the
databases by using instances (although that means more overhead - you'll be
dealing with two sets of system databases, instance names on connection
strings, etc.). This might be more hassle than it's worth.

hth,
kt

Kimberly L. Tripp
********************
Please do not send mail to me directly - reply on the newsgroup.
Please include legible and tested code samples (and ddl if possible!). This
makes it easier to test and answer your questions. Thanks!


Quote:
> Hello all.  I'm new to SQL security and wondering if the Public role
> is as big of a pain as it appears.  Our goal is to allow the minimum
> amount of access neccesary to read a single view in Access/EM and call
> a single stored procedure.  It appears we can't do this without also
> allowing:

>  - Users to be able to enumerate databases (master.sysdatabases)
>  - Look at SPROC text (syscomments)
>  - Determine physical database structure (sysobjects)
>  - Find out "stuff" about the system from master (sp_help*)

> What am I missing?  I can get rid of Public's permissions one by one,
> but this is tedious and there's the danger of breaking external tools.
>  Are there scripts available that close down Public as much as
> feasable?  Will these scripts break when a new version of SQL comes
> out?

> Thanks in advance for any help that can be provided.  Tell me it isn't
> this bad!



Fri, 25 Mar 2005 06:51:50 GMT
 PUBLIC Role - What am I missing?
I'd like to thank Mary and Kimberly for their helpful replies.  They
clarify a lot.  Our concern wasn't really that people would be able to
get into secured data - we faith in SQL Server that this isn't
possible.  It's the meta-data that troublesome.

The issue is that in accounting software (our product), people's
feelings are hurt by seeing options that are unavailable to them -
someone else has decided they aren't trustworthy.  For example, in our
GUI, we remove, rather than grey out menu options that someone's
permissions won't allow.  This way, they don't go to their supervisor
and say "how come you won't allow me to set up rate tables".

The only issue here is reporting and people seeing tables that they
are locked out of.  If that's the way it is, though, we're not going
to lose sleep - our clients will just need to live with this.

Thanks again for the help!



Sat, 26 Mar 2005 07:25:53 GMT
 PUBLIC Role - What am I missing?
I've heard your comments before... This would make a great DCR (Design
Change Request).


to hide metadata and/or more selectively allow users to see an object's
existence... Even disallowing the SA would be a great option IMHO.

hth,
kt

Kimberly L. Tripp
********************
Please do not send mail to me directly - reply on the newsgroup.
Please include legible and tested code samples (and ddl if possible!). This
makes it easier to test and answer your questions. Thanks!


Quote:
> I'd like to thank Mary and Kimberly for their helpful replies.  They
> clarify a lot.  Our concern wasn't really that people would be able to
> get into secured data - we faith in SQL Server that this isn't
> possible.  It's the meta-data that troublesome.

> The issue is that in accounting software (our product), people's
> feelings are hurt by seeing options that are unavailable to them -
> someone else has decided they aren't trustworthy.  For example, in our
> GUI, we remove, rather than grey out menu options that someone's
> permissions won't allow.  This way, they don't go to their supervisor
> and say "how come you won't allow me to set up rate tables".

> The only issue here is reporting and people seeing tables that they
> are locked out of.  If that's the way it is, though, we're not going
> to lose sleep - our clients will just need to live with this.

> Thanks again for the help!



Sat, 26 Mar 2005 13:56:12 GMT
 PUBLIC Role - What am I missing?
Good thought, Kimberly.  I've sent a "wish" E-Mail to the SQL team.
Thanks again for your help...


Sun, 27 Mar 2005 01:50:02 GMT
 PUBLIC Role - What am I missing?
Hi Bill,

I don't know the easy way to change ownership of a role. But it looks like
you can make it less painful by using the EM and "Generating SQL Script"
under All Task for the database and generate all the permissions for that
role.

Check the script all objects on the general panel
Check ONLY the " .. Create <object> .. " on the format panel
Check "Script database users and database roles" on the options panel

After generating the SQL script file then extract all the grants and denies
for that role.
Then in QA drop the role and recreate the new role with the new owner and
last but not least, run the extracted grants and denies from the generated
script. It's easier than doing it by hand.

You might have to play around with the different checkboxes in the different
panels to get every grant/deny permission to be generated other than what I
have mentioned above. But what I did worked pretty good and pretty fast.

Hope that is of some help.
John

PS

Someone should write a sproc that does this functionality of changing the
owner of a Role.. Wouldn't that be great!


Quote:
> Hello all.  I'm new to SQL security and wondering if the Public role
> is as big of a pain as it appears.  Our goal is to allow the minimum
> amount of access neccesary to read a single view in Access/EM and call
> a single stored procedure.  It appears we can't do this without also
> allowing:

>  - Users to be able to enumerate databases (master.sysdatabases)
>  - Look at SPROC text (syscomments)
>  - Determine physical database structure (sysobjects)
>  - Find out "stuff" about the system from master (sp_help*)

> What am I missing?  I can get rid of Public's permissions one by one,
> but this is tedious and there's the danger of breaking external tools.
>  Are there scripts available that close down Public as much as
> feasable?  Will these scripts break when a new version of SQL comes
> out?

> Thanks in advance for any help that can be provided.  Tell me it isn't
> this bad!



Sun, 27 Mar 2005 10:01:23 GMT
 PUBLIC Role - What am I missing?
and we got it... :-)

--
Richard Waymire, MCSE, MCDBA

This posting is provided "AS IS" with no warranties, and confers no rights.

Quote:
> Good thought, Kimberly.  I've sent a "wish" E-Mail to the SQL team.
> Thanks again for your help...



Sun, 27 Mar 2005 12:20:49 GMT
 
 [ 8 post ] 

 Relevant Pages 

1. user defined role and public role

2. Delete nonexistant user from public role

3. Removing login id from public roles.

4. Public role

5. Removing members from the role Public

6. Removing Member from the role Public

7. Removing Members from the role public

8. Removing Members from the role Public

9. PUBLIC ROLE

10. Public Role

11. public role???

12. How to remove invalid user from public role?


 
Powered by phpBB® Forum Software