Re: Temp Tables: User can not perform the operation in schema 'SESSION'.

[Craig L Russell]

Is it possible that this is a file system permission issue?



Craig

On Dec 5, 2007, at 11:34 PM, Mamta Satoor wrote:

Can both the owner of the database and the user have the same group permissions to create files in the database directory?

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 <[EMAIL PROTECTED]> wrote:

That makes sense to me. Thanks.

It helps me understand the problem, but does not solve it.



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 <[EMAIL PROTECTED]> wrote:

How can userb create a temp table in a database that was created by usera?

Thanks for providing a reproducible case so quickly.


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.


create table t1, that is because when you connect as USERB, your
current schema is USERB and not USERA and since USERB owns USERB

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

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

Statement executed.
ij> call SYSCS_UTIL.SYSCS_SET_USER_ACCESS('userb','FULLACCESS');
Statement executed.
ij> disconnect;
ij> connect

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

does not own it
ij> create table t2(c11 int);

'USE
RA'.

I hope this helps,
Mamta

On 12/5/07, adam jvok <[EMAIL PROTECTED]> wrote:

The reason 'USERB' can't create global temp tables is because SESSION When you say that there is no other problems with USERB ie you can do schema there are no errors raised when you issue create table t1. But 'jdbc:derby://localhost:1527/c:/dellater/ netdb;user=usera;password=pwd;create=true'; -------------------------------------------------------------------- ------------ ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY ('derby.user.userb','pwd'); 'jdbc:derby://localhost:1527/c:/dellater/ netdb;user=userb;password=pwd;create=true'; -------------------------------------------------------------------- ------------ ----attempt to create objects in USERA schema will fail because USERB ERROR 42507: User 'USERB' can not perform the operation in schema 'USERA'. java.sql.SQLException: User 'USERB' can not perform the operation in schema

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):

Statement executed.
ij>  call SYSCS_UTIL.SYSCS_SET_USER_ACCESS('userb','FULLACCESS');
Statement executed.
ij> disconnect;
ij> connect

ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY ('derby.user.userb','pwd');

'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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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:



Is it possible to overcome this?

User 'whatever' can not perform the operation in schema 'SESSION'.

Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:[EMAIL PROTECTED]
P.S. A good JDO? O, Gasp!

smime.p7s
Description: S/MIME cryptographic signature