Professional Web Applications Themes

Question on creating procedures with invoker's rights: - Microsoft SQL / MS SQL Server

By default stored procedures are creating in definer's right. Definer is the owner who owns the stored procedure. Any SQL statements from the stored procedure are executed with the owner's permission. Even if the caller of the stored procedure does not have rights to access database objects, he can access them throught the stored procedure if the owner of the procedure can access it provided the caller has the execution rights on the stored procedure. On the other hand, stored procedures with the invoker's rights with execute the sql statements in the stored procedure with the callers permissions irrespective of ...

  1. #1

    Default Re: Question on creating procedures with invoker's rights:


    By default stored procedures are creating in definer's
    right. Definer is the owner who owns the stored
    procedure. Any SQL statements from the stored procedure
    are executed with the owner's permission. Even if the
    caller of the stored procedure does not have rights to
    access database objects, he can access them throught the
    stored procedure if the owner of the procedure can access
    it provided the caller has the execution rights on the
    stored procedure.

    On the other hand, stored procedures with the invoker's
    rights with execute the sql statements in the stored
    procedure with the callers permissions irrespective of
    the owner of the stored procedure.

    Definer's rights give an extra level of security whereas
    invoker's rights gives a shared compiled code that can be
    shared among different owners.
    Koova Guest

  2. #2

    Default Re: Question on creating procedures with invoker's rights:

    hi,

    i am interested in definer and invoker, but I can't find any information in
    sql online book. is it really a concept in sql server? maybe it is just for
    Oracle??





    "Koova" <com> wrote in message
    news:05bc01c3589c$764a2920$gbl... 


    Matt Guest

  3. #3

    Default RE: Question on creating procedures with invoker's rights:

    Hi,

    It seems perhaps what you are after is the topic 'Using Ownership Chains'
    in SQL Book Online.

    There is a correction of the topic:

    BUG: Doentation of Ownership Chain in BOL May Be Misleading WGID:249
    ID: 296197
    http://support.microsoft.com/default.aspx?scid=KB;EN-US;296197



    Sincerely,

    Alick Ye, MCSD
    Microsoft Online Partner Support

    Get Secure! - www.microsoft.com/security
    This posting is provided "as is" with no warranties and confers no rights.



    --------------------
    | Content-Class: urn:content-classes:message
    | From: "Koova" <com>
    | Sender: "Koova" <com>
    | X-Tomcat-NG: microsoft.public.sqlserver.programming
    |
    |
    | I have a requirement to create stored procedures with
    | invoker's rights instead of it being in the definer's
    | rights.
    |
    | It was easy for me to implement this Oracle database but
    | when it comes to SQLServer, I am not able to find support
    | for creating it with invoker's rights.
    |
    | I will appreciate a solution or atleast a workaround to
    | this problem.
    |
    | Thanks a lot!!
    |

    Alick Guest

  4. #4

    Default RE: Question on creating procedures with invoker's rights:


    Hi Alick,

    Thank you for referring me to the article on using
    ownership chains. It seems that SQLServer allows to
    configure the database for "Cross DB Ownership Chaining"
    but I could not find any settings for individual stored
    procedure or view.

    I want to disable ownership chaining for certain stored
    procedures. Is there any settings available? This can be
    done in Oracle as follows:

    CREATE OR REPLACE TestProc
    AUTHID CURRENT_USER AS...

    I badly need this feature and as the last option, I am
    planning to check the permissions manually for all the
    objects that are referred in each stored procedures for
    the login user using "PERMISSION" system function.

    Thanks,


     
    Ownership Chains' 
    Misleading WGID:249 
    US;296197 
    confers no rights. 
    but 
    support 
    to 
    Koova Guest

  5. #5

    Default RE: Question on creating procedures with invoker's rights:

    Hi,

    By default ownership chaining is broken between databases objects
    (including stored procedure) unless in SP3 you enable it.

    Within one database ownership chaining works as normal, including objects
    stored procedure. If stored procedure is owned by different owner, the
    chain by default is broken and SQL server will check the permission when
    someone executes the SP.

    By default, all database objects have owners. When an object such as a
    view, a stored procedure, or a user-defined function references another
    object, an ownership chain is established. For example, a table that is
    owned by the same user. When the same user owns the source object, the
    view, stored procedure, or user-defined function, and all target objects
    (underlying tables, views, or other objects), the ownership chain is said
    to be unbroken. When the ownership chain is unbroken, SQL Server checks
    permissions on the source object but not on the target objects.

    If the ownership chain of a procedure or view is broken (not all the
    objects in the chain are owned by the same user), SQL Server checks
    permissions on each object in the chain whose next lower link is owned by a
    different user. In this way, SQL Server allows the owner of the original
    data to retain control over its accessibility. The above sentence means in
    such a situation, only those statements where the user has the necessary
    permissions are executed, and the remaining statements receive an
    "Insufficient Permissions" error.

    We can configure cross-database ownership chaining by using SQL Enterprise
    Manager:

    A. To set this option for all databases, follow these steps:

    Right-click <server>.
    Click to select Properties.
    Click Security.
    Click to select Allow cross-database ownership chaining in the Ownership
    chaining section.
    Click OK. You are prompted to stop and restart the SQL Server services.
    Click OK .

    B. To enable this option at the database level, follow these steps:

    Right-click the <database>.
    Click to select Properties.
    Click Options.
    Click to select Allow Cross Database Ownership Chaining in the Settings
    section

    http://support.microsoft.com/?id=810474




    Sincerely,

    Alick Ye, MCSD
    Microsoft Online Partner Support

    Get Secure! - www.microsoft.com/security
    This posting is provided "as is" with no warranties and confers no rights.





    --------------------
    | Content-Class: urn:content-classes:message
    | From: "Koova" <com>
    | X-Tomcat-NG: microsoft.public.sqlserver.programming
    |
    |
    | Hi Alick,
    |
    | Thank you for referring me to the article on using
    | ownership chains. It seems that SQLServer allows to
    | configure the database for "Cross DB Ownership Chaining"
    | but I could not find any settings for individual stored
    | procedure or view.
    |
    | I want to disable ownership chaining for certain stored
    | procedures. Is there any settings available? This can be
    | done in Oracle as follows:
    |
    | CREATE OR REPLACE TestProc
    | AUTHID CURRENT_USER AS...
    |
    | I badly need this feature and as the last option, I am
    | planning to check the permissions manually for all the
    | objects that are referred in each stored procedures for
    | the login user using "PERMISSION" system function.
    |
    | Thanks,
    |
    |
    |
    | >-----Original Message-----
    | >Hi,
    | >
    | >It seems perhaps what you are after is the topic 'Using
    | Ownership Chains'
    | >in SQL Book Online.
    | >
    | >There is a correction of the topic:
    | >
    | >BUG: Doentation of Ownership Chain in BOL May Be
    | Misleading WGID:249
    | >ID: 296197
    | >http://support.microsoft.com/default.aspx?scid=KB;EN-
    | US;296197
    | >
    | >
    | >
    | >Sincerely,
    | >
    | >Alick Ye, MCSD
    | >Microsoft Online Partner Support
    | >
    | >Get Secure! - www.microsoft.com/security
    | >This posting is provided "as is" with no warranties and
    | confers no rights.
    | >
    | >
    | >
    | >--------------------
    | >| Content-Class: urn:content-classes:message
    | >| From: "Koova" <com>
    | >| Sender: "Koova" <com>
    | >| X-Tomcat-NG: microsoft.public.sqlserver.programming
    | >|
    | >|
    | >| I have a requirement to create stored procedures with
    | >| invoker's rights instead of it being in the definer's
    | >| rights.
    | >|
    | >| It was easy for me to implement this Oracle database
    | but
    | >| when it comes to SQLServer, I am not able to find
    | support
    | >| for creating it with invoker's rights.
    | >|
    | >| I will appreciate a solution or atleast a workaround
    | to
    | >| this problem.
    | >|
    | >| Thanks a lot!!
    | >|
    | >
    | >.
    | >
    |

    Alick Guest

  6. #6

    Default RE: Question on creating procedures with invoker's rights:


    Hi,

    I very well understand the ownership chaining and how it
    works. My question is "how can I disable ownership
    chaining on a stored procedure, view or a function?"

    Ownership chainging in Oracle works exactly the same way
    but the owner has an ability to define the object (sp,
    view or function) to execute in invoker's rights. This
    will allow the objects created in invoker's rights to be
    shared by other users and execute the procedures with
    their own previliges instead of the owner who created it.
    This is a useful feature when you donot want to manage
    the security provided by ownership chaining.

    Appreciate your feedback.

    Naresh
    Koova Guest

  7. #7

    Default RE: Question on creating procedures with invoker's rights:

    Hi,

    1. By default ownership chaining is broken between databases objects
    (including stored procedure) unless in SP3 you enable it. This is the
    situation across database, across database ownership chaining can be
    enabled explicitly from SQL 2000 SP3.

    2. Within one database ownership chaining works as normal, including
    objects stored procedure. If stored procedure is owned by different owner,
    the chain by default is broken and SQL server will check the permission
    when someone executes the SP, which means in this situation, SQL Server
    will check the permissions, and the ownership chain is broken (disabled) by
    default and we don't need to disable it again.

    Since Oracle is a different product I don't think all the feature/concept
    in Oracle will be the same or even will have in SQL.

    Hope it helps. If you have any questions, please feel free to let me know.


    Sincerely,

    Alick Ye, MCSD
    Microsoft Online Partner Support

    Get Secure! - www.microsoft.com/security
    This posting is provided "as is" with no warranties and confers no rights.



    --------------------
    | Content-Class: urn:content-classes:message
    | From: "Koova" <com>
    | Sender: "Koova" <com>
    | X-Tomcat-NG: microsoft.public.sqlserver.programming
    |
    |
    | Hi,
    |
    | I very well understand the ownership chaining and how it
    | works. My question is "how can I disable ownership
    | chaining on a stored procedure, view or a function?"
    |
    | Ownership chainging in Oracle works exactly the same way
    | but the owner has an ability to define the object (sp,
    | view or function) to execute in invoker's rights. This
    | will allow the objects created in invoker's rights to be
    | shared by other users and execute the procedures with
    | their own previliges instead of the owner who created it.
    | This is a useful feature when you donot want to manage
    | the security provided by ownership chaining.
    |
    | Appreciate your feedback.
    |
    | Naresh
    |

    Alick Guest

Similar Threads

  1. Question on rights required for debugging
    By Abhi in forum ASP Components
    Replies: 0
    Last Post: April 12th, 01:38 PM
  2. Access 2000 and creating stored procedures
    By Mike in forum ASP Database
    Replies: 11
    Last Post: August 20th, 11:10 AM
  3. Newbie - Stored Procedures/ASP Question
    By newbie in forum ASP Database
    Replies: 11
    Last Post: February 19th, 06:27 PM
  4. Profiler Question (Stored Procedures)
    By Amos Soma in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 14th, 12:56 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