|
Home > Archive > MS SQL Server > July 2005 > Understanding the Public Role
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Understanding the Public Role
|
|
| Jim Abel 2005-07-15, 8:23 pm |
| I'm in the process pf hardening the SQL Server 2000 DB's that I manage.
I have come into a situation that I don't understand as follows
The task is to revoke EXECUTE Permissions to Public on the Stored Procedures
in the msdb database for
sp+add_job
sp_start_job
When I did this I broke a Reporting Services utility for a subscription.
Assuming that the Reporting Services account was impacted I GRANTED Execute
to the account for those SP's.
The error then changed to tell me that permissions were denied to msdb.
Can you shed some light on what is happening?
thxs Jim
| |
| David Gugick 2005-07-15, 8:23 pm |
| Jim Abel wrote:
> I'm in the process pf hardening the SQL Server 2000 DB's that I
> manage.
> I have come into a situation that I don't understand as follows
> The task is to revoke EXECUTE Permissions to Public on the Stored
> Procedures in the msdb database for
> sp+add_job
> sp_start_job
> When I did this I broke a Reporting Services utility for a
> subscription. Assuming that the Reporting Services account was
> impacted I GRANTED Execute to the account for those SP's.
>
> The error then changed to tell me that permissions were denied to
> msdb.
>
> Can you shed some light on what is happening?
>
> thxs Jim
I don't believe the public group has rights to create and start jobs by
default. Is this something that was added?
--
David Gugick
Quest Software
www.imceda.com
www.quest.com
| |
| Jim Abel 2005-07-15, 8:23 pm |
|
"David Gugick" wrote:
> Jim Abel wrote:
>
> I don't believe the public group has rights to create and start jobs by
> default. Is this something that was added?
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
>
Public had Execute for these SP's and RS could create the subscription.
When I Revoked Execute to the Stored Procs then RS Subscriptions faild saying
that It couldn't execute sp_add_job
I then Greanted Execute to the RS account and the error changed to access
denied to msdb
I then Granted Execute to Public for the sp's and the RS subscriptions are
working once again.
I guess that I question the validity of revking Public from Stored
Procedures but am trying to understand the role and why it is considered a
security risk.
| |
| Tibor Karaszi 2005-07-16, 8:23 pm |
| > Public had Execute for these SP's and RS could create the subscription.
Yes, by default there's a guest in msdb and everyone, through guest and execute granted to public
for these procs can create jobs. Bad default, IMO. Fixed in 2005, I believe.
> When I Revoked Execute to the Stored Procs then RS Subscriptions faild saying
> that It couldn't execute sp_add_job
Revoked from public, I assume? Makes sense so log, assuming the RS user weren't granted permissions
explicitly.
> I then Greanted Execute to the RS account and the error changed to access
> denied to msdb
That should no happen. Can you redo this, using TSQL?
REVOKE EXEC ON sp_add_job FROM public
GRANT EXEC ON sp_add_job TO rs_account_name
And then post back the exact error message?
Also, login using QA using the RS account and try to create jobs using TSQL...
> I then Granted Execute to Public for the sp's and the RS subscriptions are
> working once again.
> I guess that I question the validity of revking Public from Stored
> Procedures but am trying to understand the role and why it is considered a
> security risk
Personally, I prefer to remove guest from msdb,
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/
"Jim Abel" <JimAbel@discussions.microsoft.com> wrote in message
news:A0C93AA3-B439-44B2-B91E- B66BFCEDB04D@microso
ft.com...
>
>
> "David Gugick" wrote:
>
> Public had Execute for these SP's and RS could create the subscription.
> When I Revoked Execute to the Stored Procs then RS Subscriptions faild saying
> that It couldn't execute sp_add_job
> I then Greanted Execute to the RS account and the error changed to access
> denied to msdb
> I then Granted Execute to Public for the sp's and the RS subscriptions are
> working once again.
> I guess that I question the validity of revking Public from Stored
> Procedures but am trying to understand the role and why it is considered a
> security risk.
|
|
|
|
|