Derby
  1. Derby
  2. DERBY-4295

SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE cannot be executed after granting permission for execute.

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Minor Minor
    • Resolution: Unresolved
    • Affects Version/s: 10.5.1.1
    • Fix Version/s: None
    • Component/s: Documentation
    • Environment:
      Windows Vista
    • Urgency:
      Normal
    • Issue & fix info:
      Newcomer, Repro attached
    • Bug behavior facts:
      Security

      Description

      When using derby.properties file from mailjdbc tests, call SYSCS_UTIL.SYCS_INPLACE_COMPRESs_TABLE is fine user 'BACKUP' with the following operation. Please see below:
      ij version 10.3
      ij> connect 'jdbc:derby:tpri;user=BACKUP;password=Backup';
      ij> create table a (col1 int, col2 clob);
      0 rows inserted/updated/deleted
      ij> insert into a values (1, '1');
      1 row inserted/updated/deleted
      ij> insert into a values (2, '2');
      1 row inserted/updated/deleted
      ij> grant execute on procedure SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE to BACKUP;
      0 rows inserted/updated/deleted
      ij version 10.3
      ij> connect 'jdbc:derby:tpri;user=BACKUP;password=Backup';
      ij> call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('REFRESH','A',1,1,1);
      0 rows inserted/updated/deleted <<<====Operation successful=====

      However, on 10.5, the same operation result ERROR 38000
      ij> connect 'jdbc:derby:tpri;create=true;user=REFRESH;password=Refresh';
      ij> create table a (col1 int, col2 clob);
      0 rows inserted/updated/deleted
      ij> insert into a values (1, '1');
      1 row inserted/updated/deleted
      ij> insert into a values (1, '2');
      1 row inserted/updated/deleted
      ij> grant execute on procedure SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE to BACKUP;
      0 rows inserted/updated/deleted
      ij> exit;

      ij version 10.5
      ij> connect 'jdbc:derby:tpri;user=BACKUP;password=Backup';
      ij> call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('REFRESH','A',1,1,1);
      ERROR 38000: The exception 'java.sql.SQLException: User 'BACKUP' can not perform
      the operation in schema 'REFRESH'.' was thrown while evaluating an expression.
      ERROR 42507: User 'BACKUP' can not perform the operation in schema 'REFRESH'. <<<====Operation result with ERROR 38000
      ERROR 42507: User 'BACKUP' can not perform the operation in schema 'REFRESH'.^M
      at org.apache.derby.iapi.error.StandardException.newException(StandardEx
      ception.java:303)^M
      at org.apache.derby.iapi.sql.dictionary.StatementSchemaPermission.check(
      StatementSchemaPermission.java:83)^M
      at org.apache.derby.impl.sql.conn.GenericAuthorizer.authorize(GenericAut
      horizer.java:186)^M
      at org.apache.derby.impl.sql.execute.GenericResultSetFactory.getDDLResul
      tSet(GenericResultSetFactory.java:1073)^M
      at org.apache.derby.impl.sql.execute.ConstantActionActivation.execute(Co
      nstantActionActivation.java:61)^M
      at org.apache.derby.impl.sql.GenericActivationHolder.execute(GenericActi
      vationHolder.java:352)^M
      at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(Generi
      cPreparedStatement.java:414)^M
      at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPre
      paredStatement.java:297)^M
      at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedState
      ment.java:1235)^M
      at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Em
      bedPreparedStatement.java:1648)^M
      at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(Embed
      PreparedStatement.java:294)^M
      at org.apache.derby.catalog.SystemProcedures.SYSCS_INPLACE_COMPRESS_TABL
      E(SystemProcedures.java:1145)^M
      at org.apache.derby.exe.ac83ba410fx0122x159fx9a84x0000004261f80.g0(Unkno
      wn Source)^M
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)^M
      at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)^M
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)^M
      at java.lang.reflect.Method.invoke(Unknown Source)^M
      at org.apache.derby.impl.services.reflect.ReflectMethod.invoke(ReflectMe
      thod.java:46)
      at org.apache.derby.impl.sql.execute.CallStatementResultSet.open(CallSta
      tementResultSet.java:76)^M
      at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(Generi
      cPreparedStatement.java:416)^M
      at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPre
      paredStatement.java:297)^M
      at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedState
      ment.java:1235)^M
      at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Em
      bedPreparedStatement.java:1648)^M
      at org.apache.derby.impl.jdbc.EmbedCallableStatement.executeStatement(Em
      bedCallableStatement.java:117)^M
      at org.apache.derby.impl.jdbc.EmbedPreparedStatement.execute(EmbedPrepar
      edStatement.java:1303)^M
      at org.apache.derbyTesting.system.mailjdbc.utils.DbTasks.compressTable(D
      bTasks.java:618)^M
      at org.apache.derbyTesting.system.mailjdbc.tasks.Backup.DoCompress(Backu
      p.java:79)^M
      at org.apache.derbyTesting.system.mailjdbc.tasks.Backup.doWork(Backup.ja
      va:68)^M
      at org.apache.derbyTesting.system.mailjdbc.tasks.Backup.run(Backup.java:
      46)^M
      Cleanup action completed^M

      I will add the test to test/langGrantRevokeDDLTest.java.

        Activity

        Hide
        Mamta A. Satoor added a comment -

        Went through the jira and it doesn't look like we came to any conclusion on what is the right behavior. We would need to decide on the correct behavior when we work on this jira again,

        Show
        Mamta A. Satoor added a comment - Went through the jira and it doesn't look like we came to any conclusion on what is the right behavior. We would need to decide on the correct behavior when we work on this jira again,
        Hide
        Mamta A. Satoor added a comment -

        I am unassigning myself from this jira for now. We need to conside backward compatibility, SQL standards when we decide to look at this jira again.

        Show
        Mamta A. Satoor added a comment - I am unassigning myself from this jira for now. We need to conside backward compatibility, SQL standards when we decide to look at this jira again.
        Hide
        Kathey Marsden added a comment -

        I talked to Mike who felt that compress should require the same permissions as alter table. I checked the SQL spec and found this about ALTER TABLE.
        The enabled authorization identifiers shall include the <authorization identifier> that owns the schema
        identified by the <schema name> of the table identified by <table name>.

        I verified that this is how it is currently implemented as follows.
        derby.properties:
        derby.database.sqlAuthorization=true
        derby.connection.requireAuthentication=true
        derby.infolog.append=true
        derby.authentication.provider=BUILTIN
        derby.stream.error.logSeverityLevel=0

        #derby.language.logStatementText=true

        1. User's Definitions
          derby.user.DBO=Dbo
          derby.user.SCHAOWNER=SCHAOwner
          derby.user.SCHBOWNER=SCHBOwner
          ij> connect 'jdbc:derby:wombat;create=true;user=DBO;password=Dbo';
          ij> connect 'jdbc:derby:wombat;user=SCHAOWNER;password=SCHAOwner';
          ij(CONNECTION1)> create table atab (i int);
          0 rows inserted/updated/deleted
          ij(CONNECTION1)> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('SCHAOWNER', 'ATAB', 1);
          0 rows inserted/updated/deleted
          ij(CONNECTION1)> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('SCHBOWNER', 'BTAB', 1);
          ERROR 38000: The exception 'java.sql.SQLException: Schema 'SCHBOWNER' does not exist' was thrown while evaluating an expression.
          ERROR 42Y07: Schema 'SCHBOWNER' does not exist
          ij(CONNECTION1)> call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('SCHAOWNER', 'ATAB', 1, 1, 1);
          0 rows inserted/updated/deleted
          ij(CONNECTION1)> call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('SCHAOWNER', 'ATAB', 1, 1, 1);
          0 rows inserted/updated/deleted
          ij(CONNECTION1)> connect 'jdbc:derby:wombat;user=SCHBOWNER;password=SCHBOwner';
          ij(CONNECTION2)> create table btab (i int);
          0 rows inserted/updated/deleted
          ij(CONNECTION2)> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('SCHAOWNER', 'ATAB', 1);
          ERROR 38000: The exception 'java.sql.SQLException: User 'SCHBOWNER' can not perform the operation in schema 'SCHAOWNER'.' was thrown while evaluating an expression.
          ERROR 42507: User 'SCHBOWNER' can not perform the operation in schema 'SCHAOWNER'.
          ij(CONNECTION2)> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('SCHBOWNER', 'BTAB', 1);
          0 rows inserted/updated/deleted
          ij(CONNECTION2)> call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('SCHAOWNER', 'ATAB', 1, 1, 1);
          ERROR 38000: The exception 'java.sql.SQLException: User 'SCHBOWNER' can not perform the operation in schema 'SCHAOWNER'.' was thrown while evaluating an expression.
          ERROR 42507: User 'SCHBOWNER' can not perform the operation in schema 'SCHAOWNER'.
          ij(CONNECTION2)> call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('SCHAOWNER', 'ATAB', 1, 1, 1);
          ERROR 38000: The exception 'java.sql.SQLException: User 'SCHBOWNER' can not perform the operation in schema 'SCHAOWNER'.' was thrown while evaluating an expression.
          ERROR 42507: User 'SCHBOWNER' can not perform the operation in schema 'SCHAOWNER'.
          ij(CONNECTION2)> connect 'jdbc:derby:wombat;user=DBO;password=Dbo';
          ij(CONNECTION3)> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('SCHAOWNER', 'ATAB', 1);
          0 rows inserted/updated/deleted
          ij(CONNECTION3)> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('SCHBOWNER', 'BTAB', 1);
          0 rows inserted/updated/deleted
          ij(CONNECTION3)> call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('SCHAOWNER', 'ATAB', 1, 1, 1);
          0 rows inserted/updated/deleted
          ij(CONNECTION3)> call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('SCHAOWNER', 'ATAB', 1, 1, 1);
          0 rows inserted/updated/deleted
          ij(CONNECTION3)>

        At least the DBO can perform the operation. As Lily reported earlier no level of grant privilege will make a difference. I still would prefer that only grant execute priv be required on the procedure because even though our code passes through ALTER TABLE, it is not really an ALTER TABLE from the user perspective.

        Show
        Kathey Marsden added a comment - I talked to Mike who felt that compress should require the same permissions as alter table. I checked the SQL spec and found this about ALTER TABLE. The enabled authorization identifiers shall include the <authorization identifier> that owns the schema identified by the <schema name> of the table identified by <table name>. I verified that this is how it is currently implemented as follows. derby.properties: derby.database.sqlAuthorization=true derby.connection.requireAuthentication=true derby.infolog.append=true derby.authentication.provider=BUILTIN derby.stream.error.logSeverityLevel=0 #derby.language.logStatementText=true User's Definitions derby.user.DBO=Dbo derby.user.SCHAOWNER=SCHAOwner derby.user.SCHBOWNER=SCHBOwner ij> connect 'jdbc:derby:wombat;create=true;user=DBO;password=Dbo'; ij> connect 'jdbc:derby:wombat;user=SCHAOWNER;password=SCHAOwner'; ij(CONNECTION1)> create table atab (i int); 0 rows inserted/updated/deleted ij(CONNECTION1)> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('SCHAOWNER', 'ATAB', 1); 0 rows inserted/updated/deleted ij(CONNECTION1)> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('SCHBOWNER', 'BTAB', 1); ERROR 38000: The exception 'java.sql.SQLException: Schema 'SCHBOWNER' does not exist' was thrown while evaluating an expression. ERROR 42Y07: Schema 'SCHBOWNER' does not exist ij(CONNECTION1)> call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('SCHAOWNER', 'ATAB', 1, 1, 1); 0 rows inserted/updated/deleted ij(CONNECTION1)> call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('SCHAOWNER', 'ATAB', 1, 1, 1); 0 rows inserted/updated/deleted ij(CONNECTION1)> connect 'jdbc:derby:wombat;user=SCHBOWNER;password=SCHBOwner'; ij(CONNECTION2)> create table btab (i int); 0 rows inserted/updated/deleted ij(CONNECTION2)> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('SCHAOWNER', 'ATAB', 1); ERROR 38000: The exception 'java.sql.SQLException: User 'SCHBOWNER' can not perform the operation in schema 'SCHAOWNER'.' was thrown while evaluating an expression. ERROR 42507: User 'SCHBOWNER' can not perform the operation in schema 'SCHAOWNER'. ij(CONNECTION2)> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('SCHBOWNER', 'BTAB', 1); 0 rows inserted/updated/deleted ij(CONNECTION2)> call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('SCHAOWNER', 'ATAB', 1, 1, 1); ERROR 38000: The exception 'java.sql.SQLException: User 'SCHBOWNER' can not perform the operation in schema 'SCHAOWNER'.' was thrown while evaluating an expression. ERROR 42507: User 'SCHBOWNER' can not perform the operation in schema 'SCHAOWNER'. ij(CONNECTION2)> call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('SCHAOWNER', 'ATAB', 1, 1, 1); ERROR 38000: The exception 'java.sql.SQLException: User 'SCHBOWNER' can not perform the operation in schema 'SCHAOWNER'.' was thrown while evaluating an expression. ERROR 42507: User 'SCHBOWNER' can not perform the operation in schema 'SCHAOWNER'. ij(CONNECTION2)> connect 'jdbc:derby:wombat;user=DBO;password=Dbo'; ij(CONNECTION3)> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('SCHAOWNER', 'ATAB', 1); 0 rows inserted/updated/deleted ij(CONNECTION3)> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('SCHBOWNER', 'BTAB', 1); 0 rows inserted/updated/deleted ij(CONNECTION3)> call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('SCHAOWNER', 'ATAB', 1, 1, 1); 0 rows inserted/updated/deleted ij(CONNECTION3)> call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('SCHAOWNER', 'ATAB', 1, 1, 1); 0 rows inserted/updated/deleted ij(CONNECTION3)> At least the DBO can perform the operation. As Lily reported earlier no level of grant privilege will make a difference. I still would prefer that only grant execute priv be required on the procedure because even though our code passes through ALTER TABLE, it is not really an ALTER TABLE from the user perspective.
        Hide
        Mamta A. Satoor added a comment -

        Dag, the current behavior of 10.5 is surely different than the one in 10.3. Depending on what we decide to do, the behavior may end up being same again (ie if we decide to require just the execute privilege and not individual table level privileges).

        Show
        Mamta A. Satoor added a comment - Dag, the current behavior of 10.5 is surely different than the one in 10.3. Depending on what we decide to do, the behavior may end up being same again (ie if we decide to require just the execute privilege and not individual table level privileges).
        Hide
        Mamta A. Satoor added a comment -

        Kathey, since the user can't see/alter the actual data, it should probably be fine to have just the execute privilege for this particular procedure. The only other thing I can think of is it will be little confusing for users to need table level privileges for other procedures(which involve tables) and not to require those privileges for this compress procedure. Maybe we can avoid this confusion with proper documentation right under each procedure which will provide the necesarry privilege requirement.

        Show
        Mamta A. Satoor added a comment - Kathey, since the user can't see/alter the actual data, it should probably be fine to have just the execute privilege for this particular procedure. The only other thing I can think of is it will be little confusing for users to need table level privileges for other procedures(which involve tables) and not to require those privileges for this compress procedure. Maybe we can avoid this confusion with proper documentation right under each procedure which will provide the necesarry privilege requirement.
        Hide
        Dag H. Wanvik added a comment -

        Checked "repro attached".

        Show
        Dag H. Wanvik added a comment - Checked "repro attached".
        Hide
        Dag H. Wanvik added a comment -

        Triaged for 10.5.2: checked "security". Is it a regression as well?

        Show
        Dag H. Wanvik added a comment - Triaged for 10.5.2: checked "security". Is it a regression as well?
        Hide
        Kathey Marsden added a comment -

        From a practical perspective, I think it is common for a single administrative user to compress the tables and backup the database, so it is important to be able to grant this privilege for all tables to a single user. I personally think that permission to execute the procedure should be sufficient since compress is not really DELETE, INSERT or UPDATE or SELECT. It could be that we just want our backup user to perform this task but not see or change the data.

        Show
        Kathey Marsden added a comment - From a practical perspective, I think it is common for a single administrative user to compress the tables and backup the database, so it is important to be able to grant this privilege for all tables to a single user. I personally think that permission to execute the procedure should be sufficient since compress is not really DELETE, INSERT or UPDATE or SELECT. It could be that we just want our backup user to perform this task but not see or change the data.
        Hide
        Mamta A. Satoor added a comment -

        I am surprised that even with correct privileges, we do not allow inplace compress by non-table owner. I can't recall if we decided to implement it this way. If no one has objection, i think we should allow a user with correct privileges to compress the table.

        Show
        Mamta A. Satoor added a comment - I am surprised that even with correct privileges, we do not allow inplace compress by non-table owner. I can't recall if we decided to implement it this way. If no one has objection, i think we should allow a user with correct privileges to compress the table.
        Hide
        Lily Wei added a comment -

        For user BACKUP, it can not perform SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE after getting all the privileges from owner of the table. I think 'BACKUP' user should be able to perform SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE if it has the privileges. For example, if user 'REFRESH' perform 'grant all privileges on REFERSH.INBOX to BACKUP', user BACKUP should be able to do call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE. I talked to Kathey. She seems to like this idea too. What do you think? Right now, with this check:
        if (!authid.equals(sd.getAuthorizationId()))
        throw StandardException.newException(
        SQLState.AUTH_NO_ACCESS_NOT_OWNER, authid, schemaName);

        Only owner of the table can compress the table.

        Show
        Lily Wei added a comment - For user BACKUP, it can not perform SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE after getting all the privileges from owner of the table. I think 'BACKUP' user should be able to perform SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE if it has the privileges. For example, if user 'REFRESH' perform 'grant all privileges on REFERSH.INBOX to BACKUP', user BACKUP should be able to do call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE. I talked to Kathey. She seems to like this idea too. What do you think? Right now, with this check: if (!authid.equals(sd.getAuthorizationId())) throw StandardException.newException( SQLState.AUTH_NO_ACCESS_NOT_OWNER, authid, schemaName); Only owner of the table can compress the table.
        Hide
        Mamta A. Satoor added a comment -

        While researching on this jira, I found that Derby is not requiring a user to have execute privileges on SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE which I think should be required before a user can use it to try compressing a table. At least, that is what we require for SYSCS_UTIL.SYSCS_EXPORT_TABLE. I have entered DERBY-4296 regarding the requirement of execute privilege on SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE. May be there are other procedures too for which we do not do this check. That research can be done when we decide to fix DERBY-4296

        Show
        Mamta A. Satoor added a comment - While researching on this jira, I found that Derby is not requiring a user to have execute privileges on SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE which I think should be required before a user can use it to try compressing a table. At least, that is what we require for SYSCS_UTIL.SYSCS_EXPORT_TABLE. I have entered DERBY-4296 regarding the requirement of execute privilege on SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE. May be there are other procedures too for which we do not do this check. That research can be done when we decide to fix DERBY-4296
        Hide
        Mamta A. Satoor added a comment -

        I looked through the documentation and didn't see any privilege granting being mentioned for various procedures.

        For instance, in the example above, if user BACKUP was granted execute privilege on SYSCS_EXPORT_TABLE but not access to table a, then that user will not be able to execute following.
        call SYSCS_UTIL.SYSCS_EXPORT_TABLE('REFRESH','A','a',null,null,null);
        Caused by: java.sql.SQLException: User 'BACKUP' does not have SELECT permission on column 'COL1' of table 'REFRESH'.'A'.

        But there is no mention on this privilege requirement in Derby Reference Manual where we discuss SYSCS_EXPORT_TABLE.

        It seems like Derby Reference Manual might be the correct place to document what are various privilege requirements for different procedures. Alternative place could be where Grant/Revoke are discussed. Would appreciate any feedback on the right doc that should be changed.

        Show
        Mamta A. Satoor added a comment - I looked through the documentation and didn't see any privilege granting being mentioned for various procedures. For instance, in the example above, if user BACKUP was granted execute privilege on SYSCS_EXPORT_TABLE but not access to table a, then that user will not be able to execute following. call SYSCS_UTIL.SYSCS_EXPORT_TABLE('REFRESH','A','a',null,null,null); Caused by: java.sql.SQLException: User 'BACKUP' does not have SELECT permission on column 'COL1' of table 'REFRESH'.'A'. But there is no mention on this privilege requirement in Derby Reference Manual where we discuss SYSCS_EXPORT_TABLE. It seems like Derby Reference Manual might be the correct place to document what are various privilege requirements for different procedures. Alternative place could be where Grant/Revoke are discussed. Would appreciate any feedback on the right doc that should be changed.
        Hide
        Lily Wei added a comment - - edited

        Thanks Mamta for looking at the issue and point out our intenstion is to throw the error. Yes, there is a test in GrantRevokeDDLTest as user sam try to do SYSCS_UTIL.SYSCS_COMPRESS_TABLE on table SWIPER.MYTAB that is owned by SWIPER. Please disregard my changes. Will users ever want to be able to grant execute permission to another user? I guess we are saying we don't allow such case. I will vote for having this mention in the documentation.

        I will change mailjdbc test to reflect this behavior. Thanks.

        Show
        Lily Wei added a comment - - edited Thanks Mamta for looking at the issue and point out our intenstion is to throw the error. Yes, there is a test in GrantRevokeDDLTest as user sam try to do SYSCS_UTIL.SYSCS_COMPRESS_TABLE on table SWIPER.MYTAB that is owned by SWIPER. Please disregard my changes. Will users ever want to be able to grant execute permission to another user? I guess we are saying we don't allow such case. I will vote for having this mention in the documentation. I will change mailjdbc test to reflect this behavior. Thanks.
        Hide
        Mamta A. Satoor added a comment -

        I looked at this more and DERBY-1062 and the existing tests in GrantRevokeDDLTest and actually this was an intentional behavior change. Have copied couple comments from DERBY-1062 here in regarding to the table permission
        ********************
        Kathey Marsden added a comment - 13/May/08 12:37 PM
        I didn't take a close look at the patch, but this diff caught my eye. Is there a case that used to compress that now throws an error?

        "SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('SWIPER', "
        + "'MYTAB', 1, 1, 1)");

        • assertUpdateCount(cSt, 0);
          + assertStatementError("38000", cSt);
          cSt.close();

        Mamta A. Satoor added a comment - 13/May/08 12:57 PM
        Thanks for taking a look at the patch, Kathey. The test was testing to compress a table that is not owned by the user issuing SYSCS_INPLACE_COMPRESS_TABLE. After the fix for this jira entry, we correctly throw "38000" just like what we do for SYSCS_COMPRESS_TABLE. I hope that answers your question. I have fixed the comment in GrantRevokeDDLTest which said that SYSCS_INPLACE_COMPRESS_TABLE will not behave correctly until DERBY-1062 is fixed. The comment fix went in as revision 655990.
        **************************

        So, Lily, what you are experiencing is the expected behavior. I wonder if we made any changes to the doc to reflect this change in behavior which went in Derby 10.5 I will look into the docs.

        Also, thanks for working on adding the test but we already have coverage for it in GrantRevokeDDLTest. Following existing test does the table ownership check

        // Try compressing tables not owned.

        cSt = samConnection.prepareCall(
        "call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('SWIPER', 'MYTAB', 1)");
        assertStatementError("38000", cSt);
        cSt.close();

        cSt = samConnection.prepareCall(
        " call "
        + "SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('SWIPER', "
        + "'MYTAB', 1, 1, 1)");
        assertStatementError("38000", cSt);
        cSt.close();

        Show
        Mamta A. Satoor added a comment - I looked at this more and DERBY-1062 and the existing tests in GrantRevokeDDLTest and actually this was an intentional behavior change. Have copied couple comments from DERBY-1062 here in regarding to the table permission ******************** Kathey Marsden added a comment - 13/May/08 12:37 PM I didn't take a close look at the patch, but this diff caught my eye. Is there a case that used to compress that now throws an error? "SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('SWIPER', " + "'MYTAB', 1, 1, 1)"); assertUpdateCount(cSt, 0); + assertStatementError("38000", cSt); cSt.close(); Mamta A. Satoor added a comment - 13/May/08 12:57 PM Thanks for taking a look at the patch, Kathey. The test was testing to compress a table that is not owned by the user issuing SYSCS_INPLACE_COMPRESS_TABLE. After the fix for this jira entry, we correctly throw "38000" just like what we do for SYSCS_COMPRESS_TABLE. I hope that answers your question. I have fixed the comment in GrantRevokeDDLTest which said that SYSCS_INPLACE_COMPRESS_TABLE will not behave correctly until DERBY-1062 is fixed. The comment fix went in as revision 655990. ************************** So, Lily, what you are experiencing is the expected behavior. I wonder if we made any changes to the doc to reflect this change in behavior which went in Derby 10.5 I will look into the docs. Also, thanks for working on adding the test but we already have coverage for it in GrantRevokeDDLTest. Following existing test does the table ownership check // Try compressing tables not owned. cSt = samConnection.prepareCall( "call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('SWIPER', 'MYTAB', 1)"); assertStatementError("38000", cSt); cSt.close(); cSt = samConnection.prepareCall( " call " + "SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('SWIPER', " + "'MYTAB', 1, 1, 1)"); assertStatementError("38000", cSt); cSt.close();
        Hide
        Lily Wei added a comment -

        I add the tests for this issue on GrantRevokeDDLTest.java. Please help me review it.
        This is my test result:
        $ java junit.textui.TestRunner org.apache.derbyTesting.functionTests.tests.lang
        .GrantRevokeDDLTest
        ....
        Time: 17.082

        OK (4 tests)

        Show
        Lily Wei added a comment - I add the tests for this issue on GrantRevokeDDLTest.java. Please help me review it. This is my test result: $ java junit.textui.TestRunner org.apache.derbyTesting.functionTests.tests.lang .GrantRevokeDDLTest .... Time: 17.082 OK (4 tests)

          People

          • Assignee:
            Unassigned
            Reporter:
            Lily Wei
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:

              Development