db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta Satoor" <msat...@gmail.com>
Subject Re: SYSTEMALIAS flag in SYSALIASES table
Date Wed, 08 Mar 2006 22:52:52 GMT
Actually, I was getting ready to submit my patch for EXTERNAL SECURITY
changes but realized that my upgrade code(in
RoutineAliasInfo.readExternal) will
not mark the system routines with EXTERNAL SECURITY of INVOKER.

Here is how RoutineAliasInfo.readExternal looks like with my changes for
upgrade(changes in *Bold*)

 public void readExternal( ObjectInput in )
   throws IOException, ClassNotFoundException
 {
  super.readExternal(in);
  specificName = (String) in.readObject();
  dynamicResultSets = in.readInt();
  parameterCount = in.readInt();
  parameterStyle = in.readShort();
  sqlAllowed = in.readShort();
  returnType = (TypeDescriptor) in.readObject();
  calledOnNullInput = in.readBoolean();
*  //Following will be 0 from pre-10.2 db, 1 for 10.2 db
  //Can be bumped for future releases which require a change
  //in RoutineAliasInfo.
  int readMoreFromDisk = in.readInt();*

  if (parameterCount != 0) {
   parameterNames = new String[parameterCount];
   parameterTypes = new TypeDescriptor[parameterCount];

   ArrayUtil.readArrayItems(in, parameterNames);
   ArrayUtil.readArrayItems(in, parameterTypes);
   parameterModes = ArrayUtil.readIntArray(in);

  } else {
   parameterNames = null;
   parameterTypes = null;
   parameterModes = null;
  }
  *if (readMoreFromDisk > 1 || readMoreFromDisk < 0) {
   if (SanityManager.DEBUG)
    SanityManager.THROWASSERT("Invalid value " + readMoreFromDisk + " read
from the disk.");
  } else if(readMoreFromDisk == 1)
   //If true, that means we are dealing with 10.2 db and hence
   //we should read external security info from the disk
   executeUsingPermissionsOfRoutineInvoker = in.readBoolean();
  else
   //We are dealing with pre-10.2 db, which doesn't have external
   //security feature on routines and hence no information about
   //external security will be found on the disk. Hence, initialize
   //external security information to definer's permissions as per
   //SQL standards.
   executeUsingPermissionsOfRoutineInvoker = false;*

* }
*
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.

Let me know if I am on the right track or there is any other suggestion to
make sure that at the end of pre-10.2 db upgrade to 10.2, we have system
routines with EXTERNAL SECURITY of INVOKER and user routines with EXTERNAL
SECURITY of OWNER.

thanks,
Mamta


On 3/8/06, Satheesh Bandaram <satheesh@sourcery.org> wrote:
>
>
>
> Mamta Satoor wrote:
>
> > Hi,
> >
> > I ran following query in a 10.2 db
> > select alias,systemalias from sys.sysaliases;
> >
>
> I don't think systemalias column in sys.sysaliases is being used in
> Derby currently. It was probably used in Cloudscape product, from which
> Derby originally came from. All routines in system schemas have this
> flag set to 'false'. May be documentation should say this column is
> 'Reserved'?
>
> What information do you need?
>
> Satheesh
>
> > I was expecting to see true for systemalias column for Derby supplied
> > routines like SYSCS_COMPRESS_TABLE, SYSCS_EXPORT_TABLE etc. But all
> > the rows returned by the query above have systemalias set to false.
> > According to the docs on SYSALIASES table, the column systemalias will
> > be set to true for system supplief/built-in aliases. Is the doc
> > incorrect, system table incorrect or I am missing something?
> >
> > thanks,
> > Mamta
>
>
>

Mime
View raw message