Peter Eisentraut wrote:
>> SQL/PSM default for SQL procedures are SECURITY DEFINER (like views),
>
> I can't find this in the standard. Where did you get this
> information?
I only have a draft version of SQL:2003, which says in the 'Foundation'
book, chapter 11.50 ("<SQL-invoked routine>"), about the "<rights
clause>"
(which can be "SQL SECURITY INVOKER" or "SQL SECURITY DEFINER") in
Syntax Rule
19) b):
"If R is an SQL routine, then if <rights clause> is not specified,
then SQL SECURITY DEFINER is implicit."
It adds, however, in chapter 4.27.2, that
"An SQL routine is an SQL-invoked routine whose <language clause>
specifies SQL."
Rule 20) b) of chapter 11.50 says that
"If R is an external routine, then if <external security clause> is not
specified, then EXTERNAL SECURITY IMPLEMENTATION DEFINED is implicit."
Here, "An external routine is one whose <language clause> does not
specify SQL."
"Implementation defined" here means the obvious thing.
So one can make a case that SQL functions should be SECURITY DEFINER by
default, but for all other procedural languages the standard explicitly
sets no rules.
For me, who comes from a UNIX background, SECURITY INVOKER is the
natural
default value, and the standard's decision surprises me. I guess that it
is counter-intuitive to most people, and moreover it would break
compatibility with current behaviour.
I think that it is wise to break with the standard in this case,
SECURITY INVOKER being the safer option.
It should be noted, however, that Oracle's PL/SQL functions have
AUTHID DEFINER by default, which corresponds to our SECURITY DEFINER.
Yours,
Laurenz Albe
