January 31, 2003 at 11:48 am
I'm not sure I follow you on this one:
quote:
As mostdatabases are created as dbo, and the owner is typically sa, then the default
rights that MSFT has placed upon all of the procedures in MASTER are
ineffective.
Most of the stored procedures and system tables are defaulted to the public role anyway. This is irrespective of cross-database ownership chaining.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
January 31, 2003 at 5:12 pm
Most procedures in master do have public access, but many do not.
Take for example :
sp_helprotect sp_addlogin
GO
sp_helprotect sp_OACreate
GO
sp_helprotect xp_cmdshell
GO
Outputs :
quote:
Owner Object Grantee Grantor ProtectType Action Column------ ---------------------- ------------ ------- ----------- ------- ------
dbo sp_addlogin public dbo Grant Execute .
(1 row(s) affected)
Server: Msg 15330, Level 11, State 1, Procedure sp_helprotect, Line 346
There are no matching rows on which to report.
Server: Msg 15330, Level 11, State 1, Procedure sp_helprotect, Line 346
There are no matching rows on which to report.
The procedure in question sp_OACreate does not have any security accounts that
are assigned to be able to execute it. So, how do most users execute this
function from their procedures? Most DBA's / Developers when they create a database
in Enterprise Manager, they are connected as sa, or trusted, and they have
administrative rights so it creates the database as sa. (If in mixed mode) When
they create their procedures from then on the procedures and they are owned by
dbo, these procedures will execute as sa. Allowing a user with public role access
only to execute procedures he does not normally have rights to!
BOL Quote :
quote:
System stored procedures are the exception because EXECUTE permissions arealready granted to the public role, allowing everyone to execute them. However,
after a system stored procedure is executed, it checks the user's role
membership. If the user is not a member of the appropriate fixed server or
database role necessary to run the stored procedure, the stored procedure does
not continue.
I am going to post 3 scripts in the next post to demonstrate this. They will
only work properly if SQL SP3 is installed.
1)The first will create a blank database(change the file locations),
create a user and grant him access to that database from the public role.
2)The second will create a stored proc that makes a call to the sp_OACreate
stored procedure, and does not much really except return whether an error
occurred doing so.
3) -Turns OFF Server chaining
-Impersonates the user
-Calls the stored procedure as the user
-Reverts back to SA
-Calls the stored procedure
-Turns ON Server chaining
-Impersonates the user
-Calls the stored procedure as the user
-Reverts back to SA
-Turns OFF Server chaining(NOTE: I turn this off, but if you want this on,
then comment this step out)
Tim C.
//Will write code for food
One Windows to rule them all, One Windows to find them,
One Windows to bring them all and in the darkness bind them
In the Land of Microsoft where the Shadows lie.
Tim C //Will code for food
January 31, 2003 at 5:13 pm
Script ONE - create the DB and user
CREATE DATABASE [foo] ON (
NAME = N'foo_Data',
FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL\data\foo_Data.MDF' ,
SIZE = 1, FILEGROWTH = 10%
)
LOG ON (
NAME = N'foo_Log',
FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL\data\foo_Log.LDF' ,
SIZE = 1,
FILEGROWTH = 10%
)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO
USE MASTER
GO
--add the login to the server
EXEC sp_addlogin N'foouser', N'foo'
GO
USE FOO
GO
--give the foo user access to the database
EXEC sp_grantdbaccess N'foouser', N'foouser'
GO
EXEC sp_changedbowner 'sa'
GO
/*
****CLEANUP SCRIPT****
USE FOO
GO
EXEC sp_revokedbaccess N'foouser'
GO
EXEC sp_droplogin N'foouser'
GO
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'foo')
DROP DATABASE [foo]
GO
*/
Script TWO - create the procedure
USE FOO
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spCallComObj]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spCallComObj]
GO
CREATE PROCEDURE dbo.spCallComObj AS
SET NOCOUNT ON
--MODIFIED FROM BOL
DECLARE @object int
DECLARE @hr int
DECLARE @property varchar(255)
DECLARE @return varchar(255)
DECLARE @src varchar(255), @desc varchar(255)
-- Create an object.
EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUT
IF @hr <> 0 GOTO ERRH
-- Destroy the object.
EXEC @hr = sp_OADestroy @object
IF @hr <> 0 GOTO ERRH
SELECT 'SUCCESS'
RETURN 1
ERRH:
SELECT 'FAILURE'
SET NOCOUNT OFF
RETURN 0
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
GRANT EXECUTE ON dbo.spCallComObj TO PUBLIC
GO
Script THREE - test it out
USE FOO
GO
SETUSER --just in case, revert to self
--turn chainingng off
PRINT ''
PRINT ''
PRINT '**turning server chaining off'
EXEC master..sp_configure 'Cross DB Ownership Chaining', 0
GO
RECONFIGURE
GO
PRINT ''
PRINT ''
PRINT '**Impersonating foouser'
SETUSER 'foouser'
GO
--with cross db ownership turned off this will FAIL
PRINT ''
PRINT ''
PRINT '**CALLING spCallComObj As foouser'
EXEC spCallComObj
GO
--revert back to sa
PRINT ''
PRINT ''
PRINT '**Reverting back to sa'
SETUSER
GO
--will work
PRINT ''
PRINT ''
PRINT '**CALLING spCallComObj As sa'
EXEC spCallComObj
GO
--turn on the SERVER 'Cross DB Ownership Chaining'
PRINT ''
PRINT ''
PRINT '**turning server chaining on'
EXEC master..sp_configure 'Cross DB Ownership Chaining', 1
GO
RECONFIGURE
GO
--impersonate a user who is only member of public
PRINT ''
PRINT ''
PRINT '**Impersonating foouser'
SETUSER 'foouser'
GO
--with cross db ownership turned on this will PASS
PRINT ''
PRINT ''
PRINT '**CALLING spCallComObj As foouser with chaining on'
EXEC spCallComObj
GO
--revert back to sa
PRINT ''
PRINT ''
PRINT '**Reverting back to sa'
SETUSER
GO
--set it back to off
PRINT ''
PRINT ''
PRINT '**turning server chaining off'
EXEC master..sp_configure 'Cross DB Ownership Chaining', 0
GO
RECONFIGURE
GO
Tim C.
//Will write code for food
One Windows to rule them all, One Windows to find them,
One Windows to bring them all and in the darkness bind them
In the Land of Microsoft where the Shadows lie.
Tim C //Will code for food
January 31, 2003 at 6:18 pm
I'm not questioning what you've demonstrated in the scripts. What I am trying to make sure we're on the same page on is that the cross-database ownership chains don't have to be turned on to allow public access to the system stored procedures and tables that public has had access to in previous versions of SQL Server 2000. In other words, public has access to sp_help by default, and it doesn't matter if cross-database ownership chains are turned on or not.
The addition of cross-database ownership chains don't make the permissions ineffective per se. You can use cross-database ownership chains to bypass security in very bad ways, yes. But simply adding the functionality doesn't mean MS undid the default permissions on the system objects.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
February 3, 2003 at 8:45 am
I am in total agreement with you Brian, I was just trying to state that many
dbas / developers have written procedures that call procedures in master that
public does not have execute rights on. The ownership chain previously allowed
them to call the "secure" procedures when the database was set to sa ownership.
This is going to cause much confusion on the part of many dbas / developers when
suddenly they are getting "EXECUTION DENIED" with procedures that were already
in production if they apply SP3 and leave off ownership chaining like the
install highly recommends. I know that I am preaching to the choir explaining
this to you.
Tim C.
//Will write code for food
One Windows to rule them all, One Windows to find them,
One Windows to bring them all and in the darkness bind them
In the Land of Microsoft where the Shadows lie.
Tim C //Will code for food
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply