Re: Confused with security

From: Howard Carr (hcarr_at_nf.sympatico.ca)
Date: 08/31/04 

Date: Tue, 31 Aug 2004 22:33:00 GMT

Thanks so much Hugo, I thought I was really lost!

"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
news:teo9j0l1fnknj0frfq3l0u6hgs2s5oh9bm@4ax.com...
> 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)

Relevant Pages

  • Re: Confused with security
    ... >Using windows integrated security. ... Your guess is correct - welcome to the pitfalls of dynamic SQL. ... If a stored procedure references a table owned by the same userid 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)