March 19, 2009 at 11:04 am
The clarification is regarding database user permissions:
1. procedure spUpdate which can perform insert or update based on parameters passed.
2. user1
3. permission for user1 are execute only on procedure spUpdate. But, no other permission granted like read, select, delete, update, references to any objects in the database.
4. when I ran the procedure spUpdate by execute as user = 'user1' with correct parameters I saw the records are being inserted/updated to the underlying tables used in the procedure. But, the user1 do not have any direct access to these tables at all.
My question here is under what permission or context did the insert/update happens when the user executes the procedure.
Thanks
March 19, 2009 at 11:40 am
This is called ownership chaining, here is the BOL link:
http://msdn.microsoft.com/en-us/library/ms188676(SQL.90).aspx
The user has implicit access to the objects (via the procedure only) because the owner of the procedure matches the owner of the objects, and the user has permission to execute the procedure.
March 19, 2009 at 12:08 pm
Here's an example that shows how ownership chaining works in addition to the link Matt provided:
-- drop objects if they exits
IF OBJECT_ID('test.test_table') IS NOT NULL
BEGIN
DROP TABLE test.test_table
END
IF Schema_ID('test') IS NOT NULL
BEGIN
DROP SCHEMA test
END
GO
-- create a schema
CREATE SCHEMA test AUTHORIZATION dbo
GO
-- create a table in the schema
CREATE TABLE test.test_table
(
test_table_id INT IDENTITY(1,1) PRIMARY KEY,
test_name VARCHAR(25) NOT NULL
)
GO
-- drop the proc if it exists
IF OBJECT_ID('dbo.test_table_ins') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.test_table_ins
END
GO
/*
create the procedure to insert into the table
but in a schema owned by the owner of the test schema
*/
CREATE PROCEDURE dbo.test_table_ins
(
@test_name VARCHAR(25)
)
AS
SET NOCOUNT ON
INSERT INTO test.test_table (
test_name
) VALUES (
@test_name
)
RETURN
GO
-- if the user doesn't exist creat it with a default schema of test
IF USER_ID('test_user') IS NULL
BEGIN
CREATE USER test_user Without LOGIN WITH default_Schema = test;
END
GO
-- grant execute on the test proce to the new user
GRANT EXECUTE ON dbo.test_table_ins TO test_user;
GO
-- change context to the new user and execute the proc
EXECUTE AS USER = 'test_user';
EXEC dbo.test_table_ins @test_name = 'Test';
REvert;
-- show the inserted roe
SELECT 'Row Inserted', * FROM test.test_table AS TT ;
GO
-- change the owner of the test scema where test_table is
PRINT 'schema owner changed'
ALTER AUTHORIZATION ON SCHEMA::test To db_owner;
GO
/*
now change to the test user and execute the proc
which should now fail because the ownership chain is
broken
*/
BEGIN TRY
EXECUTE AS USER = 'test_user';
EXEC dbo.test_table_ins @test_name = 'Test2';
REvert;
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE()
END CATCH
REvert;
GO
SELECT 'Row not Inserted', * FROM test.test_table AS TT ;
GO
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 19, 2009 at 1:06 pm
Matt, Jack, Thank you. The detailed answers are excellent & this explains me how to design our systems.
Thanks
RJ
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply