Derby
  1. Derby
  2. DERBY-1538

Unexpected behavior on self privilege revocation

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.2.1.6
    • Fix Version/s: 10.2.1.6, 10.3.1.4
    • Component/s: SQL
    • Labels:
      None
    • Environment:
      Windows XP Pro

      Description

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

        Issue Links

          Activity

          Hide
          Mamta A. Satoor added a comment -

          Welcome to Derby, Yip.

          As for this particular JIRA entry, I am not sure what SQL specification says about revoking privileges from self. But I do know that, currently in Derby, if a user in general tries to revoke a permission from another user that was never granted, the revoke is a no-op and returns back silently, just like what it did for your specific example. So, what you are seeing might be expected behavior which is not mentioned in the Grant/Revoke spec. Satheesh might now the specifics.

          Show
          Mamta A. Satoor added a comment - Welcome to Derby, Yip. As for this particular JIRA entry, I am not sure what SQL specification says about revoking privileges from self. But I do know that, currently in Derby, if a user in general tries to revoke a permission from another user that was never granted, the revoke is a no-op and returns back silently, just like what it did for your specific example. So, what you are seeing might be expected behavior which is not mentioned in the Grant/Revoke spec. Satheesh might now the specifics.
          Hide
          Satheesh Bandaram added a comment -

          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.

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

          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.

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

          Closing this issue... If this fix is ported to 10.2, reopen to mark as fixed in 10.2.

          Show
          Satheesh Bandaram added a comment - Closing this issue... If this fix is ported to 10.2, reopen to mark as fixed in 10.2.
          Hide
          Rajesh Kartha added a comment -

          Does not look like the fix has gone into the 10.2 branch yet. I think this check for self privilege is useful, hence Re-opening this issue so it does not get left behind for 10.2

          Can one of the committers do the needful to merge this.

          Show
          Rajesh Kartha added a comment - Does not look like the fix has gone into the 10.2 branch yet. I think this check for self privilege is useful, hence Re-opening this issue so it does not get left behind for 10.2 Can one of the committers do the needful to merge this.
          Hide
          Rick Hillegas added a comment -

          Ported to 10.2 branch at subversion revision 436859.

          Show
          Rick Hillegas added a comment - Ported to 10.2 branch at subversion revision 436859.

            People

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

              Dates

              • Created:
                Updated:
                Resolved:

                Development