EXECUTE permission on stored proc
-
Hi,
I'm running MSSQL 2000 (Though I'm a newbie) and this is my problem:
The following text was extracted from Books on Line:
"EXECUTE permissions for a stored procedure default to the owner of the stored procedure, who can transfer them to other users. Permissions to use the statement(s) within the EXECUTE string are checked at the time EXECUTE is encountered, even if the EXECUTE statement is included within a stored procedure. When a stored procedure is run that executes a string, permissions are checked in the context of the user who executes the procedure, not in the context of the user who created the procedure."
Very clear, isn't it??!!
Since now I'm willing to implement NT integrated security, so I stablished permissions on database objects as "I assume" it's needed, specifically, I granted EXECUTE right on an Stored Proc named spa_GetHistory to the user "User1".
Monthly some user "User2" creates a table containing all the information about the operations of the month. The name of the table is "HistoryYYYYMM", where YYYY is the year and the MM is the actual month.
Under this situation, the user "User1" needs to fetch partial historical data executing the spa_GetHistory stored proc passing the initial and final dates as parameters.
I don't want the User1 to have direct "select" permission on any HistoryYYYYMM, as when any new "user1" is created I need to assign select permisions on all existing HistoryYYYYMMs. By the way, when a new HistoryYYYYMM is created I must set "select" permissions on this table to all user1s.
So... I coded spa_GetHistory, but...... (this is the meat). As the number of tables to consult depends on the initial and final dates supplied, I have to include an EXEC clause that loops through a cursor containing all the table names and selects data from all HistoryYYYYMMs contained in the range and inserting their data into a temp table which is then returned.
At this point when the User1 runs the sp, he gets the error: "SELECT permission denied on object History200501...." because of the :
Exec('Insert Into TempTable select * from History'+@N+'''')
where @N contains the year and month (200501).
Any advice about recoding sp, modifiying security design or any other workaround will be highly appreciated.
Thanks in advance,
Felix
-
Any dynamic sql breaks the ownership chain.
Perhaps you could make a view which is an union of the history tables, and have your stored procedure select on the view.
Then you just have to alter the view each month (permissions are kept)
-
Pls don't cross post - we get all new messages in the daily email anyway
-
I think Jo`s idea makes sense, create a view with something like:
SELECT * FROM HistoryYYYYMM
UNION
SELECT * FROM HistoryYYYYMM
etc....
Then grant permissions as required on the view, then you only need to update the tables in the view and not have to keep adding permissions to the underlying tables.
"Don`t try to engage my enthusiasm, I don`t have one."(Marvin)
-
Ownership chaining is broke when any of the following is true:
(1) You are calling an object with a different owner than the object doing the call. For instance, if your stored procedure is owned by User1 and your table is owned by User2, SQL Server will check security on the table.
(2) You execute a separate batch, such as with sp_executesql or EXEC( <some sql code> ). In this case SQL Server will recheck security.
On that note, can you build the process which creates the table such that 1 larger table is used for all months? For instance, 1 history table with columns for year and month. If you can do this (and the table isn't prohibitively large as a result), then you can build the stored procedure such that it takes the month and year paramaters and filters on that using the WHERE clause when you execute your SELECT statement.
K. Brian Kelley
@kbriankelley
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply