Revoking PUBLIC role access from objects.
-
יום שני 02 אפריל 2012 15:24
Hi,
I am working on a project to revoke the PUBLIC roles access on all our SQL servers as part of a FISMA compliance project. So far I have had some success on a few servers. I now am having a problem on one server that I don't understand. The server in question is running SQL 2005 and the latest SP.
In the initial audit of the server several thousand objects were found to have PUBLIC role access. The auditing software we are using built us a fix script to revoke the PUBLIC roles access with several thousand commands that looked like this:
IF DB_ID(N'DBName') IS NOT NULL
EXEC('USE [DBName]' + ' REVOKE EXECUTE ON [Object].[Objectname] FROM PUBLIC')
Before running these revoke commands I created a new role and wrote a reverse the script to give the new role access to the objects, this script looks something like:
IF DB_ID(N'DBName') IS NOT NULL
EXEC('USE [DBName]' + ' GRANT EXECUTE ON [Object].[Objectname] TO NewRole')
After running the script on a server yesterday I was left with about 30 objects that the PUBLIC role still had access to. The odd thing is that the script completed successfully. In the past when I've had issues doing running this script when the script failed to revoke the access I got an error message, but I did not see that yesterday.
For example, if I execute:
IF DB_ID(N'master') IS NOT NULL
EXEC('USE [master]' + ' REVOKE EXECUTE ON [dbo].[sp_clean_db_free_space] FROM PUBLIC')
In a query window "Command(s) completed successfully." is returned.
But when I go back and check the object I can see the PUBLIC role still has EXECUTE, and running the audit software indicates this is the case as well.
Can anyone explain this behavior?
Thanks
כל התגובות
-
יום שני 02 אפריל 2012 19:14Are they got any schema binding enabled?
http://uk.linkedin.com/in/ramjaddu
-
יום שני 02 אפריל 2012 20:18. I don't think they have Schema Binding enabled, since they are stored proc's in the Master DB and we have done nothing to enable Schema Binding. But I'm not sure how to tell for sure.
-
יום שני 02 אפריל 2012 22:00
First of all, the procedure will raise an error if you are not member of db_owner of the database you pass as parameter.
Next, I did
REVOKE EXECUTE ON dbo.sp_clean_db_free_space TO public
in the master database. Then when I run
sp_helprotect sp_clean_db_free_space
I get
Msg 15330, Level 11, State 1, Procedure sp_helprotect, Line 291
There are no matching rows on which to report.Nor do I get any rows when I run:
SELECT * FROM sys.database_permissions where major_id = object_id('sys.sp_clean_db_free_space')
I tested this on SQL 2005 SP4.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se -
יום שלישי 03 אפריל 2012 19:19
Thanks for the reply,
I am not receiving an error and have used this same account to revoke the public role access on 1000's of object on this system, and I have used the account to revoke access on the objects that are not working on this server successfully on other SQL 2005 servers, so I don't think that is the issue.
When I run
REVOKE EXECUTE ON dbo.sp_clean_db_free_space TO public
I get "Command(s) completed successfully."
When I then run
sp_helprotect sp_clean_db_free_space
I get:
dbo sp_clean_db_free_space public dbo Grant Execute .
sys sp_clean_db_free_space APP01_USERS dbo Grant Execute .When I run
SELECT * FROM sys.database_permissions where major_id = object_id('sys.sp_clean_db_free_space')
This is returned:
1 OBJECT_OR_COLUMN -701756037 0 9 1 EX EXECUTE G GRANT
Also the sp_clean_db_free_space is an odd object because it is one of two objects (sp_clean_db_file_free_space being the other one) that still appears in the SMSS when I select the properties for the Public Role in the Master db and then select Securables, there I can see that EXECUTE is still checked. For what it is worth, if I uncheck EXECUTE from here, click on OK, then launch the Securables properties again EXECUTE is still checked.
Thanks for your help.
- נערך על-ידי rich1233 יום שלישי 03 אפריל 2012 19:38
-
יום שלישי 03 אפריל 2012 21:53
-
יום רביעי 04 אפריל 2012 13:42
Hi
Thanks for the reply.
App01 is role I created to replace the Public role.
Yes this server was upgraded from SQL 2000.
-
יום רביעי 04 אפריל 2012 21:50
Yes this server was upgraded from SQL 2000.
That explains why it does not work on this server, when it works on others. I don't have any such server at hand to test with, but with the knowledge of the changes between SQL 2000 and SQL 2005, I find this very likely.
If you consider this really serious, you could call CSS. Don't forget to refer to the compliance requirements you have. , I don't think it's likely you will get a fix, but if you need a written document to show to the auditors.
As I mentioned, the procedure checks for membership in the db_owner role, so there is no real security issue.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se -
יום שני 09 אפריל 2012 15:23Thanks is there a link I can use to open a case with CSS, or a phone number I can call?
-
יום שני 09 אפריל 2012 17:28
Here is where they want you to go: http://support.microsoft.com/select/?target=assistance
Here is an old link that contains phone numbers: http://support.microsoft.com/kb/295539
Also, you can post problems to Microsoft Connect. This may eventually get an answer or even a fix, but is not a fast track to a solution. Think of it as an open-ended problem report. http://connect.microsoft.com/
FWIW,
RLF -
יום שני 09 אפריל 2012 19:32
I think in this case, Connect is not the way to go. I view it as highly unlikely that Microsoft would issue a fix for this. But given that there are regulations that calls for this, I think Rich have reason to expect that Microsoft to release a statement that explains the situation. (Provided that they can show that there is no security issue with public having access to the procedure.)
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se -
יום שני 09 אפריל 2012 20:20I have opened a ticket with MS Support. They are researching the issue now. I'll let you know what happens.
.png)
