[jira] Commented: (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'."

["John H. Embretsen (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DERBY-3266?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12549986 ] 

John H. Embretsen commented on DERBY-3266:
------------------------------------------

It's important to note that this is only an issue when sqlAuthorization is enabled in Derby (this is not clear from this issue's Summary or Description).

It seems to me that this is an area that was overlooked when the SQL Authorization (GRANT/REVOKE) feature was implemented. The functional spec attached to DERBY-464 says:

"The owner of the database may create any schema with any owner (authorization). Other users may only create schemas whose name is the same as their user name and that are owned by the user. For example, if user angelica does not own the database then she can only create a schema with name and authorization angelica."

It seems that operations in the SESSION schema is a special case which should probably be handled a bit differently.

> 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
>    Affects Versions: 10.3.1.4
>         Environment: Linux 2.6.17-5mdv
>            Reporter: adam jvok
>
> 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.