You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by Mamta Satoor <ms...@gmail.com> on 2006/02/21 20:19:23 UTC
Grant/Revoke subtask - EXTERNAL SECURITY DEFINER | EXTERNAL SECURITY INVOKER
Hi,
Satheesh has added the parser support for EXTERNAL SECURITY DEFINER |
EXTERNAL SECURITY INVOKER on a routine (function or procedure). eg from
lang/grantRevoke.sql test
CREATE PROCEDURE AUTH_TEST.addUserUtility(IN userName VARCHAR(50), IN
permission VARCHAR(22))
LANGUAGE JAVA PARAMETER STYLE JAVA
EXTERNAL SECURITY INVOKER
EXTERNAL NAME 'org.apache.derby.database.UserUtility.add';
But this information about INVOKER vs DEFINER doesn't get stored in any
system table. I am looking into finishing up this subtask to see what may be
required during compile, execute and upgrade times for this subtask.
Will send more information as I make more progress.
thanks,
Mamta
ps Will it be useful to create a subtask of Derby-464 to keep track of this
work?
Re: Grant/Revoke subtask - EXTERNAL SECURITY DEFINER | EXTERNAL SECURITY INVOKER
Posted by Mamta Satoor <ms...@gmail.com>.
That does sound cleaner. Thanks for your suggestion.
Mamta
On 2/24/06, Satheesh Bandaram <sa...@sourcery.org> wrote:
>
> Sounds right!... Minor comment, may be this should be changed:
>
> if (readMoreFromDisk == 1)
>
> to
>
> if (readMoreFromDisk *> 0*)
>
> to support future versions. This approach does seem easier and "cleaner"
> in this context.
>
> Satheesh
>
> Mamta Satoor wrote:
>
> Actually, I think I figured it out.
>
> A pre-10.2 db, in it's on disk AliasInfo format, will have an unused value
> which is set to 0.
>
> 10.2 release Derby will check if the on disk AliasInfo's unused value is
> set to 0(which means it is dealing with pre-10.2 db) and if yes, then it
> will default the external security info to true meaning use invoker's
> privileges.
>
> A 10.2 db's on disk AliasInfo will have the unused value set to 1. This
> will be an indication that we are dealing with a 10.2 database and we
> should go ahead and read the external security information from the disk
> which could be true/false.
>
> Here is how I think the changed readExternal and writeExternal should look
> like in 10.2 catalog.types.RoutineAliasInfo
>
> 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();
> int readMoreFromDisk = in.readInt(); //This will be 0 from pre-10.2 db
>
> 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)
> //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
> executeUsingPermissionsOfRoutineInvoker = true;
>
> in.readInt(); //future expansion for post 10.2 release
>
> }
>
> /**
> * Write this object to a stream of stored objects.
> *
> * @param out write bytes here.
> *
> * @exception IOException thrown on error
> */
> public void writeExternal( ObjectOutput out )
> throws IOException
> {
> super.writeExternal(out);
> out.writeObject(specificName);
> out.writeInt(dynamicResultSets);
> out.writeInt(parameterCount);
> out.writeShort(parameterStyle);
> out.writeShort (sqlAllowed);
> out.writeObject(returnType);
> out.writeBoolean(calledOnNullInput);
> out.writeInt(1); // 1 means we are dealing with 10.2 on disk format
> if (parameterCount != 0) {
> ArrayUtil.writeArrayItems (out, parameterNames);
> ArrayUtil.writeArrayItems(out, parameterTypes);
> ArrayUtil.writeIntArray(out, parameterModes);
> }
> out.writeBoolean(executeUsingPermissionsOfRoutineInvoker);
> out.writeInt(0); //future expansion for post 10.2 releases
> }
>
>
>
> On 2/24/06, Mamta Satoor <ms...@gmail.com> wrote:
> >
> > Hi,
> >
> > Dan, could you expand a little more on
> > "The RoutineAliasInfo has the correct structure to support expansion. It
> > writes out an unused value, set at 0. This could be bumped to indicate
> > that more data needs to be read, e.g. 1 means the external security info
> >
> > flag is written to disk etc. etc"?
> >
> > What I am looking for is what happens during upgrade if I add another
> > field to RoutineAliasInfo(since this change will become part of SYSALIASES
> > AliasInfo field)? I am not sure if it is as easy as just changing the
> > RoutineAliasInfo class and everything will work fine, especially during an
> > upgrade.
> >
> > Also, looking at Derby-337 (dblook doesn't generate SQL statements for
> > SQL functions.), it seems like Army had encountered some problems when he
> > had to add a filed to RoutineAliasInfo.
> >
> > Any thoughts on this will be helpful,
> > Mamta
> >
> >
> > On 2/22/06, Daniel John Debrunner <djd@apache.org > wrote:
> > >
> > > Satheesh Bandaram wrote:
> > >
> > > >
> > > > Mamta Satoor wrote:
> > > >
> > > >> Satheesh, I was looking through the code last night and saw
> > > following
> > > >> comment, about external security info flag, in CreateAliasNode.init
> > > >> line 195
> > > >> // GrantRevoke TODO: Figure out how to save external security
> > > >> info. Putting this in
> > > >> // RoutineAliasInfo may not be the best long term solution
> > > >> It seems like RoutineAliasInfo will be the logical place to keep
> > > this
> > > >> external security information, similar to the way we keep other
> > > >> information like called on null input, parameter count etc. Did you
> > >
> > > >> have reservations about this approach because we want to move away
> > > >> from using objects in the system tables (which in this case is
> > > >> AliasInfo in SYSALIASES table)?
> > > >
> > > > Here are some of my concerns about adding another field to
> > > > RoutineAliasInfo.
> > > >
> > > > 1. It would become harder to extract this info from
> > > RoutineAliasInfo
> > > > as it is a Java object for any metadata processing... like in
> > > > dblook or for other GUI tools. We would have to document how
> > > > RoutineAliasInfo gets generated as a character type and
> > > maintain
> > > > that format in the future.
> > > > 2. Have to support existing RoutineAliasInfo instances created in
> > > > existing databases. You would have to introduce a new
> > > > RoutineAliasInfo version or add a new mapping to another java
> > > object.
> > >
> > > The RoutineAliasInfo has the correct structure to support expansion.
> > > It
> > > writes out an unused value, set at 0. This could be bumped to indicate
> > >
> > > that more data needs to be read, e.g. 1 means the external security
> > > info
> > > flag is written to disk etc. etc.
> > >
> > > Dan.
> > >
> > >
> > >
> >
>
Re: Grant/Revoke subtask - EXTERNAL SECURITY DEFINER | EXTERNAL SECURITY
INVOKER
Posted by Satheesh Bandaram <sa...@Sourcery.Org>.
Mamta, the patch I submitted also implements many parts of routine
privileges... I have another follow up patch, that is running derbyAll
right now, that should implement remaining parts of routine privileges.
I hope to checkin that tomorrow, if all tests pass. This would implement
EXTERNAL SECURITY INVOKER model.
As I understand it, you are working on DEFINER model of execution. You
wouldn't be implementing routine privileges themself. Once a routine is
authorized to execute, your patch will determine if the routine would
use permission scheme of the creator of the routine or the invoker of
the routine. Let me know if this makes sense and sounds correct.
Satheesh
Mamta Satoor wrote:
> Satheesh, I noticed that you made a big checkin this morning for
> grant/revoke and the checkin comment said that you included some code
> for routine privileges. Can you please provide more information on
> your work on routine privileges? In the mean time, I will look at your
> code changes to see how it can be used for my work on EXTERNAL
> SECURITY DEFINER/INVOKER.
>
> thanks,
> Mamta
Re: Grant/Revoke subtask - EXTERNAL SECURITY DEFINER | EXTERNAL SECURITY INVOKER
Posted by Mamta Satoor <ms...@gmail.com>.
Satheesh, I noticed that you made a big checkin this morning for
grant/revoke and the checkin comment said that you included some code for
routine privileges. Can you please provide more information on your work on
routine privileges? In the mean time, I will look at your code changes to
see how it can be used for my work on EXTERNAL SECURITY DEFINER/INVOKER.
thanks,
Mamta
Re: Grant/Revoke subtask - EXTERNAL SECURITY DEFINER | EXTERNAL SECURITY INVOKER
Posted by Mamta Satoor <ms...@gmail.com>.
Actually, thinking more about it, I think there should be checks for
specific numbers, ie 0 for pre-10.2, 1 for 10.2, 2 for some future release
which requires more fields in AliasInfo and so on and so forth. That way, we
can keep using the same "unused value" to indiciate different versions of on
disk AliasInfo formats.
if (readMoreFromDisk == 0) //means pre-10.2
if (readMoreFromDisk == 1) //means 10.2
In future
if (readMoreFromDisk == 2) //future release X that changes the AliasInfo
structure
if (readMoreFromDisk == 3) //future release X+n that changes the AliasInfo
structure
Mamta
On 2/24/06, Satheesh Bandaram <sa...@sourcery.org> wrote:
>
> Sounds right!... Minor comment, may be this should be changed:
>
> if (readMoreFromDisk == 1)
>
> to
>
> if (readMoreFromDisk *> 0*)
>
> to support future versions. This approach does seem easier and "cleaner"
> in this context.
>
> Satheesh
>
> Mamta Satoor wrote:
>
> Actually, I think I figured it out.
>
> A pre-10.2 db, in it's on disk AliasInfo format, will have an unused value
> which is set to 0.
>
> 10.2 release Derby will check if the on disk AliasInfo's unused value is
> set to 0(which means it is dealing with pre-10.2 db) and if yes, then it
> will default the external security info to true meaning use invoker's
> privileges.
>
> A 10.2 db's on disk AliasInfo will have the unused value set to 1. This
> will be an indication that we are dealing with a 10.2 database and we
> should go ahead and read the external security information from the disk
> which could be true/false.
>
> Here is how I think the changed readExternal and writeExternal should look
> like in 10.2 catalog.types.RoutineAliasInfo
>
> 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();
> int readMoreFromDisk = in.readInt(); //This will be 0 from pre-10.2 db
>
> 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)
> //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
> executeUsingPermissionsOfRoutineInvoker = true;
>
> in.readInt(); //future expansion for post 10.2 release
>
> }
>
> /**
> * Write this object to a stream of stored objects.
> *
> * @param out write bytes here.
> *
> * @exception IOException thrown on error
> */
> public void writeExternal( ObjectOutput out )
> throws IOException
> {
> super.writeExternal(out);
> out.writeObject(specificName);
> out.writeInt(dynamicResultSets);
> out.writeInt(parameterCount);
> out.writeShort(parameterStyle);
> out.writeShort (sqlAllowed);
> out.writeObject(returnType);
> out.writeBoolean(calledOnNullInput);
> out.writeInt(1); // 1 means we are dealing with 10.2 on disk format
> if (parameterCount != 0) {
> ArrayUtil.writeArrayItems (out, parameterNames);
> ArrayUtil.writeArrayItems(out, parameterTypes);
> ArrayUtil.writeIntArray(out, parameterModes);
> }
> out.writeBoolean(executeUsingPermissionsOfRoutineInvoker);
> out.writeInt(0); //future expansion for post 10.2 releases
> }
>
>
>
> On 2/24/06, Mamta Satoor <ms...@gmail.com> wrote:
> >
> > Hi,
> >
> > Dan, could you expand a little more on
> > "The RoutineAliasInfo has the correct structure to support expansion. It
> > writes out an unused value, set at 0. This could be bumped to indicate
> > that more data needs to be read, e.g. 1 means the external security info
> >
> > flag is written to disk etc. etc"?
> >
> > What I am looking for is what happens during upgrade if I add another
> > field to RoutineAliasInfo(since this change will become part of SYSALIASES
> > AliasInfo field)? I am not sure if it is as easy as just changing the
> > RoutineAliasInfo class and everything will work fine, especially during an
> > upgrade.
> >
> > Also, looking at Derby-337 (dblook doesn't generate SQL statements for
> > SQL functions.), it seems like Army had encountered some problems when he
> > had to add a filed to RoutineAliasInfo.
> >
> > Any thoughts on this will be helpful,
> > Mamta
> >
> >
> > On 2/22/06, Daniel John Debrunner <djd@apache.org > wrote:
> > >
> > > Satheesh Bandaram wrote:
> > >
> > > >
> > > > Mamta Satoor wrote:
> > > >
> > > >> Satheesh, I was looking through the code last night and saw
> > > following
> > > >> comment, about external security info flag, in CreateAliasNode.init
> > > >> line 195
> > > >> // GrantRevoke TODO: Figure out how to save external security
> > > >> info. Putting this in
> > > >> // RoutineAliasInfo may not be the best long term solution
> > > >> It seems like RoutineAliasInfo will be the logical place to keep
> > > this
> > > >> external security information, similar to the way we keep other
> > > >> information like called on null input, parameter count etc. Did you
> > >
> > > >> have reservations about this approach because we want to move away
> > > >> from using objects in the system tables (which in this case is
> > > >> AliasInfo in SYSALIASES table)?
> > > >
> > > > Here are some of my concerns about adding another field to
> > > > RoutineAliasInfo.
> > > >
> > > > 1. It would become harder to extract this info from
> > > RoutineAliasInfo
> > > > as it is a Java object for any metadata processing... like in
> > > > dblook or for other GUI tools. We would have to document how
> > > > RoutineAliasInfo gets generated as a character type and
> > > maintain
> > > > that format in the future.
> > > > 2. Have to support existing RoutineAliasInfo instances created in
> > > > existing databases. You would have to introduce a new
> > > > RoutineAliasInfo version or add a new mapping to another java
> > > object.
> > >
> > > The RoutineAliasInfo has the correct structure to support expansion.
> > > It
> > > writes out an unused value, set at 0. This could be bumped to indicate
> > >
> > > that more data needs to be read, e.g. 1 means the external security
> > > info
> > > flag is written to disk etc. etc.
> > >
> > > Dan.
> > >
> > >
> > >
> >
>
Re: Grant/Revoke subtask - EXTERNAL SECURITY DEFINER | EXTERNAL SECURITY INVOKER
Posted by Mamta Satoor <ms...@gmail.com>.
Actually, I think I figured it out.
A pre-10.2 db, in it's on disk AliasInfo format, will have an unused value
which is set to 0.
10.2 release Derby will check if the on disk AliasInfo's unused value is set
to 0(which means it is dealing with pre-10.2 db) and if yes, then it will
default the external security info to true meaning use invoker's privileges.
A 10.2 db's on disk AliasInfo will have the unused value set to 1. This will
be an indication that we are dealing with a 10.2 database and we should go
ahead and read the external security information from the disk which could
be true/false.
Here is how I think the changed readExternal and writeExternal should look
like in 10.2 catalog.types.RoutineAliasInfo
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();
int readMoreFromDisk = in.readInt(); //This will be 0 from pre-10.2 db
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)
//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
executeUsingPermissionsOfRoutineInvoker = true;
in.readInt(); //future expansion for post 10.2 release
}
/**
* Write this object to a stream of stored objects.
*
* @param out write bytes here.
*
* @exception IOException thrown on error
*/
public void writeExternal( ObjectOutput out )
throws IOException
{
super.writeExternal(out);
out.writeObject(specificName);
out.writeInt(dynamicResultSets);
out.writeInt(parameterCount);
out.writeShort(parameterStyle);
out.writeShort(sqlAllowed);
out.writeObject(returnType);
out.writeBoolean(calledOnNullInput);
out.writeInt(1); // 1 means we are dealing with 10.2 on disk format
if (parameterCount != 0) {
ArrayUtil.writeArrayItems(out, parameterNames);
ArrayUtil.writeArrayItems(out, parameterTypes);
ArrayUtil.writeIntArray(out, parameterModes);
}
out.writeBoolean(executeUsingPermissionsOfRoutineInvoker);
out.writeInt(0); //future expansion for post 10.2 releases
}
On 2/24/06, Mamta Satoor <ms...@gmail.com> wrote:
>
> Hi,
>
> Dan, could you expand a little more on
> "The RoutineAliasInfo has the correct structure to support expansion. It
> writes out an unused value, set at 0. This could be bumped to indicate
> that more data needs to be read, e.g. 1 means the external security info
> flag is written to disk etc. etc"?
>
> What I am looking for is what happens during upgrade if I add another
> field to RoutineAliasInfo(since this change will become part of SYSALIASES
> AliasInfo field)? I am not sure if it is as easy as just changing the
> RoutineAliasInfo class and everything will work fine, especially during an
> upgrade.
>
> Also, looking at Derby-337 (dblook doesn't generate SQL statements for SQL
> functions.), it seems like Army had encountered some problems when he had to
> add a filed to RoutineAliasInfo.
>
> Any thoughts on this will be helpful,
> Mamta
>
>
> On 2/22/06, Daniel John Debrunner <djd@apache.org > wrote:
> >
> > Satheesh Bandaram wrote:
> >
> > >
> > > Mamta Satoor wrote:
> > >
> > >> Satheesh, I was looking through the code last night and saw following
> >
> > >> comment, about external security info flag, in CreateAliasNode.init
> > >> line 195
> > >> // GrantRevoke TODO: Figure out how to save external security
> > >> info. Putting this in
> > >> // RoutineAliasInfo may not be the best long term solution
> > >> It seems like RoutineAliasInfo will be the logical place to keep this
> > >> external security information, similar to the way we keep other
> > >> information like called on null input, parameter count etc. Did you
> > >> have reservations about this approach because we want to move away
> > >> from using objects in the system tables (which in this case is
> > >> AliasInfo in SYSALIASES table)?
> > >
> > > Here are some of my concerns about adding another field to
> > > RoutineAliasInfo.
> > >
> > > 1. It would become harder to extract this info from
> > RoutineAliasInfo
> > > as it is a Java object for any metadata processing... like in
> > > dblook or for other GUI tools. We would have to document how
> > > RoutineAliasInfo gets generated as a character type and maintain
> > > that format in the future.
> > > 2. Have to support existing RoutineAliasInfo instances created in
> > > existing databases. You would have to introduce a new
> > > RoutineAliasInfo version or add a new mapping to another java
> > object.
> >
> > The RoutineAliasInfo has the correct structure to support expansion. It
> > writes out an unused value, set at 0. This could be bumped to indicate
> > that more data needs to be read, e.g. 1 means the external security info
> > flag is written to disk etc. etc.
> >
> > Dan.
> >
> >
> >
>
Re: Grant/Revoke subtask - EXTERNAL SECURITY DEFINER | EXTERNAL SECURITY INVOKER
Posted by Mamta Satoor <ms...@gmail.com>.
Hi,
Dan, could you expand a little more on
"The RoutineAliasInfo has the correct structure to support expansion. It
writes out an unused value, set at 0. This could be bumped to indicate
that more data needs to be read, e.g. 1 means the external security info
flag is written to disk etc. etc"?
What I am looking for is what happens during upgrade if I add another field
to RoutineAliasInfo(since this change will become part of SYSALIASES
AliasInfo field)? I am not sure if it is as easy as just changing the
RoutineAliasInfo class and everything will work fine, especially during an
upgrade.
Also, looking at Derby-337 (dblook doesn't generate SQL statements for SQL
functions.), it seems like Army had encountered some problems when he had to
add a filed to RoutineAliasInfo.
Any thoughts on this will be helpful,
Mamta
On 2/22/06, Daniel John Debrunner <dj...@apache.org> wrote:
>
> Satheesh Bandaram wrote:
>
> >
> > Mamta Satoor wrote:
> >
> >> Satheesh, I was looking through the code last night and saw following
> >> comment, about external security info flag, in CreateAliasNode.init
> >> line 195
> >> // GrantRevoke TODO: Figure out how to save external security
> >> info. Putting this in
> >> // RoutineAliasInfo may not be the best long term solution
> >> It seems like RoutineAliasInfo will be the logical place to keep this
> >> external security information, similar to the way we keep other
> >> information like called on null input, parameter count etc. Did you
> >> have reservations about this approach because we want to move away
> >> from using objects in the system tables (which in this case is
> >> AliasInfo in SYSALIASES table)?
> >
> > Here are some of my concerns about adding another field to
> > RoutineAliasInfo.
> >
> > 1. It would become harder to extract this info from RoutineAliasInfo
> > as it is a Java object for any metadata processing... like in
> > dblook or for other GUI tools. We would have to document how
> > RoutineAliasInfo gets generated as a character type and maintain
> > that format in the future.
> > 2. Have to support existing RoutineAliasInfo instances created in
> > existing databases. You would have to introduce a new
> > RoutineAliasInfo version or add a new mapping to another java
> object.
>
> The RoutineAliasInfo has the correct structure to support expansion. It
> writes out an unused value, set at 0. This could be bumped to indicate
> that more data needs to be read, e.g. 1 means the external security info
> flag is written to disk etc. etc.
>
> Dan.
>
>
>
Re: Grant/Revoke subtask - EXTERNAL SECURITY DEFINER | EXTERNAL SECURITY INVOKER
Posted by Mamta Satoor <ms...@gmail.com>.
Thinking further about database upgrade for EXTERNAL SECURITY DEFINER |
EXTERNAL SECURITY INVOKER information, I think when a pre-10.2 Derby
database is upgraded, the SYSALIASES table should initialize the new column
for external security info with "true". This "true" for external
security means that the routine should be executed with the invoker's
privileges. This will be more restrictive than granting the execute
privileges of the routine definer. Does that sound right? Once we decide on
this, the functional spec for grant/revoke should be updated with this
information.
Also, I am thinking of using "EXECUTE_USING_INVOKER_PRIVILEGES" as column
name for new column in SYSALIASES for external security info. Any other
suggestion?
thanks,
Mamta
On 2/22/06, Mamta Satoor <ms...@gmail.com> wrote:
>
> Thanks for sharing your thoughts on this, Satheesh. I will spend more time
> on it to see if external security info can be added as another column to the
> SYSALIASES table rather than as another filed to RoutineAliasInfo.
>
> Mamta
>
> On 2/22/06, Daniel John Debrunner <dj...@apache.org> wrote:
> >
> > Satheesh Bandaram wrote:
> >
> > >
> > > Mamta Satoor wrote:
> > >
> > >> Satheesh, I was looking through the code last night and saw following
> >
> > >> comment, about external security info flag, in CreateAliasNode.init
> > >> line 195
> > >> // GrantRevoke TODO: Figure out how to save external security
> > >> info. Putting this in
> > >> // RoutineAliasInfo may not be the best long term solution
> > >> It seems like RoutineAliasInfo will be the logical place to keep this
> > >> external security information, similar to the way we keep other
> > >> information like called on null input, parameter count etc. Did you
> > >> have reservations about this approach because we want to move away
> > >> from using objects in the system tables (which in this case is
> > >> AliasInfo in SYSALIASES table)?
> > >
> > > Here are some of my concerns about adding another field to
> > > RoutineAliasInfo.
> > >
> > > 1. It would become harder to extract this info from
> > RoutineAliasInfo
> > > as it is a Java object for any metadata processing... like in
> > > dblook or for other GUI tools. We would have to document how
> > > RoutineAliasInfo gets generated as a character type and maintain
> > > that format in the future.
> > > 2. Have to support existing RoutineAliasInfo instances created in
> > > existing databases. You would have to introduce a new
> > > RoutineAliasInfo version or add a new mapping to another java
> > object.
> >
> > The RoutineAliasInfo has the correct structure to support expansion. It
> > writes out an unused value, set at 0. This could be bumped to indicate
> > that more data needs to be read, e.g. 1 means the external security info
> > flag is written to disk etc. etc.
> >
> > Dan.
> >
> >
> >
>
Re: Grant/Revoke subtask - EXTERNAL SECURITY DEFINER | EXTERNAL SECURITY INVOKER
Posted by Mamta Satoor <ms...@gmail.com>.
Based on pros and cons listed by Dan, it seems like it would be better to
continue with the existing paradigm of RoutineAliasInfo for the new column.
So, I will go ahead and make changes in my codeline to put invoker security
info in RoutineAliasInfo.
thanks,
Mamta
> On 2/23/06, Daniel John Debrunner <dj...@apache.org> wrote:
> >
> > Mamta Satoor wrote:
> >
> > > I chose to have a new column in SYSALISES rather than expanding
> > > RoutineAliasInfo becasue, like Satheesh brought it up in his following
> >
> > > comment below, metadata extraction won't be straightforward if it was
> > > part of RoutineAliasInfo.
> > > "It would become harder to extract this info from RoutineAliasInfo as
> > it
> > > is a Java object for any metadata processing... like in dblook or for
> > > other GUI tools. We would have to document how RoutineAliasInfo gets
> > > generated as a character type and maintain that format in the future."
> >
> > But that's the case today. The toString() output is used by dblook. Is
> > there any specific requirement for the invoker security to be separate
> > from the rest of the information stored in RoutineAliasInfo, when
> > accessed through SQL?
> >
> > Seems like this to me:
> >
> > new column
> > upgrade code
> > dblook additional code
> >
> > RoutineAliasInfo
> > no upgrade change
> > no dblook change
> > change self contained in RoutineAliasInfo
> >
> > I was planning at some point to add some other routine attributes to
> > RoutineAliasInfo, just trying to understand why a change in existing
> > paradigm is happening.
> >
> > Thanks,
> > Dan.
> >
> >
> >
>
Re: Grant/Revoke subtask - EXTERNAL SECURITY DEFINER | EXTERNAL SECURITY INVOKER
Posted by Mamta Satoor <ms...@gmail.com>.
No, there is no specific requirement for invoker security to be different.
The reason I brought up the issue of what would be a good place to store
this is, I remember seeing on dev list that metadata extraction is not
straight forward with objects in system tables. And then I also noticed the
comment in the code CreateAliasNode.init line 195
// GrantRevoke TODO: Figure out how to save external security info.
Putting this in
// RoutineAliasInfo may not be the best long term solution
And so I wanted to get a feedback to see what would be an ideal place to
keep invoker security information.
thanks,
Mamta
On 2/23/06, Daniel John Debrunner <dj...@apache.org> wrote:
>
> Mamta Satoor wrote:
>
> > I chose to have a new column in SYSALISES rather than expanding
> > RoutineAliasInfo becasue, like Satheesh brought it up in his following
> > comment below, metadata extraction won't be straightforward if it was
> > part of RoutineAliasInfo.
> > "It would become harder to extract this info from RoutineAliasInfo as it
> > is a Java object for any metadata processing... like in dblook or for
> > other GUI tools. We would have to document how RoutineAliasInfo gets
> > generated as a character type and maintain that format in the future."
>
> But that's the case today. The toString() output is used by dblook. Is
> there any specific requirement for the invoker security to be separate
> from the rest of the information stored in RoutineAliasInfo, when
> accessed through SQL?
>
> Seems like this to me:
>
> new column
> upgrade code
> dblook additional code
>
> RoutineAliasInfo
> no upgrade change
> no dblook change
> change self contained in RoutineAliasInfo
>
> I was planning at some point to add some other routine attributes to
> RoutineAliasInfo, just trying to understand why a change in existing
> paradigm is happening.
>
> Thanks,
> Dan.
>
>
>
Re: Grant/Revoke subtask - EXTERNAL SECURITY DEFINER | EXTERNAL SECURITY
INVOKER
Posted by Daniel John Debrunner <dj...@apache.org>.
Mamta Satoor wrote:
> I chose to have a new column in SYSALISES rather than expanding
> RoutineAliasInfo becasue, like Satheesh brought it up in his following
> comment below, metadata extraction won't be straightforward if it was
> part of RoutineAliasInfo.
> "It would become harder to extract this info from RoutineAliasInfo as it
> is a Java object for any metadata processing... like in dblook or for
> other GUI tools. We would have to document how RoutineAliasInfo gets
> generated as a character type and maintain that format in the future."
But that's the case today. The toString() output is used by dblook. Is
there any specific requirement for the invoker security to be separate
from the rest of the information stored in RoutineAliasInfo, when
accessed through SQL?
Seems like this to me:
new column
upgrade code
dblook additional code
RoutineAliasInfo
no upgrade change
no dblook change
change self contained in RoutineAliasInfo
I was planning at some point to add some other routine attributes to
RoutineAliasInfo, just trying to understand why a change in existing
paradigm is happening.
Thanks,
Dan.
Re: Grant/Revoke subtask - EXTERNAL SECURITY DEFINER | EXTERNAL SECURITY INVOKER
Posted by Mamta Satoor <ms...@gmail.com>.
I chose to have a new column in SYSALISES rather than expanding
RoutineAliasInfo becasue, like Satheesh brought it up in his following
comment below, metadata extraction won't be straightforward if it was part
of RoutineAliasInfo.
"It would become harder to extract this info from RoutineAliasInfo as it is
a Java object for any metadata processing... like in dblook or for other GUI
tools. We would have to document how RoutineAliasInfo gets generated as a
character type and maintain that format in the future."
Mamta
On 2/23/06, Daniel John Debrunner <dj...@apache.org> wrote:
>
> Mamta Satoor wrote:
>
> > Thanks for sharing your thoughts on this, Satheesh. I will spend more
> > time on it to see if external security info can be added as another
> > column to the SYSALIASES table rather than as another filed to
> > RoutineAliasInfo.
>
> Just curious as to why the new column approach was choosen, over the
> existing RoutineAliasInfo column?
>
> Dan.
>
>
>
>
Re: Grant/Revoke subtask - EXTERNAL SECURITY DEFINER | EXTERNAL SECURITY
INVOKER
Posted by Daniel John Debrunner <dj...@apache.org>.
Mamta Satoor wrote:
> Thanks for sharing your thoughts on this, Satheesh. I will spend more
> time on it to see if external security info can be added as another
> column to the SYSALIASES table rather than as another filed to
> RoutineAliasInfo.
Just curious as to why the new column approach was choosen, over the
existing RoutineAliasInfo column?
Dan.
Re: Grant/Revoke subtask - EXTERNAL SECURITY DEFINER | EXTERNAL SECURITY INVOKER
Posted by Mamta Satoor <ms...@gmail.com>.
Thanks for sharing your thoughts on this, Satheesh. I will spend more time
on it to see if external security info can be added as another column to the
SYSALIASES table rather than as another filed to RoutineAliasInfo.
Mamta
On 2/22/06, Daniel John Debrunner <dj...@apache.org> wrote:
>
> Satheesh Bandaram wrote:
>
> >
> > Mamta Satoor wrote:
> >
> >> Satheesh, I was looking through the code last night and saw following
> >> comment, about external security info flag, in CreateAliasNode.init
> >> line 195
> >> // GrantRevoke TODO: Figure out how to save external security
> >> info. Putting this in
> >> // RoutineAliasInfo may not be the best long term solution
> >> It seems like RoutineAliasInfo will be the logical place to keep this
> >> external security information, similar to the way we keep other
> >> information like called on null input, parameter count etc. Did you
> >> have reservations about this approach because we want to move away
> >> from using objects in the system tables (which in this case is
> >> AliasInfo in SYSALIASES table)?
> >
> > Here are some of my concerns about adding another field to
> > RoutineAliasInfo.
> >
> > 1. It would become harder to extract this info from RoutineAliasInfo
> > as it is a Java object for any metadata processing... like in
> > dblook or for other GUI tools. We would have to document how
> > RoutineAliasInfo gets generated as a character type and maintain
> > that format in the future.
> > 2. Have to support existing RoutineAliasInfo instances created in
> > existing databases. You would have to introduce a new
> > RoutineAliasInfo version or add a new mapping to another java
> object.
>
> The RoutineAliasInfo has the correct structure to support expansion. It
> writes out an unused value, set at 0. This could be bumped to indicate
> that more data needs to be read, e.g. 1 means the external security info
> flag is written to disk etc. etc.
>
> Dan.
>
>
>
Re: Grant/Revoke subtask - EXTERNAL SECURITY DEFINER | EXTERNAL SECURITY
INVOKER
Posted by Daniel John Debrunner <dj...@apache.org>.
Satheesh Bandaram wrote:
>
> Mamta Satoor wrote:
>
>> Satheesh, I was looking through the code last night and saw following
>> comment, about external security info flag, in CreateAliasNode.init
>> line 195
>> // GrantRevoke TODO: Figure out how to save external security
>> info. Putting this in
>> // RoutineAliasInfo may not be the best long term solution
>> It seems like RoutineAliasInfo will be the logical place to keep this
>> external security information, similar to the way we keep other
>> information like called on null input, parameter count etc. Did you
>> have reservations about this approach because we want to move away
>> from using objects in the system tables (which in this case is
>> AliasInfo in SYSALIASES table)?
>
> Here are some of my concerns about adding another field to
> RoutineAliasInfo.
>
> 1. It would become harder to extract this info from RoutineAliasInfo
> as it is a Java object for any metadata processing... like in
> dblook or for other GUI tools. We would have to document how
> RoutineAliasInfo gets generated as a character type and maintain
> that format in the future.
> 2. Have to support existing RoutineAliasInfo instances created in
> existing databases. You would have to introduce a new
> RoutineAliasInfo version or add a new mapping to another java object.
The RoutineAliasInfo has the correct structure to support expansion. It
writes out an unused value, set at 0. This could be bumped to indicate
that more data needs to be read, e.g. 1 means the external security info
flag is written to disk etc. etc.
Dan.
Re: Grant/Revoke subtask - EXTERNAL SECURITY DEFINER | EXTERNAL SECURITY INVOKER
Posted by Mamta Satoor <ms...@gmail.com>.
Entered Derby-1023.
Satheesh, I was looking through the code last night and saw following
comment, about external security info flag, in CreateAliasNode.init line 195
// GrantRevoke TODO: Figure out how to save external security info.
Putting this in
// RoutineAliasInfo may not be the best long term solution
It seems like RoutineAliasInfo will be the logical place to keep this
external security information, similar to the way we keep other information
like called on null input, parameter count etc. Did you have reservations
about this approach because we want to move away from using objects in the
system tables (which in this case is AliasInfo in SYSALIASES table)?
thanks,
Mamta
On 2/21/06, Satheesh Bandaram <sa...@sourcery.org> wrote:
>
> Thanks for picking this up. A sub-task under DERBY-464 sounds good.
>
> Satheesh
>
> Mamta Satoor wrote:
>
> > Hi,
> >
> > Satheesh has added the parser support for EXTERNAL SECURITY DEFINER |
> > EXTERNAL SECURITY INVOKER on a routine (function or procedure). eg
> > from lang/grantRevoke.sql test
> > CREATE PROCEDURE AUTH_TEST.addUserUtility(IN userName VARCHAR(50), IN
> > permission VARCHAR(22))
> > LANGUAGE JAVA PARAMETER STYLE JAVA
> > EXTERNAL SECURITY INVOKER
> > EXTERNAL NAME 'org.apache.derby.database.UserUtility.add ';
> >
> > But this information about INVOKER vs DEFINER doesn't get stored in
> > any system table. I am looking into finishing up this subtask to see
> > what may be required during compile, execute and upgrade times for
> > this subtask. Will send more information as I make more progress.
> >
> > thanks,
> > Mamta
> >
> > ps Will it be useful to create a subtask of Derby-464 to keep track of
> > this work?
>
>
>
Re: Grant/Revoke subtask - EXTERNAL SECURITY DEFINER | EXTERNAL SECURITY
INVOKER
Posted by Satheesh Bandaram <sa...@Sourcery.Org>.
Thanks for picking this up. A sub-task under DERBY-464 sounds good.
Satheesh
Mamta Satoor wrote:
> Hi,
>
> Satheesh has added the parser support for EXTERNAL SECURITY DEFINER |
> EXTERNAL SECURITY INVOKER on a routine (function or procedure). eg
> from lang/grantRevoke.sql test
> CREATE PROCEDURE AUTH_TEST.addUserUtility(IN userName VARCHAR(50), IN
> permission VARCHAR(22))
> LANGUAGE JAVA PARAMETER STYLE JAVA
> EXTERNAL SECURITY INVOKER
> EXTERNAL NAME 'org.apache.derby.database.UserUtility.add ';
>
> But this information about INVOKER vs DEFINER doesn't get stored in
> any system table. I am looking into finishing up this subtask to see
> what may be required during compile, execute and upgrade times for
> this subtask. Will send more information as I make more progress.
>
> thanks,
> Mamta
>
> ps Will it be useful to create a subtask of Derby-464 to keep track of
> this work?