db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "adam jvok" <ajv...@gmail.com>
Subject Re: Temp Tables: User can not perform the operation in schema 'SESSION'.
Date Thu, 06 Dec 2007 07:19:26 GMT
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