Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2003
    Location
    Brisbane, Australia
    Posts
    110

    Unanswered: Stored procs, Exec and security

    I have an application which calls a stored procedure. This stored procedure contains something like:

    @SQL = 'select col1, ... from table1 ... etc'
    EXEC(@SQL)

    The user that the application uses to log on to the database has execute permissions on the stored procedure, however I don't want to have to give that user permissions on the table. Is this possible?

    For example:

    GRANT execute on my_stored_proc to user1
    Stored proc contents:= select col1, ... from table1 ... etc

    Success!!
    ---------------------
    GRANT execute on my_stored_proc to user1
    Stored proc contents:= @SQL = 'select col1, ... from table1 ... etc', EXEC(@SQL)

    Failure... no select permissions on table1
    ----------------------
    GRANT execute on my_stored_proc to user1
    GRANT select on table1 to user1
    Stored proc contents:= @SQL = 'select col1, ... from table1 ... etc', EXEC(@SQL)

    Success!!... But not desirable..


    Any ideas?

    thanks

  2. #2
    Join Date
    Apr 2003
    Location
    Israel
    Posts
    81
    Hi,

    I am afraid you can't do it.
    Since EXEC(@slqstr) contains a variable, its permissions are not checked when the procedure created. Instead, the SQL Server architecture requires you to have permissions to execute the things that will be contained in this variable.
    --
    kukuk

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    149
    To summarize kukuk's comments, you need to have execute permissions on the base objects if you use dynamic SQL. (That is what you were trying to do in your SP). One of the advantages of a stored procedure, from a security stanpoint, is that you can revoke the access on the base tables and give the users only acces to the stored procedures. But if you use Dynamic SQL, this purpose is defeated. Most of the times, you can avoid use of Dynamic SQL using CASE statements in your selects. Try it. If you need help, let us know.

    All the best

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    if you elect to use dynamic SQL, only grant "SELECT" permissions on the base tables. This is by no means ideal but you do limit your exposure to people being able to change data out from under you.
    Paul Young
    (Knowledge is power! Get some!)

  5. #5
    Join Date
    Feb 2003
    Location
    Brisbane, Australia
    Posts
    110
    Thanks all. Just as I thought, but hoping that it wasn't going to be the case.

  6. #6
    Join Date
    Dec 2002
    Posts
    63
    Another option would be to create a view of the table and grant permissions on the view. That way you can be more selective as to what data is available and you dont have to give up permission on the table itself.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •