[jira] Commented: (DERBY-1738) An user is able to grant select privilege on a view but the underlying object is not own by the user.
[ http://issues.apache.org/jira/browse/DERBY-1738?page=comments#action_12429488 ] Satheesh Bandaram commented on DERBY-1738: -- This is a duplicate of the issue Rajesh has already filed. I think that is DERBY-1686. An user is able to grant select privilege on a view but the underlying object is not own by the user. - Key: DERBY-1738 URL: http://issues.apache.org/jira/browse/DERBY-1738 Project: Derby Issue Type: Bug Components: SQL Affects Versions: 10.2.1.0 Environment: Sun JDK 1.4.2 Reporter: Yip Ng An user is able to grant select privilege on a view but the underlying object is not own by the user. The grant statement should fail since the user does not have privilege to grant. i.e.: ij version 10.3 ij connect 'jdbc:derby:wombat;create=true' user 'user1' as user1; WARNING 01J01: Database 'wombat' not created, connection made to existing database instead. WARNING 01J14: SQL authorization is being used without first enabling authentication. ij create table t1 (i int); ERROR X0Y32: Table/View 'T1' already exists in Schema 'USER1'. ij insert into t1 values 1,2,3; 3 rows inserted/updated/deleted ij grant select on t1 to user2; 0 rows inserted/updated/deleted ij connect 'jdbc:derby:wombat' user 'user2' as user2; WARNING 01J14: SQL authorization is being used without first enabling authentication. ij(USER2) -- ok create view v1 as select * from user1.t1; ERROR X0Y32: Table/View 'V1' already exists in Schema 'USER2'. ij(USER2) -- attempt to grant this view to others, should fail since user2 -- does not have grant privilege on object user1.t1 grant select on user1.t1 to user3; ERROR 2850C: User 'USER2' is not the owner of Table/View 'USER1'.'T1'. ij(USER2) -- expect error grant select on v1 to user3; 0 rows inserted/updated/deleted ij(USER2) -- Java Information -- Java Version:1.4.2_12 Java Vendor: Sun Microsystems Inc. Java home: C:\Program Files\Java\j2re1.4.2_12 Java classpath: classes;. OS name: Windows XP OS architecture: x86 OS version: 5.1 Java user name: Yip Java user home: C:\Documents and Settings\Yip Java user dir: C:\work3\derby\trunk java.specification.name: Java Platform API Specification java.specification.version: 1.4 - Derby Information JRE - JDBC: J2SE 1.4.2 - JDBC 3.0 [C:\work3\derby\trunk\classes] 10.3.0.0 alpha - (1) -- - Locale Information - Current Locale : [English/United States [en_US]] Found support for locale: [de_DE] version: 10.3.0.0 alpha - (1) Found support for locale: [es] version: 10.3.0.0 alpha - (1) Found support for locale: [fr] version: 10.3.0.0 alpha - (1) Found support for locale: [it] version: 10.3.0.0 alpha - (1) Found support for locale: [ja_JP] version: 10.3.0.0 alpha - (1) Found support for locale: [ko_KR] version: 10.3.0.0 alpha - (1) Found support for locale: [pt_BR] version: 10.3.0.0 alpha - (1) Found support for locale: [zh_CN] version: 10.3.0.0 alpha - (1) Found support for locale: [zh_TW] version: 10.3.0.0 alpha - (1) -- -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1651) Develop a mechanism to migrate mySQL databases to Derby. Migration tool should include both schema and data migration options.
[ http://issues.apache.org/jira/browse/DERBY-1651?page=comments#action_12429249 ] Satheesh Bandaram commented on DERBY-1651: -- Some general comments: 1) I think a design document to explain what each class does and how they interact with each other is required. Other option is to create a package.html file and document design/code in place. I think the second option is better. At the mininum, each class needs a detailed javadoc at the beginning and for each major method. Important and useful contribution like this may not get accepted if not documented properly. 2) Package location needs to be changed. All tools are in org.apache.derby.tools package. Best to follow existing tools like IJ, DBLOOK model so it would be easy to use and to enable derbyrun option for this tool later. 3) Looks like the code uses generics, which limit the use to JDK 1.5 platforms. While this may be ok, need to document need for JDK 1.5 platform and raise appropriate message if used in earlier platforms. Most of derby can still run on JDK 1.3 platforms, though not required for new indepedent modules. 4) There are many areas that need improvements in the code... like exception handling, importing only classes that are needed not the whole package, deleting code that is in comments etc. I understand the code is still in development. I would like to suggest you focus on completing current functionality very well, document what you have done, move code and integrate it at appropriate location and test current functionality before adding more. Better to submit partial code that is well written than submit more code that is not very usable. Develop a mechanism to migrate mySQL databases to Derby. Migration tool should include both schema and data migration options. -- Key: DERBY-1651 URL: http://issues.apache.org/jira/browse/DERBY-1651 Project: Derby Issue Type: New Feature Components: Tools Environment: All platforms Reporter: Ramin Moazeni Assigned To: Ramin Moazeni Fix For: 10.2.1.0 Attachments: DBMigration.diff, MigrationTool-MySQLtoDerby.zip Develop a mechanism to migration databases created by other database engines to Derby. While my current interest is to migrate mySQL databases to Derby, the tool could be developed in a way to extend this mechanism to allow migration from other database engines in the future. More details of proposed functionality and implementation strategy can be found at: http://wiki.apache.org/db-derby/MysqlDerbyMigration The plan is to develop and submit patches incrementally. First patch supports migration of tables and views from mySQL database to Derby. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Created: (DERBY-1733) Redesign dynamic subquery materialization optimization to be based on cost analysis during compile/optimization phases.
Redesign dynamic subquery materialization optimization to be based on cost analysis during compile/optimization phases. --- Key: DERBY-1733 URL: http://issues.apache.org/jira/browse/DERBY-1733 Project: Derby Issue Type: Improvement Affects Versions: 10.1.3.1, 10.1.3.0, 10.1.2.1, 10.1.1.0, 10.0.2.1, 10.0.2.0 Environment: All platforms Reporter: Satheesh Bandaram Separating this issue from DERBY-634. DERBY-634 addressed a specific bug (that causes stack overflow for some queries with subqueries) in dynamic subquery materialization optimization. I am filing this IMPROVEMENT request to redesign the optimization to be cost based decision during compile/optimization phases of query compilation, instead of being a runtime decision. Much more info present in DERBY-634, which includes Jeff Lichman's analysis. Also note DERBY-781 added subquery materialization framework that is more generic approach that works for SELECT subqueries. A similar mechanism is needed for WHERE clause subqueries as well. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Resolved: (DERBY-634) Subquery materialization can cause stack overflow
[ http://issues.apache.org/jira/browse/DERBY-634?page=all ] Satheesh Bandaram resolved DERBY-634. - Fix Version/s: 10.3.0.0 (was: 10.2.1.0) Resolution: Fixed I would like this fix backported to 10.2 release as soon as possible. Have a customer that is waiting for this fix for a long time. Subquery materialization can cause stack overflow - Key: DERBY-634 URL: http://issues.apache.org/jira/browse/DERBY-634 Project: Derby Issue Type: Bug Components: SQL Affects Versions: 10.1.1.1 Reporter: Jeff Lichtman Fix For: 10.3.0.0 Attachments: DERBY-634.diff A performance optimization in subquery processing can cause a stack overflow. The optimization materializes a subquery ResultSet in memory where it thinks the rows will fit in memory. The materialization is done as a set of nested unions of constant rows (UnionResultSets and RowResultSets). If there are a lot of rows this can cause a stack overflow when fetching a row. The obvious fix is to make it use an iterative technique rather than a recursive one for storing and returning the rows. See the method BaseActivation.materializeResultSetIfPossible() in the language execution code. There are some other issues with this performance optimization that should be looked at: 1) The optimization can backfire, making the query run much slower. For example, in the query: select * from one_row_table where column1 not in (select column2 from million_row_table) reading million_row_table into memory is an expensive operation. If there is an index on million_row_table.column2, the query should return a result very quickly despite the large size of million_row_table by doing a single probe into million_row_table via the index. Since in-memory materialization can be an expensive operation, the decision about whether to do it should be made based on query optimizer cost estimates. See SubqueryNode.generateExpression(). 2) It may not be wise to cache partial query results in memory at all. Although this can help performance in some cases, it also chews up memory. This is different from a limited-size cache with a backing store (like what the store uses for page caching). The language has no way to limit the total amount of memory used in this type of processing. Note that hash joins originally used in-memory hash tables with no backing store, and that a backing store was added later. 3) The implementation of this optimization has some problems. The decision to materialize the subquery results in memory is made during code generation - all such decisions should be made during the compilation phase. It's not clear to me why materializeResultSetIfPossible() is in BaseActivation - I would expect the of materialization to be done by a type of ResultSet, not by a method in BaseActivation. Also, this method calls getMaxMemoryPerTable() in the OptimizerFactory - nothing in the execution code should refer to anything in the compilation code (perhaps getMaxMemoryPerTable() should be moved somewhere else). -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Closed: (DERBY-634) Subquery materialization can cause stack overflow
[ http://issues.apache.org/jira/browse/DERBY-634?page=all ] Satheesh Bandaram closed DERBY-634. --- Assignee: Satheesh Bandaram I am closing this issue as I am not doing any further work. But I would appreciate if someone can port this fix to 10.2 beta and then reopen this task to mark as such. Subquery materialization can cause stack overflow - Key: DERBY-634 URL: http://issues.apache.org/jira/browse/DERBY-634 Project: Derby Issue Type: Bug Components: SQL Affects Versions: 10.1.1.1 Reporter: Jeff Lichtman Assigned To: Satheesh Bandaram Fix For: 10.3.0.0 Attachments: DERBY-634.diff A performance optimization in subquery processing can cause a stack overflow. The optimization materializes a subquery ResultSet in memory where it thinks the rows will fit in memory. The materialization is done as a set of nested unions of constant rows (UnionResultSets and RowResultSets). If there are a lot of rows this can cause a stack overflow when fetching a row. The obvious fix is to make it use an iterative technique rather than a recursive one for storing and returning the rows. See the method BaseActivation.materializeResultSetIfPossible() in the language execution code. There are some other issues with this performance optimization that should be looked at: 1) The optimization can backfire, making the query run much slower. For example, in the query: select * from one_row_table where column1 not in (select column2 from million_row_table) reading million_row_table into memory is an expensive operation. If there is an index on million_row_table.column2, the query should return a result very quickly despite the large size of million_row_table by doing a single probe into million_row_table via the index. Since in-memory materialization can be an expensive operation, the decision about whether to do it should be made based on query optimizer cost estimates. See SubqueryNode.generateExpression(). 2) It may not be wise to cache partial query results in memory at all. Although this can help performance in some cases, it also chews up memory. This is different from a limited-size cache with a backing store (like what the store uses for page caching). The language has no way to limit the total amount of memory used in this type of processing. Note that hash joins originally used in-memory hash tables with no backing store, and that a backing store was added later. 3) The implementation of this optimization has some problems. The decision to materialize the subquery results in memory is made during code generation - all such decisions should be made during the compilation phase. It's not clear to me why materializeResultSetIfPossible() is in BaseActivation - I would expect the of materialization to be done by a type of ResultSet, not by a method in BaseActivation. Also, this method calls getMaxMemoryPerTable() in the OptimizerFactory - nothing in the execution code should refer to anything in the compilation code (perhaps getMaxMemoryPerTable() should be moved somewhere else). -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Resolved: (DERBY-1538) Unexpected behavior on self privilege revocation
[ http://issues.apache.org/jira/browse/DERBY-1538?page=all ] Satheesh Bandaram resolved DERBY-1538. -- Fix Version/s: 10.3.0.0 Resolution: Fixed If anyone has itch or interest to port this to 10.2, I will leave it up to them. This fix raises an error for both GRANT or REVOKE operations to or from object owners. Database owners also can't GRANT or REVOKE privileges to object owners. Unexpected behavior on self privilege revocation Key: DERBY-1538 URL: http://issues.apache.org/jira/browse/DERBY-1538 Project: Derby Issue Type: Bug Components: SQL Affects Versions: 10.2.1.0 Environment: Windows XP Pro Reporter: Yip Ng Assigned To: Satheesh Bandaram Fix For: 10.3.0.0 When revoking the owner's own privilege against the table he have created, Derby executes the revocation successfully but the owner is able to select from the table later as if though the REVOKE statement has no effect. More importantly, I was expecting a SQLException with the appropriate SQLSTATE to be thrown when the owner attempts to revoke privilege from himself. i.e.: ij connect 'jdbc:derby:authtest' user 'yip' as conn1; ij create table t1 (c1 int); 0 rows inserted/updated/deleted ij insert into t1 values 1,2,3; 3 rows inserted/updated/deleted ij revoke select on t1 from yip; 0 rows inserted/updated/deleted ij select * from t1; C1 --- 1 2 3 3 rows selected Here is the sysinfo: -- Java Information -- Java Version:1.4.2_12 Java Vendor: Sun Microsystems Inc. Java home: C:\jdk142\jre Java classpath: derby.jar;derbytools.jar;. OS name: Windows XP OS architecture: x86 OS version: 5.1 Java user name: yip Java user home: C:\Documents and Settings\Administrator Java user dir: C:\derby\trunk\jars\sane java.specification.name: Java Platform API Specification java.specification.version: 1.4 - Derby Information JRE - JDBC: J2SE 1.4.2 - JDBC 3.0 [C:\derby\trunk\jars\sane\derby.jar] 10.2.0.4 alpha - (423353) [C:\derby\trunk\jars\sane\derbytools.jar] 10.2.0.4 alpha - (423353) -- - Locale Information - Current Locale : [English/United States [en_US]] Found support for locale: [de_DE] version: 10.2.0.4 alpha - (423353) Found support for locale: [es] version: 10.2.0.4 alpha - (423353) Found support for locale: [fr] version: 10.2.0.4 alpha - (423353) Found support for locale: [it] version: 10.2.0.4 alpha - (423353) Found support for locale: [ja_JP] version: 10.2.0.4 alpha - (423353) Found support for locale: [ko_KR] version: 10.2.0.4 alpha - (423353) Found support for locale: [pt_BR] version: 10.2.0.4 alpha - (423353) Found support for locale: [zh_CN] version: 10.2.0.4 alpha - (423353) Found support for locale: [zh_TW] version: 10.2.0.4 alpha - (423353) -- -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Closed: (DERBY-1538) Unexpected behavior on self privilege revocation
[ http://issues.apache.org/jira/browse/DERBY-1538?page=all ] Satheesh Bandaram closed DERBY-1538. Closing this issue... If this fix is ported to 10.2, reopen to mark as fixed in 10.2. Unexpected behavior on self privilege revocation Key: DERBY-1538 URL: http://issues.apache.org/jira/browse/DERBY-1538 Project: Derby Issue Type: Bug Components: SQL Affects Versions: 10.2.1.0 Environment: Windows XP Pro Reporter: Yip Ng Assigned To: Satheesh Bandaram Fix For: 10.3.0.0 When revoking the owner's own privilege against the table he have created, Derby executes the revocation successfully but the owner is able to select from the table later as if though the REVOKE statement has no effect. More importantly, I was expecting a SQLException with the appropriate SQLSTATE to be thrown when the owner attempts to revoke privilege from himself. i.e.: ij connect 'jdbc:derby:authtest' user 'yip' as conn1; ij create table t1 (c1 int); 0 rows inserted/updated/deleted ij insert into t1 values 1,2,3; 3 rows inserted/updated/deleted ij revoke select on t1 from yip; 0 rows inserted/updated/deleted ij select * from t1; C1 --- 1 2 3 3 rows selected Here is the sysinfo: -- Java Information -- Java Version:1.4.2_12 Java Vendor: Sun Microsystems Inc. Java home: C:\jdk142\jre Java classpath: derby.jar;derbytools.jar;. OS name: Windows XP OS architecture: x86 OS version: 5.1 Java user name: yip Java user home: C:\Documents and Settings\Administrator Java user dir: C:\derby\trunk\jars\sane java.specification.name: Java Platform API Specification java.specification.version: 1.4 - Derby Information JRE - JDBC: J2SE 1.4.2 - JDBC 3.0 [C:\derby\trunk\jars\sane\derby.jar] 10.2.0.4 alpha - (423353) [C:\derby\trunk\jars\sane\derbytools.jar] 10.2.0.4 alpha - (423353) -- - Locale Information - Current Locale : [English/United States [en_US]] Found support for locale: [de_DE] version: 10.2.0.4 alpha - (423353) Found support for locale: [es] version: 10.2.0.4 alpha - (423353) Found support for locale: [fr] version: 10.2.0.4 alpha - (423353) Found support for locale: [it] version: 10.2.0.4 alpha - (423353) Found support for locale: [ja_JP] version: 10.2.0.4 alpha - (423353) Found support for locale: [ko_KR] version: 10.2.0.4 alpha - (423353) Found support for locale: [pt_BR] version: 10.2.0.4 alpha - (423353) Found support for locale: [zh_CN] version: 10.2.0.4 alpha - (423353) Found support for locale: [zh_TW] version: 10.2.0.4 alpha - (423353) -- -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1723) Database owner revokes select privilege from a schema owner but owner is still able to select
[ http://issues.apache.org/jira/browse/DERBY-1723?page=comments#action_12429153 ] Satheesh Bandaram commented on DERBY-1723: -- No one can revoke a privilege from object owner, including the owner herself or even the database owner. Deepa is right.. fixing DERBY-1538 will address this also. If anything thinks spec needs to be more clear, we can make it. Hard to imagine every combination for the spec. May be documentation to be added needs to cover all these cases. I would mark this as a DUPLICATE. Database owner revokes select privilege from a schema owner but owner is still able to select - Key: DERBY-1723 URL: http://issues.apache.org/jira/browse/DERBY-1723 Project: Derby Issue Type: Bug Components: SQL Affects Versions: 10.2.1.0 Environment: Sun JDK 1.4.2 Reporter: Yip Ng Database owner attempts to revoke select privilege from a schema owner's own table but the owner later can still select from the revoked table. Behavior is inconsistent. e.g.: ij version 10.2 ij connect 'jdbc:derby:wombat;create=true' user 'user1' as user1; WARNING 01J14: SQL authorization is being used without first enabling authentication. ij connect 'jdbc:derby:wombat' user 'user2' as user2; WARNING 01J14: SQL authorization is being used without first enabling authentication. ij(USER2) create table tshared0 (i int); 0 rows inserted/updated/deleted ij(USER2) -- db owner tries to revoke select access from schema owner user2 set connection user1; ij(USER1) revoke select on user2.tshared0 from user2; 0 rows inserted/updated/deleted ij(USER1) set connection user2; ij(USER2) select * from user2.tshared0; I --- 0 rows selected ij(USER2) sysinfo: -- Java Information -- Java Version:1.4.2_12 Java Vendor: Sun Microsystems Inc. Java home: C:\Program Files\Java\j2re1.4.2_12 Java classpath: derby.jar;derbytools.jar OS name: Windows XP OS architecture: x86 OS version: 5.1 Java user name: Yip Java user home: C:\Documents and Settings\Yip Java user dir: C:\work3\derby\tests\derby-10.2.1.0\lib java.specification.name: Java Platform API Specification java.specification.version: 1.4 - Derby Information JRE - JDBC: J2SE 1.4.2 - JDBC 3.0 [C:\work3\derby\tests\derby-10.2.1.0\lib\derby.jar] 10.2.1.0 beta - (430903) [C:\work3\derby\tests\derby-10.2.1.0\lib\derbytools.jar] 10.2.1.0 beta - (430903) -- - Locale Information - Current Locale : [English/United States [en_US]] Found support for locale: [de_DE] version: 10.2.1.0 - (430903) Found support for locale: [es] version: 10.2.1.0 - (430903) Found support for locale: [fr] version: 10.2.1.0 - (430903) Found support for locale: [it] version: 10.2.1.0 - (430903) Found support for locale: [ja_JP] version: 10.2.1.0 - (430903) Found support for locale: [ko_KR] version: 10.2.1.0 - (430903) Found support for locale: [pt_BR] version: 10.2.1.0 - (430903) Found support for locale: [zh_CN] version: 10.2.1.0 - (430903) Found support for locale: [zh_TW] version: 10.2.1.0 - (430903) -- -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1729) Invoking Java stored procedure that contains GRANT or REVOKE statement with CONTAINS SQL from a trigger should fail.
[ http://issues.apache.org/jira/browse/DERBY-1729?page=comments#action_12429169 ] Satheesh Bandaram commented on DERBY-1729: -- Hmm... I have to think about this some more, but not sure why the trigger should fail. Invoking Java stored procedure that contains GRANT or REVOKE statement with CONTAINS SQL from a trigger should fail. - Key: DERBY-1729 URL: http://issues.apache.org/jira/browse/DERBY-1729 Project: Derby Issue Type: Bug Components: SQL Affects Versions: 10.2.1.0 Environment: Sun JDK 1.4.2 Reporter: Yip Ng Attachments: repro-trunk-diff01.txt In Derby SQL authorization mode, invoking Java stored procedure that contains GRANT or REVOKE statement with CONTAINS SQL from a trigger should fail but in the following test, it successfully executed the trigger action. Attaching repro patch for trunk. i.e.: ij connect 'triggerProcSQLAuth;create=true' user 'APP' as app; WARNING 01J14: SQL authorization is being used without first enabling authentication. ij --- setup the environment --- table used in the procedures create table t1 (i int primary key, b char(15)); 0 rows inserted/updated/deleted ij insert into t1 values (1, 'XYZ'); 1 row inserted/updated/deleted ij insert into t1 values (2, 'XYZ'); 1 row inserted/updated/deleted ij --- table used in this test create table t2 (x integer, y integer); 0 rows inserted/updated/deleted ij create procedure grant_select_proc() parameter style java dynamic result sets 0 language java contains sql external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.grantSelect'; 0 rows inserted/updated/deleted ij create procedure revoke_select_proc() parameter style java dynamic result sets 0 language java contains sql external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.revokeSelect'; 0 rows inserted/updated/deleted ij --- tests create trigger grant_select_trig AFTER delete on t1 for each STATEMENT mode db2sql call grant_select_proc(); 0 rows inserted/updated/deleted ij --- should fail delete from t1 where i = 1; 1 row inserted/updated/deleted ij --- check delete failed select * from t1; I |B --- 2 |XYZ 1 row selected ij --- check if there are rows in sys.systableperms, should be 0 select count(*) from SYS.SYSTABLEPERMS; 1 --- 1 1 row selected ij drop trigger grant_select_trig; 0 rows inserted/updated/deleted ij create trigger revoke_select_trig AFTER delete on t1 for each STATEMENT mode db2sql call revoke_select_proc(); 0 rows inserted/updated/deleted ij --- should fail delete from t1 where i = 2; 1 row inserted/updated/deleted ij --- check delete failed select * from t1; I |B --- 0 rows selected ij --- check if there are rows in sys.systableperms, should be 0 select count(*) from SYS.SYSTABLEPERMS; 1 --- 0 1 row selected ij drop trigger revoke_select_trig; 0 rows inserted/updated/deleted ij -- Java Information -- Java Version:1.4.2_12 Java Vendor: Sun Microsystems Inc. Java home: C:\Program Files\Java\j2re1.4.2_12 Java classpath: derby.jar;derbytools.jar OS name: Windows XP OS architecture: x86 OS version: 5.1 Java user name: Yip Java user home: C:\Documents and Settings\Yip Java user dir: C:\work3\derby\trunk\jars\sane java.specification.name: Java Platform API Specification java.specification.version: 1.4 - Derby Information JRE - JDBC: J2SE 1.4.2 - JDBC 3.0 [C:\work3\derby\trunk\jars\sane\derby.jar] 10.3.0.0 alpha - (432670M) [C:\work3\derby\trunk\jars\sane\derbytools.jar] 10.3.0.0 alpha - (432670M) -- - Locale Information - Current Locale : [English/United States [en_US]] Found support for locale: [de_DE] version: 10.3.0.0 alpha - (432670M) Found support for locale: [es] version: 10.3.0.0 alpha - (432670M) Found support for locale: [fr] version: 10.3.0.0 alpha - (432670M) Found support for locale: [it] version: 10.3.0.0 alpha - (432670M) Found support for locale: [ja_JP] version: 10.3.0.0 alpha - (432670M) Found support for locale: [ko_KR] version: 10.3.0.0 alpha - (432670M) Found support for locale: [pt_BR] version: 10.3.0.0 alpha - (432670M) Found support for locale: [zh_CN] version: 10.3.0.0 alpha - (432670M) Found support for locale: [zh_TW] version
[jira] Commented: (DERBY-1729) Invoking Java stored procedure that contains GRANT or REVOKE statement with CONTAINS SQL from a trigger should fail.
[ http://issues.apache.org/jira/browse/DERBY-1729?page=comments#action_12429173 ] Satheesh Bandaram commented on DERBY-1729: -- Hmm... Correct. It should fail. Guess just invoking the procedure itself (outside a trigger) should fail. I was thinking about the link to trigger and GRANT statement. The problem is that procedure should always fail. Another missed negative test in the code. Invoking Java stored procedure that contains GRANT or REVOKE statement with CONTAINS SQL from a trigger should fail. - Key: DERBY-1729 URL: http://issues.apache.org/jira/browse/DERBY-1729 Project: Derby Issue Type: Bug Components: SQL Affects Versions: 10.2.1.0 Environment: Sun JDK 1.4.2 Reporter: Yip Ng Attachments: repro-trunk-diff01.txt In Derby SQL authorization mode, invoking Java stored procedure that contains GRANT or REVOKE statement with CONTAINS SQL from a trigger should fail but in the following test, it successfully executed the trigger action. Attaching repro patch for trunk. i.e.: ij connect 'triggerProcSQLAuth;create=true' user 'APP' as app; WARNING 01J14: SQL authorization is being used without first enabling authentication. ij --- setup the environment --- table used in the procedures create table t1 (i int primary key, b char(15)); 0 rows inserted/updated/deleted ij insert into t1 values (1, 'XYZ'); 1 row inserted/updated/deleted ij insert into t1 values (2, 'XYZ'); 1 row inserted/updated/deleted ij --- table used in this test create table t2 (x integer, y integer); 0 rows inserted/updated/deleted ij create procedure grant_select_proc() parameter style java dynamic result sets 0 language java contains sql external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.grantSelect'; 0 rows inserted/updated/deleted ij create procedure revoke_select_proc() parameter style java dynamic result sets 0 language java contains sql external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.revokeSelect'; 0 rows inserted/updated/deleted ij --- tests create trigger grant_select_trig AFTER delete on t1 for each STATEMENT mode db2sql call grant_select_proc(); 0 rows inserted/updated/deleted ij --- should fail delete from t1 where i = 1; 1 row inserted/updated/deleted ij --- check delete failed select * from t1; I |B --- 2 |XYZ 1 row selected ij --- check if there are rows in sys.systableperms, should be 0 select count(*) from SYS.SYSTABLEPERMS; 1 --- 1 1 row selected ij drop trigger grant_select_trig; 0 rows inserted/updated/deleted ij create trigger revoke_select_trig AFTER delete on t1 for each STATEMENT mode db2sql call revoke_select_proc(); 0 rows inserted/updated/deleted ij --- should fail delete from t1 where i = 2; 1 row inserted/updated/deleted ij --- check delete failed select * from t1; I |B --- 0 rows selected ij --- check if there are rows in sys.systableperms, should be 0 select count(*) from SYS.SYSTABLEPERMS; 1 --- 0 1 row selected ij drop trigger revoke_select_trig; 0 rows inserted/updated/deleted ij -- Java Information -- Java Version:1.4.2_12 Java Vendor: Sun Microsystems Inc. Java home: C:\Program Files\Java\j2re1.4.2_12 Java classpath: derby.jar;derbytools.jar OS name: Windows XP OS architecture: x86 OS version: 5.1 Java user name: Yip Java user home: C:\Documents and Settings\Yip Java user dir: C:\work3\derby\trunk\jars\sane java.specification.name: Java Platform API Specification java.specification.version: 1.4 - Derby Information JRE - JDBC: J2SE 1.4.2 - JDBC 3.0 [C:\work3\derby\trunk\jars\sane\derby.jar] 10.3.0.0 alpha - (432670M) [C:\work3\derby\trunk\jars\sane\derbytools.jar] 10.3.0.0 alpha - (432670M) -- - Locale Information - Current Locale : [English/United States [en_US]] Found support for locale: [de_DE] version: 10.3.0.0 alpha - (432670M) Found support for locale: [es] version: 10.3.0.0 alpha - (432670M) Found support for locale: [fr] version: 10.3.0.0 alpha - (432670M) Found support for locale: [it] version: 10.3.0.0 alpha - (432670M) Found support for locale: [ja_JP] version: 10.3.0.0 alpha - (432670M) Found support for locale: [ko_KR] version: 10.3.0.0 alpha - (432670M) Found support for locale: [pt_BR
Re: Moved out of working on Derby project...
Thanks David and Narayanan for kind words. I have a feeling I will miss Derby and Derby communities more than it will miss me! :-) Satheesh Narayanan wrote: :( Thank you for all the support and guidance you gave us. Will miss you. Hope to see u around. Narayanan David Van Couvering wrote: :( Good luck in your next role! Hope to see you around. David Satheesh Bandaram wrote: As some of you may have noticed, I have not been as active, on Apache Derby project. My work assignment has changed and my new role does not leave much time to continue working on Apache Derby. I was one of the original 5 committers of Derby as the project started and stayed very active till now. I have seen both developer and user communities grow significantly over last few years. It has been a very satisfying experience for me. Wish Apache Derby very best and will be watching success of this project and communities closely. If anyone needs to reach me, my personal email address is: [EMAIL PROTECTED] Satheesh
Moved out of working on Derby project...
As some of you may have noticed, I have not been as active, on Apache Derby project. My work assignment has changed and my new role does not leave much time to continue working on Apache Derby. I was one of the original 5 committers of Derby as the project started and stayed very active till now. I have seen both developer and user communities grow significantly over last few years. It has been a very satisfying experience for me. Wish Apache Derby very best and will be watching success of this project and communities closely. If anyone needs to reach me, my personal email address is: [EMAIL PROTECTED] Satheesh
[jira] Commented: (DERBY-634) Subquery materialization can cause stack overflow
[ http://issues.apache.org/jira/browse/DERBY-634?page=comments#action_12428436 ] Satheesh Bandaram commented on DERBY-634: - Kathey, here are some answers... 1) No... There was a specific bug in the original optimization that could cause stack overflow. That bug has been correctly fixed. There should be no cases of stack overflow now. 2) This is a comprehensive fix to the stack overflow problem. I was only saying generalizing the optimization that was implemented long time ago to be based on new subquery materialization framework would improve performance for more classes of queries. This would be an improvement request, like we already have several optimizer/preprocess improvements. Army and myself compiled about 10 known optimizations that could be implemented in Derby. You can say this would be another.. But this has nothing to do with stack overflow problem itself. If this is not clear, let me know. I can provide more info. Subquery materialization can cause stack overflow - Key: DERBY-634 URL: http://issues.apache.org/jira/browse/DERBY-634 Project: Derby Issue Type: Bug Components: SQL Affects Versions: 10.1.1.1 Reporter: Jeff Lichtman Fix For: 10.2.0.0 Attachments: DERBY-634.diff A performance optimization in subquery processing can cause a stack overflow. The optimization materializes a subquery ResultSet in memory where it thinks the rows will fit in memory. The materialization is done as a set of nested unions of constant rows (UnionResultSets and RowResultSets). If there are a lot of rows this can cause a stack overflow when fetching a row. The obvious fix is to make it use an iterative technique rather than a recursive one for storing and returning the rows. See the method BaseActivation.materializeResultSetIfPossible() in the language execution code. There are some other issues with this performance optimization that should be looked at: 1) The optimization can backfire, making the query run much slower. For example, in the query: select * from one_row_table where column1 not in (select column2 from million_row_table) reading million_row_table into memory is an expensive operation. If there is an index on million_row_table.column2, the query should return a result very quickly despite the large size of million_row_table by doing a single probe into million_row_table via the index. Since in-memory materialization can be an expensive operation, the decision about whether to do it should be made based on query optimizer cost estimates. See SubqueryNode.generateExpression(). 2) It may not be wise to cache partial query results in memory at all. Although this can help performance in some cases, it also chews up memory. This is different from a limited-size cache with a backing store (like what the store uses for page caching). The language has no way to limit the total amount of memory used in this type of processing. Note that hash joins originally used in-memory hash tables with no backing store, and that a backing store was added later. 3) The implementation of this optimization has some problems. The decision to materialize the subquery results in memory is made during code generation - all such decisions should be made during the compilation phase. It's not clear to me why materializeResultSetIfPossible() is in BaseActivation - I would expect the of materialization to be done by a type of ResultSet, not by a method in BaseActivation. Also, this method calls getMaxMemoryPerTable() in the OptimizerFactory - nothing in the execution code should refer to anything in the compilation code (perhaps getMaxMemoryPerTable() should be moved somewhere else). -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1057) documentation to address Grant/Revoke (Derby-464)
[ http://issues.apache.org/jira/browse/DERBY-1057?page=comments#action_12428170 ] Satheesh Bandaram commented on DERBY-1057: -- I think GRANT/REVOKE documentation to reference guide and developer guide can be committed, following several reviews by myself, mamta and Dan. I would also recommend second review after the commit, looking at the whole documentation, not just the changes, considering the scope of authorization impact. I will also be doing this second round review and invite others, like Francois, Mamta, Dan or anyone else. Would appreciate if someone can commit patches Derby1057_ref4.diff and Derby1057_devguide4.diff. documentation to address Grant/Revoke (Derby-464) - Key: DERBY-1057 URL: http://issues.apache.org/jira/browse/DERBY-1057 Project: Derby Issue Type: Sub-task Components: Documentation Affects Versions: 10.0.2.0 Reporter: Eric Radzinski Assigned To: Laura Stewart Fix For: 10.2.0.0 Attachments: derby1057_devguide.diff, derby1057_devguide3.diff, derby1057_devguide4.ddiff, derby1057_devguide_html.zip, derby1057_devguide_html3.zip, derby1057_devguide_html4.zip, derby1057_ref.diff, derby1057_ref3.diff, derby1057_ref4.diff, derby1057_ref_html.zip, derby1057_ref_html4.zip, derby1057_tuning3.diff, derby1057_tuning4.diff, derby1057_tuning_html.zip, derby1058_ref_html3.zip, devguide_html2.zip, ref_html2.zip, rtunpropersqlauth.html -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
Re: [jira] Commented: (DERBY-634) Subquery materialization can cause stack overflow
I committed this patch over the weekend on trunk. I would like this fix to be ported to 10.2 Beta as soon as possible, as I have a customer who is waiting for it. Anyone know how to get this ported to 10.2 Beta? Satheesh Satheesh Bandaram (JIRA) wrote: [ http://issues.apache.org/jira/browse/DERBY-634?page=comments#action_12427671 ] Satheesh Bandaram commented on DERBY-634: - Oops... my previous comment got little garbled. Here it is again. I propose to address this issue in phases. Here is my current thinking: Background information: --- A performance optimization was introduced in Cloudscape before open sourcing as Apache Derby. Before this optimization was introduced, a query like: Select a, b, c from Table1 where a 5 and b in (select x from Table2, Table3 where Table2.x=Table3.x and y 100) would take longer time time to execute than needed. This becomes worse as the complexity of the subquery increases. Basic problem was that for every qualified value of 'b', the subquery was getting executed, recreating the results multiple times. Cloudscape, at that time, had the ability to materialize results of a subquery only if a single row is returned from the subquery. (where subquery is of the form select max(i) from Table2) A performance optimization was introduced that allowed for some small number of rows greater than 1 to be cached dynamically at runtime. As the subquery was executed first time, results of the subquery were cached until MAX_MEMORY_PER_TABLE is reached, which was 1MG by default. If the results of the subquery could be fit into memory less than this configurable size, a nested union resultset would be built to cache the results. Future invocations of the subquery would simply return results from this subquery cache without actually evaluating subquery. This resulted in performance boost for a customer query from 10 minutes to a few seconds. Side effect of this optimization: - While the optimization worked well for the customer query, it is causing issues for the query in question here. If the subquery were returning just an integer, the optimization could attempt to cache 1MG/4, about 250,000 rows in nested union resultsets. Nesting of this deep would cause stack overflow. Jeff Lichman also identified several other issues mentioned in the description of this entry. Proposed Fix: - First, I think it is important to fix regression caused by this optimization. The optimization was intended to cache small number of subquery results. Instead of caching single row result of a subquery, this optimization could be adjusted to cache a small number of results. Second, caching results of subquery in nested union resultsets is not needed. This can be rewritten to generate a linear resultset, which would save runtime stack growth. Third, as Jeff pointed out, a global subquery optimization that is performed during optimization is the best approach. He pointed out subquery materialization based on hash joins decided during optimization phase is the ideal solution. Fortunately, Army's optimizer enhancements introduced recently builds a subquery materialization capability to Derby and this could be extended to handle this case as well. I propose to address the regression first by caching only small number of rows dynamically. If number of subquery results could cross 512, I think this optimization should be dynamically disabled. I will also file another Improvement that would suggest reworking the original optimization to be built on Army's subquery materialization framework. My current impression is that Army's subquery work needs to be more generalized and stabilized before extending to cover other cases. Let me know if anyone has any comments. Subquery materialization can cause stack overflow - Key: DERBY-634 URL: http://issues.apache.org/jira/browse/DERBY-634 Project: Derby Issue Type: Bug Components: SQL Affects Versions: 10.1.1.1 Reporter: Jeff Lichtman Fix For: 10.2.0.0 A performance optimization in subquery processing can cause a stack overflow. The optimization materializes a subquery ResultSet in memory where it thinks the rows will fit in memory. The materialization is done as a set of nested unions of constant rows (UnionResultSets and RowResultSets). If there are a lot of rows this can cause a stack overflow when fetching a row. The obvious fix is to make it use an iterative technique rather than a recursive one for storing and returning the rows. See the method BaseActivation.materializeResultSetIfPossible() in the language execution code. There are some other issues with this performance optimization that should be looked at: 1) The optimization can
[jira] Commented: (DERBY-1057) documentation to address Grant/Revoke (Derby-464)
[ http://issues.apache.org/jira/browse/DERBY-1057?page=comments#action_12427580 ] Satheesh Bandaram commented on DERBY-1057: -- I am loooking at dev Guide. Here are some of the comments: Notes on user authorization Authorization properties are set when a connection is established. Changes to any of the authorization properties during a connection, are not in affect until a new connection is established. I don't think that is correct. I thought we decided authorization would be used to define Derby Authorization or SQL Standard Authorization (with GRANT/REVOKE). Authorization is set for new database by either setting the sqlAuthorization as system property or by connecting to existing database and setting it as database property. Deepa provided a link earlier that describes this better. User authorization exceptions SQL exceptions are returned when errors occur with user authorizations. Derby validates the database properties when you set the properties. An exception is returned if you specify an invalid value when you set these properties. I think here we need to talk about some of the exceptions. It is possible to set derby.database.sqlAuthorization to true, which enables GRANT/REVOKE, but once the property is set to true as a database property, it cannot be set to FALSE. So once a database is switched to SQL authorization, it can be set back to Derby athorization. Grant and revoke user authorizations When the SQL standard authorization mode is enabled, object owners can use the GRANT and REVOKE SQL statements to set the user permissions for specific database objects or for specific SQL actions. It may be best to describe this section as 'SQL Standard Authorization' In the same page: The GRANT statement is used to grant specific permissions to users. The REVOKE statement is used to revoke permissions. Only the owner of an object can grant or revoke the following privileges on that object: I am updating functional spec to say 'only the owner of an object or database owner'can grant or revoke ' The SELECT privilege is later revoked from harry, but Harry has access to table t through the PUBLIC privilege. Are we missing an IF in the statement? If Select privilege is later revoked from harry, Harry can still access table t using PUBLIC privilege. To enable SQL standard authorization, set the derby.database.sqlAuthorization property to TRUE. For example: CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY( 'derby.database.sqlAuthorization', 'true') I think we also need to talk about another way to enable SQL Standard authorization... as mentioned above. (Through setting of the property as system property that automatically creates all new databases in SQL Standard authorization) I am assuming topics that talk about 'defaultConnectionMode' should already be in the manuals and are NOT new. If they are new, we would need to review it more carefully. Same with 'Working with Authentication'. Are these new sections? No changes are made here, so there should be no need to added these sections. User authorizations When you specify user authorizations, Derby verifies that a user has been granted permission to access a system, database, object, or SQL action. There are two types of user authorization in Derby, connection authorization and grant authorization. Connection authorization specifies the access that users have to connect to a system or database. SQL authorization controls the permissions that users have on database objects or for SQL actions. You can set the user authorization properties in Derby as system-level properties or database-level properties. I think we agreed that we will not use 'connection authorization' instead only refer to 'connection access mode' . The user authorization properties are: I would rather say 'Properties that affect authorization are:' That is all the comments I have on developer guide. Thanks for working on documenting GRANT/REVOKE. documentation to address Grant/Revoke (Derby-464) - Key: DERBY-1057 URL: http://issues.apache.org/jira/browse/DERBY-1057 Project: Derby Issue Type: Sub-task Components: Documentation Affects Versions: 10.0.2.0 Reporter: Eric Radzinski Assigned To: Laura Stewart Fix For: 10.2.0.0 Attachments: derby1057_devguide.diff, derby1057_devguide3.diff, derby1057_devguide_html.zip, derby1057_devguide_html3.zip, derby1057_ref.diff, derby1057_ref3.diff, derby1057_ref_html.zip, derby1057_tuning3.diff, derby1057_tuning4.diff, derby1057_tuning_html.zip, derby1058_ref_html3.zip, devguide_html2.zip, ref_html2.zip, rtunpropersqlauth.html -- This message is automatically generated by JIRA. - If you think it was sent incorrectly
[jira] Commented: (DERBY-634) Subquery materialization can cause stack overflow
[ http://issues.apache.org/jira/browse/DERBY-634?page=comments#action_12427670 ] Satheesh Bandaram commented on DERBY-634: - I propose twhere Table2.x=Table3.x and y 100) o address this issue in phases. Here is my current thinking: Background information: --- A performance optimization was introduced in Cloudscape before open sourcing as Apache Derby. Before this optimization was introduced, a query like: Select a, b, c from Table1 where a 5 and b in (select x from Table2, Table3 would take longer time time to execute than needed. This becomes worse as the complexity of the subquery increases. Basic problem was that for every qualified value of 'b', the subquery was getting executed, recreating the results multiple times. Cloudscape, at that time, had the ability to materialize results of a subquery only if a single row is returned from the subquery. (where subquery is of the form select max(i) from Table2) A performance optimization was introduced that allowed for some small number of rows greater than 1 to be cached dynamically at runtime. As the subquery was executed first time, results of the subquery were cached until MAX_MEMORY_PER_TABLE is reached, which was 1MG by default. If the results of the subquery could be fit into memory less than this configurable size, a nested union resultset would be built to cache the results. Future invocations of the subquery would simply return results from this subquery cache without actually evaluating subquery. This resulted in performance boost for a customer query from 10 minutes to a few seconds. Side effect of this optimization: - While the optimization worked well for the customer query, it is causing issues for the query in question here. If the subquery were returning just an integer, the optimization could attempt to cache 1MG/4, about 250,000 rows in nested union resultsets. Nesting of this deep would cause stack overflow. Jeff Lichman also identified several other issues mentioned in the description of this entry. Proposed Fix: - First, I think it is important to fix regression caused by this optimization. The optimization was intended to cache small number of subquery results. Instead of caching single row result of a subquery, this optimization could be adjusted to cache a small number of results. Second, caching results of subquery in nested union resultsets is not needed. This can be rewritten to generate a linear resultset, which would save runtime stack growth. Third, as Jeff pointed out, a global subquery optimization that is performed during optimization is the best approach. He pointed out subquery materialization based on hash joins decided during optimization phase is the ideal solution. Fortunately, Army's optimizer enhancements introduced recently builds a subquery materialization capability to Derby and this could be extended to handle this case as well. I propose to address the regression first by caching only small number of rows dynamically. If number of subquery results could cross 512, I think this optimization should be dynamically disabled. I will also file another Improvement that would suggest reworking the original optimization to be built on Army's subquery materialization framework. My current impression is that Army's subquery work needs to be more generalized and stabilized before extending to cover other cases. Let me know if anyone has any comments. Subquery materialization can cause stack overflow - Key: DERBY-634 URL: http://issues.apache.org/jira/browse/DERBY-634 Project: Derby Issue Type: Bug Components: SQL Affects Versions: 10.1.1.1 Reporter: Jeff Lichtman Fix For: 10.2.0.0 A performance optimization in subquery processing can cause a stack overflow. The optimization materializes a subquery ResultSet in memory where it thinks the rows will fit in memory. The materialization is done as a set of nested unions of constant rows (UnionResultSets and RowResultSets). If there are a lot of rows this can cause a stack overflow when fetching a row. The obvious fix is to make it use an iterative technique rather than a recursive one for storing and returning the rows. See the method BaseActivation.materializeResultSetIfPossible() in the language execution code. There are some other issues with this performance optimization that should be looked at: 1) The optimization can backfire, making the query run much slower. For example, in the query: select * from one_row_table where column1 not in (select column2 from million_row_table) reading million_row_table into memory is an expensive operation. If there is an index on million_row_table.column2, the query should
Re: [jira] Updated: (DERBY-1582) REVOKE statement does not generate a warning when no privileges are revoked.
Daniel John Debrunner wrote: Deepa Remesh (JIRA) wrote: It is not very clear to me from the spec how the following case should be handled - revoking permissions from dba (user who is the owner of the database). dba has implicit permissions on all objects and we cannot revoke privileges from dba. I think I've asked this before, but I'll ask again. I don't see in the functional spec for DERBY-464 where it says the database owner has 'implicit permissions on all objects' All I see is that the database owner can create and drop any schema. I also see comments like: Only the owner (creator) of an object can grant or revoke privileges on that object. Database owner can also grant or revoke privileges. I will update the functional specification. Derby currently allows database owner access to any object in that database. A database owner can also create any object in other user schemas. When a database owner is operating in other user schemas, objects created by database owners would be owned by owners of the those schemas. As for Deepa's question about whether it is possible to revoke a privilege from database owner, no, it is not possible currently. Satheesh
[jira] Commented: (DERBY-1646) Documentation to address Grant/Revoke Authorization for views/triggers/constraints/routines(DERBY-1330)
[ http://issues.apache.org/jira/browse/DERBY-1646?page=comments#action_12425935 ] Satheesh Bandaram commented on DERBY-1646: -- I think we need to document Derby Authorization mode and SQL standard authorization mode in Derby. That is we need to externalize the modes, as enabling GRANT/REVOKE while allows fine grained access, it also takes away something that used to work before. (like ability to create any number of schemas or ability to create objects in other schemas etc.) I think it is important to be consistant about defaultConnectionMode that controls access mode and sqlAuthorization that enables SQL standard authorization. Though both properties can interact with each other, I think, it may be best to document each independently and then may be have a section that covers what happens when both are set. I will reply to Laura's earlier post in another comment. Documentation to address Grant/Revoke Authorization for views/triggers/constraints/routines(DERBY-1330) --- Key: DERBY-1646 URL: http://issues.apache.org/jira/browse/DERBY-1646 Project: Derby Issue Type: New Feature Components: Documentation Affects Versions: 10.2.0.0 Reporter: Mamta A. Satoor Assigned To: Laura Stewart Creating a separate jira entry for documentation of Grant/Revoke Authorization for views/triggers/constraints/routines(Engine changes are going as part of DERBY-1330). Will link this jira entry to DERBY-1330 -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-1651) Develop a mechanism to migrate mySQL databases to Derby. Migration tool should include both schema and data migration options.
[ http://issues.apache.org/jira/browse/DERBY-1651?page=all ] Satheesh Bandaram updated DERBY-1651: - Summary: Develop a mechanism to migrate mySQL databases to Derby. Migration tool should include both schema and data migration options. (was: MySQL to Derby Migration Tool) Environment: All platforms Fix Version/s: 10.2.0.0 Description: Develop a mechanism to migration databases created by other database engines to Derby. While my current interest is to migrate mySQL databases to Derby, the tool could be developed in a way to extend this mechanism to allow migration from other database engines in the future. More details of proposed functionality and implementation strategy can be found at: http://wiki.apache.org/db-derby/MysqlDerbyMigration The plan is to develop and submit patches incrementally. First patch supports migration of tables and views from mySQL database to Derby. was:This is the first drop of MySQL to Derby Migration tool. There is currently support for tables and views. Develop a mechanism to migrate mySQL databases to Derby. Migration tool should include both schema and data migration options. -- Key: DERBY-1651 URL: http://issues.apache.org/jira/browse/DERBY-1651 Project: Derby Issue Type: New Feature Components: Tools Environment: All platforms Reporter: Ramin Moazeni Assigned To: Ramin Moazeni Fix For: 10.2.0.0 Attachments: MigrationTool-MySQLtoDerby.zip Develop a mechanism to migration databases created by other database engines to Derby. While my current interest is to migrate mySQL databases to Derby, the tool could be developed in a way to extend this mechanism to allow migration from other database engines in the future. More details of proposed functionality and implementation strategy can be found at: http://wiki.apache.org/db-derby/MysqlDerbyMigration The plan is to develop and submit patches incrementally. First patch supports migration of tables and views from mySQL database to Derby. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1646) Documentation to address Grant/Revoke Authorization for views/triggers/constraints/routines(DERBY-1330)
[ http://issues.apache.org/jira/browse/DERBY-1646?page=comments#action_12425983 ] Satheesh Bandaram commented on DERBY-1646: -- Thanks Dan... I missed correcting that from Laura's original description. It was little too burried down, but glad you found it. Laura, let me know if you want me to rewrite it again or if you got all the comments. Documentation to address Grant/Revoke Authorization for views/triggers/constraints/routines(DERBY-1330) --- Key: DERBY-1646 URL: http://issues.apache.org/jira/browse/DERBY-1646 Project: Derby Issue Type: New Feature Components: Documentation Affects Versions: 10.2.0.0 Reporter: Mamta A. Satoor Assigned To: Laura Stewart Creating a separate jira entry for documentation of Grant/Revoke Authorization for views/triggers/constraints/routines(Engine changes are going as part of DERBY-1330). Will link this jira entry to DERBY-1330 -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
Re: (10.2 maintenance release version for Jira?) Re: [jira] Updated: (DERBY-700) Derby does not prevent dual boot of database from different classloaders on Linux
Hi Rick, Currently both Andrew and myself have JIRA-admin access, though Andrew has been doing all the work lately. I have been thinking of giving up my JIRA-admin access... May be the right time now to switch it over to you, since you are the release manager for 10.2. Satheesh Rick Hillegas wrote: Yes, please! If I knew the JIRA magic to do this, I would mumble it. Could someone teach me the magic, and if necessary, give me karma to mumble? Thanks, -Rick Kathey Marsden wrote: Rick Hillegas (JIRA) wrote: It appears that fixing this bug will require us to agree on some mechanism for caching VM-global Derby state. This seems to be an architectural decision which requires careful thought and experiment. I think we should defer this to a future release. I am moving this to 10.3 because thatt's the next release available in the dropdown. I agree with Kathey that this is a good candidate for a bug fix release in the 10.2 lineage. Sounds like a reasonable move since there is no immediate solution apparent. I was wondering though, could we could add a new version that we can use as some of the 10.2 candidates start getting moved out? I want to say 10.2.2.0 but am not sure that is right. Kathey
Re: (10.2 maintenance release version for Jira?) Re: [jira] Updated: (DERBY-700) Derby does not prevent dual boot of database from different classloaders on Linux
Andrew McIntyre wrote: On 8/3/06, Rick Hillegas [EMAIL PROTECTED] wrote: Yes, please! If I knew the JIRA magic to do this, I would mumble it. Could someone teach me the magic, and if necessary, give me karma to mumble? granted. the admin pages are your friends. Can you please take me off from JIRA-admin group? JIRA says I can't remove my own membership to this group! Or Rick, you want to try doing this and use your new karma? :) Satheesh
[jira] Commented: (DERBY-1522) Switch(if supported) from Derby Authorization to Derby SQL Standard Authorization needs to be tested
[ http://issues.apache.org/jira/browse/DERBY-1522?page=comments#action_12425338 ] Satheesh Bandaram commented on DERBY-1522: -- 1) Mamta says: after the upgrade, all the existing schemas and objects in them should be owned by the dba and any other users will need to have permissions granted to them by the dba. Don't think that is true... Even in Derby authorization mode, schemas created by regular users have their authorizationId recorded in sysschemas. Derby authorization mode only recognises fullAccessUsers (who can modify any object owned by anyone) or readOnlyAccess or no access. But once switching to SQL authorization mode, only schema owners would have access to objects in their schema and others would need explicit GRANT from schema owner to be able to access them. As far as additional testing for switching from Derby authorization mode to SQL mode, tests to cover include new restrictions we currently have on SQL authorization mode and to make sure they are actually enforced correctly. Some of the items to check, after switching to SQL authorization mode, include 1) Only owners can access their objects 2) regular users can only create a schema that matches their authorizationId 3) Database owner can access any object in the system 4) definer model being correctly enforced etc. 5) Access to many system routines are restricted 6) Cann't switch mode back to Derby authorization Thanks Deepa for looking at DERBY-1544. Switch(if supported) from Derby Authorization to Derby SQL Standard Authorization needs to be tested Key: DERBY-1522 URL: http://issues.apache.org/jira/browse/DERBY-1522 Project: Derby Issue Type: Task Components: JDBC Affects Versions: 10.2.0.0 Reporter: Mamta A. Satoor Fix For: 10.2.0.0 There has been discussions on the Derby-dev list about switch from Derby Authorization to Derby SQL Standard Authorization for existing databases. If we do decide to support a switch like that, testing needs to be done/added to make sure everything works fine after the switch. ps I have added this JIRA entry to JDBC component but I am not 100% sure if that is the right component. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Resolved: (DERBY-1608) Execution of builtin functions by a user who is not the owner of system schemas gives NPE when authentication and SQL authorization are on.
[ http://issues.apache.org/jira/browse/DERBY-1608?page=all ]
Satheesh Bandaram resolved DERBY-1608.
--
Resolution: Fixed
Submitted a fix to trunk and also added test cases.
Execution of builtin functions by a user who is not the owner of system
schemas gives NPE when authentication and SQL authorization are on.
---
Key: DERBY-1608
URL: http://issues.apache.org/jira/browse/DERBY-1608
Project: Derby
Issue Type: Bug
Components: SQL
Reporter: Deepa Remesh
Assigned To: Satheesh Bandaram
Fix For: 10.2.0.0
1. Create a database in 10.1
2. Full upgrade to 10.2 - Booting using 10.2 jars by specifying
upgrade=true in the connection URL.
3. Execute a function e.g: VALUES { fn ACOS(0.0707) }. This passes as
expected.
4. Set database property derby.database.sqlAuthorization=true.
5. Shutdown and reconnect to database for the property to take effect.
6. Re-execute the function. This gives NPE.
Repro using ij:
Steps using 10.1 jar:
ij version 10.1
ij connect 'jdbc:derby:old_db;create=true';
ij exit;
Steps using 10.2 jar:
ij version 10.2
ij connect 'jdbc:derby:old_db;upgrade=true';
ij VALUES { fn ACOS(0.0707) };
1
--
1.5000372950430991
1 row selected
ij call
SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.sqlAuthorization',
'true');
0 rows inserted/updated/deleted
ij connect 'jdbc:derby:old_db;shutdown=true';
ERROR 08006: Database 'old_db' shutdown.
ij connect 'jdbc:derby:old_db';
ij(CONNECTION1) VALUES { fn ACOS(0.0707) };
ERROR XJ001: Java exception: ': java.lang.NullPointerException'.
ij(CONNECTION1)
Stack trace of failure:
ERROR XJ001: Java exception: ': java.lang.NullPointerException'.
java.lang.NullPointerException
at
org.apache.derby.iapi.sql.dictionary.RoutinePermsDescriptor.init(RoutinePermsDescriptor
.java:54)
at
org.apache.derby.iapi.sql.dictionary.RoutinePermsDescriptor.init(RoutinePermsDescriptor
.java:62)
at
org.apache.derby.impl.sql.catalog.DataDictionaryImpl.getRoutinePermissions(DataDictionary
Impl.java:9902)
at
org.apache.derby.iapi.sql.dictionary.StatementRoutinePermission.check(StatementRoutinePer
mission.java:55)
at
org.apache.derby.impl.sql.conn.GenericAuthorizer.authorize(GenericAuthorizer.java:157)
at
org.apache.derby.exe.ac6b91c056x010cxb687x3eb7x0012d1c00.fillResultSet(Unknown
Source
)
at
org.apache.derby.exe.ac6b91c056x010cxb687x3eb7x0012d1c00.execute(Unknown
Source)
at
org.apache.derby.impl.sql.GenericActivationHolder.execute(GenericActivationHolder.java:32
6)
at
org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:
355)
at
org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1181)
at
org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:584)
at
org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:516)
at org.apache.derby.impl.tools.ij.ij.executeImmediate(ij.java:313)
at org.apache.derby.impl.tools.ij.utilMain.doCatch(utilMain.java:433)
at org.apache.derby.impl.tools.ij.utilMain.go(utilMain.java:312)
at org.apache.derby.impl.tools.ij.Main.go(Main.java:207)
at org.apache.derby.impl.tools.ij.Main.mainCore(Main.java:173)
at org.apache.derby.impl.tools.ij.Main14.main(Main14.java:55)
at org.apache.derby.tools.ij.main(ij.java:60)
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1057) documentation to address Grant/Revoke (Derby-464)
[ http://issues.apache.org/jira/browse/DERBY-1057?page=comments#action_12424974 ] Satheesh Bandaram commented on DERBY-1057: -- All the answers provided by Mamta earlier are correct. Grant/Revoke statements can only operate on one object at a time.. though it may be possible to grant/revoke several actions to several different users in one statement. So, all the following are possible: GRANT SELECT, UPDATE, REFERENCES ON T TO SAM, RAM, PAM; REVOKE SELECT, UPDATE, REFERENCES ON T FROM SAM, RAM, PAM; GRANT EXECUTE ON FUNCTION F_ABS123(INT) TO SAM, PAM; REVOKE EXECUTE ON FUNCTION F_ABS123 FROM SAM, PAM RESTRICT; But NOT the following: GRANT SELECT ON T, T1 TO SAM; Granting Select on two different tables in statement Similarly granting EXECUTE on multiple functions (or procedures) in statement is NOT allowed, same behavior with REVOKE as well. Regarding routine signatures, I can update the spec to add some grammar info. Laura, let me know if you need any further info for documentation. BIG thanks for attempting to document this feature. documentation to address Grant/Revoke (Derby-464) - Key: DERBY-1057 URL: http://issues.apache.org/jira/browse/DERBY-1057 Project: Derby Issue Type: Sub-task Components: Documentation Affects Versions: 10.0.2.0 Reporter: Eric Radzinski Assigned To: Laura Stewart Fix For: 10.2.0.0 Attachments: derby1057_devguide.diff, derby1057_devguide_html.zip, derby1057_ref.diff, derby1057_ref_html.zip, devguide_html2.zip, ref_html2.zip -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
Re: [jira] Commented: (DERBY-1543) Address two remaining issues with GRANT/REVOKE functionality: 1) Add warning when sqlAuthorization is on with authentication off 2) Drop permission descriptors when
Checked in fix for this... Dropping of table, column and routine permission descriptors should only be attempted in SQL authorization mode, as privilege system tables may not be present in soft-upgrade mode. Should pass now. Satheesh Mamta A. Satoor (JIRA) wrote: [ http://issues.apache.org/jira/browse/DERBY-1543?page=comments#action_12424412 ] Mamta A. Satoor commented on DERBY-1543: Satheesh, I ran the upgrade test (upgradeTests/Upgrade_10_1_10_2.java) on a clean client and the test failed. It has following stack trace SQLSTATE(XSAI2):ERROR XSAI2: The conglomerate (-1) requested does not exist. at org.apache.derby.iapi.error.StandardException.newException(StandardException.java:304) at org.apache.derby.impl.store.access.RAMTransaction.findExistingConglomerate(RAMTransaction.java:394) at org.apache.derby.impl.store.access.RAMTransaction.openConglomerate(RAMTransaction.java:1315) at org.apache.derby.impl.sql.catalog.TabInfoImpl.getRow(TabInfoImpl.java:794) at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.dropTablePermDescriptor(DataDictionaryImpl.java:2509) at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.dropAllTableAndColPermDescriptors(DataDictionaryImpl.java:2416) at org.apache.derby.impl.sql.execute.DropTableConstantAction.executeConstantAction(DropTableConstantAction.java:219) at org.apache.derby.impl.sql.execute.MiscResultSet.open(MiscResultSet.java:56) at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:357) at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1181) at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:584) at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:516) at org.apache.derbyTesting.functionTests.tests.jdbcapi.metadata_test.runTest(metadata_test.java:1192) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:324) at org.apache.derbyTesting.functionTests.tests.upgradeTests.UpgradeTester.runMetadataTest(UpgradeTester.java:900) at org.apache.derbyTesting.functionTests.tests.upgradeTests.UpgradeTester.runPhase(UpgradeTester.java:367) at org.apache.derbyTesting.functionTests.tests.upgradeTests.UpgradeTester.runUpgradeTests(UpgradeTester.java:314) at org.apache.derbyTesting.functionTests.tests.upgradeTests.Upgrade_10_1_10_2.main(Upgrade_10_1_10_2.java:43) Could this be related to your recent changes for dropping the permission descriptors associated with a table? If not, may be I should open a new jira for upgrade test failure. ps I have been out of the loop for last couple days because my laptop died on Thursday. So I hope this is not a known issue and there is already somebody working on it. Address two remaining issues with GRANT/REVOKE functionality: 1) Add warning when sqlAuthorization is on with authentication off 2) Drop permission descriptors when objects they cover are dropped. Key: DERBY-1543 URL: http://issues.apache.org/jira/browse/DERBY-1543 Project: Derby Issue Type: Sub-task Components: SQL Affects Versions: 10.2.0.0 Reporter: Satheesh Bandaram Assigned To: Satheesh Bandaram Fix For: 10.2.0.0 Attachments: DERBY-1543.out, Derby1543.diff Address two remaining sub-tasks: Add warning when sqlAuthorization is on with authentication off: This is a precautionary warning, since sqlAuthorization doesn't mean much without authentication being ON. Drop permission descriptors when objects they cover are dropped: This applies to tables, routines. When these objects are dropped, automatically drop their permisson descriptors.
[jira] Resolved: (DERBY-1543) Address two remaining issues with GRANT/REVOKE functionality: 1) Add warning when sqlAuthorization is on with authentication off 2) Drop permission descriptors when objec
[ http://issues.apache.org/jira/browse/DERBY-1543?page=all ] Satheesh Bandaram resolved DERBY-1543. -- Resolution: Fixed Submitted a fix. Address two remaining issues with GRANT/REVOKE functionality: 1) Add warning when sqlAuthorization is on with authentication off 2) Drop permission descriptors when objects they cover are dropped. Key: DERBY-1543 URL: http://issues.apache.org/jira/browse/DERBY-1543 Project: Derby Issue Type: Sub-task Components: SQL Affects Versions: 10.2.0.0 Reporter: Satheesh Bandaram Assigned To: Satheesh Bandaram Fix For: 10.2.0.0 Attachments: DERBY-1543.out, Derby1543.diff Address two remaining sub-tasks: Add warning when sqlAuthorization is on with authentication off: This is a precautionary warning, since sqlAuthorization doesn't mean much without authentication being ON. Drop permission descriptors when objects they cover are dropped: This applies to tables, routines. When these objects are dropped, automatically drop their permisson descriptors. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Closed: (DERBY-1543) Address two remaining issues with GRANT/REVOKE functionality: 1) Add warning when sqlAuthorization is on with authentication off 2) Drop permission descriptors when objects
[ http://issues.apache.org/jira/browse/DERBY-1543?page=all ] Satheesh Bandaram closed DERBY-1543. Added tests and it seems to work as expected. Address two remaining issues with GRANT/REVOKE functionality: 1) Add warning when sqlAuthorization is on with authentication off 2) Drop permission descriptors when objects they cover are dropped. Key: DERBY-1543 URL: http://issues.apache.org/jira/browse/DERBY-1543 Project: Derby Issue Type: Sub-task Components: SQL Affects Versions: 10.2.0.0 Reporter: Satheesh Bandaram Assigned To: Satheesh Bandaram Fix For: 10.2.0.0 Attachments: DERBY-1543.out, Derby1543.diff Address two remaining sub-tasks: Add warning when sqlAuthorization is on with authentication off: This is a precautionary warning, since sqlAuthorization doesn't mean much without authentication being ON. Drop permission descriptors when objects they cover are dropped: This applies to tables, routines. When these objects are dropped, automatically drop their permisson descriptors. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Resolved: (DERBY-1367) add lang/grantrevoke.java to derbynetclientmats
[ http://issues.apache.org/jira/browse/DERBY-1367?page=all ] Satheesh Bandaram resolved DERBY-1367. -- Fix Version/s: 10.2.0.0 Resolution: Fixed Enabled grantRevoke.java test in DerbyNetClient framework. add lang/grantrevoke.java to derbynetclientmats Key: DERBY-1367 URL: http://issues.apache.org/jira/browse/DERBY-1367 Project: Derby Issue Type: Test Components: SQL, Test Affects Versions: 10.2.0.0 Reporter: Kathey Marsden Assigned To: Satheesh Bandaram Priority: Minor Fix For: 10.2.0.0 I noticed running lang./grantrevoke.java with client fails with : Unexpected exception 08004: Connection authentication failure occurred. Reason: userid or password invalid. java.sql.SQLException: Connection authentication failure occurred. Reason: userid or password invalid. Caused by: org.apache.derby.client.am.SqlException: Connection authentication failure occurred. Reason: userid or password invalid. ... 8 more FAILED. 1 error Test Failed. *** End: grantRevoke jdk1.4.2_07 DerbyNetClient 2006-06-01 12:17:25 *** Perhaps just a test configuration problem of some sort, but It would be good to have client testing for grant revoke. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1522) Switch(if supported) from Derby Authorization to Derby SQL Standard Authorization needs to be tested
[ http://issues.apache.org/jira/browse/DERBY-1522?page=comments#action_12424694 ] Satheesh Bandaram commented on DERBY-1522: -- I was looking through all GRANT/REVOKE sub-tasks and related bugs. I am not sure exactly what this entry was filed to address. Derby already supports switching from Derby authorization mode to SQL authorization mode. I think some of Deepa's tests already test this, though her changes are not committed yet. Not sure how much coverage those tests provide. Switch(if supported) from Derby Authorization to Derby SQL Standard Authorization needs to be tested Key: DERBY-1522 URL: http://issues.apache.org/jira/browse/DERBY-1522 Project: Derby Issue Type: Task Components: JDBC Affects Versions: 10.2.0.0 Reporter: Mamta A. Satoor Fix For: 10.2.0.0 There has been discussions on the Derby-dev list about switch from Derby Authorization to Derby SQL Standard Authorization for existing databases. If we do decide to support a switch like that, testing needs to be done/added to make sure everything works fine after the switch. ps I have added this JIRA entry to JDBC component but I am not 100% sure if that is the right component. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
Re: Question on Grant revoke functional spec
Daniel John Debrunner wrote: Satheesh Bandaram wrote: Derby currently doesn't support WITH GRANT OPTION. So, while it is OK for 'mamta2' here to create the view, an error *should be* raised if this user attempts to grant access to columns derived from user 'mamta1' table. What's the reference (section number) for this from the SQL 2003 spec, especially for the definition of columns derived? I am still trying to access SQL 2003 spec to confirm what it says. My soft copy seems to be corrupted... doesn't come up anymore. But I have DB2 8.1.5 installed on my machine and tried to confirm what DB2 does in this situation. It indeed raises an error, trying to GRANT access to columns derived from another table not owned by the view owner. But it does allow this GRANT to suceed if the SELECT privilege to the view owner was granted WITH GRANT OPTION. Again, I will try to get hold of SQL 2003 spec on Monday and confirm what it says. Satheesh
Re: [jira] Commented: (DERBY-1543) Address two remaining issues with GRANT/REVOKE functionality: 1) Add warning when sqlAuthorization is on with authentication off 2) Drop permission descriptors when
This should be mine... I will take a look.SatheeshOn 7/30/06, Mamta A. Satoor (JIRA) derby-dev@db.apache.org wrote:[ http://issues.apache.org/jira/browse/DERBY-1543?page=comments#action_12424412 ]Mamta A. Satoor commented on DERBY-1543:Satheesh, I ran the upgrade test (upgradeTests/Upgrade_10_1_10_2.java) on a clean client and the test failed. It has following stack trace SQLSTATE(XSAI2):ERROR XSAI2: The conglomerate (-1) requested does not exist.at org.apache.derby.iapi.error.StandardException.newException(StandardException.java:304)at org.apache.derby.impl.store.access.RAMTransaction.findExistingConglomerate (RAMTransaction.java:394)at org.apache.derby.impl.store.access.RAMTransaction.openConglomerate(RAMTransaction.java:1315)at org.apache.derby.impl.sql.catalog.TabInfoImpl.getRow(TabInfoImpl.java:794) at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.dropTablePermDescriptor(DataDictionaryImpl.java:2509)at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.dropAllTableAndColPermDescriptors (DataDictionaryImpl.java:2416)at org.apache.derby.impl.sql.execute.DropTableConstantAction.executeConstantAction(DropTableConstantAction.java:219)at org.apache.derby.impl.sql.execute.MiscResultSet.open (MiscResultSet.java:56)at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:357)at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java :1181)at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:584)at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:516)at org.apache.derbyTesting.functionTests.tests.jdbcapi.metadata_test.runTest (metadata_test.java:1192)at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)at sun.reflect.DelegatingMethodAccessorImpl.invoke (DelegatingMethodAccessorImpl.java:25)at java.lang.reflect.Method.invoke(Method.java:324)at org.apache.derbyTesting.functionTests.tests.upgradeTests.UpgradeTester.runMetadataTest(UpgradeTester.java :900)at org.apache.derbyTesting.functionTests.tests.upgradeTests.UpgradeTester.runPhase(UpgradeTester.java:367)at org.apache.derbyTesting.functionTests.tests.upgradeTests.UpgradeTester.runUpgradeTests (UpgradeTester.java:314)at org.apache.derbyTesting.functionTests.tests.upgradeTests.Upgrade_10_1_10_2.main(Upgrade_10_1_10_2.java:43)Could this be related to your recent changes for dropping the permission descriptors associated with a table? If not, may be I should open a new jira for upgrade test failure. ps I have been out of the loop for last couple days because my laptop died on Thursday. So I hope this is not a known issue and there is already somebody working on it. Address two remaining issues with GRANT/REVOKE functionality: 1) Add warning when sqlAuthorization is on with authentication off 2) Drop permission descriptors when objects they cover are dropped. Key: DERBY-1543 URL: http://issues.apache.org/jira/browse/DERBY-1543 Project: DerbyIssue Type: Sub-taskComponents: SQL Affects Versions: 10.2.0.0Reporter: Satheesh Bandaram Assigned To: Satheesh Bandaram Fix For: 10.2.0.0 Attachments: DERBY-1543.out, Derby1543.diff Address two remaining sub-tasks: Add warning when sqlAuthorization is on with authentication off: This is a precautionary warning, since sqlAuthorization doesn't mean much without authentication being ON. Drop permission descriptors when objects they cover are dropped: This applies to tables, routines. When these objects are dropped, automatically drop their permisson descriptors.--This message is automatically generated by JIRA. -If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa-For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Assigned: (DERBY-1608) Execution of builtin functions by a user who is not the owner of system schemas gives NPE when authentication and SQL authorization are on.
[ http://issues.apache.org/jira/browse/DERBY-1608?page=all ]
Satheesh Bandaram reassigned DERBY-1608:
Assignee: Satheesh Bandaram (was: Deepa Remesh)
I can fix this problem. Some of the newly added SYSFUN builtin functions don't
exist in system catalogs but pretend to be routines during resolution process.
Since these routines are builtin functions that don't need privilege checking,
need to bypass creating StatementPermission objects for them. Currently SQL
authorization mechanism is looking for routine descriptors to create
StatementPermission and hence fails, as they don't really have aliasIDs.
Execution of builtin functions by a user who is not the owner of system
schemas gives NPE when authentication and SQL authorization are on.
---
Key: DERBY-1608
URL: http://issues.apache.org/jira/browse/DERBY-1608
Project: Derby
Issue Type: Bug
Components: SQL
Reporter: Deepa Remesh
Assigned To: Satheesh Bandaram
Fix For: 10.2.0.0
1. Create a database in 10.1
2. Full upgrade to 10.2 - Booting using 10.2 jars by specifying
upgrade=true in the connection URL.
3. Execute a function e.g: VALUES { fn ACOS(0.0707) }. This passes as
expected.
4. Set database property derby.database.sqlAuthorization=true.
5. Shutdown and reconnect to database for the property to take effect.
6. Re-execute the function. This gives NPE.
Repro using ij:
Steps using 10.1 jar:
ij version 10.1
ij connect 'jdbc:derby:old_db;create=true';
ij exit;
Steps using 10.2 jar:
ij version 10.2
ij connect 'jdbc:derby:old_db;upgrade=true';
ij VALUES { fn ACOS(0.0707) };
1
--
1.5000372950430991
1 row selected
ij call
SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.sqlAuthorization',
'true');
0 rows inserted/updated/deleted
ij connect 'jdbc:derby:old_db;shutdown=true';
ERROR 08006: Database 'old_db' shutdown.
ij connect 'jdbc:derby:old_db';
ij(CONNECTION1) VALUES { fn ACOS(0.0707) };
ERROR XJ001: Java exception: ': java.lang.NullPointerException'.
ij(CONNECTION1)
Stack trace of failure:
ERROR XJ001: Java exception: ': java.lang.NullPointerException'.
java.lang.NullPointerException
at
org.apache.derby.iapi.sql.dictionary.RoutinePermsDescriptor.init(RoutinePermsDescriptor
.java:54)
at
org.apache.derby.iapi.sql.dictionary.RoutinePermsDescriptor.init(RoutinePermsDescriptor
.java:62)
at
org.apache.derby.impl.sql.catalog.DataDictionaryImpl.getRoutinePermissions(DataDictionary
Impl.java:9902)
at
org.apache.derby.iapi.sql.dictionary.StatementRoutinePermission.check(StatementRoutinePer
mission.java:55)
at
org.apache.derby.impl.sql.conn.GenericAuthorizer.authorize(GenericAuthorizer.java:157)
at
org.apache.derby.exe.ac6b91c056x010cxb687x3eb7x0012d1c00.fillResultSet(Unknown
Source
)
at
org.apache.derby.exe.ac6b91c056x010cxb687x3eb7x0012d1c00.execute(Unknown
Source)
at
org.apache.derby.impl.sql.GenericActivationHolder.execute(GenericActivationHolder.java:32
6)
at
org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:
355)
at
org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1181)
at
org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:584)
at
org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:516)
at org.apache.derby.impl.tools.ij.ij.executeImmediate(ij.java:313)
at org.apache.derby.impl.tools.ij.utilMain.doCatch(utilMain.java:433)
at org.apache.derby.impl.tools.ij.utilMain.go(utilMain.java:312)
at org.apache.derby.impl.tools.ij.Main.go(Main.java:207)
at org.apache.derby.impl.tools.ij.Main.mainCore(Main.java:173)
at org.apache.derby.impl.tools.ij.Main14.main(Main14.java:55)
at org.apache.derby.tools.ij.main(ij.java:60)
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1608) Execution of builtin functions by a user who is not the owner of system schemas gives NPE when authentication and SQL authorization are on.
[
http://issues.apache.org/jira/browse/DERBY-1608?page=comments#action_12424334 ]
Satheesh Bandaram commented on DERBY-1608:
--
Deepa, I did not realize you had this defect assigned to you, until after I
assigned it myself. If you want to fix it yourself, feel free to assign it back
to yourself. Otherwise, I can take care of it.
Execution of builtin functions by a user who is not the owner of system
schemas gives NPE when authentication and SQL authorization are on.
---
Key: DERBY-1608
URL: http://issues.apache.org/jira/browse/DERBY-1608
Project: Derby
Issue Type: Bug
Components: SQL
Reporter: Deepa Remesh
Assigned To: Satheesh Bandaram
Fix For: 10.2.0.0
1. Create a database in 10.1
2. Full upgrade to 10.2 - Booting using 10.2 jars by specifying
upgrade=true in the connection URL.
3. Execute a function e.g: VALUES { fn ACOS(0.0707) }. This passes as
expected.
4. Set database property derby.database.sqlAuthorization=true.
5. Shutdown and reconnect to database for the property to take effect.
6. Re-execute the function. This gives NPE.
Repro using ij:
Steps using 10.1 jar:
ij version 10.1
ij connect 'jdbc:derby:old_db;create=true';
ij exit;
Steps using 10.2 jar:
ij version 10.2
ij connect 'jdbc:derby:old_db;upgrade=true';
ij VALUES { fn ACOS(0.0707) };
1
--
1.5000372950430991
1 row selected
ij call
SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.sqlAuthorization',
'true');
0 rows inserted/updated/deleted
ij connect 'jdbc:derby:old_db;shutdown=true';
ERROR 08006: Database 'old_db' shutdown.
ij connect 'jdbc:derby:old_db';
ij(CONNECTION1) VALUES { fn ACOS(0.0707) };
ERROR XJ001: Java exception: ': java.lang.NullPointerException'.
ij(CONNECTION1)
Stack trace of failure:
ERROR XJ001: Java exception: ': java.lang.NullPointerException'.
java.lang.NullPointerException
at
org.apache.derby.iapi.sql.dictionary.RoutinePermsDescriptor.init(RoutinePermsDescriptor
.java:54)
at
org.apache.derby.iapi.sql.dictionary.RoutinePermsDescriptor.init(RoutinePermsDescriptor
.java:62)
at
org.apache.derby.impl.sql.catalog.DataDictionaryImpl.getRoutinePermissions(DataDictionary
Impl.java:9902)
at
org.apache.derby.iapi.sql.dictionary.StatementRoutinePermission.check(StatementRoutinePer
mission.java:55)
at
org.apache.derby.impl.sql.conn.GenericAuthorizer.authorize(GenericAuthorizer.java:157)
at
org.apache.derby.exe.ac6b91c056x010cxb687x3eb7x0012d1c00.fillResultSet(Unknown
Source
)
at
org.apache.derby.exe.ac6b91c056x010cxb687x3eb7x0012d1c00.execute(Unknown
Source)
at
org.apache.derby.impl.sql.GenericActivationHolder.execute(GenericActivationHolder.java:32
6)
at
org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:
355)
at
org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1181)
at
org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:584)
at
org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:516)
at org.apache.derby.impl.tools.ij.ij.executeImmediate(ij.java:313)
at org.apache.derby.impl.tools.ij.utilMain.doCatch(utilMain.java:433)
at org.apache.derby.impl.tools.ij.utilMain.go(utilMain.java:312)
at org.apache.derby.impl.tools.ij.Main.go(Main.java:207)
at org.apache.derby.impl.tools.ij.Main.mainCore(Main.java:173)
at org.apache.derby.impl.tools.ij.Main14.main(Main14.java:55)
at org.apache.derby.tools.ij.main(ij.java:60)
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-591) Several builtin functions are incorrectly exposed through the JDBC escaped function syntax {fn function_call}
[ http://issues.apache.org/jira/browse/DERBY-591?page=all ]
Satheesh Bandaram updated DERBY-591:
Assignee: (was: Satheesh Bandaram)
I am not planning to work on this bug in the short term, so unassigning from
myself.
Several builtin functions are incorrectly exposed through the JDBC escaped
function syntax {fn function_call}
---
Key: DERBY-591
URL: http://issues.apache.org/jira/browse/DERBY-591
Project: Derby
Issue Type: Bug
Components: JDBC, SQL
Affects Versions: 10.0.2.0, 10.1.1.0
Reporter: Daniel John Debrunner
Priority: Minor
Due to lack of direction in the way functions are added into the parser, any
functions that are added into the parser's miscBuiltinsCore are also
automatically JDBC/ODBC escaped functions.
E.g. the new XML functions (e.g. XMLPARSE), COALESCE, VALUE etc. etc.
The correct set of escaped scaler functions is defined in appendex C (C1- C5)
of JDBC 3.0.
Discovered this while trying to make the JDBC escape functions table driven,
at least for the valid subset of table driven functions added in DERBY-475.
Probably just need to split the miscBuiltins core into two functions, one for
functions that are SQL and allowed in JDBC escape, and one for ones only
allowed in SQL.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Assigned: (DERBY-1367) add lang/grantrevoke.java to derbynetclientmats
[ http://issues.apache.org/jira/browse/DERBY-1367?page=all ] Satheesh Bandaram reassigned DERBY-1367: Assignee: Satheesh Bandaram add lang/grantrevoke.java to derbynetclientmats Key: DERBY-1367 URL: http://issues.apache.org/jira/browse/DERBY-1367 Project: Derby Issue Type: Test Components: SQL, Test Affects Versions: 10.2.0.0 Reporter: Kathey Marsden Assigned To: Satheesh Bandaram Priority: Minor I noticed running lang./grantrevoke.java with client fails with : Unexpected exception 08004: Connection authentication failure occurred. Reason: userid or password invalid. java.sql.SQLException: Connection authentication failure occurred. Reason: userid or password invalid. Caused by: org.apache.derby.client.am.SqlException: Connection authentication failure occurred. Reason: userid or password invalid. ... 8 more FAILED. 1 error Test Failed. *** End: grantRevoke jdk1.4.2_07 DerbyNetClient 2006-06-01 12:17:25 *** Perhaps just a test configuration problem of some sort, but It would be good to have client testing for grant revoke. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-1543) Address two remaining issues with GRANT/REVOKE functionality: 1) Add warning when sqlAuthorization is on with authentication off 2) Drop permission descriptors when object
[ http://issues.apache.org/jira/browse/DERBY-1543?page=all ] Satheesh Bandaram updated DERBY-1543: - Attachment: Derby1543.diff This patch should address these two left over items in GRANT/REVOKE implementation. Some implementation notes: 1) Now Derby raises an SQLWarning when SQL authorization is ON without authentication at connect time. This is done by checking if AuthenticationService being used is an instance of NoneAuthenticationServiceImpl. Since this is the default authentication service with Derby, it should always be present. 2) Added code to drop permission descriptors from SYSTABLEPERMS, SYSCOLPERMS and SYSROUTINEPERMS when the object they provide permission for is dropped. This includes tables, views and routines and these descriptors needs to be removed from permissionCache as well. I have tested the cases when PermissionsDescriptors are in cache also. Passed GrantRevoke tests. Will run derbyAll tonight and submit changes over the weekend. Address two remaining issues with GRANT/REVOKE functionality: 1) Add warning when sqlAuthorization is on with authentication off 2) Drop permission descriptors when objects they cover are dropped. Key: DERBY-1543 URL: http://issues.apache.org/jira/browse/DERBY-1543 Project: Derby Issue Type: Sub-task Components: SQL Affects Versions: 10.2.0.0 Reporter: Satheesh Bandaram Assigned To: Satheesh Bandaram Fix For: 10.2.0.0 Attachments: DERBY-1543.out, Derby1543.diff Address two remaining sub-tasks: Add warning when sqlAuthorization is on with authentication off: This is a precautionary warning, since sqlAuthorization doesn't mean much without authentication being ON. Drop permission descriptors when objects they cover are dropped: This applies to tables, routines. When these objects are dropped, automatically drop their permisson descriptors. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1543) Address two remaining issues with GRANT/REVOKE functionality: 1) Add warning when sqlAuthorization is on with authentication off 2) Drop permission descriptors when obje
[ http://issues.apache.org/jira/browse/DERBY-1543?page=comments#action_12424136 ] Satheesh Bandaram commented on DERBY-1543: -- One question I had with selecting hard coded UUIDs for internal system table indices... How does one generate them? Can I use any UUID generated by our internal service? Right now the patch uses cooked up UUIDs that need to be replaced with correctly generated UUIDs before commit. Address two remaining issues with GRANT/REVOKE functionality: 1) Add warning when sqlAuthorization is on with authentication off 2) Drop permission descriptors when objects they cover are dropped. Key: DERBY-1543 URL: http://issues.apache.org/jira/browse/DERBY-1543 Project: Derby Issue Type: Sub-task Components: SQL Affects Versions: 10.2.0.0 Reporter: Satheesh Bandaram Assigned To: Satheesh Bandaram Fix For: 10.2.0.0 Attachments: DERBY-1543.out, Derby1543.diff Address two remaining sub-tasks: Add warning when sqlAuthorization is on with authentication off: This is a precautionary warning, since sqlAuthorization doesn't mean much without authentication being ON. Drop permission descriptors when objects they cover are dropped: This applies to tables, routines. When these objects are dropped, automatically drop their permisson descriptors. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
Disabling non-SELECT privileges for VIEWs ...
I noticed Derby currently allows granting non-SELECT privileges to VIEWs. I will checks to disable this and add some tests. Satheesh ij create view myView as select * from newTab; 0 rows inserted/updated/deleted ij grant *insert *on myView to Sammy; 0 rows inserted/updated/deleted ij grant *delete *on myView to sammy; 0 rows inserted/updated/deleted ij grant *references *on myView to sammy; 0 rows inserted/updated/deleted
[jira] Commented: (DERBY-1543) Address two remaining issues with GRANT/REVOKE functionality: 1) Add warning when sqlAuthorization is on with authentication off 2) Drop permission descriptors when obje
[ http://issues.apache.org/jira/browse/DERBY-1543?page=comments#action_12423787 ] Satheesh Bandaram commented on DERBY-1543: -- Yes, I was refering to Derby's handling of synonyms for GRANT/REVOKE. It was debated early on whether or how to handle synonyms for authorization work and was decided synonyms in Derby will be handled later. Here is the start of that thread: (http://www.nabble.com/Grant-and-Revoke%2C-Part-I-...-DERBY-464...-p1759712.html) May be I should mention this in the spec as well. Two more items to add to spec! Address two remaining issues with GRANT/REVOKE functionality: 1) Add warning when sqlAuthorization is on with authentication off 2) Drop permission descriptors when objects they cover are dropped. Key: DERBY-1543 URL: http://issues.apache.org/jira/browse/DERBY-1543 Project: Derby Issue Type: Sub-task Components: SQL Affects Versions: 10.2.0.0 Reporter: Satheesh Bandaram Assigned To: Satheesh Bandaram Fix For: 10.2.0.0 Attachments: DERBY-1543.out Address two remaining sub-tasks: Add warning when sqlAuthorization is on with authentication off: This is a precautionary warning, since sqlAuthorization doesn't mean much without authentication being ON. Drop permission descriptors when objects they cover are dropped: This applies to tables, routines. When these objects are dropped, automatically drop their permisson descriptors. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
Re: Question on Grant revoke functional spec
Hi Mamta, While it is OK for 'mamta2' to create the view, in your example, attempts to GRANT access to other users on view 'v1' should raise an error. This is because user 'mamta2' has been granted SELECT privilege and this user is attempting to allow other users to SELECT from table created by 'mamta1'. User 'mamta1' only granted SELECT to 'mamta2' and without WITH GRANT OPTION, 'mamta2' can't allow other users access to the view. If the grant statement to allow SELECT privilege where to be issued WITH GRANT OPTION, then it would have been possible for 'mamta2' to GRANT access to view 'v1' here. Derby currently doesn't support WITH GRANT OPTION. So, while it is OK for 'mamta2' here to create the view, an error *should be* raised if this user attempts to grant access to columns derived from user 'mamta1' table. So, while the spirit of the statement you high-lighted is correct, actual text below leads to think an error would be raised during create view. The error would actually be raised when 'Jane' tries to GRANT access to any user or even PUBLIC. (Since only owners of a table can actually grant that privilege in Derby currently) Not sure if your view authorization work implemented this. If not, it probably should be addressed... otherwise it would be very easy to export any privileges any user receives to others. This should not be possible in Derby until WITH GRANT OPTION is implemented. Satheesh Mamta Satoor wrote: Hi, I was going through the grant revoke functional spec which is attached to DERBY-464 and was puzzled by following example in there CREATE VIEW s.v(vc1,vc2,vc3) AS SELECT t1.c1,t1.c2,f(t1.c3) FROM t1 JOIN t2 ON t1.c1 = t2.c1 WHERE t2.c2 = 5 Jane needs the following permissions in order to create the view: * ownership of schema s (so that she can create something in it), * _*ownership of table t1 (so that she can allow others to see columns in it), *_ * select permission on t2.c1 and t2.c1, and * execute permission on f. The example says that Jane needs ownerhsip of table t1 so she can allow others to see columns in it. Is that right? Currently, in Derby SQL Authorization mode, I can create a view based on a table in some other schema as long as I have the select privilege on it. Following is the ij session showing that behavior $ java -Dderby.database.sqlAuthorization=true -Dij.exceptionTrace=true org.apache.derby.tools.ij ij version 10.2 ij connect 'jdbc:derby:c:/dellater/dbmaintest2;create=true' user 'mamta1' as mamta1; ij create table t1(c11 int); 0 rows inserted/updated/deleted ij grant select on t1 to mamta2; 0 rows inserted/updated/deleted ij connect 'jdbc:derby:c:/dellater/dbmaintest2;create=true' user 'mamta2' as mamta2; WARNING 01J01: Database 'c:/dellater/dbmaintest2' not created, connection made to existing database instead. ij(MAMTA2)create view v1 as select * from mamta1.t1; 0 rows inserted/updated/deleted ij(MAMTA2) thanks, Mamta
Re: Availability of grant/revoke for upgraded databases is not dependent on derby.database.sqlAuthorization property *during* upgrade ?
Deepa Remesh wrote: Thanks for clarifying this Satheesh. I tried this by setting it as a database property for the upgraded database. However, grant/revoke does not work right away. It works after I shutdown/reconnect to the database. Is this the expected behaviour for this property? If so, it may be good to add this to the spec and documentation. Right... sqlAuthorization property is a static property so it needs a database recycle to take effect. Dynamic properties take effect immediately. Static/dynamic properties are discussed more here, just FYI. http://db.apache.org/derby/docs/10.1/tuning/ctunsetprop824615.html I will include this in the spec update. Satheesh
[jira] Commented: (DERBY-1592) Update statement is allowed to execute even though the column that the statement access has been revoked.
[ http://issues.apache.org/jira/browse/DERBY-1592?page=comments#action_12423680 ] Satheesh Bandaram commented on DERBY-1592: -- This functionality is not supported. If a table level privilege has been granted, revoking part of that privilege (say from one or two columns only) is not supported. That is why revoke did not do anything (though would have been nice to have raised a message... Dan has already filed a defect for that). May be this should be clarified in the functional spec. Looking at DB2 manuals, it doesn't look like DB2 can do that too. (ftp://ftp.software.ibm.com/ps/products/db2/info/vr82/pdf/en_US/db2s2e81.pdf) Update statement is allowed to execute even though the column that the statement access has been revoked. - Key: DERBY-1592 URL: http://issues.apache.org/jira/browse/DERBY-1592 Project: Derby Issue Type: Bug Components: SQL Affects Versions: 10.2.0.0 Environment: Sun JDK 142 Reporter: Yip Ng Update statement is allowed to execute even though the column that the statement access has been revoked. The same applies for a select statement. Example shown below: user2 is able to execute the update statement but update privilege on c2 has already been revoked. ij version 10.2 ij connect 'jdbc:derby:authtest;create=true' user 'user1' as user1; ij create table t1 (c1 int, c2 int); 0 rows inserted/updated/deleted ij insert into t1 values (1,1); 1 row inserted/updated/deleted ij grant select,update,insert on t1 to user2; 0 rows inserted/updated/deleted ij select * from sys.systableperms; TABLEPERMSID|GRANTEE |GRANTOR |TABLEID || --- 67d0407f-010c-aa11-4d39-00101010|USER2 |USER1 |2753c07b-010c-aa11-4d39-00101010|y|N|y|y|N|N 1 row selected ij select * from sys.syscolperms; COLPERMSID |GRANTEE |GRANTOR |TABLEID ||COLUMNS - 0 rows selected ij revoke update (c2) on t1 from user2; 0 rows inserted/updated/deleted ij select * from sys.systableperms; TABLEPERMSID|GRANTEE |GRANTOR |TABLEID || --- 67d0407f-010c-aa11-4d39-00101010|USER2 |USER1 |2753c07b-010c-aa11-4d39-00101010|y|N|y|y|N|N 1 row selected ij select * from sys.syscolperms; COLPERMSID |GRANTEE
[jira] Commented: (DERBY-1544) Address remaining upgrade task(s) to complete full upgrade mechanism for GRANT/REVOKE, specifically with changing database owner name from 'DBA' to authorizationId of us
[ http://issues.apache.org/jira/browse/DERBY-1544?page=comments#action_12423717 ] Satheesh Bandaram commented on DERBY-1544: -- Changes to update database owner is already present in Derby upgrade process it seems there is a defect that is either not committing this transaction during upgrade or starting transaction wrongly. Stepping through the code sometime ago, noticed upgrade does invoke this internal mechanism to update 'DBA' as the owner of system schemas to authorizationID of the invoker of upgrade, but probably because of wrong internal transaction semantics, the change doesn't seem to get committed. For the second part of the sub-task, full upgrade needs to add 5 routine perm descriptors that allow GRANTing of EXECUTE privilege to 5 system routines that by default have execute access. This code needs to be added, much like code that adds 5 system routine permission descriptors during a NEW database creation. Address remaining upgrade task(s) to complete full upgrade mechanism for GRANT/REVOKE, specifically with changing database owner name from 'DBA' to authorizationId of user invoking upgrade. - Key: DERBY-1544 URL: http://issues.apache.org/jira/browse/DERBY-1544 Project: Derby Issue Type: Sub-task Components: SQL Affects Versions: 10.2.0.0 Environment: generic Reporter: Satheesh Bandaram Fix For: 10.2.0.0 Upgrading a database from 10.1 to 10.2 should automatically change database owner, recorded as owner of system schemas in sysschemas, from pseudo user 'DBA' to authorizationID of the user attempting upgrade. Another upgrade change I am thinking about is to grant execute privilege to 5 system routines that by default have execute privilege to public when a new database is created. Five system routines, two compress routines and three statistics related routines are given execute privilege to public when a new 10.2 database is created. This is not done when a 10.1 database is upgraded to 10.2 and probably good to include these privileges during database upgrade. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1521) Upgrade test needs to be enhanced to test grant revoke
[ http://issues.apache.org/jira/browse/DERBY-1521?page=comments#action_12423744 ] Satheesh Bandaram commented on DERBY-1521: -- I seem to have missed one of Dan's questions about GRANT REVOKE in this entry: * Add basic tests to test grant/revoke feature works when upgraded with derby.database.sqlAuthorization set to true - owner of system schemas is changed from pseudo user 'DBA' to the authorizationID of the user invoking upgrade - Does this step happen regardless of the value of the property derby.database.sqlAuthorization? Yes... Current code is implemented such that authorizationID of the owner is recorded in system catalog for system schemas regardless of the value of property derby.database.sqlAuthorization. Updating system schema owner name during upgrade would facilitate switching over to SQL authorization mode later. In Derby authorization mode, this value is not used anyway. Upgrade test needs to be enhanced to test grant revoke -- Key: DERBY-1521 URL: http://issues.apache.org/jira/browse/DERBY-1521 Project: Derby Issue Type: Improvement Components: Test Affects Versions: 10.2.0.0 Reporter: Mamta A. Satoor Assigned To: Deepa Remesh Fix For: 10.2.0.0 Attachments: d1521-patch1-v1.diff, d1521-patch1-v1.status Grant Revoke is one of the features targeted for 10.2 Release. The upgrade test should be modified to test this feature with various upgrade scenarios to make sure everything works fine. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Resolved: (DERBY-1592) Update statement is allowed to execute even though the column that the statement access has been revoked.
[ http://issues.apache.org/jira/browse/DERBY-1592?page=all ] Satheesh Bandaram resolved DERBY-1592. -- Resolution: Invalid Reporter of this defect agreed this is expected behavior. I will update functional spec to cover this issue, so it becomes clearer. Update statement is allowed to execute even though the column that the statement access has been revoked. - Key: DERBY-1592 URL: http://issues.apache.org/jira/browse/DERBY-1592 Project: Derby Issue Type: Bug Components: SQL Affects Versions: 10.2.0.0 Environment: Sun JDK 142 Reporter: Yip Ng Update statement is allowed to execute even though the column that the statement access has been revoked. The same applies for a select statement. Example shown below: user2 is able to execute the update statement but update privilege on c2 has already been revoked. ij version 10.2 ij connect 'jdbc:derby:authtest;create=true' user 'user1' as user1; ij create table t1 (c1 int, c2 int); 0 rows inserted/updated/deleted ij insert into t1 values (1,1); 1 row inserted/updated/deleted ij grant select,update,insert on t1 to user2; 0 rows inserted/updated/deleted ij select * from sys.systableperms; TABLEPERMSID|GRANTEE |GRANTOR |TABLEID || --- 67d0407f-010c-aa11-4d39-00101010|USER2 |USER1 |2753c07b-010c-aa11-4d39-00101010|y|N|y|y|N|N 1 row selected ij select * from sys.syscolperms; COLPERMSID |GRANTEE |GRANTOR |TABLEID ||COLUMNS - 0 rows selected ij revoke update (c2) on t1 from user2; 0 rows inserted/updated/deleted ij select * from sys.systableperms; TABLEPERMSID|GRANTEE |GRANTOR |TABLEID || --- 67d0407f-010c-aa11-4d39-00101010|USER2 |USER1 |2753c07b-010c-aa11-4d39-00101010|y|N|y|y|N|N 1 row selected ij select * from sys.syscolperms; COLPERMSID |GRANTEE |GRANTOR |TABLEID ||COLUMNS
[jira] Closed: (DERBY-1592) Update statement is allowed to execute even though the column that the statement access has been revoked.
[ http://issues.apache.org/jira/browse/DERBY-1592?page=all ] Satheesh Bandaram closed DERBY-1592. Assignee: Satheesh Bandaram Update statement is allowed to execute even though the column that the statement access has been revoked. - Key: DERBY-1592 URL: http://issues.apache.org/jira/browse/DERBY-1592 Project: Derby Issue Type: Bug Components: SQL Affects Versions: 10.2.0.0 Environment: Sun JDK 142 Reporter: Yip Ng Assigned To: Satheesh Bandaram Update statement is allowed to execute even though the column that the statement access has been revoked. The same applies for a select statement. Example shown below: user2 is able to execute the update statement but update privilege on c2 has already been revoked. ij version 10.2 ij connect 'jdbc:derby:authtest;create=true' user 'user1' as user1; ij create table t1 (c1 int, c2 int); 0 rows inserted/updated/deleted ij insert into t1 values (1,1); 1 row inserted/updated/deleted ij grant select,update,insert on t1 to user2; 0 rows inserted/updated/deleted ij select * from sys.systableperms; TABLEPERMSID|GRANTEE |GRANTOR |TABLEID || --- 67d0407f-010c-aa11-4d39-00101010|USER2 |USER1 |2753c07b-010c-aa11-4d39-00101010|y|N|y|y|N|N 1 row selected ij select * from sys.syscolperms; COLPERMSID |GRANTEE |GRANTOR |TABLEID ||COLUMNS - 0 rows selected ij revoke update (c2) on t1 from user2; 0 rows inserted/updated/deleted ij select * from sys.systableperms; TABLEPERMSID|GRANTEE |GRANTOR |TABLEID || --- 67d0407f-010c-aa11-4d39-00101010|USER2 |USER1 |2753c07b-010c-aa11-4d39-00101010|y|N|y|y|N|N 1 row selected ij select * from sys.syscolperms; COLPERMSID |GRANTEE |GRANTOR |TABLEID ||COLUMNS
[jira] Commented: (DERBY-1543) Address two remaining issues with GRANT/REVOKE functionality: 1) Add warning when sqlAuthorization is on with authentication off 2) Drop permission descriptors when obje
[ http://issues.apache.org/jira/browse/DERBY-1543?page=comments#action_12423757 ] Satheesh Bandaram commented on DERBY-1543: -- What is the failure you are seeing with synonyms? Attempting to GRANT or REVOKE privileges to synonyms should fail. Synonyms are supposed to be used only in DML statements and not DDL statements. For example, attempting to create an index on synonym, expecting an index to be created on target of the synonym, should fail with error. However, DML statements are supposed to resolve synonyms to their target tables or views. Address two remaining issues with GRANT/REVOKE functionality: 1) Add warning when sqlAuthorization is on with authentication off 2) Drop permission descriptors when objects they cover are dropped. Key: DERBY-1543 URL: http://issues.apache.org/jira/browse/DERBY-1543 Project: Derby Issue Type: Sub-task Components: SQL Affects Versions: 10.2.0.0 Reporter: Satheesh Bandaram Assigned To: Satheesh Bandaram Fix For: 10.2.0.0 Attachments: DERBY-1543.out Address two remaining sub-tasks: Add warning when sqlAuthorization is on with authentication off: This is a precautionary warning, since sqlAuthorization doesn't mean much without authentication being ON. Drop permission descriptors when objects they cover are dropped: This applies to tables, routines. When these objects are dropped, automatically drop their permisson descriptors. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
Grant Revoke notes ....
Thanks for creating this GrantRevoke notes, Dan. Here are some comments on some of your points. http://wiki.apache.org/db-derby/GrantRevokeImplementation 1) PrivilegeInfo looks very much like a constant action, the constant action for grant/revoke is more or less just an empty object that calls the execute method on PrivilegeInfo. Should this code be re-factored to have all the logic in a constant action, to match the pattern of other DDL statements. PrivilegeInfo and GrantRevokeConstantAction is modeled much like constraints are handled In both cases one parent statement could result in multiple different database objects being created. But this two levels is more needed for constraints as they can be created in multiple ways, than for privileges. It should be possible to refactor, but a GRANT or REVOKE action could be dealing with number of different types of privileges and already deals with two now. (TablePrivilegeInfo and RoutinePrivilegeInfo) It is likely that GRANT and REVOKE would deal with more types of privileges in the future and this design is more object oriented. But does result in extra classes, so we have to decide if overhead is worth it or not. 2) PermissionsDescriptor object passed in is reset each time with a different grantee - does this match the intended purpose of such descriptors which are TupleDescriptors and match a single row in the database? This can be changed.. probably should be. 3) Too many ways of representing permissions, PrivilegeInfo, StatementPermission, PermissionDescriptor, UUID. Possible solution PermissionDescriptors are more like TableDescriptors... represent a granted privilege present in system tables. Currently there are TablePermDescriptors, RoutinePermDescriptors and ColPermDescriptors, representing each of three new system catalogs. (RequiredPermDescriptor would go away) StatementPermission represents required database object access that need to be verified for permission at runtime. Note that these access descriptors could be for objects that don't have PermissionDescriptors... like StatementSchemaPermission object and also don't have fields like grantor/grantee/grantWithGrant etc. There is no 1-1 mapping in their purpose, I think, but some refactoring may be possible. Result could turn out be as messier if not more, but might reduce some footprint. 4) Items about HashMaps/HashSet... Possibly some improvement can be made here, but nothing seems incorrect. Hashing is done to make sure only one StatementPermission object is created for a given type of access on same database object. Otherwise, we could create multiple access checks and perform same privilege checking multiple times. Guess the *main *concern is multiple objects being created and see if they can be refactored, right? Satheesh
[jira] Updated: (DERBY-1543) Address two remaining issues with GRANT/REVOKE functionality: 1) Add warning when sqlAuthorization is on with authentication off 2) Drop permission descriptors when object
[ http://issues.apache.org/jira/browse/DERBY-1543?page=all ] Satheesh Bandaram updated DERBY-1543: - Attachment: DERBY-1543.out This (temp) patch addresses both these remaining issues. Some work is needed to complete this patch... including 1) Extending droping of descriptors to cover routine perms. Covers Table and Column permission descriptors for now 2) Adding test cases 3) Improve warning mechanism. Any thoughts on when the Warning should be raised if authorization is ON without authentication? Should it be on every new connection creation or only at the boot time? Is there a concern that raising this warning might actually alert users about lack of authentication? I will submit complete patch in a day or two. Address two remaining issues with GRANT/REVOKE functionality: 1) Add warning when sqlAuthorization is on with authentication off 2) Drop permission descriptors when objects they cover are dropped. Key: DERBY-1543 URL: http://issues.apache.org/jira/browse/DERBY-1543 Project: Derby Issue Type: Sub-task Components: SQL Affects Versions: 10.2.0.0 Reporter: Satheesh Bandaram Fix For: 10.2.0.0 Attachments: DERBY-1543.out Address two remaining sub-tasks: Add warning when sqlAuthorization is on with authentication off: This is a precautionary warning, since sqlAuthorization doesn't mean much without authentication being ON. Drop permission descriptors when objects they cover are dropped: This applies to tables, routines. When these objects are dropped, automatically drop their permisson descriptors. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Assigned: (DERBY-1538) Unexpected behavior on self privilege revocation
[ http://issues.apache.org/jira/browse/DERBY-1538?page=all ] Satheesh Bandaram reassigned DERBY-1538: Assignee: Satheesh Bandaram Unexpected behavior on self privilege revocation Key: DERBY-1538 URL: http://issues.apache.org/jira/browse/DERBY-1538 Project: Derby Issue Type: Bug Components: SQL Affects Versions: 10.2.0.0 Environment: Windows XP Pro Reporter: Yip Ng Assigned To: Satheesh Bandaram When revoking the owner's own privilege against the table he have created, Derby executes the revocation successfully but the owner is able to select from the table later as if though the REVOKE statement has no effect. More importantly, I was expecting a SQLException with the appropriate SQLSTATE to be thrown when the owner attempts to revoke privilege from himself. i.e.: ij connect 'jdbc:derby:authtest' user 'yip' as conn1; ij create table t1 (c1 int); 0 rows inserted/updated/deleted ij insert into t1 values 1,2,3; 3 rows inserted/updated/deleted ij revoke select on t1 from yip; 0 rows inserted/updated/deleted ij select * from t1; C1 --- 1 2 3 3 rows selected Here is the sysinfo: -- Java Information -- Java Version:1.4.2_12 Java Vendor: Sun Microsystems Inc. Java home: C:\jdk142\jre Java classpath: derby.jar;derbytools.jar;. OS name: Windows XP OS architecture: x86 OS version: 5.1 Java user name: yip Java user home: C:\Documents and Settings\Administrator Java user dir: C:\derby\trunk\jars\sane java.specification.name: Java Platform API Specification java.specification.version: 1.4 - Derby Information JRE - JDBC: J2SE 1.4.2 - JDBC 3.0 [C:\derby\trunk\jars\sane\derby.jar] 10.2.0.4 alpha - (423353) [C:\derby\trunk\jars\sane\derbytools.jar] 10.2.0.4 alpha - (423353) -- - Locale Information - Current Locale : [English/United States [en_US]] Found support for locale: [de_DE] version: 10.2.0.4 alpha - (423353) Found support for locale: [es] version: 10.2.0.4 alpha - (423353) Found support for locale: [fr] version: 10.2.0.4 alpha - (423353) Found support for locale: [it] version: 10.2.0.4 alpha - (423353) Found support for locale: [ja_JP] version: 10.2.0.4 alpha - (423353) Found support for locale: [ko_KR] version: 10.2.0.4 alpha - (423353) Found support for locale: [pt_BR] version: 10.2.0.4 alpha - (423353) Found support for locale: [zh_CN] version: 10.2.0.4 alpha - (423353) Found support for locale: [zh_TW] version: 10.2.0.4 alpha - (423353) -- -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Resolved: (DERBY-1532) Add GRANTOR column to primary key for SYSTABLEPERMS, SYSCOLPERMS and SYSROUTINEPERMS
[ http://issues.apache.org/jira/browse/DERBY-1532?page=all ] Satheesh Bandaram resolved DERBY-1532. -- Resolution: Invalid As mentioned, GRANTOR is already part of the primary key in all three new system catalogs. Information in the functional spec was incorrect and I am fixing that right now. Add GRANTOR column to primary key for SYSTABLEPERMS, SYSCOLPERMS and SYSROUTINEPERMS Key: DERBY-1532 URL: http://issues.apache.org/jira/browse/DERBY-1532 Project: Derby Issue Type: Sub-task Components: SQL Reporter: Daniel John Debrunner Assigned To: Satheesh Bandaram Fix For: 10.2.0.0 -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Closed: (DERBY-1532) Add GRANTOR column to primary key for SYSTABLEPERMS, SYSCOLPERMS and SYSROUTINEPERMS
[ http://issues.apache.org/jira/browse/DERBY-1532?page=all ] Satheesh Bandaram closed DERBY-1532. Add GRANTOR column to primary key for SYSTABLEPERMS, SYSCOLPERMS and SYSROUTINEPERMS Key: DERBY-1532 URL: http://issues.apache.org/jira/browse/DERBY-1532 Project: Derby Issue Type: Sub-task Components: SQL Reporter: Daniel John Debrunner Assigned To: Satheesh Bandaram Fix For: 10.2.0.0 -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-464) Enhance Derby by adding grant/revoke support. Grant/Revoke provide finner level of privileges than currently provided by Derby that is especially useful in network configur
[ http://issues.apache.org/jira/browse/DERBY-464?page=all ] Satheesh Bandaram updated DERBY-464: Attachment: grantRevokeSpec_v3.html Updated functional spec... Changes from v2 version are: 1) Added GRANTOR column to primary key to new system tables. Removed SYSREQUIREDPERMS system table. System table schema still needs more updates to reflect Mamta's work on adding UUID column to system tables. 2) Added a note saying revoking owners own privileges raises an error. Enhance Derby by adding grant/revoke support. Grant/Revoke provide finner level of privileges than currently provided by Derby that is especially useful in network configurations. --- Key: DERBY-464 URL: http://issues.apache.org/jira/browse/DERBY-464 Project: Derby Issue Type: New Feature Components: SQL Affects Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0 Environment: generic Reporter: Satheesh Bandaram Assigned To: Satheesh Bandaram Attachments: changeDescriptionPartII, grantRevoke.patch.Dec5, grantRevoke.stat.Dec5, GrantRevokePartII.stat, GrantRevokePartII.txt, GrantRevokePartII.txt, grantRevokeSpec.html, grantRevokeSpec_v2.html, grantRevokeSpec_v3.html, Privileges.java, Privileges2.java Derby currently provides a very simple permissions scheme, which is quite suitable for an embedded database system. End users of embedded Derby do not see Derby directly; they talk to a application that embeds Derby. So Derby left most of the access control work to the application. Under this scheme, Derby limits access on a per database or per system basis. A user can be granted full, read-only, or no access. This is less suitable in a general purpose SQL server. When end users or diverse applications can issue SQL commands directly against the database, Derby must provide more precise mechanisms to limit who can do what with the database. I propose to enhance Derby by implementing a subset of grant/revoke capabilities as specified by the SQL standard. I envision this work to involve the following tasks, at least: 1) Develop a specification of what capabilities I would like to add to Derby. 2) Provide a high level implementation scheme. 3) Pursue a staged development plan, with support for DDL added to Derby first. 4) Add support for runtime checking of these privileges. 5) Address migration and upgrade issues from previous releases and from old scheme to newer database. Since I think this is a large task, I would like to invite any interested people to work with me on this large and important enhancement to Derby. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-464) Enhance Derby by adding grant/revoke support. Grant/Revoke provide finner level of privileges than currently provided by Derby that is especially useful in network config
[ http://issues.apache.org/jira/browse/DERBY-464?page=comments#action_12423197 ] Satheesh Bandaram commented on DERBY-464: - Should this issue be marked as RESOLVED? This JIRA entry covers main GRANT/REVOKE functionality being developed. While there are a few sub-tasks still open, I don't see any active work remaining or being done under this entry. Enhance Derby by adding grant/revoke support. Grant/Revoke provide finner level of privileges than currently provided by Derby that is especially useful in network configurations. --- Key: DERBY-464 URL: http://issues.apache.org/jira/browse/DERBY-464 Project: Derby Issue Type: New Feature Components: SQL Affects Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0 Environment: generic Reporter: Satheesh Bandaram Assigned To: Satheesh Bandaram Fix For: 10.2.0.0 Attachments: changeDescriptionPartII, grantRevoke.patch.Dec5, grantRevoke.stat.Dec5, GrantRevokePartII.stat, GrantRevokePartII.txt, GrantRevokePartII.txt, grantRevokeSpec.html, grantRevokeSpec_v2.html, grantRevokeSpec_v3.html, Privileges.java, Privileges2.java Derby currently provides a very simple permissions scheme, which is quite suitable for an embedded database system. End users of embedded Derby do not see Derby directly; they talk to a application that embeds Derby. So Derby left most of the access control work to the application. Under this scheme, Derby limits access on a per database or per system basis. A user can be granted full, read-only, or no access. This is less suitable in a general purpose SQL server. When end users or diverse applications can issue SQL commands directly against the database, Derby must provide more precise mechanisms to limit who can do what with the database. I propose to enhance Derby by implementing a subset of grant/revoke capabilities as specified by the SQL standard. I envision this work to involve the following tasks, at least: 1) Develop a specification of what capabilities I would like to add to Derby. 2) Provide a high level implementation scheme. 3) Pursue a staged development plan, with support for DDL added to Derby first. 4) Add support for runtime checking of these privileges. 5) Address migration and upgrade issues from previous releases and from old scheme to newer database. Since I think this is a large task, I would like to invite any interested people to work with me on this large and important enhancement to Derby. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-464) Enhance Derby by adding grant/revoke support. Grant/Revoke provide finner level of privileges than currently provided by Derby that is especially useful in network configur
[ http://issues.apache.org/jira/browse/DERBY-464?page=all ] Satheesh Bandaram updated DERBY-464: Fix Version/s: 10.2.0.0 Enhance Derby by adding grant/revoke support. Grant/Revoke provide finner level of privileges than currently provided by Derby that is especially useful in network configurations. --- Key: DERBY-464 URL: http://issues.apache.org/jira/browse/DERBY-464 Project: Derby Issue Type: New Feature Components: SQL Affects Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0 Environment: generic Reporter: Satheesh Bandaram Assigned To: Satheesh Bandaram Fix For: 10.2.0.0 Attachments: changeDescriptionPartII, grantRevoke.patch.Dec5, grantRevoke.stat.Dec5, GrantRevokePartII.stat, GrantRevokePartII.txt, GrantRevokePartII.txt, grantRevokeSpec.html, grantRevokeSpec_v2.html, grantRevokeSpec_v3.html, Privileges.java, Privileges2.java Derby currently provides a very simple permissions scheme, which is quite suitable for an embedded database system. End users of embedded Derby do not see Derby directly; they talk to a application that embeds Derby. So Derby left most of the access control work to the application. Under this scheme, Derby limits access on a per database or per system basis. A user can be granted full, read-only, or no access. This is less suitable in a general purpose SQL server. When end users or diverse applications can issue SQL commands directly against the database, Derby must provide more precise mechanisms to limit who can do what with the database. I propose to enhance Derby by implementing a subset of grant/revoke capabilities as specified by the SQL standard. I envision this work to involve the following tasks, at least: 1) Develop a specification of what capabilities I would like to add to Derby. 2) Provide a high level implementation scheme. 3) Pursue a staged development plan, with support for DDL added to Derby first. 4) Add support for runtime checking of these privileges. 5) Address migration and upgrade issues from previous releases and from old scheme to newer database. Since I think this is a large task, I would like to invite any interested people to work with me on this large and important enhancement to Derby. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1543) Address two remaining issues with GRANT/REVOKE functionality: 1) Add warning when sqlAuthorization is on with authentication off 2) Drop permission descriptors when obje
[ http://issues.apache.org/jira/browse/DERBY-1543?page=comments#action_12423198 ] Satheesh Bandaram commented on DERBY-1543: -- Good point, Dan. I will address this in my final version of the patch. Address two remaining issues with GRANT/REVOKE functionality: 1) Add warning when sqlAuthorization is on with authentication off 2) Drop permission descriptors when objects they cover are dropped. Key: DERBY-1543 URL: http://issues.apache.org/jira/browse/DERBY-1543 Project: Derby Issue Type: Sub-task Components: SQL Affects Versions: 10.2.0.0 Reporter: Satheesh Bandaram Fix For: 10.2.0.0 Attachments: DERBY-1543.out Address two remaining sub-tasks: Add warning when sqlAuthorization is on with authentication off: This is a precautionary warning, since sqlAuthorization doesn't mean much without authentication being ON. Drop permission descriptors when objects they cover are dropped: This applies to tables, routines. When these objects are dropped, automatically drop their permisson descriptors. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Resolved: (DERBY-1023) Add EXTERNAL SECURITY DEFINER and EXTERNAL SECURITY INVOKER support for routines(functions or procedures)
[ http://issues.apache.org/jira/browse/DERBY-1023?page=all ] Satheesh Bandaram resolved DERBY-1023. -- Resolution: Won't Fix Not part of the functional spec being targetted for 10.2 release. Might still be useful enhancement to Derby. If someone has itch to implement, please file a new JIRA entry.. not a sub-task of DERBY-464. Add EXTERNAL SECURITY DEFINER and EXTERNAL SECURITY INVOKER support for routines(functions or procedures) -- Key: DERBY-1023 URL: http://issues.apache.org/jira/browse/DERBY-1023 Project: Derby Issue Type: Sub-task Components: SQL Reporter: Mamta A. Satoor Fix For: 10.2.0.0 Derby parser supports EXTERNAL SECURITY DEFINER and EXTERNAL SECURITY INVOKER on a routine (function or procedure) but this information doesn't get saved anywhere. eg from lang/grantRevoke.sql test CREATE PROCEDURE AUTH_TEST.addUserUtility(IN userName VARCHAR(50), IN permission VARCHAR(22)) LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL SECURITY INVOKER EXTERNAL NAME 'org.apache.derby.database.UserUtility.add '; Finish up this functionality by doing necessary work required during compile, execute and upgrade times. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Closed: (DERBY-1023) Add EXTERNAL SECURITY DEFINER and EXTERNAL SECURITY INVOKER support for routines(functions or procedures)
[ http://issues.apache.org/jira/browse/DERBY-1023?page=all ] Satheesh Bandaram closed DERBY-1023. Add EXTERNAL SECURITY DEFINER and EXTERNAL SECURITY INVOKER support for routines(functions or procedures) -- Key: DERBY-1023 URL: http://issues.apache.org/jira/browse/DERBY-1023 Project: Derby Issue Type: Sub-task Components: SQL Reporter: Mamta A. Satoor Fix For: 10.2.0.0 Derby parser supports EXTERNAL SECURITY DEFINER and EXTERNAL SECURITY INVOKER on a routine (function or procedure) but this information doesn't get saved anywhere. eg from lang/grantRevoke.sql test CREATE PROCEDURE AUTH_TEST.addUserUtility(IN userName VARCHAR(50), IN permission VARCHAR(22)) LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL SECURITY INVOKER EXTERNAL NAME 'org.apache.derby.database.UserUtility.add '; Finish up this functionality by doing necessary work required during compile, execute and upgrade times. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Assigned: (DERBY-1543) Address two remaining issues with GRANT/REVOKE functionality: 1) Add warning when sqlAuthorization is on with authentication off 2) Drop permission descriptors when objec
[ http://issues.apache.org/jira/browse/DERBY-1543?page=all ] Satheesh Bandaram reassigned DERBY-1543: Assignee: Satheesh Bandaram I have a patch that is partial. Will post final complete patch soon. Address two remaining issues with GRANT/REVOKE functionality: 1) Add warning when sqlAuthorization is on with authentication off 2) Drop permission descriptors when objects they cover are dropped. Key: DERBY-1543 URL: http://issues.apache.org/jira/browse/DERBY-1543 Project: Derby Issue Type: Sub-task Components: SQL Affects Versions: 10.2.0.0 Reporter: Satheesh Bandaram Assigned To: Satheesh Bandaram Fix For: 10.2.0.0 Attachments: DERBY-1543.out Address two remaining sub-tasks: Add warning when sqlAuthorization is on with authentication off: This is a precautionary warning, since sqlAuthorization doesn't mean much without authentication being ON. Drop permission descriptors when objects they cover are dropped: This applies to tables, routines. When these objects are dropped, automatically drop their permisson descriptors. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Assigned: (DERBY-1532) Add GRANTOR column to primary key for SYSTABLEPERMS, SYSCOLPERMS and SYSROUTINEPERMS
[ http://issues.apache.org/jira/browse/DERBY-1532?page=all ] Satheesh Bandaram reassigned DERBY-1532: Assignee: Satheesh Bandaram Add GRANTOR column to primary key for SYSTABLEPERMS, SYSCOLPERMS and SYSROUTINEPERMS Key: DERBY-1532 URL: http://issues.apache.org/jira/browse/DERBY-1532 Project: Derby Issue Type: Sub-task Components: SQL Reporter: Daniel John Debrunner Assigned To: Satheesh Bandaram Fix For: 10.2.0.0 -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1532) Add GRANTOR column to primary key for SYSTABLEPERMS, SYSCOLPERMS and SYSROUTINEPERMS
[ http://issues.apache.org/jira/browse/DERBY-1532?page=comments#action_12422934 ] Satheesh Bandaram commented on DERBY-1532: -- I was trying to address this issue, but I found that system tables in question already have GRANTOR in the primary key. Functional spec needs updating. Here is what I tried: Note that column '3' is the GRANTOR column in all three system tables. ij select cast(descriptor as char(50)) from sys.sysconglomerates i where conglomerateid='c065801d-0103-0e39-b8e7-0010f010'; 1 -- UNIQUE BTREE (2, 4, 3) ij select cast(descriptor as char(50)) from sys.sysconglomerates i where conglomerateid='787c0020-0103-0e39-b8e7-0010f010' ; 1 -- UNIQUE BTREE (2, 4, 5, 3) ij select cast(descriptor as char(50)) from sys.sysconglomerates i where conglomerateid='c851401a-0103-0e39-b8e7-0010f010'; 1 -- UNIQUE BTREE (2, 4, 3) ij select columnnumber, cast(columnname as char(10)) columnname from sys.syscolumns c, sys.systables t where c.referenceid=t.tableid and t.tablename='SYSCOLPERMS'; COLUMNNUMB|COLUMNNAME -- 1 |COLPERMSID 6 |COLUMNS 2 |GRANTEE 3 |GRANTOR 4 |TABLEID 5 |TYPE 6 rows selected ij select columnnumber, cast(columnname as char(10)) columnname from sys.syscolumns c, sys.systables t where c.referenceid=t.tableid and t.tablename='SYSTABLEPERMS' order by 1; COLUMNNUMB|COLUMNNAME -- 1 |TABLEPERMS 2 |GRANTEE 3 |GRANTOR 4 |TABLEID 5 |SELECTPRIV 6 |DELETEPRIV 7 |INSERTPRIV 8 |UPDATEPRIV 9 |REFERENCES 10 |TRIGGERPRI ij select columnnumber, cast(columnname as char(10)) columnname from sys.syscolumns c, sys.systables t where c.referenceid=t.tableid and t.tablename='SYSROUTINEPERMS' order by 1; COLUMNNUMB|COLUMNNAME -- 1 |ROUTINEPER 2 |GRANTEE 3 |GRANTOR=== 4 |ALIASID 5 |GRANTOPTIO I will update functional spec to show correct primary key and also mention that Derby disallows revoking privileges from object owners. Add GRANTOR column to primary key for SYSTABLEPERMS, SYSCOLPERMS and SYSROUTINEPERMS Key: DERBY-1532 URL: http://issues.apache.org/jira/browse/DERBY-1532 Project: Derby Issue Type: Sub-task Components: SQL Reporter: Daniel John Debrunner Assigned To: Satheesh Bandaram Fix For: 10.2.0.0 -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1532) Add GRANTOR column to primary key for SYSTABLEPERMS, SYSCOLPERMS and SYSROUTINEPERMS
[ http://issues.apache.org/jira/browse/DERBY-1532?page=comments#action_12422935 ] Satheesh Bandaram commented on DERBY-1532: -- If there are no more issues or comments with this sub-task, I will will close it on Monday. Add GRANTOR column to primary key for SYSTABLEPERMS, SYSCOLPERMS and SYSROUTINEPERMS Key: DERBY-1532 URL: http://issues.apache.org/jira/browse/DERBY-1532 Project: Derby Issue Type: Sub-task Components: SQL Reporter: Daniel John Debrunner Assigned To: Satheesh Bandaram Fix For: 10.2.0.0 -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-147) ERROR 42X79 not consistant ? - same column name specified twice
[ http://issues.apache.org/jira/browse/DERBY-147?page=comments#action_12422749 ] Satheesh Bandaram commented on DERBY-147: - Last time this patch was reviewed, there was also another concern about truely ambigious columns, like: Select a+b as x, c+d as x from T order by x This query should probably raise an error as X in ORDER BY clause is truely ambigious. To make this work, resolution process needs to find out if two different references to X actually correspond to same expression (or column) so that following is allowed: select a as x, a as x from T order by x select a, a from T order by a ERROR 42X79 not consistant ? - same column name specified twice --- Key: DERBY-147 URL: http://issues.apache.org/jira/browse/DERBY-147 Project: Derby Issue Type: Bug Components: SQL Reporter: Bernd Ruehlicke Attachments: derby-147-10.0.2.1.diff, derby-147.diff This happens from JDBC or ij. Here the output form ij ij version 10.0 CONNECTION0* -jdbc:derby:phsDB * = current connection ij select a1.XXX_foreign, a1.native, a1.kind, a1.XXX_foreign FROM slg_name_lookup a1 ORDER BY a1.XXX_foreign; ERROR 42X79: Column name 'XXX_FOREIGN' appears more than once in the result of the query expression. But when removing the ORDER BY and keeping the 2 same column names it works ij select a1.XXX_foreign, a1.native, a1.kind, a1.XXX_foreign FROM slg_name_lookup a1; XXX_FOREIGN |NATIVE |KIND|XXX_FOREIGN --- 0 rows selected ij So - it seams to be OK to specify the same column twice - as long as you do not add the ORDER BY clause. I woul dof course like that the system allows this - but at leats it should be consistant and either allow both or none of the two queries above. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-781) Materialize union subqueries in select list where possible to avoid creating invariant resultsets many times.
[
http://issues.apache.org/jira/browse/DERBY-781?page=comments#action_12422753 ]
Satheesh Bandaram commented on DERBY-781:
-
I think it would be good to modify this improvement description, as it will
likely be picked up by release notes and/or other documentation. The fix is
more generic than 'UNION' subqueries as the original description says.
Also the example in the description doesn't apply anymore, I think. When the
entry was made, join-predicate push down work wasn't completed, so the example
in the description would have shown the problem, I think. But now, (post
join-predicate pushdown work) the example may not apply.
Materialize union subqueries in select list where possible to avoid creating
invariant resultsets many times.
-
Key: DERBY-781
URL: http://issues.apache.org/jira/browse/DERBY-781
Project: Derby
Issue Type: Improvement
Components: SQL
Affects Versions: 10.1.1.0, 10.2.0.0
Environment: generic
Reporter: Satheesh Bandaram
Assigned To: A B
Attachments: d781_v1.patch, d781_v1.stat, d781_v2.patch,
DERBY-781_v1.html
Derby's handling of union subqueries in from list can be improved by
materializing invariant resultsets once, rather than creating them many times.
For example:
create view V1 as select i, j from T1 union select i,j from T2;
create view V2 as select a,b from T3 union select a,b from T4;
insert into T1 values (1,1), (2,2), (3,3), (4,4), (5,5);
For a query like select * from V1, V2 where V1.j = V2.b and V1.i in
(1,2,3,4,5), it is possible the resultset for V2 is created 5 times.
(assuming V2 is choosen as the the inner table) This can be very costly if
the underlying selects can take long time and also may perform union many
times.
Enhance materialization logic in setOperatorNode.java. It currently returns
FALSE always.
public boolean performMaterialization(JBitSet outerTables)
throws StandardException
{
// RESOLVE - just say no to materialization right now - should be a
cost based decision
return false;
/* Actual materialization, if appropriate, will be placed by our parent
PRN.
* This is because PRN might have a join condition to apply.
(Materialization
* can only occur before that.
*/
//return true;
}
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
Re: grant/revoke improvements
Hi Rick, It would be good to cover upgrade testing too. There is one known issue with upgrade and GRANT/REVOKE. I will file a JIRA entry for that. During database upgrade for a database from 10.1 to 10.2, the owner the database should change from peudo-user 'DBA' to actual authorizationID of the person performing the upgrade. This is not happening. There is mechanism in the upgrade already to perform this task, but seems to have a defect some where. I am working on two minor changes to Grant/Rekoke. They are: * Raise a warning if sqlAuthorization is ON without have authentication enabled. * Drop permission descriptors when their object is dropped. Ex: during drop table, drop all permission descriptors on that table. I will not be adding any more support to GRANT/REVOKE beyond this. Satheesh Rick Hillegas wrote: Thanks, Mamta. I have added your concern to the snapshot wiki page. Cheers, -Rick Mamta Satoor wrote: Hi Rick, Thanks for starting out this thread on grant/revoke improvement. One plea I have for the community in general is more eyes on the patches I have been submitting for grant revoke recently (one can always go and review the patch that has been already committed and provide feedback). As for testing, one particular area I am ansy about is statement caching and how statements in cache can invalidated if privilege changes after the statement has been cached. I have not done any work in that arena yet. Hopefully revoke privilege/drop table/drop view/drop routine work will take care of statement caching but I would like people to look out for this during their testing. thanks, Mamta ps I haven't done my share of looking into patches submmitted by others and hope to improve in that arena post 10.2 when there is more breathing time. On 7/19/06, *Rick Hillegas* [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Hi Satheesh and Mamta, Over the last month you have checked in a lot of great grant/revoke improvements. If there's anything in particular which you want users to test-drive, feel free to list these items on the wiki page that describes the 10.2.0.4 http://10.2.0.4 snapshot: http://wiki.apache.org/db-derby/TenTwoSnapshot#head-044f77b20b4cd15343e0b57416d4c6790d5bc3ae. Thanks, -Rick
[jira] Created: (DERBY-1543) Address two remaining issues with GRANT/REVOKE functionality: 1) Add warning when sqlAuthorization is on with authentication off 2) Drop permission descriptors when object
Address two remaining issues with GRANT/REVOKE functionality: 1) Add warning when sqlAuthorization is on with authentication off 2) Drop permission descriptors when objects they cover are dropped. Key: DERBY-1543 URL: http://issues.apache.org/jira/browse/DERBY-1543 Project: Derby Issue Type: Sub-task Components: SQL Affects Versions: 10.2.0.0 Reporter: Satheesh Bandaram Fix For: 10.2.0.0 Address two remaining sub-tasks: Add warning when sqlAuthorization is on with authentication off: This is a precautionary warning, since sqlAuthorization doesn't mean much without authentication being ON. Drop permission descriptors when objects they cover are dropped: This applies to tables, routines. When these objects are dropped, automatically drop their permisson descriptors. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1538) Unexpected behavior on self privilege revocation
[ http://issues.apache.org/jira/browse/DERBY-1538?page=comments#action_12422308 ] Satheesh Bandaram commented on DERBY-1538: -- Hi Yip... Dan had asked this question long time ago during spec reviews and I had proposed at that time that Derby should disallow revoking owners own permissions. I also thought I added checks in the code to disable revoking owner's own permissions, but I will check where I missed this. Unexpected behavior on self privilege revocation Key: DERBY-1538 URL: http://issues.apache.org/jira/browse/DERBY-1538 Project: Derby Issue Type: Bug Components: SQL Affects Versions: 10.2.0.0 Environment: Windows XP Pro Reporter: Yip Ng When revoking the owner's own privilege against the table he have created, Derby executes the revocation successfully but the owner is able to select from the table later as if though the REVOKE statement has no effect. More importantly, I was expecting a SQLException with the appropriate SQLSTATE to be thrown when the owner attempts to revoke privilege from himself. i.e.: ij connect 'jdbc:derby:authtest' user 'yip' as conn1; ij create table t1 (c1 int); 0 rows inserted/updated/deleted ij insert into t1 values 1,2,3; 3 rows inserted/updated/deleted ij revoke select on t1 from yip; 0 rows inserted/updated/deleted ij select * from t1; C1 --- 1 2 3 3 rows selected Here is the sysinfo: -- Java Information -- Java Version:1.4.2_12 Java Vendor: Sun Microsystems Inc. Java home: C:\jdk142\jre Java classpath: derby.jar;derbytools.jar;. OS name: Windows XP OS architecture: x86 OS version: 5.1 Java user name: yip Java user home: C:\Documents and Settings\Administrator Java user dir: C:\derby\trunk\jars\sane java.specification.name: Java Platform API Specification java.specification.version: 1.4 - Derby Information JRE - JDBC: J2SE 1.4.2 - JDBC 3.0 [C:\derby\trunk\jars\sane\derby.jar] 10.2.0.4 alpha - (423353) [C:\derby\trunk\jars\sane\derbytools.jar] 10.2.0.4 alpha - (423353) -- - Locale Information - Current Locale : [English/United States [en_US]] Found support for locale: [de_DE] version: 10.2.0.4 alpha - (423353) Found support for locale: [es] version: 10.2.0.4 alpha - (423353) Found support for locale: [fr] version: 10.2.0.4 alpha - (423353) Found support for locale: [it] version: 10.2.0.4 alpha - (423353) Found support for locale: [ja_JP] version: 10.2.0.4 alpha - (423353) Found support for locale: [ko_KR] version: 10.2.0.4 alpha - (423353) Found support for locale: [pt_BR] version: 10.2.0.4 alpha - (423353) Found support for locale: [zh_CN] version: 10.2.0.4 alpha - (423353) Found support for locale: [zh_TW] version: 10.2.0.4 alpha - (423353) -- -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1542) Implicit revoke via drop table does not clean up its associated row(s) in SYS.SYSTABLEPERMS
[ http://issues.apache.org/jira/browse/DERBY-1542?page=comments#action_12422309 ] Satheesh Bandaram commented on DERBY-1542: -- This is still pending development task. I had sent several messages to derbyDev about two remaining tasks and this is one of them. I have now filed a new sub-task to cover remaining two minor tasks, including this one. Should we mark this as Duplicate? Implicit revoke via drop table does not clean up its associated row(s) in SYS.SYSTABLEPERMS --- Key: DERBY-1542 URL: http://issues.apache.org/jira/browse/DERBY-1542 Project: Derby Issue Type: Bug Components: SQL Affects Versions: 10.2.0.0 Environment: Windows XP Pro, Sun JDK 1.4.2 Reporter: Yip Ng When a DROP TABLE statement is performed on the table which has access privilege granted to other user(s), its associated rows in the system table SYS.SYSTABLEPERMS does not get removed implicitly. i.e.: ij connect 'jdbc:derby:authtest;create=true' user 'yip' as conn1; ij select count(*) from sys.systableperms; 1 --- 0 1 row selected ij grant select on t1 to ally; ERROR 42Y07: Schema 'YIP' does not exist ij create table t1 (c1 int); 0 rows inserted/updated/deleted ij grant select on t1 to ally; 0 rows inserted/updated/deleted ij select count(*) from sys.systableperms; 1 --- 1 1 row selected ij revoke select on t1 from ally; 0 rows inserted/updated/deleted ij select count(*) from sys.systableperms; 1 --- 0 1 row selected ij grant select on t1 to ally; 0 rows inserted/updated/deleted ij select count(*) from sys.systableperms; 1 --- 1 1 row selected ij drop table t1; 0 rows inserted/updated/deleted ij select count(*) from sys.systableperms; 1 --- 1 1 row selected ij select grantor, grantee from sys.systableperms; GRANTOR |GRANTEE - YIP |ALLY 1 row selected sysinfo: -- Java Information -- Java Version:1.4.2_12 Java Vendor: Sun Microsystems Inc. Java home: C:\jdk142\jre Java classpath: derby.jar;derbytools.jar;. OS name: Windows XP OS architecture: x86 OS version: 5.1 Java user name: yip Java user home: C:\Documents and Settings\Administrator Java user dir: C:\derby\trunk\jars\sane java.specification.name: Java Platform API Specification java.specification.version: 1.4 - Derby Information JRE - JDBC: J2SE 1.4.2 - JDBC 3.0 [C:\derby\trunk\jars\sane\derby.jar] 10.2.0.5 alpha - (423580) [C:\derby\trunk\jars\sane\derbytools.jar] 10.2.0.5 alpha - (423580) -- - Locale Information - Current Locale : [English/United States [en_US]] Found support for locale: [de_DE] version: 10.2.0.5 alpha - (423580) Found support for locale: [es] version: 10.2.0.5 alpha - (423580) Found support for locale: [fr] version: 10.2.0.5 alpha - (423580) Found support for locale: [it] version: 10.2.0.5 alpha - (423580) Found support for locale: [ja_JP] version: 10.2.0.5 alpha - (423580) Found support for locale: [ko_KR] version: 10.2.0.5 alpha - (423580) Found support for locale: [pt_BR] version: 10.2.0.5 alpha - (423580) Found support for locale: [zh_CN] version: 10.2.0.5 alpha - (423580) Found support for locale: [zh_TW] version: 10.2.0.5 alpha - (423580) -- -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Created: (DERBY-1544) Address remaining upgrade task(s) to complete full upgrade mechanism for GRANT/REVOKE, specifically with changing database owner name from 'DBA' to authorizationId of user
Address remaining upgrade task(s) to complete full upgrade mechanism for GRANT/REVOKE, specifically with changing database owner name from 'DBA' to authorizationId of user invoking upgrade. - Key: DERBY-1544 URL: http://issues.apache.org/jira/browse/DERBY-1544 Project: Derby Issue Type: Sub-task Components: SQL Affects Versions: 10.2.0.0 Environment: generic Reporter: Satheesh Bandaram Fix For: 10.2.0.0 Upgrading a database from 10.1 to 10.2 should automatically change database owner, recorded as owner of system schemas in sysschemas, from pseudo user 'DBA' to authorizationID of the user attempting upgrade. Another upgrade change I am thinking about is to grant execute privilege to 5 system routines that by default have execute privilege to public when a new database is created. Five system routines, two compress routines and three statistics related routines are given execute privilege to public when a new 10.2 database is created. This is not done when a 10.1 database is upgraded to 10.2 and probably good to include these privileges during database upgrade. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1357) Short-circuit logic in optimizer appears to be incorrect...
[
http://issues.apache.org/jira/browse/DERBY-1357?page=comments#action_12422336 ]
Satheesh Bandaram commented on DERBY-1357:
--
Submitted this patch. Thanks for documenting the changes very well!
Sendingcompile\OptimizerImpl.java
Transmitting file data .
Committed revision 423754.
Short-circuit logic in optimizer appears to be incorrect...
---
Key: DERBY-1357
URL: http://issues.apache.org/jira/browse/DERBY-1357
Project: Derby
Issue Type: Bug
Components: Performance
Affects Versions: 10.0.2.0, 10.0.2.1, 10.1.1.0, 10.2.0.0, 10.1.2.0,
10.1.1.1, 10.1.1.2, 10.1.2.1, 10.1.2.2, 10.1.2.3, 10.1.2.4
Reporter: A B
Assigned To: A B
Priority: Minor
Fix For: 10.2.0.0
Attachments: d1357_v1.patch, d1357_v1.stat
When considering different join orders for the FROM tables in a query, the
optimizer will decide to give up on a join order midway through if the cost
of that (partial) join order is already higher than the cost of some other
*complete* join order that the optimizer previously found. This
short-circuiting of a join order can save compilation time.
That said, the logic to perform this short-circuit of a join order is
currently as follows (from OptimizerImpl.java):
/*
** Pick the next table in the join order, if there is an unused position
** in the join order, and the current plan is less expensive than
** the best plan so far, and the amount of time spent optimizing is
** still less than the cost of the best plan so far, and a best
** cost has been found in the current join position. Otherwise,
** just pick the next table in the current position.
*/
boolean joinPosAdvanced = false;
if ((joinPosition (numOptimizables - 1))
((currentCost.compare(bestCost) 0) ||
(currentSortAvoidanceCost.compare(bestCost) 0))
( ! timeExceeded )
)
{
...
}
There are two current costs in this statement: one for the cost if the
optimizer is calculating a sort avoidance plan (which it does if there is a
required row ordering on the results) and one if it is calculating a plan for
which row order is not important.
I admit that I'm not all that familiar with what goes on with the costing of
a sort-avoidance plan, but inspection of the code shows that, when there is
no required row ordering--i.e. when we aren't looking for a sort-avoidance
plan--the cost field of currentSortAvoidanceCost will always be 0.0d. That in
turn means that in the above if statement, the check for
((currentCost.compare(bestCost) 0) ||
(currentSortAvoidanceCost.compare(bestCost) 0))
will always return true (because bestCost should--in theory--always be
greater than 0.0d). Thus, in the case where we don't have a required row
ordering, the short-circuit logic will fail even if currentCost is actually
greater than bestCost.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
Re: Google SOC:MySQL to Derby Migration tool design question
Daniel John Debrunner wrote: Isn't the point of Google summer of code to introduce students to open source development, and this switch to ddlutils and additonal community involvement is typical of open source? Scratch your own itch but also benefit the community as well. Dan I am all for contributing code to ddlUtil. Only question is should we develop these modules for Derby *first* in a generic way and then contribute (modified version) ddlUtil or directly modify ddlUtil and hope they will include it quickly enough for Ramin to use in his tool. From what I understand he has another month to complete his migration tool. He may or may not have any time to work on this after that point. While ddlUtil may accept code from Ramin, they may want to make it work on their supported platforms before they include it in their distribution. They also have multiple ways to invoke ddlUtils and need to store database schema in Turbine XML form. So, there may be much more additional work that is needed before contribution from Ramin could be seen an complete. Satheesh
Re: Language based matching
Rick Hillegas wrote: At one point I was keen on re-enabling the national string types. Now I am leaning toward implementing the ANSI collation language. I think this is more powerful. In particular, it lets you support more than one language-sensitive ordering in the same database. I also had interest in enabling national characters for 10.2, but after dates for 10.2 became clearer, I decided it wouldn't be feasible to research and implement this functionality in the remaining time. I will add a comment to JIRA entry stating this. I also don't have itch to look into this issue for 10.3, though I think this is very useful functionality. Satheesh
Re: Google SOC:MySQL to Derby Migration tool design question
Jean T. Anderson wrote: One thing to consider is DdlUtils is database agnostic. For example, adding support for create view doesn't mean just adding it for Derby, but also adding it for every database supported (see the list at http://db.apache.org/ddlutils/database-support.html ). This is important... While ddlUtils goal is to support all databases, Ramin is attempting to fix his target database to Derby and to make source database mySQL for his migration utility. (for now) It doesn't seem like Ramin should take on adding database specific code to ddlUtils (that DatabaseMetadata might not expose directly) and to test on 13 databases that ddlUtils currently supports. Here are the issues I see with using ddlUtils 1. Doesn't support many database objects, like views, procedures, functions and check constraints. It may not even be ddlUtils goal to support database specific objects that other databases may not support. 2. Database migration is likely to require some intervention when automatic migration doesn't succeed. When this happens, would users have to modify XML files that ddlUtils generates and also other schema files that migration utility generates? Doesn't seem right... having to modify XML files for tables, but other output files for views or constraints etc. 3. ddlUtils is still under development and has not had an official release yet. While it may or may not be stable enough, should Derby community vote to include pre-released software into Derby's official releases? I, for one, would like to see this mySQL to Derby migration in 10.2 release in some form. It is also possible to develop mySQL specific schema capture (for views and other objects) just for this utility and then contribute that logic to ddlUtils project if there is interest. To do complete and successful migration, some database specific operations must be performed and that may not be ddlUtils goals for now. Satheesh You might consider posting to ddlutils-dev@db.apache.org to ask what level of effort people think might be required to implement the missing features. -jean Thanks Ramin On 7/11/06, Bryan Pendleton [EMAIL PROTECTED] wrote: The DdlUtils tool seems not be capable of migrating views, CHECK constraints, and stored procedures. I would like to know what do you think if DdlUtils tool can be reused for migrating the tables and Indexes, and use the DatabaseMetadata for migrating views and stored procedures? . Perhaps another possibility would be for you to improve DdlUtils so that it has these desirable features. The end result would be a better DdlUtils *and* a MySQL-to-Derby migration tool. thanks, bryan
[jira] Commented: (DERBY-533) Re-enable national character datatypes
[ http://issues.apache.org/jira/browse/DERBY-533?page=comments#action_12420746 ] Satheesh Bandaram commented on DERBY-533: - I earlier had some interest in enabling national characters for 10.2, but after dates for 10.2 became clearer, I decided it wouldn't be feasible to research and implement this functionality in the remaining time. I will add a comment to JIRA entry stating this. I also don't have itch to look into this issue for 10.3, though I think this is very useful functionality. Re-enable national character datatypes -- Key: DERBY-533 URL: http://issues.apache.org/jira/browse/DERBY-533 Project: Derby Type: New Feature Components: SQL Versions: 10.1.1.0 Reporter: Rick Hillegas SQL 2003 coyly defines national character types as implementation defined. Accordingly, there is considerable variability in how these datatypes behave. Oracle and MySQL use these datatypes to store unicode strings. This would not distinguish national from non-national character types in Derby since Derby stores all strings as unicode sequences. The national character datatypes (NCHAR, NVARCHAR, NCLOB and their synonymns) used to exist in Cloudscape but were disabled in Derby. The disabling comment in the grammar says need to re-enable according to SQL standard. Does this mean that the types were removed because they chafed against SQL 2003? If so, what are their defects? -- Cloudscape 3.5 provided the following support for national character types: - NCHAR and NVARCHAR were legal datatypes. - Ordering operations on these datatypes was determined by the collating sequence associated with the locale of the database. - The locale was a DATABASE-wide property which could not be altered. - Ordering on non-national character datatypes was lexicographic, that is, character by character. -- Oracle 9i provides the following support for national character types: - NCHAR, NVARCHAR2, and NCLOB datatypes are used to store unicode strings. - Sort order can be overridden per SESSION or even per QUERY, which means that these overridden sort orders are not supported by indexes. -- DB2 does not appear to support national character types. Nor does its DRDA data interchange protocol. -- MySQL provides the following support for national character types: - National Char and National Varchar datatypes are used to hold unicode strings. I cannot find a national CLOB type. - The character set and sort order can be changed at SERVER-wide, TABLE-wide, or COLUMN-specific levels. -- If we removed the disabling logic in Derby, I believe that the following would happen: - We would get NCHAR, NVARCHAR, and NCLOB datatypes. - These would sort according to the locale that was bound to the database when it was created. - We would have to build DRDA transport support for these types. The difference between national and non-national datatypes would be their sort order. I am keenly interested in understanding what defects (other than DRDA support) should be addressed in the disabled implementation. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
Re: Optimizer patch reviews? (DERBY-781, DERBY-1357)
I will start to review these patches with the goal of committing them. If anyone has comments or suggestions, please share with the group... Satheesh Army wrote: I posted two patches for some optimizer changes a little over a week ago: one for DERBY-781 and one for DERBY-1357. Has anyone had a chance to review either of them, or is anyone planning to? I'm hoping to have these reviewed and committed sometime in the next few days so that I'm not forced to try to address issues at the last minute for the first 10.2 release candidate. Optimizer changes can sometimes be rather tricky, so the sooner the review--and the more eyes on the code--the better. The DERBY-1357 changes are quite small and are very easily reviewable, while the DERBY-781 changes are more involved. Anyone have some time to review either of these patches? Many thanks, Army
[jira] Closed: (DERBY-242) DatabaseMetaData.supportsGetGeneratedKeys needs to return FALSE, since Derby only has limited support.
[ http://issues.apache.org/jira/browse/DERBY-242?page=all ] Satheesh Bandaram closed DERBY-242: --- Fix has been in the codeline for sometime. DatabaseMetaData.supportsGetGeneratedKeys needs to return FALSE, since Derby only has limited support. -- Key: DERBY-242 URL: http://issues.apache.org/jira/browse/DERBY-242 Project: Derby Type: Bug Components: JDBC Versions: 10.0.2.0 Environment: ALL Reporter: Satheesh Bandaram Assignee: Lance Andersen Fix For: 10.1.1.0 Derby only has partial support for retrieving generated values. Because of this, it is desirable to make DatabaseMetaData.supportsGetGeneratedKeys return FALSE. One of the restrictions include Jira entry Derby-201. Till this is fixed, we should change derby to return FALSE for this. This would also match Derby client behavior. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Closed: (DERBY-335) Enhance Derby by adding SYNONYM support. A synonym is an alternate name for a view or a table.
[ http://issues.apache.org/jira/browse/DERBY-335?page=all ] Satheesh Bandaram closed DERBY-335: --- This new feature has been in Derby since 10.1. Enhance Derby by adding SYNONYM support. A synonym is an alternate name for a view or a table. -- Key: DERBY-335 URL: http://issues.apache.org/jira/browse/DERBY-335 Project: Derby Type: New Feature Components: SQL Versions: 10.1.1.0 Environment: Generic Reporter: Satheesh Bandaram Assignee: Satheesh Bandaram Fix For: 10.1.1.0 Attachments: synonym.patch.jira Synonym provides an alternate name for a table or a view that is present in the same schema or another schema. A synonym can also be created for another synonym, causing nesting of synonyms. A synonym can be used in SELECT, INSERT, UPDATE, DELETE or LOCK TABLE statements instead of the original qualified table or view name. Note that a synonym can be created for a table or a view that doesn't yet exists. But the target table/view must be present before the synonym can be used. Synonyms are supported by all major database vendors, including Oracle, DB2 and mySQL. DB2 also allows CREATE ALIAS statement, which does exactly same as CREATE SYNONYM. Creating aliases instead of synonyms is not supported by Oracle or mySQL, so I propose that Derby not support creating aliases. Synonyms are not part of SQL-2003 spec, but is a common-SQL statement among major database vendors. SQL standard doesn't pay attention to DDLs as much, so I suspect they skipped synonyms. I will be adding two new DDL statements to Derby: CREATE SYNONYM SynonymSchema.SynonymName FOR TargetSchema.TargetName DROP SYNONYM SynonymSchema.SynonymName Synonyms share the same namespace as tables or views. It is not possible to create a synonym with same name as a table that already exists in the same schema. Similarly, a table/view can't be created that matches a synonym already present. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
Re: Issue with using dependency manager for table level permission tracking
Have you considered using PROVIDERFINDER that is part of dependency system? TableDescriptor, for example, stores column list that a view depends on using ColumnsInTable finder. A similar mechanism might work for TablePermDescriptor. SatheeshOn 7/10/06, Mamta Satoor [EMAIL PROTECTED] wrote: Hi, I wondered if anyone got a chance to go over my earlier mail and if they think of an alternative way to solve the problem than the one suggested by me. If I don't hear anything in a day or so, then I will go ahead and changeSYSTABLEPERMS to look like SYSCOlPERMS. After that change, I will be able to associate a view/trigger/constraint's dependnecy on the exact privilege type for a give table. thanks, Mamta On 6/28/06, Mamta Satoor [EMAIL PROTECTED] wrote: Hi, I recently submitted a patch ( http://www.nabble.com/-PATCH-DERBY-1330-Collect-privilege-requirements-for-views-tf1858264.html#a5075008 )that tracks a view's(view descriptor)dependency on different privileges (PermissionsDescriptor)using the dependency manager which in turn saves these dependencies in SYSDEPENDS. Currently, Derby 10.2 codeline tracks table level privileges per user in SYSTABLEPERMS. If a user has one/more privileges on a table, they are all tracked in one row in SYSTABLEPERMS. The structure of SYSTABLEPRMS looks as follows create table SYS.SYSREQUIREDPERM ( GRANTEE varchar(128) not null, GRANTOR varchar(128) not null, TABLEID char(36) not null, SELECTPRIV char(1) not null, DELETEPRIV char(1) not null, INSERTPRIV char(1) not null, UPDATEPRIV char(1) not null, REFERENCESPRIV char(1) not null, TRIGGERPRIV char(1) not null, primary key( GRANTEE, TABLEID), foreign key( TABLEID references SYS.SYSTABLES) ) Following eg showshow table level privileges are tracked in SYSTABLEPERMS and the problem at hand user1 connects create table t1 grant select on t1 to user2 --Then at this point, there is one row in SYSTABLEPERMS for t1 and user2 and that row hasSELECTPRIV column set to true. user2 connects create view v1 as select * from user1.t1 -- view above depends on SELECTPRIV on user1.t1and dependency managerkeepstrack of that dependency by inserting a row for viewdescriptor depending on permissiondescriptor(the uuid column in SYSTABLEPERMS). Notice that there is no differentiation here which says that the view depends only on the SELECTPRIV privilege type on table t1. We just know that view is dependent on SOME privilege type on table t1 user1 connects grant insert on t1 to user2 --Then at this point, we still have one row in SYSTABLEPERMS for t1 and user2 and that row hasboth SELECTPRIV and INSERTPRIV set to true. user2 connects create table t2 create insert trigger tr1on t2 which inserts into user1.t1 All the table level privileges are kept in -- trigger above depends on INSERTPRIV on user1.t1and dependency managerkeepstrack of that dependency by inserting a row for triggerdescriptor depending on permissiondescriptor(the uuid column in SYSTABLEPERMS). Notice that there is no differentiation here which says that the trigger depends only on the INSERTPRIV privilege type on table t1. We just know that trigger is dependent on SOME privilege type on table t1 user1 connects revoke select on t1 from user2 -- at this point, only view user2.v1 should get dropped because it depends on the SELECTPRIV on t1. But dependency manager has no way to know that user2.v1 needs only SELECTPRIV and hence only object affected by this revoke in user2.v1 and there should be no impact on user2.tr2. That's because user2.tr2 needs INSERTPRIV on user1.t1 and that is still in place So, my question is how can I fine tune the dependency manager to know that user2.v1 needs SELECTPRIV on user1.t1? And hence, revoke above should result in dropping user2.v1 but should not touch user2.tr2 One solution that comes to my mind is to have one row for each privilege type for a given user in SYSTABLEPERMS. ie in the example above grant select on t1 to user2 --There should beone row in SYSTABLEPERMS for t1 and user2 witha privilege type column set to S(for select) grant insert on t1 to user2 --There should be another row inSYSTABLEPERMS for t1 and user2 with a privilege type column set to I(for insert). ie at the end of the above 2 grant statements, there should be rows in SYSTABLEPERMS for table t1 and user user2. This is what we do for column level privileges in SYSCOLPERMS. That is, there is one row for every type of privilege that is granted on columns level for a given user. If we did the same thing for SYSTABLEPERMS, it will solve my problem. Maybe there is another way to solve the table level privilege type dependency which I am overlooking and would appreciate any suggestionfrom the community. thanks, Mamta
Re: [jira] Commented: (DERBY-883) Enhance GROUP BY clause to support expressions instead of just column references.
This is great... Looks like you are close to finishing up. Do your current changes support expressions and function calls in GROUP BY? You had raised questions about whether function calls could be in GROUP BY... because Derby functions can not be defined invariant yet.
I think end of July is the current timeframe for 10.2 features, with Aug 10 being code freeze for first Release candidate. But I have been out for a month, so not sure if these dates may have changed recently.
SatheeshOn 7/5/06, Manish Khettry [EMAIL PROTECTED] wrote:
Actually, I'm still working on it and have got itworking for the most part.I still need to cleanup the code, add more errormessages, more compile time checks and tests (all thefun stuff) before its ready for checkin. What is the
timeframe for getting things into 10.2?m--- Satheesh Bandaram [EMAIL PROTECTED] wrote: Hi Manish, Do you have any updates on your progress to share
with us? It seemed you had a good design to support GROUP BY expressions last time it was discussed. Would this be ready in time for 10.2? I have itch to see this issue progress and can help.
Even if you have basic framework for rewriting the query, but haven't implemented matching facility, I would suggest you contribute that if ready. You could add *isEquivalent()* function that
simply returns FALSE except for simple column references. Then it should be possible to incrementally allow specific _expression_ matching by either yourself or others in the community.
Thanks, Satheesh Manish Khettry wrote: Here is what I think needs to be implemented First, an _expression_ matching facility. The
first cut can be restricted to looking at two expressions and detecting if they are the same-- by same, I mean, they will evaluate to the same value at runtime. Thus the _expression_ f() is not the same
as f(). (I don't see a way in the derby docs to declare a function as deterministic-- is that correct?) Second, the group by binding/validation code will
need to lose assumptions about grouping expressions being columns. We'll also have to do a little query rewrite (I'm guessing) to replace references to the grouping expressions in the having/select
clause. When we have this functionality ofcourse, as you suggest in your email, we'll look into allowing subexpressions and such. As far as the first step goes, I plan on adding a
function to the base class (ValueNode). protected boolean isEquivalent(ValueNode other) { return false; } and having each subclass override this method and
ofcourse do the right thing. I'll have to spend time looking at the ValueNode hierarchy and see what the correct behavior for isEquivalent is
going to be for each class. Just wanted to swing this proposal by people more familiar with the code. If you see anything wrong in this proposal, let me know. I'll
probably start on it in a day or two. cheers Manish On 5/23/06, *Satheesh Bandaram* [EMAIL PROTECTED]
mailto:[EMAIL PROTECTED] wrote: Manish, good research. I think you are on the right track. Derby already rewrites GROUP BY clause into select
subquery so that HAVING clause can be converted into a WHERE clause on top of select subquery, with aggregate references converted into simple columnReferences. Search for groupByList in
sqlgrammar.jj. While this rewrite makes implementation easier without any performance penality, it does confuse a few query resolutions. See DERBY-280. (
http://issues.apache.org/jira/browse/DERBY-280) So, I think both the approaches below are essentially same thing,
because of current Derby rewrite. This rewrite could make implementing this feature easier, like you said. Most of the work seems to be in the compile phase.
To start with, you could implement simple _expression_ matching, which only finds exact matches: Select day(ts), count(*) from timeStampTab
group by day(ts) having day(ts) = 1 Then it can be expanded to support proper sub-expressions. Select (a+b)+c, count(*) from intTab group by
a+b Note that DB2 and Oracle don't seem to have fancy _expression_ matching, rejecting this: Select b+a, count(*) from intTab group by a+b
so, Derby _expression_ matching can be simple matching algorithm, to start with. Good thing with this new feature is that it can be expanded incrementally, without breaking
existing or intermediate stage queries. Does that help? SatheeshOn 5/20/06, *Manish Khettry *
[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Thanks Alex and Satheesh, that does
clarify things quite a bit. I spent some time looking at the GroupedAggregateResultSet and I have the feeling that atleast on the execution side of
things not much will have to change. A grouped aggregate RS already sits on top of a ProjectRestrictRS and if we can have the prRS evaluate the expressions we are
grouping on, the existing logic should work without any (or perhaps minimal) changes. It is on the compilation side
Signing of ICLAs by Google Summer of Code students ...
I don't see ICLAs of three summer of code students recorded at Apache. (http://people.apache.org/~jim/committers.html) Not sure if the students have sent ICLAs or not, but I think this is a required step and it may be difficult to track them after their project is done. Would the students consider FAXing ICLAs soon, please? Mentors probably should make sure this step happens soon. Satheesh
Re: DERBY-396: Drop Column dependency questions
Hi Bryan, Thanks for working on this long requested feature. More below... Bryan Pendleton wrote: Does it seem correct that: a) RESTRICT processing should consider an index on a column to be a dependent object and fail the DROP COLUMN if the column is used in an index? What happens when you drop a column with restrict that is part of a composite index? How about if there are any constraints on that column? Also have to worry about that being a triggering column or part of trigger body. I personally prefer if DROP COLUMN also implicitly drops an index on that column, even for RESTRICT. It should also be able to drop a default and any constraints that affect only that column. (though not sure if there is a primary key on that column) b) CASCADE processing should cascade the DROP COLUMN to include dropping a view which uses the column that is dropped? Derby doesn't support CASCADE option for any DDLs that I am aware of. Like dropping a table with CASCADE option or dropping a schema with CASCADE option to drop dependent objects. I wouldn't implement CASCADE option only for DROP COLUMN... that probably depends on real cascade implementation underneath it. Also would raise lots of questions about what else you should drop... like constraints, triggers, composite indexes etc... Satheesh thanks, bryan
Re: [jira] Commented: (DERBY-883) Enhance GROUP BY clause to support expressions instead of just column references.
Hi Manish,
Do you have any updates on your progress to share with us? It seemed you
had a good design to support GROUP BY expressions last time it was
discussed. Would this be ready in time for 10.2? I have itch to see this
issue progress and can help.
Even if you have basic framework for rewriting the query, but haven't
implemented matching facility, I would suggest you contribute that if
ready. You could add *isEquivalent()* function that simply returns FALSE
except for simple column references. Then it should be possible to
incrementally allow specific expression matching by either yourself or
others in the community.
Thanks,
Satheesh
Manish Khettry wrote:
Here is what I think needs to be implemented
First, an expression matching facility. The first cut can be
restricted to looking at two expressions and detecting if they are the
same-- by same, I mean, they will evaluate to the same value at
runtime. Thus the expression f() is not the same as f(). (I don't
see a way in the derby docs to declare a function as deterministic--
is that correct?)
Second, the group by binding/validation code will need to lose
assumptions about grouping expressions being columns. We'll also have
to do a little query rewrite (I'm guessing) to replace references to
the grouping expressions in the having/select clause.
When we have this functionality ofcourse, as you suggest in your
email, we'll look into allowing subexpressions and such.
As far as the first step goes, I plan on adding a function to the base
class (ValueNode).
protected boolean isEquivalent(ValueNode other)
{
return false;
}
and having each subclass override this method and ofcourse do the
right thing.
I'll have to spend time looking at the ValueNode hierarchy and see
what the correct behavior for isEquivalent is going to be for each
class.
Just wanted to swing this proposal by people more familiar with the
code. If you see anything wrong in this proposal, let me know. I'll
probably start on it in a day or two.
cheers
Manish
On 5/23/06, *Satheesh Bandaram* [EMAIL PROTECTED]
mailto:[EMAIL PROTECTED] wrote:
Manish, good research. I think you are on the right track. Derby
already rewrites GROUP BY clause into select subquery so that
HAVING clause can be converted into a WHERE clause on top of
select subquery, with aggregate references converted into simple
columnReferences. Search for groupByList in sqlgrammar.jj. While
this rewrite makes implementation easier without any performance
penality, it does confuse a few query resolutions. See DERBY-280.
( http://issues.apache.org/jira/browse/DERBY-280)
So, I think both the approaches below are essentially same thing,
because of current Derby rewrite. This rewrite could make
implementing this feature easier, like you said. Most of the work
seems to be in the compile phase.
To start with, you could implement simple expression matching,
which only finds exact matches:
Select day(ts), count(*) from timeStampTab group by day(ts) having
day(ts) = 1
Then it can be expanded to support proper sub-expressions.
Select (a+b)+c, count(*) from intTab group by a+b
Note that DB2 and Oracle don't seem to have fancy expression
matching, rejecting this:
Select b+a, count(*) from intTab group by a+b
so, Derby expression matching can be simple matching algorithm, to
start with. Good thing with this new feature is that it can be
expanded incrementally, without breaking existing or intermediate
stage queries.
Does that help?
Satheesh
On 5/20/06, *Manish Khettry * [EMAIL PROTECTED]
mailto:[EMAIL PROTECTED] wrote:
Thanks Alex and Satheesh, that does clarify things quite a bit.
I spent some time looking at the GroupedAggregateResultSet and
I have the feeling that atleast on the execution side of
things not much will have to change. A grouped aggregate RS
already sits on top of a ProjectRestrictRS and if we can have
the prRS evaluate the expressions we are grouping on, the
existing logic should work without any (or perhaps minimal)
changes. It is on the compilation side of things that this is,
err rather hairy :) Someone should correct me if I'm missing
something here or simplifying things.
The other approach is to rewrite the query to use a select
subquery like Satheesh suggested. Perhaps this is a better way
to go. Any thoughts on which approach is better?
On 5/18/06, *Alex Miller* [EMAIL PROTECTED]
mailto:[EMAIL PROTECTED] wrote:
No, we don't go quite that far. Because most dbs support
group by expressions, we haven't needed to implement this.
*From:* Satheesh Bandaram
Re: MySQL to Derby Migration Tool
Great document and good comments from David. I think it would be good to follow ddlUtils approach (approach 2) if feasible. Ramin, should this approach be also discussed on ddlUtils development alias?I think you would need to handle some database objects like CHECK constraints and others in a database specific way in either approach. DatabaseMetadata doesn't provide a way and probably why ddlUtil doesn't support that either. These objects will have to be migrated by reading source database specific system tables... so your generic migration tool would probably need a source database specific plug-in... I would also like to encourage others to review the document as Ramin seems to be making good progress towards implementation.SatheeshOn 6/27/06, David Van Couvering [EMAIL PROTECTED] wrote: Hi, Ramin.Great document!Very nicely done.Here are my comments:- You're a UML use case guy.Great!Wish there was more of that insoftware design, especially for tools.- What's the mapping from MySql YEAR to Derby? - Can you explain a little more how DdlUtils takes care of data typemapping?- Can you explain a little more what you mean by A possible solutionwould be to execute database-specific commands to capture required information. when you say DdlUtils doesn't support constraints,triggers, stored procedures, etc.- I guess I don't follow the application flow very well when usingDdlUtils.What exactly are the steps taken by the user and/or the tool using DdlUtils?- What is *your* recommended approach (DatabaseMetadata vs. DdlUtils)and why?- Can you talk more to what happens if there is an error duringmigration at various phases. What steps does the tool take to set things right?Can you end up with a half-created database?What do you do tofix the errors and start over?I suspect migration is often arepetitive effort as you catch migration/mapping errors, fix things, and try again...This should be as painless as possible.- Can you please show the proposed syntax and other details (inputfiles, config files, env variables, exit status) for the CLI?- Does the CLI have a headless mode ( e.g. no interactive prompts) soyou can run it in a script?- What is the syntax for starting the tool?Will you make use ofderbyrun.jar?- Can you cover quickly how you'll address internationalizing the GUI? - Can you explain briefly how, using this architecture, you can plug inother data sources besides MySQL?- You might want to touch on the impact to existing JDBC applicationsthat are running against MySQL, which you want to migrate to Derby.How are *those* migrated, and how much or how little does this tool helpwith that?If anything, just make it clear that application migrationis out of the scope of this tool.- What mechanism are you going to use to do the data transfer?Simple JDBC inserts?Bulk insert?For large databases the mechanism you usecan have a large impact on performance, and may actually prevent somedatabases from migrating.It would be good to have a section onperformance and what approaches you will take to improve performance and test performance.- What about migration of users, groups, permissions, etc. -- is thatout of scope?- Perhaps it would be good to be *very* explicit about what you plan tomigrate and what you *don't* plan to migrate in this tool, and what you recommend for users for the bits that aren't being migrated(applications, authorizations, triggers, whatever is on that list).- This may not be your itch to scratch, but can you address what you'replanning to do to (or not do) to address governmental accessibility requirements (called Section 508)?Seehttp://java.sun.com/products/jfc/accessibility/index.jsp.At a minimumit would be great if you don't do anything that makes it difficult for someone else to make the tool accessible.Thanks!DavidRamin Moazeni wrote: Hello, I am a Google Summer of code participant working on the Derby Migration tool project. The High level design document is posted at http://wiki.apache.org/db-derby/MysqlDerbyMigration/DesignDocument There are 2 approaches described in the document -- one based on the use DatabaseMetaData class and the other is based on the use ddlUtils tool (http://db.apache.org/ddlutils) which is also an apache project. I would appreciate your feedback and comments Thanks much Ramin Moazeni
Re: Wrapping up coding for 10.2
Rick, I have been on vacation and later this week I will be at ApacheCon Europe. Looks like I have been missing lots of *fun* about release dates, JDBC4 and other discussions.About Grant/Revoke, one big missing piece (Definer authorization model) is being worked on by Mamta Satoor. She should probably speak about its completion date. There are couple of minor changes I need to make and I think they will be done by Aug 10th. Let me know if you need any further information from me...SatheeshOn 6/26/06, Rick Hillegas [EMAIL PROTECTED] wrote:I hate to be a nudge, but I didn't get a response when I asked this question last week: Do we expect to be done with development for 10.2 byAugust 10?I expect that the JDBC4 work will have wrapped up by then. Besides theJDBC4 effort, 10.2 is waiting for feature work from Army, Dan, and Satheesh. Do you guys think you'll be done by August 10?If anyone else is working on 10.2 features, could you let us knowwhether August 10 sounds like a reasonable date for cutting a releasebranch? Thanks,-Rick
OutBufferedStream test fails on JDK13 platforms ...
I have been seeing this test fail on JDK13 platforms, both SUN and IBM JVMs since the test was committed. Tomohito, do you have access to JDK13 platform and have interest to fix this failure? If not, I can help. Satheesh [bandaram:satheesh] svn log OutBufferedStream.out r405037 | tmnk | 2006-05-08 05:36:39 -0700 (Mon, 08 May 2006) | 1 line - DERBY-326 Improve streaming of large objects for network server and client - Patch by Tomohito Nakayama ([EMAIL PROTECTED])
Re: [jira] Commented: (DERBY-1397) Tuning Guide: Puzzling optimizer documentation
*Good *write up! I am not a big fan of silently ignoring user specified value, even if specified incorrectly. I would vote for filing a big to correct two issues 1) Checking for less than Integer.MAX_VALUE value after multiplying specified value by 1024 and 2) Raising an error for negative values. Satheesh A B (JIRA) wrote: [ http://issues.apache.org/jira/browse/DERBY-1397?page=comments#action_12416418 ] A B commented on DERBY-1397: Well I spent a little time investigating this property and here's what I found out, based on your questions. - Are we sure that the name of the property is derby.language.maxMemoryPerTable ? Yes -- I ran some simple tests with this property name and it is indeed picked up by the Derby optimizer and used accordingly. - What is the maximum value allowed? The property is stored as an integer value (as found in the OptimizerFactorImpl.boot() method), so the *theoretical* maximum value is Integer.MAX_VALUE (i.e. 2^31 - 1). Attempts to specify a larger value will result in the following error at connect time: ij connect 'simpDB'; ERROR XJ040: Failed to start database 'simpDB', see the next exception for details. ERROR XJ001: Java exception: 'For input string: 2147483650: java.lang.NumberFormatException'. But that said, one of the gotchas here is that the property is actually specifying *kilobytes*, not bytes--i.e. the value that the user specifies is multiplied by 1024. What this means is that if the user specifies Integer.MAX_VALUE, Derby will grab the value and think that it's fine, then it will multiply the value by 1024, which causes integer overflow and thus leads to a maxMemoryPerTable that is negative--which is wrong. I think this warrants a new Jira issue--Derby should check to make sure that the value, when multiplied by 1024, is still less than Integer.MAX_VALUE. To answer your question, then, the documentation should explicitly say that this number is specified *in kilobytes* (i.e. 1 means 1K which means 1024 bytes) and that, therefore, the maximum value allowed is Integer.MAX_VALUE / 1024 (which is 2097151). Then we need to file a Jira issue (as mentioned above) to check for this maximum and throw the appropriate error if it's exceeded. - Is there a value that we want to recommend for users to specify? The default value is 1024, which translates into 1024K, which means the default max memory per table is 1M. I think we should update the documentation to indicate what the default is; if a user wants to change it, then I don't think we need to (or want to) recommend a value, so long as we indicate what the tradeoff is for lower/higher values (see below). - The text says that it can be set to smaller values, what is the minimum value that we recommend? Again, I don't know about recommending a value. If the default isn't good enough, then I think all we can do is say what happens for smaller values and what happens for larger values, and then let the user make the decision him/herself. That said, the theoretical minimum is 0--I checked and Derby will accept that value and use it (but the performance hit is huge--see below). If the user specifies a negative value, Derby will silently ignore the value and just use the default--which is something that either warrants a fix (Derby should complain about the negative value like it does for other invalid numbers) or else should be documented (in my opinion). In terms of the documentation, then, I suggest that we add a link for the property to the paragraph preceding the one cited in this issue description, perhaps as follows: begin The hash table for a hash join is held in memory and if it gets big enough, it will spill to the disk. The optimizer makes a very rough estimate of the amount of memory required to make the hash table. If it estimates that the amount of memory required would exceed the system-wide limit of memory use for a table (see derby.language.maxMemoryPerTable), the optimizer chooses a nested loop join instead. end Then the words derby.language.maxMemoryPerTable would link to the Properties section (which already exists) and there is where we would put the cited paragraph, with the following changes (feel free to modify as you see fit): begin derby.language.maxMemoryPerTable Function: When considering whether or not to do a hash join with a FROM table, the Derby optimizer will use this value to determine if the resultant hash table would consume too much memory, where too much means that the hash table would take up more than maxMemoryPerTable kilobytes of memory. If the optimizer decides that the hash table will require greater than maxMemoryPerTable kilobytes, it will reject the hash join and instead do a nested loop join. If memory use is not a problem for your environment, setting this property to a high number gives the optimizer the maximum flexibility in considering
Re: [Db-derby Wiki] Update of MysqlDerbyMigration by RaminMoazeni
Bernt M. Johnsen wrote: Is it possible to get some synergy between this project and ddl-utils? (See http://db.apache.org/derby/integrate/db_ddlutils.html) Right... Jean and myself were just looking at ddlUtils yesterday and forwarded relavent links to Ramin. This will be looked at during design review. Satheesh Apache Wiki wrote (2006-06-01 08:12:55): Dear Wiki user, You have subscribed to a wiki page or wiki category on Db-derby Wiki for change notification. The following page has been changed by RaminMoazeni: http://wiki.apache.org/db-derby/MysqlDerbyMigration -- [http://www-scf.usc.edu/~moazeni/Derby/MySQL-DerbyFeatureMatrix.doc MySQL-Derby Feature Matrix] + [http://www-scf.usc.edu/~moazeni/Derby/MySQL-DerbyComparison.doc MySQL-Derby Datatype Comparison] +
Re: another question about optimizer documentation
Rick Hillegas wrote: hose operations are: * (all indexes) When you execute SYSCS_UTIL.SYSCS_COMPRESS_TABLE. * (index only) When you drop a column that is part of a table's index; the statistics for the affected index are dropped, and statistics for the other indexes on the table are updated. What does the second bullet mean? Derby doesn't let you drop a column from a table right now. Documentation update needed here. I would file a documentation bug. Satheesh
Re: optimizer documentation
Rick Hillegas wrote: If memory use is not a problem for your environment, set this property to a high number; allowing the optimizer the maximum flexibility in considering a join strategy queries involving large queries leads to better performance. It can also be set to smaller values for more limited environments. I can't find the name of this property on that page of the Tuning Guide. I'm also confused about what we consider to be a high number versus what we consider to be smaller values. Would appreciate advice here. The property it may be referring to is *derby.language.maxMemoryPerTable*. The default value is 1024 KB. Current default value is too small, so it would be a good tip for developers to know and tune this property. It would be great if Derby can configure this property value based on factors like max heap size, size of data cache and/or other parameters. If you are opening a documentation bug for other issue you found, may be you can include this omission as well. Satheesh
[jira] Updated: (DERBY-464) Enhance Derby by adding grant/revoke support. Grant/Revoke provide finner level of privileges than currently provided by Derby that is especially useful in network configur
[ http://issues.apache.org/jira/browse/DERBY-464?page=all ] Satheesh Bandaram updated DERBY-464: Derby Info: (was: [Patch Available]) Enhance Derby by adding grant/revoke support. Grant/Revoke provide finner level of privileges than currently provided by Derby that is especially useful in network configurations. --- Key: DERBY-464 URL: http://issues.apache.org/jira/browse/DERBY-464 Project: Derby Type: New Feature Components: SQL Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0 Environment: generic Reporter: Satheesh Bandaram Assignee: Satheesh Bandaram Attachments: GrantRevokePartII.stat, GrantRevokePartII.txt, GrantRevokePartII.txt, Privileges.java, Privileges2.java, changeDescriptionPartII, grantRevoke.patch.Dec5, grantRevoke.stat.Dec5, grantRevokeSpec.html, grantRevokeSpec_v2.html Derby currently provides a very simple permissions scheme, which is quite suitable for an embedded database system. End users of embedded Derby do not see Derby directly; they talk to a application that embeds Derby. So Derby left most of the access control work to the application. Under this scheme, Derby limits access on a per database or per system basis. A user can be granted full, read-only, or no access. This is less suitable in a general purpose SQL server. When end users or diverse applications can issue SQL commands directly against the database, Derby must provide more precise mechanisms to limit who can do what with the database. I propose to enhance Derby by implementing a subset of grant/revoke capabilities as specified by the SQL standard. I envision this work to involve the following tasks, at least: 1) Develop a specification of what capabilities I would like to add to Derby. 2) Provide a high level implementation scheme. 3) Pursue a staged development plan, with support for DDL added to Derby first. 4) Add support for runtime checking of these privileges. 5) Address migration and upgrade issues from previous releases and from old scheme to newer database. Since I think this is a large task, I would like to invite any interested people to work with me on this large and important enhancement to Derby. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1384) Increase default BLOB/CLOB length to maximum supported (2G?)
[ http://issues.apache.org/jira/browse/DERBY-1384?page=comments#action_12415387 ] Satheesh Bandaram commented on DERBY-1384: -- While I still don't know if this change is good or not for Derby at this time, change in max length, if applied, should probably be prevented in soft-upgrade mode. When Derby is running in soft-upgrade mode in 10.2, it is best to avoid increasing default max length, as it could cause problems after any downgrade. Increase default BLOB/CLOB length to maximum supported (2G?) Key: DERBY-1384 URL: http://issues.apache.org/jira/browse/DERBY-1384 Project: Derby Type: Improvement Components: SQL Reporter: Bernt M. Johnsen Assignee: Bernt M. Johnsen Priority: Minor Fix For: 10.2.0.0 Attachments: derby-1384-code.diff, derby-1384-code.stat, derby-1384-docs.diff, derby-1384-docs.stat Default BLOB/CLOB length should be the maximum length supported by Derby (2G?) -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-1329) ASSERT failure/IndexOutOfBoundsException with correlated subquery for UPDATE ... SET ... WHERE CURRENT OF ... statement.
[ http://issues.apache.org/jira/browse/DERBY-1329?page=all ]
Satheesh Bandaram updated DERBY-1329:
-
Derby Info: (was: [Patch Available])
Submitted this patch to 10.1 branch. Please resolve and/or close accordingly.
ASSERT failure/IndexOutOfBoundsException with correlated subquery for UPDATE
... SET ... WHERE CURRENT OF ... statement.
Key: DERBY-1329
URL: http://issues.apache.org/jira/browse/DERBY-1329
Project: Derby
Type: Bug
Components: SQL
Versions: 10.0.2.0, 10.0.2.1, 10.1.1.0, 10.2.0.0, 10.1.2.0, 10.1.1.1,
10.1.1.2, 10.1.2.1, 10.1.2.2, 10.1.2.3, 10.1.2.4
Reporter: A B
Assignee: A B
Priority: Minor
Fix For: 10.2.0.0, 10.1.3.0
Attachments: d1329.java, d1329.patch, d1329_10_1.patch
If in a statement of the form UPDATE ... SET ... WHERE CURRENT OF ... the
SET clause includes a correlated subquery that has a predicate referencing
the table that is being updated, Derby will fail with an ASSERT failure in
sane mode and an IndexOutOfBounds exception in insane mode.
For example, if we have a cursor CUR1 for the results of a SELECT query on
BASICTABLE1, and then we try to execute the following update statement:
update BASICTABLE1 set C3 = (SELECT CC3 FROM
BASICTABLE2 WHERE BASICTABLE1.ID=BASICTABLE2.IID)
where current of CUR1
the result in SANE mode will be:
org.apache.derby.shared.common.sanity.AssertFailure: ASSERT FAILED
tableNumber is expected to be non-negative.
and in INSANE mode will be:
java.lang.IndexOutOfBoundsException: bitIndex 0: -1
The failure occurs during preprocessing of the subquery node when Derby is
trying to categorize a predicate to see if it is pushable. The exact code
is in ColumnReference.categorize():
public boolean categorize(JBitSet referencedTabs, boolean simplePredsOnly)
{
if (SanityManager.DEBUG)
SanityManager.ASSERT(tableNumber = 0,
tableNumber is expected to be non-negative);
referencedTabs.set(tableNumber);
return ( ! replacesAggregate )
( (source.getExpression() instanceof ColumnReference) ||
(source.getExpression() instanceof VirtualColumnNode) ||
(source.getExpression() instanceof ConstantNode));
}
We get to this code for a ColumnReference who's tableNumber is -1, which
means that, in sane mode, the assert will fire; in insane mode, we'll call
referencedTabs.set() passing in a -1, which leads to the
IndexOutOfBoundsException.
This failure occurs in embedded and with both clients, and occurs in 10.0,
10.1, and the 10.2 trunk.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1384) Increase default BLOB/CLOB length to maximum supported (2G?)
[ http://issues.apache.org/jira/browse/DERBY-1384?page=comments#action_12415410 ] Satheesh Bandaram commented on DERBY-1384: -- Here is one situation where changing default max length would cause problem. In soft upgrade, if some one creates a default length BLOB/CLOB, if the default is changed, they could successfully create larger than 1MB blob/clob. On downgrade, statements like this would fail: INSERT INTO TAB SELECT * FROM BLOBTAB; where all blob/clob instances are created using default size, just some are created on soft-upgrade to 10.2. Soft upgrade checks are cheap and are designed to maintain same behavior across versions. Increase default BLOB/CLOB length to maximum supported (2G?) Key: DERBY-1384 URL: http://issues.apache.org/jira/browse/DERBY-1384 Project: Derby Type: Improvement Components: SQL Reporter: Bernt M. Johnsen Assignee: Bernt M. Johnsen Priority: Minor Fix For: 10.2.0.0 Attachments: derby-1384-code.diff, derby-1384-code.stat, derby-1384-docs.diff, derby-1384-docs.stat Default BLOB/CLOB length should be the maximum length supported by Derby (2G?) -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1384) Increase default BLOB/CLOB length to maximum supported (2G?)
[ http://issues.apache.org/jira/browse/DERBY-1384?page=comments#action_12415433 ] Satheesh Bandaram commented on DERBY-1384: -- This is the case I was thinking about. I have coded my application without specifying lengths for BLOB/CLOB. I would normally expect this to work: INSERT INTO tblob select * from tblob; OR INSERT INTO tblob select * from myblobTab; My concern was on soft-update, it would be possible to insert larger than 1MB and later on downgrade, same operations like above will not work as some instances of BLOB could now be larger than 1MB. (and hence above operations could fail on 10.1) May be this is extreme case and may not be worth protecting... but one could look at 10.1 BLOB (without size) datatype to be between 1-1MB and is being changed to 1-2GB. Increase default BLOB/CLOB length to maximum supported (2G?) Key: DERBY-1384 URL: http://issues.apache.org/jira/browse/DERBY-1384 Project: Derby Type: Improvement Components: SQL Reporter: Bernt M. Johnsen Assignee: Bernt M. Johnsen Priority: Minor Fix For: 10.2.0.0 Attachments: derby-1384-code.diff, derby-1384-code.stat, derby-1384-docs.diff, derby-1384-docs.stat Default BLOB/CLOB length should be the maximum length supported by Derby (2G?) -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1327) Identity column can be created with wrong and very large start with value with J2RE 1.5.0 IBM Windows 32 build pwi32dev-20060412 (SR2) with JIT on
[
http://issues.apache.org/jira/browse/DERBY-1327?page=comments#action_12415458 ]
Satheesh Bandaram commented on DERBY-1327:
--
I was reviewing this change and I noticed we are moving this:
- /* NOTE: We use the autoincColumn variable in order to work
around
-* a 1.3.0 HotSpot bug. (#4361550)
-*/
- boolean autoincColumn = (autoincInc != 0);
Does anyone know if this HotSpot bug is still an issue and any issues caused
by removing this code?
Noticed this code has been there since 10.0... so likely came from before code
contribution.
Identity column can be created with wrong and very large start with value
with J2RE 1.5.0 IBM Windows 32 build pwi32dev-20060412 (SR2) with JIT on
--
Key: DERBY-1327
URL: http://issues.apache.org/jira/browse/DERBY-1327
Project: Derby
Type: Bug
Components: SQL
Versions: 10.2.0.0, 10.1.2.4
Reporter: Kathey Marsden
Assignee: Mamta A. Satoor
Attachments: Derby1327WrongStartKeyPatch1CodelineTrunk.txt
Using the following JRE with JIT on an identity column may be created with a
wrong and very large START WITH value. When the problem occurs it affects
not only the table being created, but also other tables that were created in
previous transactions.
For example attempting to create 1000 tables with identity columns the 126th
table creation changes the start with value in sys.syscolumns to
41628850257395713 for ALL 125 tables. Attempts to insert into any of the
tables cause
SQL Exception: A truncation error was encountered trying to
shrink ... to length 12.
This program will create up to 1000 tables until the problem
occurs
Note:
- The problem does not occur with -Xnojit (JIT OFF)
- The problem, when it occurs, changes not only the table being
created but all previous tables created. See output below.
Every thing was fine up until mytable126 and then all the
tables got changed to start with 41628850257395713
- Problem occurs with autocommit on/off.
- The problem occurs after the create table but before the
commit.
- If the non-identity columns are removed the problem does not
reproduce.
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.DriverManager;
public class BadStartWith
{
public static void main (String args [])throws Exception
{
testBadStartWith();
}
/**
* After some number of table creations with JIT turned on, the START
WITH value
* for the table being created and all the ones already created gets
mysteriously
* changed with pwi32dev-20060412 (SR2)
*
* @throws Exception
*/
public static void testBadStartWith() throws Exception
{
Class.forName(org.apache.derby.jdbc.EmbeddedDriver).newInstance();
Connection conn =
DriverManager.getConnection(jdbc:derby:wombat;create=true);
conn.setAutoCommit(false);
Statement stmt = null;
DatabaseMetaData md = conn.getMetaData() ;
System.out.println(md.getDatabaseProductVersion());
System.out.println(md.getDatabaseProductName());
System.out.println(md.getDriverName());
dropAllAppTables(conn);
System.out.println(Create tables until we get a wrong Start
with value);
stmt = conn.createStatement();
// numBadStartWith will be changed if any columns get a bad
start with value.
int numBadStartWith = 0;
try {
// create 1000 tables. Break out if we get a table
that has a bad
// start with value.
for (int i = 0; (i 1000) (numBadStartWith == 0);
i++)
{
String tableName = APP.MYTABLE + i;
String createTableSQL = CREATE TABLE + tableName
+ (ROLEID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 2,
INCREMENT BY 1), INSTANCEID INTEGER, STATUS INTEGER, LOGICAL_STATE INTEGER,
LSTATE_TSTAMP TIMESTAMP, UPDT_TSTAMP TIMESTAMP, TSTAMP TIMESTAMP,
CLALEVEL1_CLALEVEL2_CLALEVEL2ID VARCHAR(255),
CLALEVEL1_CLALEVEL2_CLALEVEL3_CLALEVEL3ID VARCHAR(255));
stmt.executeUpdate(createTableSQL);
System.out.println(createTableSQL);
System.out.println(Check before commit);
numBadStartWith = checkBadStartWithCols(conn,2);
conn.commit
[jira] Commented: (DERBY-1365) Address potential problems with optimizer logic in some rarely-exercised code.
[
http://issues.apache.org/jira/browse/DERBY-1365?page=comments#action_12415056 ]
Satheesh Bandaram commented on DERBY-1365:
--
Submitted this patch to trunk and 10.1 branches. Thanks Army.
Address potential problems with optimizer logic in some rarely-exercised code.
--
Key: DERBY-1365
URL: http://issues.apache.org/jira/browse/DERBY-1365
Project: Derby
Type: Bug
Components: Performance
Versions: 10.1.2.4, 10.2.0.0, 10.1.3.0
Reporter: A B
Assignee: A B
Priority: Minor
Fix For: 10.2.0.0, 10.1.3.0, 10.1.2.5
Attachments: d1365_v1.patch, d1365_v1.stat
While looking at the optimization code in Derby for some other work I'm
doing, I've noticed a couple of small pieces of code that could potentially
lead to failures for some queries. Thus far I have been unable to come up
with any examples to demonstrate these problems with the current codeline
(hence the term potential problems) because the relevant lines of code are
hard to exercise--they require large, complex databases and/or some tricky
timing scenarios that I have thus far been unable to produce in the context
of the test harness. And in fact, a look at the code coverage results for
the pieces of code shows that neither is actually exercised by the current
derbyall suite--which I believe is more indicative of how hard it is to
exercise the code in question than it is of incomplete/lacking tests.
All of that said, analysis of the relevant code does indeed seem to indicate
that some (minor) changes are in order. In particular, consider the
following two potential problems.
1) Potential logic error when determining the best join order for a subquery
that has more than one FROM table.
This particular issue is very timing-sensitive. It will only occur if there
is an outer query which has a subquery and the optimization phase of the
subquery times out in the middle of costing a join order. The relevant
point in the code can be found in OptimizerImpl.java, around line 420:
if (permuteState != JUMPING)
{
// By setting firstLookOrder to our target join order
// and then setting our permuteState to JUMPING, we'll
// jump to the target join order and get the cost. That
// cost will then be saved as bestCost, allowing us to
// proceed with normal timeout logic.
for (int i = 0; i numOptimizables; i++)
firstLookOrder[i] = bestJoinOrder[i];
permuteState = JUMPING;
// If we were in the middle of a join order when this
// happened, then reset the join order before jumping.
if (joinPosition 0)
rewindJoinOrder();
}
The problem occurs at the last part of this if block, with the call to
rewind the join order. The rewindJoinOrder() method will pull each
optimizable that has an assigned position in the current join order and, for
each one, decrement joinPosition--until all optimizables have been pulled and
joinPosition has a value of 0. So far so good.
The trick is that, unlike the other calls to rewindJoinOrder() in
OptimizerImpl, this particular call occurs *before* the joinPosition variable
is incremented (it's incremented every time a call to find the next
permutation is made, until all optimizables (FROM tables) have been assigned
a position in the join order). What this means is that, with the code as it
currently stands, the call to rewindJoinOrder() will put joinPosition at 0,
but shortly thereafter joinPosition will be incremented to 1. The
subsequent search for a next permutation will then try to place an
optimizable at position 1 in the join order--but since there would be no
optimizable at position 0 (we would have inadvertently skipped over it
because of the increment after rewinding), the logic for finding/setting the
next optimizable in the join order would break down. So I think this needs
to be addressed--and it should be as simple as setting joinPosition to -1
after the aforementioned call to rewindJoinOrder(). This -1 value is what
joinPosition is set to before each round of optimization, so there is a
precedent and, I believe, that is the right thing to do. Once that's done
all of the existing logic will work as normal.
2) Potential NullPointerException if optimizer comes up with unreasonably
high cost estimates (esp. Double.POSITIVE_INFINITY) and then, at execution
time, Derby tries to do a hash join based on such an estimate with a
ResultSet that has no rows.
In this case, the code in question is in the constructor logic for
BackingStoreHashtable.java. In cases where the backing hash table receives
an unreasonably high cost estimate (a red flag estimate as noted in the
comments), it's possible that, for cases where the row_source
[jira] Commented: (DERBY-1329) ASSERT failure/IndexOutOfBoundsException with correlated subquery for UPDATE ... SET ... WHERE CURRENT OF ... statement.
[
http://issues.apache.org/jira/browse/DERBY-1329?page=comments#action_12414557 ]
Satheesh Bandaram commented on DERBY-1329:
--
Submitted this patch to trunk. If you have interest in putting this patch into
10.1.3, please post a 10.1 patch. Looks like this change will not apply
directly to 10.1 as fix for DERBY-171 is not in 10.1.
ASSERT failure/IndexOutOfBoundsException with correlated subquery for UPDATE
... SET ... WHERE CURRENT OF ... statement.
Key: DERBY-1329
URL: http://issues.apache.org/jira/browse/DERBY-1329
Project: Derby
Type: Bug
Components: SQL
Versions: 10.0.2.0, 10.0.2.1, 10.1.1.0, 10.2.0.0, 10.1.2.0, 10.1.1.1,
10.1.1.2, 10.1.2.1, 10.1.2.2, 10.1.2.3, 10.1.2.4
Reporter: A B
Assignee: A B
Priority: Minor
Fix For: 10.2.0.0, 10.1.3.0
Attachments: d1329.java, d1329.patch
If in a statement of the form UPDATE ... SET ... WHERE CURRENT OF ... the
SET clause includes a correlated subquery that has a predicate referencing
the table that is being updated, Derby will fail with an ASSERT failure in
sane mode and an IndexOutOfBounds exception in insane mode.
For example, if we have a cursor CUR1 for the results of a SELECT query on
BASICTABLE1, and then we try to execute the following update statement:
update BASICTABLE1 set C3 = (SELECT CC3 FROM
BASICTABLE2 WHERE BASICTABLE1.ID=BASICTABLE2.IID)
where current of CUR1
the result in SANE mode will be:
org.apache.derby.shared.common.sanity.AssertFailure: ASSERT FAILED
tableNumber is expected to be non-negative.
and in INSANE mode will be:
java.lang.IndexOutOfBoundsException: bitIndex 0: -1
The failure occurs during preprocessing of the subquery node when Derby is
trying to categorize a predicate to see if it is pushable. The exact code
is in ColumnReference.categorize():
public boolean categorize(JBitSet referencedTabs, boolean simplePredsOnly)
{
if (SanityManager.DEBUG)
SanityManager.ASSERT(tableNumber = 0,
tableNumber is expected to be non-negative);
referencedTabs.set(tableNumber);
return ( ! replacesAggregate )
( (source.getExpression() instanceof ColumnReference) ||
(source.getExpression() instanceof VirtualColumnNode) ||
(source.getExpression() instanceof ConstantNode));
}
We get to this code for a ColumnReference who's tableNumber is -1, which
means that, in sane mode, the assert will fire; in insane mode, we'll call
referencedTabs.set() passing in a -1, which leads to the
IndexOutOfBoundsException.
This failure occurs in embedded and with both clients, and occurs in 10.0,
10.1, and the 10.2 trunk.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-655) getImportedKeys returns duplicate rows in some cases
[ http://issues.apache.org/jira/browse/DERBY-655?page=comments#action_12414558 ] Satheesh Bandaram commented on DERBY-655: - This patch is committed to trunk and 10.1. Please RESOLVE and close accordingly. getImportedKeys returns duplicate rows in some cases Key: DERBY-655 URL: http://issues.apache.org/jira/browse/DERBY-655 Project: Derby Type: Bug Components: SQL Versions: 10.2.0.0 Environment: Sun JDK 1.4.2, Windows XP Reporter: Deepa Remesh Assignee: Mamta A. Satoor Attachments: Derby655GetImportedKeysPatch1.txt, Derby655GetImportedKeysPatch1Codeline101.txt, Derby655GetImportedKeysPatch1CodelineTrunk.txt, Derby655GetImportedKeysPatch2Codeline101.txt, Derby655GetImportedKeysStat1.txt, bigdb.sql, keys.java, keys_test.zip, smalldb.sql I have a database with a large number of tables. I get duplicate rows when I call DatabaseMetaData.getImportedKeys for a particular table. However, if I create the same table with same number of foreign keys in another database (which has fewer number of tables), then getImportedKeys returns me the correct number of rows. This error seems to happen only when the database has a large number of tables. I will attach a repro for this shortly. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
