Derby
  1. Derby
  2. 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 configurations.
  3. 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 they cover are dropped.

    Details

    • Type: Sub-task Sub-task
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.2.1.6
    • Fix Version/s: 10.2.1.6
    • Component/s: SQL
    • Labels:
      None
    • Urgency:
      Normal

      Description

      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.

      1. DERBY-1543.out
        11 kB
        Satheesh Bandaram
      2. Derby1543.diff
        43 kB
        Satheesh Bandaram

        Activity

        Hide
        Satheesh Bandaram added a comment -

        Added tests and it seems to work as expected.

        Show
        Satheesh Bandaram added a comment - Added tests and it seems to work as expected.
        Hide
        Satheesh Bandaram added a comment -

        Submitted a fix.

        Show
        Satheesh Bandaram added a comment - Submitted a fix.
        Hide
        Mamta A. Satoor added a comment -

        syscat.sql is failing when run with framework as DerbyNetClient. Seems like it might be just a master update issue. I ran the test with Sun jdk1.4 on Windows XP machine as follows
        java -Dframework=DerbyNetClient org.apache.derbyTesting.functionTests.harness.RunTest lang/syscat.sql

        Show
        Mamta A. Satoor added a comment - syscat.sql is failing when run with framework as DerbyNetClient. Seems like it might be just a master update issue. I ran the test with Sun jdk1.4 on Windows XP machine as follows java -Dframework=DerbyNetClient org.apache.derbyTesting.functionTests.harness.RunTest lang/syscat.sql
        Hide
        Mamta A. Satoor added a comment -

        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.

        Show
        Mamta A. Satoor added a comment - 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.
        Hide
        Satheesh Bandaram added a comment -

        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.

        Show
        Satheesh Bandaram added a comment - 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.
        Hide
        Satheesh Bandaram added a comment -

        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.

        Show
        Satheesh Bandaram added a comment - 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.
        Hide
        Satheesh Bandaram added a comment -

        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!

        Show
        Satheesh Bandaram added a comment - 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!
        Hide
        Yip Ng added a comment -

        Are you referring in the context of Derby? For example, DB2 and IDS allows synonyms in their GRANT or REVOKE statements.

        db2 => create table t1 (c1 int)
        DB20000I The SQL command completed successfully.
        db2 => create synonym s1 for t1
        DB20000I The SQL command completed successfully.
        db2 => grant select on s1 to user2
        DB20000I The SQL command completed successfully.
        db2 => revoke select on s1 from user2
        DB20000I The SQL command completed successfully.
        db2=>

        Show
        Yip Ng added a comment - Are you referring in the context of Derby? For example, DB2 and IDS allows synonyms in their GRANT or REVOKE statements. db2 => create table t1 (c1 int) DB20000I The SQL command completed successfully. db2 => create synonym s1 for t1 DB20000I The SQL command completed successfully. db2 => grant select on s1 to user2 DB20000I The SQL command completed successfully. db2 => revoke select on s1 from user2 DB20000I The SQL command completed successfully. db2=>
        Hide
        Satheesh Bandaram added a comment -

        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.

        Show
        Satheesh Bandaram added a comment - 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.
        Hide
        Yip Ng added a comment -

        Hi Satheesh:

        One of my testcases failed for synonym for grant and revoke, will this be addressed in DERBY-1543 or it will be documented as a restriction on GRANT/REVOKE functionality?

        Show
        Yip Ng added a comment - Hi Satheesh: One of my testcases failed for synonym for grant and revoke, will this be addressed in DERBY-1543 or it will be documented as a restriction on GRANT/REVOKE functionality?
        Hide
        Satheesh Bandaram added a comment -

        I have a patch that is partial. Will post final complete patch soon.

        Show
        Satheesh Bandaram added a comment - I have a patch that is partial. Will post final complete patch soon.
        Hide
        Satheesh Bandaram added a comment -

        Good point, Dan. I will address this in my final version of the patch.

        Show
        Satheesh Bandaram added a comment - Good point, Dan. I will address this in my final version of the patch.
        Hide
        Daniel John Debrunner added a comment -

        How does the dropping of permission descriptors relate to the permission cache? In this code it seems the cache is not touched when a table's permissions are removed.

        Show
        Daniel John Debrunner added a comment - How does the dropping of permission descriptors relate to the permission cache? In this code it seems the cache is not touched when a table's permissions are removed.
        Hide
        Satheesh Bandaram added a comment -

        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.

        Show
        Satheesh Bandaram added a comment - 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.
        Hide
        Mamta A. Satoor added a comment -

        Satheesh, when you say "This applies to tables, routines", you mean base tables, derived tables(views) and routines, right? I guess table can implictly mean base table as well as views.

        Show
        Mamta A. Satoor added a comment - Satheesh, when you say "This applies to tables, routines", you mean base tables, derived tables(views) and routines, right? I guess table can implictly mean base table as well as views.

          People

          • Assignee:
            Satheesh Bandaram
            Reporter:
            Satheesh Bandaram
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development