none
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:
    1. I need to preserve the stored procedure because complex security checks occurs within it
    2. I want to preserve static sql to minimize performance impact since the resultsets may be quite large
    3. I want it to be completly T-SQL driven, i don't want to rely on the software
    4. 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 ....)

    Any way you see this possible?

    Groupe-CDGI Developper
    Tuesday, December 23, 2008 2:11 PM

Answers

  • Fair enough. How about investigating the use of CONTEXT_INFO() and SET CONTEXT_INFO?

    You should be able to set the context info when creating a session, then read the context info on subsequent calls.

    Chris
    MCTS : SQL Server 2005 ; MCITP : Database Developer
    Tuesday, December 23, 2008 3:38 PM
  • 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!

        SELECT  
            agents.id,  
            agents.environmentid,  
            agents.loginname,  
            agents.contactid,  
            CASE WHEN 0=(SELECT COUNT(*) FROM AgentPermissions INNER JOIN Actions ON Actions.Id = AgentPermissions.ActionId INNER JOIN Sessions ON Sessions.AgentId = AgentPermissions.AgentId AND Sessions.SessionId = CONTEXT_INFO() WHERE Actions.ModuleKeyName = 'Agents' AND Actions.KeyName = 'ReadNote'THEN NULL ELSE agents.note END AS note,  
            agents.supervisoragentid,  
            CASE WHEN 0=(SELECT COUNT(*) FROM AgentPermissions INNER JOIN Actions ON Actions.Id = AgentPermissions.ActionId INNER JOIN Sessions ON Sessions.AgentId = AgentPermissions.AgentId AND Sessions.SessionId = CONTEXT_INFO() WHERE Actions.ModuleKeyName = 'Agents' AND Actions.KeyName = 'ReadSignature'THEN NULL ELSE agents.emailautosignature END AS emailautosignature,  
            CASE WHEN 0=(SELECT COUNT(*) FROM AgentPermissions INNER JOIN Actions ON Actions.Id = AgentPermissions.ActionId INNER JOIN Sessions ON Sessions.AgentId = AgentPermissions.AgentId AND Sessions.SessionId = CONTEXT_INFO() WHERE Actions.ModuleKeyName = 'Agents' AND Actions.KeyName = 'ReadSignature'THEN NULL ELSE agents.emailautosubject END AS emailautosubject,  
            agents.lastupdated,  
            agents.lockedtoagentid,  
            agents.defaultcasetemplateid,  
            agents.defaultcitemplateid,  
            agents.defaultcontacttemplateid,  
            agents.optionisinactive,  
            agents.optionusesclientfilter,  
            agents.optionconfirmclose,  
            agents.optionnotifytransfers, 
            agents.optionhidegroupedcolumns, 
            agents.optionmaxrecords, 
            agents.optionlanguage, 
            agents.optionemailjoinrequest, 
            agents.optionemailjoinfiles, 
            agents.optionautomaticrefresh, 
            agents.optionshowtransfers, 
            agents.optionemailusemapi 
        FROM  
            agents 
            INNER JOIN sessions ON sessions.environmentid = agents.environmentid 
                AND sessions.sessionid = CONTEXT_INFO() 

    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?

    Thanks again, you are a big help here!!!

    Groupe-CDGI Developper
    Tuesday, December 23, 2008 4:12 PM

All replies

  • Tuesday, December 23, 2008 2:18 PM
  • 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.

    Thanks for trying though.
    Groupe-CDGI Developper
    Tuesday, December 23, 2008 2:36 PM
  • Is it not possible to make a view based on the procedures and just remove  @SearchTerms NVARCHAR(MAX) and @OrderTerms NVARCHAR(MAX) 

    That covers your 4 points and its way more secure using a view than a proc if your  worried about injection. 

    http://weblogs.sqlteam.com/
    Tuesday, December 23, 2008 2:45 PM
  • 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
    Tuesday, December 23, 2008 2:56 PM
  • 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
    Tuesday, December 23, 2008 3:00 PM
  • Would SUSER_SNAME() help you here?

    Incidentally you can use this function to perform filtering in a VIEW by either 'hiding' the sensitive columns with a CASE statement, or by including the function in the WHERE clause/JOIN.

    Chris


    MCTS : SQL Server 2005 ; MCITP : Database Developer
    Tuesday, December 23, 2008 3:29 PM
  • 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
    Tuesday, December 23, 2008 3:33 PM
  • Fair enough. How about investigating the use of CONTEXT_INFO() and SET CONTEXT_INFO?

    You should be able to set the context info when creating a session, then read the context info on subsequent calls.

    Chris
    MCTS : SQL Server 2005 ; MCITP : Database Developer
    Tuesday, December 23, 2008 3:38 PM
  • 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
    Tuesday, December 23, 2008 3:53 PM
  • 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!

        SELECT  
            agents.id,  
            agents.environmentid,  
            agents.loginname,  
            agents.contactid,  
            CASE WHEN 0=(SELECT COUNT(*) FROM AgentPermissions INNER JOIN Actions ON Actions.Id = AgentPermissions.ActionId INNER JOIN Sessions ON Sessions.AgentId = AgentPermissions.AgentId AND Sessions.SessionId = CONTEXT_INFO() WHERE Actions.ModuleKeyName = 'Agents' AND Actions.KeyName = 'ReadNote'THEN NULL ELSE agents.note END AS note,  
            agents.supervisoragentid,  
            CASE WHEN 0=(SELECT COUNT(*) FROM AgentPermissions INNER JOIN Actions ON Actions.Id = AgentPermissions.ActionId INNER JOIN Sessions ON Sessions.AgentId = AgentPermissions.AgentId AND Sessions.SessionId = CONTEXT_INFO() WHERE Actions.ModuleKeyName = 'Agents' AND Actions.KeyName = 'ReadSignature'THEN NULL ELSE agents.emailautosignature END AS emailautosignature,  
            CASE WHEN 0=(SELECT COUNT(*) FROM AgentPermissions INNER JOIN Actions ON Actions.Id = AgentPermissions.ActionId INNER JOIN Sessions ON Sessions.AgentId = AgentPermissions.AgentId AND Sessions.SessionId = CONTEXT_INFO() WHERE Actions.ModuleKeyName = 'Agents' AND Actions.KeyName = 'ReadSignature'THEN NULL ELSE agents.emailautosubject END AS emailautosubject,  
            agents.lastupdated,  
            agents.lockedtoagentid,  
            agents.defaultcasetemplateid,  
            agents.defaultcitemplateid,  
            agents.defaultcontacttemplateid,  
            agents.optionisinactive,  
            agents.optionusesclientfilter,  
            agents.optionconfirmclose,  
            agents.optionnotifytransfers, 
            agents.optionhidegroupedcolumns, 
            agents.optionmaxrecords, 
            agents.optionlanguage, 
            agents.optionemailjoinrequest, 
            agents.optionemailjoinfiles, 
            agents.optionautomaticrefresh, 
            agents.optionshowtransfers, 
            agents.optionemailusemapi 
        FROM  
            agents 
            INNER JOIN sessions ON sessions.environmentid = agents.environmentid 
                AND sessions.sessionid = CONTEXT_INFO() 

    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?

    Thanks again, you are a big help here!!!

    Groupe-CDGI Developper
    Tuesday, December 23, 2008 4:12 PM
  • 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!

    merry christmas and a happy new year to all.
    Groupe-CDGI Developper
    Tuesday, December 23, 2008 6:47 PM
  • 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.

    Chris
    MCTS : SQL Server 2005 ; MCITP : Database Developer
    Tuesday, December 23, 2008 10:36 PM
  • 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
    Wednesday, December 24, 2008 2:49 PM