Hi Dan,
Answers below.
thanks,
Mamta
On 3/8/06, Daniel John Debrunner <[EMAIL PROTECTED]> wrote:
Mamta Satoor wrote:
>
> SQL standards have the default EXTERNAL SECURITY as DEFINER and the
> upgrade code changes in RoutineAliasInfo will mark the pre-10.2
> databases with EXTERNAL SECURITY of DEFINER for the existing routines.
> But that is not what we want for system routines (as per the
> Grant/Revoke spec). The system routines should run with the permissions
> of INVOKER and not DEFINER and hence above assumption, in
> RoutineAliasInfo.readExeternal, of marking all pre-10.2 routines with
> EXECUTE SECURITY of INVOKER is incorrect.
>
> To get around this problem, I was thinking that during hard upgrade of
> pre-10.2 db, I should drop the system routines in
> DD_Version.doFullUpgrade and recreate them with EXTERNAL SECURITY of
> INVOKER. In order to determine what routines in SYSALIASES belong to
> system routines, I was going to check the systemalias boolean column but
> looks like I can't rely on it. I am thinking I can use SCHEMAID in
> SYSALIASES to determine if a routine is a system routine and if so, then
> drop that routine and recreate it with EXTERNAL SECURITY of INVOKER.
> This will take care of system routines. As for pre-10.2 user defined
> routines, they will get marked with EXTERNAL SECURITY of OWNER when
> RoutineAliasInfo.readExternal gets called on them.
What's the effect of the EXTERNAL SECURITY INVOKER/OWNER flag when the
sql standard grant/revoke mode is not enabled?
In my code for RoutineAliasInfo.toString() method(I have not submitted that patch for review yet), I return EXTERNAL SECURITY INVOKER/DEFINER clause along with the rest of the CREATE FUNCTION/PROCEDURE sql. I think I need to fix it so that information about EXTERNAL SECURITY INVOKER/DEFINER gets returned only if the sql standard grant/revoke mode is enabled. In addition, when I work on execution time enforcement of these permissions, at the routine execution time, I should generate the code for EXTERNAL SECURITY only if sql standard grant/revoke mode is enabled. Other than that, I can't see any other effects at this point from EXTERNAL SECURITY INVOKER/DEFINER flag in legacy mode.
Will the EXTERNAL SECURITY INVOKER/OWNER clauses be accepted on the
CREATE FUNCTION/PROCEDURE when the sql standard grant/revoke mode is not
enabled?
No.
Dan.
