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 Fri, 07 Dec 2007 02:07:29 GMT
I don't think its a file permission issue.

Neither usera (who created the db) nor userb (who tries to create the
temp table in the db) are 'real' users on my (linux) system.
All files are owned by the same linux user.
Even if I do something nasty like 'chmod -R 777' (i.e. give everyone
all access), the result is the same (userb can not create the temp
table).

On 12/7/07, Craig L Russell <Craig.Russell@sun.com> wrote:
> Is it possible that this is a file system permission issue?
>
> Can both the owner of the database and the user have the same group
> permissions to create files in the database directory?
>
> Craig
>
> On Dec 5, 2007, at 11:34 PM, Mamta Satoor wrote:
>
> > 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?
> >>>>>>
> >>>>>
> >>>>
> >>>
> >>
>
> Craig Russell
> Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
> 408 276-5638 mailto:Craig.Russell@sun.com
> P.S. A good JDO? O, Gasp!
>
>

Mime
View raw message