Professional Web Applications Themes

Question on Ownership chaining: - Microsoft SQL / MS SQL Server

CREATE TABLE XYZ.TABLE1 (col1, col2); CREATE PROCEDURE dbo.PROC1 AS SELECT * FROM XYZ.TABLE1; CREATE PROCEDURE dbo.PROC2 AS EXEC sp_excutesql N'select * from XYZ.TABLE1'; IF the login user X has rights to dbo.Proc1 & dbo.Proc2 and no rights on XYZ.TABLE1 then X can execute dbo.Proc1 but fails to execute dbo.Proc2. Why does the ownership chain break on calling "EXEC" or "sp_excutesql" stored procedures? Does it mean, "EXEC" and "sp_executesql" stored procedures are not owned by "dbo" owner? Who is the owner of these system procedures? Thanks, Naresh...

  1. #1

    Default Question on Ownership chaining:


    CREATE TABLE XYZ.TABLE1 (col1, col2);

    CREATE PROCEDURE dbo.PROC1 AS SELECT * FROM XYZ.TABLE1;

    CREATE PROCEDURE dbo.PROC2 AS EXEC sp_excutesql N'select
    * from XYZ.TABLE1';

    IF the login user X has rights to dbo.Proc1 & dbo.Proc2
    and no rights on XYZ.TABLE1 then

    X can execute dbo.Proc1 but fails to execute dbo.Proc2.

    Why does the ownership chain break on calling "EXEC"
    or "sp_excutesql" stored procedures?

    Does it mean, "EXEC" and "sp_executesql" stored
    procedures are not owned by "dbo" owner? Who is the owner
    of these system procedures?

    Thanks,
    Naresh




    Koova Guest

  2. #2

    Default Re: Question on Ownership chaining:

    Dynamic SQL effectively breaks the ownership chain. A user must have
    EXECUTE permissions on both dbo.PROC2 and SELECT permissions on
    dbo.XYZTABLE1 because of the dynamic SQL.

    --
    Hope this helps.

    Dan Guzman
    SQL Server MVP

    -----------------------
    SQL FAQ links (courtesy Neil Pike):

    http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
    http://www.sqlserverfaq.com
    http://www.mssqlserver.com/faq
    -----------------------

    "Koova" <com> wrote in message
    news:002001c368c8$6c72da00$gbl... 


    Dan Guest

  3. #3

    Default Re: Question on Ownership chaining:


    Thanks Dan,

    I think it is a limitation and defeats the purpose of
    implmenting security using stored procedures.

    I find it difficult to understand this behavior...

    Thanks again,
    Koova
    Koova Guest

  4. #4

    Default Re: Question on Ownership chaining:

    It is actually a security feature. SQL injection would be just too easy to do if it weren't for this
    behaviour. Example below uses EXEC for simplicity, but same applies for sp_executesql.

    CREATE PROCEDURE dbo.PROC2 x varchar(10)
    AS
    EXEC('SELECT * from XYZ.TABLE1 WHERE col1 = ' x)

    Say that the user now executes:
    EXEC PROC2 '23 TRUNCATE TABLE orders'

    Ouch!

    --
    Tibor Karaszi, SQL Server MVP
    Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver


    "Koova" <com> wrote in message news:020a01c368dc$93ead380$gbl... 


    Tibor Guest

Similar Threads

  1. Replies: 2
    Last Post: August 3rd, 04:45 PM
  2. Chaining live video stream
    By CHUNG_1977 in forum Macromedia Flash Flashcom
    Replies: 2
    Last Post: January 22nd, 06:26 PM
  3. Chaining streams between 2 media server
    By ninjateapartyover in forum Macromedia Flash Flashcom
    Replies: 2
    Last Post: April 18th, 09:52 PM
  4. chaining comparisons
    By Kurt M. Dresner in forum Ruby
    Replies: 28
    Last Post: July 22nd, 05:03 AM
  5. chaining of -> operator
    By Phil Roberts in forum PHP Development
    Replies: 1
    Last Post: July 15th, 03:21 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139