February 29, 2012 at 8:19 am
Hi,
I have a database with a stored procedure that updates a table. This procedure for argument's sake is called sprocRefresh
this stored procedure calls 2 other stored procedures, one of them clears down old data, another re-populates the table with new data. these are called sprocClear and sprocPopulate
A user in my business needs to be able to execute sprocRefresh, which means they need to be able to execute sprocClear and sprocPopulate.
I want to be certain, however, that they cannot execute sprocClear or sprocPopulate unless called from sprocRefresh.
Is there a simple way to acheive this? I can easy grant 'execute' permission on all 3 sprocs on the securables options but that would allow the users to execute sprocClear and thereby delete chunks of data without re-populating it and causing annoying support tickets!
Apologies for the vagueness of my question but it's for a highly sensitive financial database and I'm paranoid about broadcasting anything too detailed about it!
Cheers.
(oh and it's running in sql 2k8 express by the way - and yes, I would much prefer to use standard edition for a finance db!)
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
February 29, 2012 at 8:34 am
Ben you can grant that special user like this:
GRANT EXECUTE ON sprocRefresh TO SPECIALUSER
do not grant anything else.
as long as sprocClear or sprocPopulate are owned by the same schema(ie they are all dbo.) then you are all set...the ownership chaining basically says if you have execute permissions on this proc, the proc will perform whatever it is supposed to do, even if the calling user does not have access to the underlying objects.
Lowell
February 29, 2012 at 8:36 am
Oh wow that's pretty cool
Thanks mate.
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
February 29, 2012 at 8:39 am
I am not sure if this is exact solution but I have this idea:
1. Create a user without login. Suppose you created "ProcUser".
2. Grant this user execute rights on spClear & spPopulate
3. Alter the procedure spRefresh by adding "EXECUTE AS ProcUser" in its text.
4. Now, grant execute rights on only spRefresh to that person in your company.
After doing this, I think he should be able to execute spRefresh but he will not be able to execute spClear & spPopulate.
I will test this assumption & post it if I got some time. Meanwhile you can test it.
February 29, 2012 at 8:40 am
Are all three procs owned by the same person? If so, you get an ownership chain there.
Try this, Joe is a normal user, not roles/rights assigned.
-- As DB_Owner
CREATE PROCEDURE dbo.spRefresh
AS
BEGIN
select 'Refresh start'
EXEC spClear
EXEC spLoad
SELECT 'Refresh End'
END
go
CREATE PROCEDURE dbo.spClear
AS
BEGIN
select 'Clear'
END
go
CREATE PROCEDURE dbo.spLoad
as
BEGIN
select 'Load'
END
go
GRANT EXECUTE ON dbo.spRefresh TO Joe
go
SETUSER 'Joe'
go
-- Fails
EXEC spClear
go
-- fails
EXEC spLoad
go
- works
EXEC spRefresh
go
February 29, 2012 at 8:46 am
All the procedures in question are indeed part of the dbo schema.
this makes things much easier than I expected!
Thanks
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply