SP gens & Exec SQL but have permissions issues.

[sdives]

I'm working with an SP that generates a SELECT stmt with different WHERE clauses based upon up to 5 parameters supplied to the SP from the application. The statement is created in a variable then EXECuted from within the SP. The application is receiveing a "Select Permission Denied" error on the various tables involved when attempting to run the SP. I granted SELECT permissons to the id running the SP but that's not what I want to do. I only want to allow EXEC permissons for the SP.

Is there a way to code the SQL stmt to accept any combination of the parameters and still not have to perform the additional EXEC on a constructed SQL stmt?

Sidney Ives

[rmiao]

Do sp and involved tables have same owner? If so, should be fine.

[sdives]

The sp and tables are owned by dbo. The id used by the application is not dbo. I only want id to have exec permissions on sp's in the db.

Sidney

[rmiao]

It should work with exec permission on sp only. But may look at sp code to see if breaks ownership somewhere.

[sdives]

The app. login has exec permissons only for the sp. It's allowed no other permissions. When the sp is called and the SELECT stmt is generated and stored in a variable, the sp then performs 'Exec (@SQL)'. Doing this spawns another thread under the context of the app. login. It is no longer under the permissions umbrella of the sp. That's when I receive the error.

If I could build and exec the select statment within the sp that would be fine. The problem is that there are 5 parms that are called with the sp. Any or all of them may, or may not be populated. I could use conditional logic to exec predefind queries but I would have to have a query with every possible combination of the parameters.

I've considered executing only a portion of the query and capturing the data into a temporary table and use a cursor to go through the temp table pulling out what I wanted based on the parameters supplied but it would take too long to run.

I've attached the sp if you'd like to look at it.

Sidney Ives

[mist]

It's behaving as it should. Since you are using dynamic SQL the user executing the procedure needs to have permission to carry out whatever is in the dynamic SQL. In this case that means select permission. The only way around that is not to use dynamic sql.

[sdives]

I agree that it's working exactly as it's supposed to. That's why I asked the question in the original post, "Is there a way to code the SQL stmt to accept any combination of the parameters and still not have to perform the additional EXEC on a constructed SQL stmt?"

Sidney Ives

[mist]

Originally posted by sdives
I agree that it's working exactly as it's supposed to. That's why I asked the question in the original post, "Is there a way to code the SQL stmt to accept any combination of the parameters and still not have to perform the additional EXEC on a constructed SQL stmt?"

Sidney Ives

I must have missed that bit.

I'm tempted to say that it might involve coding for all the possibilities as you've said. Perhaps if your different queries are so diverse then they *should* be in separate procedures anyway.

Your other post said that you had attached the proceudre, but I can not see it. Depending on the specifics it may be possible to code around the problem.

[sdives]

I've accepted the fact that I have to give the app. login Select permissions but I hate having the hole in my security.

We have several instances of this type of situation in other databases. I would really like to know if there's a better way to code this. This version will go into prod. like this. If I find a better solution then it will go live in the next release of the application.

I've included the sp at the bottom of this post.

Create Procedure AuditMessageGetByApplication

@ApplicationID Int,
@TransactionTypeID Int = Null,
@ActionCodeID Int = Null,
@RecordStatusID Int = Null,
@TagID Int = Null,
@TagValue VarChar(6000) = Null

As

-- Error message variables
Declare @ErrorMsg VarChar(300)
Declare @Error Int

Declare @SQL As VarChar(8000)

Set @SQL = 'Select am.AuditMessageID, Convert(varchar(20), am.AuditDate, 101) ''AuditDate'', Convert(VarChar(20), am.AuditDate, 108) ''AuditTime'', ' +
'am.SeverityID, sm.SeverityDesc, am.AuditUser, am.ApplicationID, a.AppDesc, ' +
'am.ModuleName, am.RoutineName, am.SystemID, am.TransactionTypeID, ttm.TransactionTypeDesc, am.ActionCodeID, ' +
'acm.ActionCodeDesc, am.DTS, am.RecordstatusID, amt.TagID, t.TagDesc, t.TagTypeID, tgtm.TagTypeDesc, amt.Value ' +
'From AuditMessage am Join SeverityMaster sm On am.SeverityID = sm.SeverityID ' +
'JOIN Application a On a.ApplicationID = am.ApplicationID ' +
'JOIN ActionCodeMaster acm On am.ActionCodeID = acm.ActionCodeID ' +
'JOIN TransactionTypeMaster ttm On am.TransactionTypeID = ttm.TransactionTypeID ' +
'LEFT Outer Join AuditMessageTags amt On am.AuditMessageID = amt.AuditMessageID ' +
'LEFT Outer Join Tag t On amt.TagID = t.TagID ' +
'LEFT Outer Join TagTypeMaster tgtm On t.TagTypeID = tgtm.TagTypeID ' +
'WHERE am.ApplicationID = ' + Cast(@ApplicationID As VarChar(5))

If @TransactionTypeID Is Not Null
Set @SQL = @SQL + ' And am.TransactionTypeID = ' + Cast(@TransactionTypeID As VarChar(10))

If @ActionCodeID Is Not Null
Set @SQL = @SQL + ' And am.ActionCodeID = ' + Cast(@ActionCodeID As VarChar(10))

If @RecordStatusID Is Not Null
Set @SQL = @SQL + ' And am.RecordStatusID = ' + Cast(@RecordStatusID As VarChar(10))

If @TagID Is Not Null
Set @SQL = @SQL + ' And t.TagID = ' + Cast(@TagID As VarChar(10))

If @TagValue Is Not Null
Set @SQL = @SQL + ' And amt.Value Like ''%' + @TagValue + '%'''

Set @SQL = @SQL + ' Order By am.AuditDate'

--Print @SQL

Exec (@SQL)

CleanUp:
Return

ErrorCheck:
Select @ErrorMsg = 'The Following Error Occured: ' + Convert(VarChar(20),@@Error) + ' Error Ocurred During Get AuditMessage By Application. ApplicationID {%d}'
RaisError(@ErrorMsg,16, -1, @ApplicationID)
GoTo CleanUp

GO

[mist]

One possibility - that would be inefficient - is to provide defaults for the arguments that would pass everything, and include all of the WHERE conditions..

So, for example, if you're passing a text parameter, make the default for that parameter '%' and make the condition

...WHERE testfield like @parameter

for text comparisions and for numerical values have a BETWEEN comparison with parameters that default to the entire possible range of values, or if one is supplied the other is also set to that value .. so the comparison is always

...BETWEEN @parameter and @parameter2

and you just set what @parameter and @parameter2 are depending on what values have been supplied (or not) to the procedure.

This approach is incredibly hackish though. I'd avoid it myself.

I'd probably be tempted to code however many different stored procedures are necessary to cope with the possible variations and then use application layer logic to determine which one is called.

[mist]

You could also try something like this suggestion, which may be exactly what you need

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=14860

[sdives]

This looks very promising. I'll starting running some tests.

Thanks a bunch for your help.

Sidney Ives