Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Ownership Chains in SQL Server

Someone asked the question recently about allowing a user to run a process,but not execute the individual pieces of the process. I replied that ownership chains allow this, and then explained things. Here’s a short example of that.

Let’s suppose I have a process that removes data from a table and then reloads it. Perhaps it’s a report, perhaps it’s a static look at inventory, it doesn’t matter. Can I allow a normal user to execute the entire process, but not the clear or load pieces?

I can, and here’s how.

Suppose I have a clear procedure:

CREATE PROCEDURE dbo.spClear
AS
BEGIN
   select 'Clear'
END
go

For the sake of this example, I don’t have any work here, but the "SELECTClear"’” could be replaced by a TRUNCATE TABLE or other statement.

By default in SQL Server, I don’t have rights to execute procedures that I haven’t created. In this case, assume a db_owner created this procedure, so a normal user, Joe, would not be able to execute it. In fact, if I log in as Joe and run this, I get:

SETUSER 'Joe'
go
-- Fails
EXEC spClear
go

Msg 229, Level 14, State 5, Procedure spClear, Line 1

The EXECUTE permission was denied on the object ‘spClear’, database ‘db1′, schema ‘dbo’.

 

That’s expected, and it’s good.

Let’s also create a load procedure, as the db_owner.

CREATE PROCEDURE dbo.spLoad
as
BEGIN
   select 'Load'
END
go

Now to manage the process, let’s create a wrapper stored procedure that calls these. For the sake of tracking what’s happening, I have a few statements in there.

CREATE PROCEDURE dbo.spRefresh
AS
BEGIN
   select 'Refresh start'
   EXEC spClear
   EXEC spLoad
   SELECT 'Refresh End'
END
go

If I were to call this process, this is what I’d expect from the call stack:

  • a call spRefresh
  • a result set returning “Refresh Start’”
  • a call to spClear
  • a result set returning “Clear”
  • return to spRefresh
  • a call to spLoad
  • a result set returning “Load”
  • a return to spRefresh
  • a result set returning “Refresh End”
  • end of spRefresh

Obviously I could have actual delete or truncates in the clear procedure and then some insert in the load procedure. If I attempt to run this as Joe, I get:

Msg 229, Level 14, State 5, Procedure spRefresh, Line 1

The EXECUTE permission was denied on the object ‘spRefresh’, database ‘db1′, schema ‘dbo’.

Expected, because I haven’t granted Joe rights. Let’s do that and execute the procedure:

GRANT EXECUTE ON dbo.spRefresh TO Joe
go
SETUSER 'Joe'
GO
EXEC spRefresh

I get what I expected for results:

ownership_a

I have rights to execute spRefresh. Since the same owner exists for all three objects, dbo in this case, I have a chain that permissions are not checked for spLoad and spClear. I can execute them in the context of spRefresh.

What about separately?

EXEC spLoad

gets me:

Msg 229, Level 14, State 5, Procedure spLoad, Line 1

The EXECUTE permission was denied on the object ‘spLoad’, database ‘db1′, schema ‘dbo’.

and

EXEC spClear

gets me

Msg 229, Level 14, State 5, Procedure spClear, Line 1

The EXECUTE permission was denied on the object ‘spClear’, database ‘db1′, schema ‘dbo’.

A nice way to allow someone to execute a group of processes without allowing them to execute any individual one.


Filed under: Blog Tagged: security, sql server, syndicated

Comments

Leave a comment on the original post [voiceofthedba.wordpress.com, opens in a new window]

Loading comments...