Answered by:

Query on a stored procedure

Question

I have to limit the information returned to the user from a stored procedure. Not too hard to do but my problem is more at the filtering and ordering level.

I wish to prevent @SearchTerms NVARCHAR(MAX) and @OrderTerms NVARCHAR(MAX) because first it slows down the store procedure by using dynamic t-sql and having to plan the query. Second, i'd have to use sp_executesql and it would require me to use an execute as statement which defeats the purpose of security since the search params may completetly break the secure aspect..

Thus i came to the conclusion that the only way for a user would be to query on the results of a stored procedure. Catch, i have to pass variables to the store proc. So now i'm wondering how i can do this...

Here are the facts:

I need to preserve the stored procedure because complex security checks occurs within it

I want to preserve static sql to minimize performance impact since the resultsets may be quite large

I want it to be completly T-SQL driven, i don't want to rely on the software

I want this solution to be able to prevent injections but allow for easy filtering and sorting (Such as SELECT * FROM sp_getusers WHERE crits ORDER BY ....)

Take this SQL for example, this is here a simple restricted access, imagine now that i have to implement template checks. For agents it is not a problem since there are no templates for agents but for cases, configuraiton items or contacts.... ouch!

Now after looking at this, this is what you would have done in a view is that it?

Note that i'm trying to evade the potential performance problems too. In some tables such as the contacts table, a user can be limited on the phone numbers, email adresses, up to 20 custom fields and these must be template based. Do you think it will slow down enormously?

Nope sorry... doesn't fix it. I want my users to be able to query the results of the stored procedure, not filter for them. People may need to do complex queries such as complex search statements and groupings and order bys so these techniques do not apply.

Problem is, i want to use a stored procedure because the stored procedure returns information based on the user querying the information so i can hide some details such as application restricted information. It's a templated application where configured fields may contain sensitive information that the admin may want to hide from view in the event the user doesn't have access to that information.

The stored procedure is being called right now with 2 output params which control the error state but i think i can remove them and use only the SESSIONID which is a key returned to the user when he calls SESSIONS_CREATE. This way i can keep a track of what user queries the database and i prepare the permission settings before hand into the sessions table so i don't have to recalculate them on each call.

I know it is not possible to pass params to a view, at least, not that i remember of, so i couldn't push that SP into a view. Unless i am mistaken? Could i actually ask my user to SELECT FROM A VIEW and pass in the SESSIONID parameter to the call?Groupe-CDGI Developper

Maybe the solution would be to change the calling of the procedure to detect which user is calling the stored procedure... Is there a way to uniquely identity a user sending a request to the server? One that i could without a flaw assign in my sessions table and keep track of the activity and so on?

I know there is a hostname, but this is invalid in TS servers as many users could have the same hostname and web requests would come all from the same hostname... but i wonder...Groupe-CDGI Developper

It could but i can't work on username since my users may use SQL Authentication with a single shared login depending on their network structure. Here at work we use Widnows Auth, and it would solve several login problems since i plan to add a windows authentication bypass to my app, but in a non domain based structure, this can't be used sadly and i have a lot like that, out of 30 clients, about 10 are in Novell which doesn'T really work correctly with windows authentication.

Note though that i have no one in workgroups, there is always a managed network involved.Groupe-CDGI Developper

Ok, nice thing it can actually make it lighter to use the application api using this little function and i only have to remove the params from my stored procedures that don't need to session id and all i automatically handled, i like that, but it still doesn't solve the problem of subquerying the stored procedure.

I thought a view could run a stored procedure but no, it seems it is not possible so i wonder now...Groupe-CDGI Developper

Take this SQL for example, this is here a simple restricted access, imagine now that i have to implement template checks. For agents it is not a problem since there are no templates for agents but for cases, configuraiton items or contacts.... ouch!

Now after looking at this, this is what you would have done in a view is that it?

Note that i'm trying to evade the potential performance problems too. In some tables such as the contacts table, a user can be limited on the phone numbers, email adresses, up to 20 custom fields and these must be template based. Do you think it will slow down enormously?

Confirmed, it seems my new solution solves many problems at once and
looks like it is working fine, i'm able to issue a complex command as
stated above and it solves all the problems i had regarding data
restrictions, column based restrictions, template based restrictions
and non-logged in as an agent to the system restrictions. But when you
are authorized, then you can execute all kinds of manipulations, should
it be goruping, filtering and sorting... so me = happy!

Excellent, I'm glad that you got this to work. In response to your earlier post - yes you're right, that is the way that I was anticipating that you could use the function within a view.

One thing that I should point out is that any user with permission to view the sys.processes view will be able to view the context info values for other connections - you need to decide whether or not this is a security risk in your particular scenario.

I would suggest that you thoroughly test the solution, particularly with regards to multiple users concurrently performing similar tasks.

We will recommend to the administrators of the software to only give the contact_user role to their users and these users are basic users with denydataread and denydatawrite + specific accesses to SPs and Views for the software only. We are not responsible if the user is given more rights and finds a way to hack the database. Groupe-CDGI Developper