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 06:58:53 GMT
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