"Jim Starkey" ...

>
> Vlad Horsun wrote:
>
> >>An underlying question is whether security exists to protect the
> >>careless from themselves? A developer who allows procedures to execute
> >>arbitrary SQL in an application with sophisticated security rules is
> >>careless at best. Or do we want to make it possible to build systems
> >>that use all the capabilities of the system together?
> >>
> >>
> >
> > I think both security principles for EXECUTE STATEMENT have rights
> >for existence. We can extend syntax of EXECUTE STATEMENT for example :
> >
> >EXECUTE STATEMENT ... [WITH {USER | OWNER | PROCEDURE} RIGHTS]
> >
> >
> >
> The privileges applied in the original implementation were dictated by
> its implementation -- it looped back into the engine with a separate
> attachment, and had no context for the procedure privileges. While I
> think that the pragmatics justified an inelegant solution, we really
> should recognize that it was a "hack", and under different
> circumstances, no one would have implement a situation where an "execute
> statement" would ever have a security context different than the code
> that executed it.

I'm pretty sure that current security rules for EXECUTE STATEMENT is a
good and right rules. Dynamic SQL must be checked against privileges of its
creator. Since we can't check whether this dynamic statement wrote by
procedure creator within that procedure or came into procedure as parameter
then we must apply most strong security check. For example

CREATE PROCEDURE EXEC_ALL (SQL VARCHAR(4096))
AS
BEGIN
EXECUTE STATEMENT :SQL;
END

Since "SQL" is written by calling user then it must be checked against
privileges of that user. Procedure creator knows nothing about this statement.
Procedure privileges has nothing common with that dynamic statement. Else
we can have big security hole in worst case.

In the other case procedure can construct whole dynamic statement itself.
Therefore procedure creator can know all needed privileges and can grant it to
procedure.

I propose not restrict users with one hard security model but give them
choose

> That being the case, I think the right thing to do is breath a sigh of
> relief that unfortunate compromise will shortly be in our past.
>
> There is no precedent for any SQL statement specifying the security
> context in which it executes. Let's not create one.

I'm not propose what you wrote. I propose to give procedure writers
mechanism to exactly define security rules for unknown dynamic statement

> We have too many options. Let's find some to get rid of rather than
> adding more. A friend, at the time supervisor for VAX DBMS, described
> the product as having "few options but many defaults." That sort of
> befuddlement is the software gods telling you that you have too many
> options.

This is not an options.

May be INNER | LEFT OUTER | RIGHT OUTER | FULL in JOIN
specification also options ? ;)

Regards,
Vlad