Re: SYSTEMALIAS flag in SYSALIASES table

[Mamta Satoor]

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 <[EMAIL PROTECTED]> 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