January 27, 2012 at 6:47 am
Hello, what access for user I need to give if they need to run sp, update and insert records.
Thank you
January 27, 2012 at 6:52 am
FYI: You might want to update the title of this thread to properly reflect your issue, which is security questions.
Look up Books Online for fixed database roles. Read and Write permissions are encompassed in two of the roles and they will cover updates as well.
EDIT: Also look up "Grant" in BOL. You'll need to grant them EXECUTE permissions on the stored procedure.
January 27, 2012 at 7:04 am
you only need to grant EXECUTE on a stored procedure.
by design, if the user has EXECUTE permissions, whatever the proc does, whether INSERT/UPDATE/DELETE, the proc will run successfully, and the end user does not need access to the underlying objects the procedure is using.
this is ideal, as you might not want the end users to even SEE a table, but the proc they CAN call can do update/insert/delete.
The above is true as long as , as long as the following is true:
the procedure shouldn't use dynamic SQL. if it uses dynamic SQL, the caller needs access to the objects called in thedynamic sql.
all the objects used are owned by the same user(ownership chaining) if the dbo.proc used dbo.Tablename and dev.OtherTablename, that would break ownership chaining.
all the objects used are in the same database. Cross database calls require the user to have access in the other database as well. (you might override that with EXECUTE AS)
Lowell
January 27, 2012 at 7:16 am
I should mention that my response before the edit was completely predicated on the fact that I totally missed the comment "run sp" in the original post. I saw "update and insert". That was my bad.
January 27, 2012 at 7:20 am
Lowell (1/27/2012)
you only need to grant EXECUTE on a stored procedure.by design, if the user has EXECUTE permissions, whatever the proc does, whether INSERT/UPDATE/DELETE, the proc will run successfully, and the end user does not need access to the underlying objects the procedure is using.
I don't believe this to be accurate - more of a maybe... as it really depends on how you've set up security on the server: Are you granting permissions to a "group/role" or to users individually? In most scenarios, you grant a specific user/group public on the instance, then at the database level you grant additional permissions as appropriate. If the user/group has been assigned datareader only, yet has EXEC permissions, running a procedure will result in an error. Take for example in a typical reporting environment (that uses replicated data), you may want to grant EXEC permissions to procedures and SELECT permissions to functions but restrict the ability of INSERTS/UPDATES to the actual data.
If tables can be updated, the you need to grant INSERT/SELECT/and UPDATE accordingly. Granting EXEC to a user/group will only allow a user to execute the procedure. If that procedure has INSERTS or UPDATES into tables (not temp/parameter tables) then the procedure will fail if the user/role has not been granted those permissions.
This of course being said based upon my opinion/experience in how we do things at our company (and my differ greatly at your place of employment)
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 27, 2012 at 7:29 am
I have a lot of sp and I want provide access to all sp with execute,is there way to select all of them at the same time or I have to go sp by sp and provide execute
Thank you
January 27, 2012 at 7:34 am
Krasavita (1/27/2012)
I have a lot of sp and I want provide access to all sp with execute,is there way to select all of them at the same time or I have to go sp by sp and provide execute
You can do it individually or grant EXECUTE on the whole schema. I advise doing your research on the security holes you open up for yourself if you choose the second option! I cannot stress enough that if you don't understand what granting EXECUTE on the whole schema means, do not use it.
January 27, 2012 at 7:49 am
MyDoggieJessie (1/27/2012)
I don't believe this to be accurate - more of a maybe... as it really depends on how you've set up security on the server: Are you granting permissions to a "group/role" or to users individually? In most scenarios, you grant a specific user/group public on the instance, then at the database level you grant additional permissions as appropriate. If the user/group has been assigned datareader only, yet has EXEC permissions, running a procedure will result in an error. Take for example in a typical reporting environment (that uses replicated data), you may want to grant EXEC permissions to procedures and SELECT permissions to functions but restrict the ability of INSERTS/UPDATES to the actual data.If tables can be updated, the you need to grant INSERT/SELECT/and UPDATE accordingly. Granting EXEC to a user/group will only allow a user to execute the procedure. If that procedure has INSERTS or UPDATES into tables (not temp/parameter tables) then the procedure will fail if the user/role has not been granted those permissions.
This of course being said based upon my opinion/experience in how we do things at our company (and my differ greatly at your place of employment)
love to go over this, as strong posters like ourselves digging at the details will make everyone who reads this understand this better.
I'll stick to my guns on this: it's very easy to set up a testable scenario.
Here's what I want to show:
1.create the table(s) for the demo with some data.
2.create a procedure that, say, inserts,updates and deletes one of those tables.
3.create a user/role, and give that user access only to the procedure....no db_datareader or any other rights.
4. prove the caller can execute the procedure, the data changes. the caller doesn't have direct access to the data
5. no conflict or error occurs for the issue you identified: If the user/group has been assigned datareader only, yet has EXEC permissions, running a procedure will result in an error.
--use a testing database.
USE SandBox;
GO
--create a table
CREATE TABLE [dbo].[Colors] (
[colorID] INT IDENTITY(1,1) NOT NULL,
[colorName] VARCHAR(30) NULL,
[info] VARCHAR(200) NULL,
CONSTRAINT [PK__Colors] PRIMARY KEY CLUSTERED ([colorID]) )
GO
--create a procedure that fiddles with stuff
CREATE PROCEDURE PR_Fiddle_with_Colors(@colorName VARCHAR(30))
AS
BEGIN --PROC
IF NOT EXISTS(SELECT 1 FROM dbo.Colors WHERE colorName=@colorName)
BEGIN
INSERT INTO dbo.Colors(colorName,info) VALUES(@colorName,'Original Insert')
END --IF
ELSE --it exists, add a notation: to prove an update occurred.
BEGIN
UPDATE dbo.Colors
SET info = USER_NAME() + ' called the procedure ' + OBJECT_NAME(@@PROCID)
WHERE colorName=@colorName
END --ELSE
END --PROC
GO
--create a role to contain our permissions.
CREATE ROLE EXECUTEONLY
--add permissions to one and only one procedure
GRANT EXECUTE ON dbo.PR_Fiddle_with_Colors TO EXECUTEONLY
--create our test user
CREATE USER ClarkKent WITHOUT LOGIN
--Add that user to our special role
EXEC sp_addrolemember N'EXECUTEONLY',N'ClarkKent';
--change from superman(sa?) to Clark Kent
EXECUTE AS USER = 'ClarkKent';
--who am i? ClarkKent!
select USER_NAME()
--returns ClarkKent.
select IS_MEMBER('EXECUTEONLY') --confirm if ClarkKent is a member of our role.
--call our procedure as our special user:
EXEC PR_Fiddle_with_Colors 'blue'
EXEC PR_Fiddle_with_Colors 'green'
EXEC PR_Fiddle_with_Colors 'yellow'
EXEC PR_Fiddle_with_Colors 'blue'
GO
---Can clarkKent see the table he fiddled with?
SELECT * FROM dbo.Colors
/*--error message
Msg 229, Level 14, State 5, Line 2
The SELECT permission was denied on the object 'Colors', database 'SandBox', schema 'dbo'.
*/
--change back into superman/sa
REVERT;
--see the changes ourself:
SELECT * FROM dbo.Colors
--confirm that adding the db_datareader role to the role does not break anything clark kent does,
--but would give him access beyond our one procedure.
EXEC sp_addrolemember N'db_datareader',N'EXECUTEONLY';
--change from superman(sa?) to Clark Kent
EXECUTE AS USER = 'ClarkKent';
--who am i? ClarkKent!
select USER_NAME()
--returns ClarkKent.
select IS_MEMBER('EXECUTEONLY') --confirm if ClarkKent is a member of our role.
--call our procedure as our special user:
EXEC PR_Fiddle_with_Colors 'black'
EXEC PR_Fiddle_with_Colors 'red'
EXEC PR_Fiddle_with_Colors 'yellow'
EXEC PR_Fiddle_with_Colors 'orange'
GO
---Can clarkKent see the table he fiddled with?
SELECT * FROM dbo.Colors --yes due to db_datareader
--change back into superman/sa
REVERT;
--clean up after our examples
GO
DROP PROCEDURE dbo.PR_Fiddle_with_Colors;
DROP TABLE dbo.COLORS
GO
DROP USER ClarkKent;
GO
DROP ROLE EXECUTEONLY;
GO
Lowell
January 27, 2012 at 8:26 am
I stand corrected 😀
Lowell, I love your example however, there's one small error in your code, it errors out at the SELECT:---Can clarkKent see the table he fiddled with?
SELECT * FROM dbo.ColorsNeeds to have GRANT SELECT TO [ClarkKent] before the EXECUTE as user = 'ClarkKent'
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 27, 2012 at 9:43 am
MyDoggieJessie (1/27/2012)
I stand corrected 😀Lowell, I love your example however, there's one small error in your code, it errors out at the SELECT:
---Can clarkKent see the table he fiddled with?SELECT * FROM dbo.ColorsNeeds to have GRANT SELECT TO [ClarkKent] before the EXECUTE as user = 'ClarkKent'
nono! that error was on purpose, so that we could prove he did not have access.
later,we granted db_datareader to the role, and when the second portion executes, no errors.
Lowell
January 27, 2012 at 9:51 am
Yikes it's one of those days :w00t: I'm going to have a glass of water to wash down my foot!
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply