Re: Confused with security

From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 08/31/04

  • Next message: anonymous_at_discussions.microsoft.com: "Re: SQL Profiler producing phantom traffic" 
    Date: Tue, 31 Aug 2004 22:44:24 +0200

    On Tue, 31 Aug 2004 19:03:22 GMT, Howard Carr wrote:

    >I thought I understood what was happening - obviously not.
    >
    >Here is my problem:
    >
    >Using windows integrated security.
    >No users should have access to the table directly.
    >
    >I have a UDF that I have granted select, insert, update, and delete on to a
    >created roll that has windows users added to it.
    >The user can select through the function ok. but when they try to insert,
    >they get an error complaining of lack of rights to insert.
    >I had to add insert rights to the table for the user to insert through the
    >function.
    >Is it becuse the function is a select statement, and it dynamically creates
    >the update, insert methods directly against the table and by-passes the
    >function and that is my problem?
    >
    >Thanks for any clarification.
    >

    Hi Howard,

    Your guess is correct - welcome to the pitfalls of dynamic SQL.

    If a stored procedure references a table owned by the same userid that
    owns the stored procedure, no additional check for access right to that
    table is made. Each user that has rights to execute the procedure can
    access that table through that procedure. Other means of access to the
    table still need explicit permissions for that user on the table!

    Dynamic SQL is executed in a seperate environment, just as if the user
    exeecuted the SQL from Query Analyzer. During execution, SQL is not aware
    that this is started from a stored procedure. Therefor, the ownership
    chain from procedure to table breaks when dynamic SQL is executed.

    Best, Hugo 

    -- 
(Remove _NO_ and _SPAM_ to get my e-mail address)
  • Next message: anonymous_at_discussions.microsoft.com: "Re: SQL Profiler producing phantom traffic"

    Relevant Pages

    • Re: Confused with security
      ... >>Using windows integrated security. ... > Your guess is correct - welcome to the pitfalls of dynamic SQL. ... > owns the stored procedure, no additional check for access right to that ... Each user that has rights to execute the procedure can ...
      (microsoft.public.sqlserver.server)
    • Re: Logging within User_Defined Functions (UDF)
      ... You can't perform dynamic SQL or DML in a function. ... Looks like a stored procedure that you ... Ultimately you can only execute ... > DECLARE @cmdstr nvarchar ...
      (microsoft.public.sqlserver.programming)
    • Re: stored procedure security
      ... > execute privelages on the stored procedure, ... As soon as you employ dynamic sql, the logon executing the stored procedure ... In order to execute this stored ...
      (microsoft.public.sqlserver.security)
    • Re: DENY table SELECT, GRANT stored proc EXEC, but EXECUTE a string
      ... Once you go to dynamic SQL, you are outside the ownership / security context ... I wrote a script that GRANTS EXECUTE to ... every stored procedure in the database to and DENY ...
      (microsoft.public.sqlserver.security)
    • RE: SQL stored procedure executing twice
      ... I wasn't aware that DLookupwould execute the "domain" more than once. ... caused the stored procedure to execute twice. ... Dim stDocName As String ... My pass-thru query properties ...
      (microsoft.public.access.modulesdaovba)