Re: security definer default for some PL languages (SQL/PSM)?

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