November 11, 2009 at 12:19 am
Senario : SQL SERVER 2000/2005
--Creation OF SQL SERVER Login
CREATE LOGIN [abc] WITH PASSWORD=N'123',
DEFAULT_DATABASE=[master],
DEFAULT_LANGUAGE=[us_english],
CHECK_EXPIRATION=ON,
CHECK_POLICY=ON
go
--Creation of Dtabase User ABC with READ & Write permission
USE Database
exec sp_adduser 'abc'
exec sp_addrolemember 'db_datareader', 'abc'
exec sp_addrolemember 'db_datawriter', 'abc'
-- Creation of table
Create Table test (id int,name varchar(30))
go
INSERT INTO test VALUES(12,'abc')
INSERT INTO test VALUES(1,'abcd')
-- Remove Update & delete permission from user abc on table test
Deny Update,Delete on test to ABC
go
--Creation of proc to delete record from table test
CREATE PROC abc_test
AS
DELETE FROM Test
GO
GRANT EXEC ON abc_test TO abc
--When i try to delete record from table test it gives error
--Msg 229, Level 14, State 5, Line 1
--The DELETE permission was denied on the object 'test', database '', schema 'dbo'.
--As ABC user does not have delete permission on table test
--But WHEN I EXECUTE PROCEDURE;
EXEC abc_test
-- it works & DELETE ALL the records FROM TABLE test.
Kindly suggest what is going wrong .... I want TO REMOVE DELETE Permission FROM USER ABC ON TABLE test..IN this senario USER abc can DELETE records WITH the HELP OF PROC abc_test
Kindly suggest
Thanks & Regards,
Deepali Lokhande
November 11, 2009 at 3:05 am
This is because of the ownership chain. Since the stored procedure and the tables that the procedure works with are owned by the same user, ownership chain is obtained. An ownership chain means that all the objects that the procedure works with are owned by the same user that owns the procedure. For example if I have the fallowing procedure:
Create procedure DemoProc
As
Select * from MyView
SQL Server will check if MyView is owned by the same user that owns DemoProc. If both are owned by the same user, it will also check which objects are referenced in MyViews’s code and then it will check who owns those objects. If MyView references another view or a user defined function, the server will continue to check the owners of the objects that they references until it will get to the point that there are no referenced objects to check. If all the objects in that chain are owned by the same user and the code didn’t have something that can break the ownership chain, then the owner of the stored procedure can grant permissions on the procedure to users that have no permissions on the objects that the procedure references. You can read more about ownership chain in BOL.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 11, 2009 at 3:50 am
Thanks Adi.
Can you pls suggest on above senario how can I protect my table test from deleting records by user ABC who can delete records by executing stored proc.
Pls suggest.
Rd,
Deepali
November 11, 2009 at 3:58 am
If you don’t want the user abc to delete the data and you want to have the delete statement in the procedure, then you shouldn’t give the user abc permissions to execute the procedure.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 12, 2009 at 5:36 am
Hello Experts,
Can anyone provide Document which explain about database Cross ownership with examples apart from books online.
It will help a lot .......thanks in advance
Rd,
Deepali
November 12, 2009 at 2:00 pm
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply