You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Dag H. Wanvik (JIRA)" <ji...@apache.org> on 2008/10/07 03:01:47 UTC
[jira] Updated: (DERBY-3266) Not possible for non-db-owner to
create a temporary table. "Get ERROR 42507: User 'USERB' can not perform
the operation in schema 'SESSION'."
[ https://issues.apache.org/jira/browse/DERBY-3266?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik updated DERBY-3266:
---------------------------------
Attachment: derby-3266-1.stat
derby-3266-1.diff
Uploading an experimental patch that seems to solve the issue,
by omitting the privilege checks iff the table is a temporary table (but they
still apply for persistent tables in schema SESSION, if any).
Ij run after applying it (dag is owner, knut is ordinary user):
java -Dij.exceptionTrace=true org.apache.derby.tools.ij
ij version 10.5
ij> connect 'jdbc:derby:wombat;user=knut;password=anders';
ij> select * from session.t;
ERROR 42502: User 'KNUT' does not have SELECT permission on column 'I' of table 'SESSION'.'T'.
ij> declare global temporary table t ( i int) on commit preserve rows not logged;
0 rows inserted/updated/deleted
ij> insert into session.t values 1,2,3;
3 rows inserted/updated/deleted
ij> select * from session.t;
I
-----------
1
2
3
3 rows selected
ij> connect 'jdbc:derby:wombat;user=dag;password=wanvik';
ij(CONNECTION1)> select * from session.t;
I |J
-----------------------
0 |0
1 |1
2 rows selected
> Not possible for non-db-owner to create a temporary table. "Get ERROR 42507: User 'USERB' can not perform the operation in schema 'SESSION'."
> ---------------------------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-3266
> URL: https://issues.apache.org/jira/browse/DERBY-3266
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.3.1.4
> Environment: Linux 2.6.17-5mdv
> Reporter: adam jvok
> Attachments: derby-3266-1.diff, derby-3266-1.stat
>
>
> It seems that only the owner of a database may create tempoary tables in that db.
> This is not helpful as many other users may want to run a query on that db that relies upon the creation of temporary tables.
> I would expect non-db-owners to be able create temporay tables.
> The problem is demonstrated with:
> 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
> 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'.
> SYSINFO:
> ========
> ------------------ Java Information ------------------
> Java Version: 1.6.0_02-ea
> Java Vendor: Sun Microsystems Inc.
> Java home: /usr/java/jdk1.6.0_02/jre
> Java classpath: /home/ajvok/derby/db-derby-10.3.1.4-bin/lib/derby.jar:/home/ajvok/derby/db-derby-10.3.1.4-bin/lib/derbynet.jar:/home/ajvok/derby/db-derby-
> 10.3.1.4-bin/lib/derbytools.jar:/home/ajvok/derby/db-derby-10.3.1.4-bin/lib/derbyclient.jar:/home/ajvok/derby/local/sp1.jar
> OS name: Linux
> OS architecture: i386
> OS version: 2.6.17-5mdv
> Java user name: ajvok
> Java user home: /home/ajvok
> Java user dir: /home/ajvok/derby/local
> java.specification.name: Java Platform API Specification
> java.specification.version: 1.6
> --------- Derby Information --------
> JRE - JDBC: Java SE 6 - JDBC 4.0
> [/home/ajvok/derby/db-derby-10.3.1.4-bin/lib/derby.jar] 10.3.1.4 - (561794)
> [/home/ajvok/derby/db-derby-10.3.1.4-bin/lib/derbytools.jar] 10.3.1.4 - (561794)
> [/home/ajvok/derby/db-derby-10.3.1.4-bin/lib/derbynet.jar] 10.3.1.4 - (561794)
> [/home/ajvok/derby/db-derby-10.3.1.4-bin/lib/derbyclient.jar] 10.3.1.4 - (561794)
> ------------------------------------------------------
> ----------------- Locale Information -----------------
> Current Locale : [English/United Kingdom [en_GB]]
> Found support for locale: [cs]
> version: 10.3.1.4 - (561794)
> Found support for locale: [de_DE]
> version: 10.3.1.4 - (561794)
> Found support for locale: [es]
> version: 10.3.1.4 - (561794)
> Found support for locale: [fr]
> version: 10.3.1.4 - (561794)
> Found support for locale: [hu]
> version: 10.3.1.4 - (561794)
> Found support for locale: [it]
> version: 10.3.1.4 - (561794)
> Found support for locale: [ja_JP]
> version: 10.3.1.4 - (561794)
> Found support for locale: [ko_KR]
> version: 10.3.1.4 - (561794)
> Found support for locale: [pl]
> version: 10.3.1.4 - (561794)
> Found support for locale: [pt_BR]
> version: 10.3.1.4 - (561794)
> Found support for locale: [ru]
> version: 10.3.1.4 - (561794)
> Found support for locale: [zh_CN]
> version: 10.3.1.4 - (561794)
> Found support for locale: [zh_TW]
> version: 10.3.1.4 - (561794)
> ------------------------------------------------------
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.