Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (athena.apache.org: domain of ajvok1@gmail.com designates
 209.85.198.184 as permitted sender)
DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=gamma;
        h=message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
        b=JY22rFR6/SQAi09UDfsnj1GO2pSXwi0xIZEihooBULf5AJGkJH0uc1llMb2lu+QLCECS2Ul6uPyYe+0QWxjMdmzqimKk5LGP5jdurjWaIbrU12FJBN6X9YDbh+Nk63J+jItodCNtdngQne6ecQYdu969Q9fFzKwnLUi50z9ZwWg=
Message-ID: <a466d5390712052319n5ac2fe7aq47df6b9119c60f83@mail.gmail.com>
Date: Thu, 6 Dec 2007 15:19:26 +0800
From: "adam jvok" <ajvok1@gmail.com>
To: "Derby Discussion" <derby-user@db.apache.org>
Subject: Re: Temp Tables: User can not perform the operation in schema
 'SESSION'.
In-Reply-To: <d9619e4a0712052258r7ef7e0b0y387d30d9e9a7c3c5@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <a466d5390712052039m76a85885ydc90d5a1f154f504@mail.gmail.com>
	 <d9619e4a0712052141q3a4dd94cs13a8671a0c6d4c98@mail.gmail.com>
	 <a466d5390712052212t1e3e4792jf575a349a5ec1a6a@mail.gmail.com>
	 <d9619e4a0712052258r7ef7e0b0y387d30d9e9a7c3c5@mail.gmail.com>

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?
> > > >
> > >
> >
>