December 29, 2011 at 3:28 am
how to restrict access to user at runtime to SPs having DML statement .
some SPs may only have SELECTs. So, instead of verifying each SPs scripts to give/restrict access on it, is there a generic way to restrict such SPs.
Appreciating ur help.
December 29, 2011 at 5:57 am
If you don’t want a user to perform DML operations, revoke or deny those permissions.
For More:
December 29, 2011 at 6:18 am
there is an ownership chaining assumption in SQL Server:
if you create a procedure ie dbo.myProc, and you grant a user EXECUTE rights to that proc, no matter what that proc does, whether insert,update,delete, create table, drop database....if all the objects are owned by the same owner, the work inside the proc will be done.
the advantage to this is you can create procedures, and give users rights only to the procedures, and they never need access to the underlying tables.
because of this model, there is no way to differentiate between procs that only select, and procs that do more than that from a meta-data perspective...it's just a proc.
that's important, because even if you were to directly revoke access to a specific table for a user, if they call the procedure which did a delete, it will still delete.
don't believe me? here's a proof of concept:
Use SandBox;
--i'm logged in as a sysadmin
CREATE TABLE dbo.Example(id int identity(1,1) not null primary key,
SomeData varchar(30) )
INSERT INTO dbo.Example(SomeData)
SELECT 'AntiqueWhite' UNION ALL
SELECT 'Aqua' UNION ALL
SELECT 'Aquamarine' UNION ALL
SELECT 'Azure' UNION ALL
SELECT 'Beige'
GO
CREATE PROCEDURE dbo.MyProc
AS
DELETE FROM dbo.Example
WHERE SomeData LIKE 'A%'
GO
CREATE USER MyTestGuy WITHOUT LOGIN
GO
GRANT EXECUTE ON dbo.MyProc TO MyTestGuy
DENY DELETE ON dbo.Example TO MyTestGuy
--change identities into MyTestGuy
EXECUTE AS USER='MyTestGuy'
--am i really someone else?
SELECT USER_NAME() AS WHOAMI
--try to SELECT from the underlying table...no access!
SELECT * FROM dbo.Example
EXECUTE dbo.MyProc --4 rows affected!
--change back into my superuser
REVERT;
SELECT * FROM dbo.Example
DROP USER MyTestGuy
DROP PROCEDURE dbo.MyProc
DROP TABLE dbo.Example
Lowell
December 29, 2011 at 6:36 am
December 29, 2011 at 6:42 am
December 29, 2011 at 6:45 am
i should add that if the procedure uses dynamic SQL instead of direct DDL commands, then the ownership chaining is not used, and the specific permissions, like SELECT or DELETE needs to be granted tot eh user calling the procedure;
using the same proof of concept above, substitute that previous procedure definition with this one, the difference is with the proc using dynamic SQL;
in that case i get two errors, stating both SELECT and DELETE permissions for the myTestGuy in order to execute the procedure.
CREATE PROCEDURE dbo.MyProc
AS
DECLARE @sql varchar(100)
SET @sql = 'DELETE FROM dbo.Example WHERE SomeData LIKE ''A%'' ; '
EXECUTE(@sql)
GO
Lowell
December 29, 2011 at 7:14 am
No sorry i repair them as
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=850&lngWId=5
you can copy and paste at address url
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply