db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta Satoor" <msat...@gmail.com>
Subject Re: Temp Tables: User can not perform the operation in schema 'SESSION'.
Date Thu, 06 Dec 2007 07:34:31 GMT
That's a very good question. It would seem that users other than the
one who created the database should be able to create temporary tables
in SESSION schema. I think you should go ahead and file a jira for
this issue.

Mamta
On 12/5/07, adam jvok <ajvok1@gmail.com> wrote:
> That makes sense to me. Thanks.
>
> It helps me understand the problem, but does not solve it.
>
> How can userb create a temp table in a database that was created by usera?
>
> Say usera  creates the db and many other users want to run a query on
> that db that relies upon them being able to create a temp table. How
> can it be done?
>
> Thanks for your help.
>
>
> On 12/6/07, Mamta Satoor <msatoor@gmail.com> wrote:
> > Thanks for providing a reproducible case so quickly.
> >
> > The reason 'USERB' can't create global temp tables is because SESSION
> > schema is owned by 'USERA' (SESSION schema is where global temporary
> > tables reside). You will see the same behavior for any other schemas
> > owned by USERA, ie, USERB will not be able to create objects in any
> > schemas owned by USERA.
> >
> > When you say that there is no other problems with USERB ie you can do
> > create table t1, that is because when you connect as USERB, your
> > current schema is USERB and not USERA and since USERB owns USERB
> > schema there are no errors raised when you issue create table t1. But
> > if USERB were to set it's current schema as USERA, you will noticed
> > that same create table t1 will fail now because USERB does not own
> > schema USERA. Let me show following ij session to demonstrate the
> > behavior.
> >
> > Start the network server and then ij
> > ij> connect
> > 'jdbc:derby://localhost:1527/c:/dellater/netdb;user=usera;password=pwd;create=true';
> > ij> values current schema;
> > 1
> > --------------------------------------------------------------------------------
> > USERA
> > 1 row selected
> > -----notice that table t below has been created in schema USERA
> > ij> create table t(c11 int);
> > 0 rows inserted/updated/deleted
> > ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.user.userb','pwd');
> > Statement executed.
> > ij> call SYSCS_UTIL.SYSCS_SET_USER_ACCESS('userb','FULLACCESS');
> > Statement executed.
> > ij> disconnect;
> > ij> connect
> > 'jdbc:derby://localhost:1527/c:/dellater/netdb;user=userb;password=pwd;create=true';
> > -----notice current schema is USERB and not USERA
> > ij> values current schema;
> > 1
> > --------------------------------------------------------------------------------
> > USERB
> > 1 row selected
> > -----notice that table t1 below is getting created in current schema
> > which is USERB and
> > -----which is owned by user USERB
> > ij> create table t1(c11 int);
> > 0 rows inserted/updated/deleted
> > ----switch to schema not owned by USERB
> > ij> set schema usera;
> > 0 rows inserted/updated/deleted
> > ----attempt to create objects in USERA schema will fail because USERB
> > does not own it
> > ij> create table t2(c11 int);
> > ERROR 42507: User 'USERB' can not perform the operation in schema 'USERA'.
> > java.sql.SQLException: User 'USERB' can not perform the operation in schema
> > 'USE
> > RA'.
> >
> > I hope this helps,
> > Mamta
> >
> > On 12/5/07, adam jvok <ajvok1@gmail.com> wrote:
> > > I have a derby.properties like this:
> > >
> > > derby.connection.requireAuthentication=true
> > > derby.authentication.provider=BUILTIN
> > > derby.database.sqlAuthorization=TRUE
> > > derby.user.usera=pwd
> > > derby.fullAccessUsers=usera
> > > derby.drda.host=192.168.1.50
> > >
> > > I start the network server and run up 'ij'.
> > > ij>connect
> > 'jdbc:derby://192.168.1.50:1527/TEST1;user=usera;password=pwd;create=true;';
> > > ij> declare global temporary table t11(a int) on commit preserve rows
> > > not logged;
> > > 0 rows inserted/updated/deleted
> > >
> > > All good so far.
> > >
> > > Now try this (while still connected as usera):
> > > ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.user.userb','pwd');
> > > Statement executed.
> > > ij>  call SYSCS_UTIL.SYSCS_SET_USER_ACCESS('userb','FULLACCESS');
> > > Statement executed.
> > > ij> disconnect;
> > > ij> connect
> > 'jdbc:derby://192.168.1.50:1527/TEST1;user=userb;password=pwd;';
> > > ij> declare global temporary table t1(a int) on commit preserve rows not
> > logged;
> > > ERROR 42507: User 'USERB' can not perform the operation in schema
> > 'SESSION'.
> > >
> > > There is no other problem with userb, e.g. I can do:
> > > ij> create table t1(a int);
> > > 0 rows inserted/updated/deleted
> > >
> > > It just doesn't like userb creating temp tables???
> > >
> > >
> > > On 12/6/07, Mamta Satoor <msatoor@gmail.com> wrote:
> > > > Adam, can you provide simple ij script or sequence of sql statements
> > > > to demonstrate the problem. That will make it easier for people to see
> > > > exactly what is going on.
> > > >
> > > > Mamta
> > > >
> > > > On 12/5/07, adam jvok <ajvok1@gmail.com> wrote:
> > > > > I can create a temp table with no problem like this:
> > > > >
> > > > >   declare global temporary table t1(a int) on commit preserve rows
not
> > > > logged;
> > > > >
> > > > > ONLY if I am connected using the username that created the db.
> > > > >
> > > > > Attempting to create a temp table as any other user results in:
> > > > >
> > > > >   User 'whatever' can not perform the operation in schema 'SESSION'.
> > > > >
> > > > > Is it possible to overcome this?
> > > > >
> > > >
> > >
> >
>

Mime
View raw message