Post reply

Dynamic Permissions on Stored Procedure

  • June 20, 2008 at 10:10 am

    #188218

    We need to execute a dynamic query where we cannot ensure the query is not malicious (think of it as allowable SQL injection). We know that this query should ONLY be able to:

    1) Select records from specific tables. These tables are known only right before the query is executed.

    2) Insert records to one specific table. This table is known only right before the query is executed.

    The only solution I know is the following (I'm not sure it will even work):

    1) In the code, create a temporary user specifc to the SPID with the appropriate permissions.

    2) In the code, create a temporary stored procedure specific to the SPID that would EXECUTE AS that new user. This stored procedure would execute the dynamic query.

    3) Execute the new stored procedure

    4) Drop the temporary user

    5) Drop the temporary stored procedure

    This seems like an inefficient approach. Does anyone have a more elegant way to solve this? I believe that the solution to this problem would be a great way to manage SQL injection in many other applicable areas.

  • June 23, 2008 at 12:06 pm

    #832961

    Well, you could make a separate call before and after to give rights and then take them away.

    This way you can keep your user and object static, and just modify the permissions each time.

    1. Call the SP that grants the rights dynamically.

    2. Call the SP you need to do the work.

    3. Call the SP that deletes the rights dynamically.

    Another way would be to make your process such that these dynamic writes happen in a more solid fashion. So what you'd do is make a series of SPs that insert on the various tables you need. Then on the front end, whenever you find out which table it is, just call the appropriate SP. That way you've got solid code that doesn't have to be dynamic, and you've got a solid security model that doesn't fluctuate. Tha'ts the way I would do it.

    Don't force the dynamic model. If security is honestly your goal, then this is much better because it gets you away from dynamic sql and now you've got plans you can actually cache.

    Watch my free SQL Server Tutorials at:
    http://MidnightDBA.com
    Blog Author of:
    DBA Rant – http://www.MidnightDBA.com/DBARant

    Minion Maintenance is FREE:

  • June 23, 2008 at 1:56 pm

    #833009

    Thanks crever, this was very helpful.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply