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.

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.0.2.1, 10.1.1.0, 10.2.1.6
    • Fix Version/s: 10.2.1.6
    • Component/s: SQL
    • Labels:
      None
    • Environment:
      generic
    • Urgency:
      Normal

      Description

      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.

      1. grantRevokeSpec_v5.html
        32 kB
        Satheesh Bandaram
      2. grantRevokeSpec_v4_1.html
        32 kB
        Mamta A. Satoor
      3. grantRevokeSpec_v4.html
        32 kB
        Mamta A. Satoor
      4. grantRevokeSpec_v3.html
        29 kB
        Satheesh Bandaram
      5. Privileges2.java
        47 kB
        Michelle Caisse
      6. Privileges.java
        47 kB
        Michelle Caisse
      7. grantRevokeSpec_v2.html
        33 kB
        Satheesh Bandaram
      8. GrantRevokePartII.stat
        3 kB
        Satheesh Bandaram
      9. GrantRevokePartII.txt
        188 kB
        Satheesh Bandaram
      10. changeDescriptionPartII
        4 kB
        Satheesh Bandaram
      11. GrantRevokePartII.txt
        188 kB
        Satheesh Bandaram
      12. grantRevokeSpec.html
        27 kB
        Satheesh Bandaram
      13. grantRevoke.patch.Dec5
        447 kB
        Satheesh Bandaram
      14. grantRevoke.stat.Dec5
        5 kB
        Satheesh Bandaram

        Issue Links

        There are no Sub-Tasks for this issue.

          Activity

          Hide
          Andrew McIntyre added a comment -

          This issue has been resolved for over a year with no further movement. Closing.

          Show
          Andrew McIntyre added a comment - This issue has been resolved for over a year with no further movement. Closing.
          Hide
          Mike Matrigali added a comment -

          All planned improvement work for this feature has been completed for 10.2. All subtasks are done. Future improvements as well as any known outstanding bugs are being tracked as separate jira issues, so closing this for 10.2.

          Show
          Mike Matrigali added a comment - All planned improvement work for this feature has been completed for 10.2. All subtasks are done. Future improvements as well as any known outstanding bugs are being tracked as separate jira issues, so closing this for 10.2.
          Hide
          Mike Matrigali added a comment -

          Satheesh is having trouble accessing JIRA, he asked me to unassign him from this issue. He
          has completed all the work he plans to do on this issue for 10.2. I think this issue is ready to
          be closed for 10.2, remaining bugs/additions I believe are being tracked by separate JIRA entries.
          If anyone believes this entry should remain open let me know, otherwise I will close it next week.

          Show
          Mike Matrigali added a comment - Satheesh is having trouble accessing JIRA, he asked me to unassign him from this issue. He has completed all the work he plans to do on this issue for 10.2. I think this issue is ready to be closed for 10.2, remaining bugs/additions I believe are being tracked by separate JIRA entries. If anyone believes this entry should remain open let me know, otherwise I will close it next week.
          Hide
          Yip Ng added a comment -

          It appears so. I filed DERBY-1708 to track the lock table issue.

          Show
          Yip Ng added a comment - It appears so. I filed DERBY-1708 to track the lock table issue.
          Hide
          Mamta A. Satoor added a comment -

          Yip, it looks like for LOCK TABLE statement, during the compile time we do not collect the privilege requirements like we do for say SELECT, INSERT etc. And hence privilege violation for LOCK TABLE does not get caught at execution time (which is when we verify if the required privileges are inplace).

          Show
          Mamta A. Satoor added a comment - Yip, it looks like for LOCK TABLE statement, during the compile time we do not collect the privilege requirements like we do for say SELECT, INSERT etc. And hence privilege violation for LOCK TABLE does not get caught at execution time (which is when we verify if the required privileges are inplace).
          Hide
          Yip Ng added a comment -

          With respect to LOCK TABLE statement, it appears that the current implementation allows non-privileged users to lock a table that they don't have access rights to. I think non-privilege users should not be able to perform the locking as now they have "control" to those tables. In DB2, the authorization id needs to have select privilege or is DBA to perform such an action.

          Show
          Yip Ng added a comment - With respect to LOCK TABLE statement, it appears that the current implementation allows non-privileged users to lock a table that they don't have access rights to. I think non-privilege users should not be able to perform the locking as now they have "control" to those tables. In DB2, the authorization id needs to have select privilege or is DBA to perform such an action.
          Hide
          Yip Ng added a comment -

          In v5 of the design doc, it uses uppercase "Y" or "N" value for each of the specific privilege for the new system tables such as sys.systableperms but in the implementation, I noticed that it is using lowercase "y". (Assuming "Y" is used to represent for grant option in the future.)

          Show
          Yip Ng added a comment - In v5 of the design doc, it uses uppercase "Y" or "N" value for each of the specific privilege for the new system tables such as sys.systableperms but in the implementation, I noticed that it is using lowercase "y". (Assuming "Y" is used to represent for grant option in the future.)
          Hide
          Satheesh Bandaram added a comment -

          Updated GRANT/REVOKE functional specification. Changes include:

          1) Added a section for database owner and how they operate in user schemas.
          2) Clarified that synonyms can't be granted privileges and also that revoking part of a table privilege is not allowed.
          3) Clarified upgrade model better.

          Show
          Satheesh Bandaram added a comment - Updated GRANT/REVOKE functional specification. Changes include: 1) Added a section for database owner and how they operate in user schemas. 2) Clarified that synonyms can't be granted privileges and also that revoking part of a table privilege is not allowed. 3) Clarified upgrade model better.
          Hide
          Mamta A. Satoor added a comment -

          I think I accidentally selected patch available when I attached the updated fucntional spec.

          Show
          Mamta A. Satoor added a comment - I think I accidentally selected patch available when I attached the updated fucntional spec.
          Hide
          Daniel John Debrunner added a comment -

          Don't see any patch that hasn't been committed.

          Show
          Daniel John Debrunner added a comment - Don't see any patch that hasn't been committed.
          Hide
          Yip Ng added a comment -

          Are there any new SQLSTATEs and messages introduced by this item? There are couple of places in the latest design doc(v4) where it states Derby returns error/throws SQLException in certain scenarios (i.e. accessing tables that you don''t have the rights, revoking owners own privilege, etc. ) but I don't see any SQLSTATEs being listed and I think the tech writers need this info to update the ref docs, no?

          Show
          Yip Ng added a comment - Are there any new SQLSTATEs and messages introduced by this item? There are couple of places in the latest design doc(v4) where it states Derby returns error/throws SQLException in certain scenarios (i.e. accessing tables that you don''t have the rights, revoking owners own privilege, etc. ) but I don't see any SQLSTATEs being listed and I think the tech writers need this info to update the ref docs, no?
          Hide
          Mamta A. Satoor added a comment -

          Rajesh, thanks for catching it. I have updated the version number to the best of my knowledge. Satheesh, feel free to change it. The spec with date and version change along with UUID changes is now attached as grantRevokeSpec_v4_1.html

          Show
          Mamta A. Satoor added a comment - Rajesh, thanks for catching it. I have updated the version number to the best of my knowledge. Satheesh, feel free to change it. The spec with date and version change along with UUID changes is now attached as grantRevokeSpec_v4_1.html
          Hide
          Rajesh Kartha added a comment -

          Trivial:

          I was starting to read the latest functional spec and noticed the date and version of the current one was not updated to reflect latest version and date.

          Show
          Rajesh Kartha added a comment - Trivial: I was starting to read the latest functional spec and noticed the date and version of the current one was not updated to reflect latest version and date.
          Hide
          Mamta A. Satoor added a comment -

          Updated functional spec to include UUID column in the three new system tables. This UUID will be used by the dependency manager to track view/trigger/constraint's dependencies on various permissions,

          The updated spec is attached as grantRevokeSpec_v4.html

          Show
          Mamta A. Satoor added a comment - Updated functional spec to include UUID column in the three new system tables. This UUID will be used by the dependency manager to track view/trigger/constraint's dependencies on various permissions, The updated spec is attached as grantRevokeSpec_v4.html
          Hide
          Daniel John Debrunner added a comment -

          I would say one definition of sub-task is that the main task is not complete until all of the sub-tasks are.
          Though I think sometimes sub-tasks are added when a better choice would be to add separate tasks.
          And the situation is not helped by Jira not allowing sub-tasks to moved once created.

          In this case it seems to me:

          DERBY-1023 is no longer part of the functional spec and should be marked no fix intended (ideally it would be moved out to be its own improvement).
          DERBY-1057 should be a separate task. I would favor this model for documentation of code features, separate improvement items that are linked but not sub-tasks.

          All the others seem to be valid sub-tasks of DERBY-464 and so should be complete before closing this.

          Show
          Daniel John Debrunner added a comment - I would say one definition of sub-task is that the main task is not complete until all of the sub-tasks are. Though I think sometimes sub-tasks are added when a better choice would be to add separate tasks. And the situation is not helped by Jira not allowing sub-tasks to moved once created. In this case it seems to me: DERBY-1023 is no longer part of the functional spec and should be marked no fix intended (ideally it would be moved out to be its own improvement). DERBY-1057 should be a separate task. I would favor this model for documentation of code features, separate improvement items that are linked but not sub-tasks. All the others seem to be valid sub-tasks of DERBY-464 and so should be complete before closing this.
          Hide
          Satheesh Bandaram added a comment -

          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.

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

          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.

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

          With revision 423580, the redundant privilege problem is resolved. I opened up DERBY-1538 to keep track of the self privilege revocation issue.

          Show
          Yip Ng added a comment - With revision 423580, the redundant privilege problem is resolved. I opened up DERBY-1538 to keep track of the self privilege revocation issue.
          Hide
          Yip Ng added a comment -

          Hi

          My name is Yip Ng and I would like to contribute to the Apache Derby project. First I thought I start off by writing a suite of testcases for Grant/Revoke functionality. I went through the Grant/Revoke design spec described in derby-464 but I couldn't find any mentioning of the Derby system behavior for revoking the owner's own privileges. So I ran the scenario below in ij to see how this is handled currently. (btw, I am currently using revision 423260.) 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

          After revoking the owner's SELECT privilege on table t1, the owner is able to select from it as if though the REVOKE statement above has no effect(bug?). But more importantly, I was expecting a SQLException with the appropriate SQLSTATE to be thrown when the owner attempts to revoke privilege from himself. Comments?

          Another problem I have found (not sure if this is already addressed in the latest codeline) is that if the owner grants himself SELECT privilege on the table he have created (redundant privilege) and when he attempts to insert another row, Derby throws a SQLException and informs that the owner needs to have permission to perform DML on the owner's table. 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> grant select on table t1 to yip;
          0 rows inserted/updated/deleted
          ij> insert into t1 values 4;
          ERROR 28506: User 'YIP' does not have insert permission on table 'YIP'.'T1'.

          I am currently writing more testcases and will submit them as a patch shortly.

          Show
          Yip Ng added a comment - Hi My name is Yip Ng and I would like to contribute to the Apache Derby project. First I thought I start off by writing a suite of testcases for Grant/Revoke functionality. I went through the Grant/Revoke design spec described in derby-464 but I couldn't find any mentioning of the Derby system behavior for revoking the owner's own privileges. So I ran the scenario below in ij to see how this is handled currently. (btw, I am currently using revision 423260.) 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 After revoking the owner's SELECT privilege on table t1, the owner is able to select from it as if though the REVOKE statement above has no effect(bug?). But more importantly, I was expecting a SQLException with the appropriate SQLSTATE to be thrown when the owner attempts to revoke privilege from himself. Comments? Another problem I have found (not sure if this is already addressed in the latest codeline) is that if the owner grants himself SELECT privilege on the table he have created (redundant privilege) and when he attempts to insert another row, Derby throws a SQLException and informs that the owner needs to have permission to perform DML on the owner's table. 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> grant select on table t1 to yip; 0 rows inserted/updated/deleted ij> insert into t1 values 4; ERROR 28506: User 'YIP' does not have insert permission on table 'YIP'.'T1'. I am currently writing more testcases and will submit them as a patch shortly.
          Hide
          Daniel John Debrunner added a comment -

          Some comments on grantRevokeSpec_v2.html

          • The primary keys for SYSTABLEPERMS, SYSCOLPERMS and SYSROUTINEPERMS do not include the GRANTOR column.
            Won't it make future upgrades easier if the GRANTOR column is included in the primary key?
          • How does SYSREQUIREDPERM relate to the current dependency system. Could this functionality not be incorporated into
            the current dependency system so that we have a single system?
          Show
          Daniel John Debrunner added a comment - Some comments on grantRevokeSpec_v2.html The primary keys for SYSTABLEPERMS, SYSCOLPERMS and SYSROUTINEPERMS do not include the GRANTOR column. Won't it make future upgrades easier if the GRANTOR column is included in the primary key? How does SYSREQUIREDPERM relate to the current dependency system. Could this functionality not be incorporated into the current dependency system so that we have a single system?
          Hide
          Michelle Caisse added a comment -

          This attachment, Privileges2.java supercedes the previous one. It contains the Apache license test. My management at Sun Microsystems has authorized me to donated this code to Apache Derby. It will need substantial rework for use with Derby outside of the test framework for which it was written. I hope that it is useful.

          Show
          Michelle Caisse added a comment - This attachment, Privileges2.java supercedes the previous one. It contains the Apache license test. My management at Sun Microsystems has authorized me to donated this code to Apache Derby. It will need substantial rework for use with Derby outside of the test framework for which it was written. I hope that it is useful.
          Hide
          Michelle Caisse added a comment -

          If you think it's useful, you may rework the attached code to test grant/revoke functionality in Derby.

          Show
          Michelle Caisse added a comment - If you think it's useful, you may rework the attached code to test grant/revoke functionality in Derby.
          Hide
          Satheesh Bandaram added a comment -

          I will make the suggested changes to the spec. I will remove the "for instance" example... in the next version of the spec update.

          Show
          Satheesh Bandaram added a comment - I will make the suggested changes to the spec. I will remove the "for instance" example... in the next version of the spec update.
          Hide
          Daniel John Debrunner added a comment -

          Thanks for adding the table of system routines in the functional spec.

          One question on that, in the intro to the table it says

          • " For instance a user needs to have SELECT privilege on a table to be able to call SYSCS_COMPRESS_TABLE"

          but that privilege is not reflected in the last column of the table for the compress routines, is it required or not?
          I wonder if it would be better to be general in the intro rather than an "for instance", e.g. say "additional privileges required are listed in the 'Other privileges needed' column".

          I think it would be good to be precise & consistent and use the term "PUBLIC" rather than "everyone", might lead to consistency in the user documentation which would be a good thing!

          Show
          Daniel John Debrunner added a comment - Thanks for adding the table of system routines in the functional spec. One question on that, in the intro to the table it says " For instance a user needs to have SELECT privilege on a table to be able to call SYSCS_COMPRESS_TABLE" but that privilege is not reflected in the last column of the table for the compress routines, is it required or not? I wonder if it would be better to be general in the intro rather than an "for instance", e.g. say "additional privileges required are listed in the 'Other privileges needed' column". I think it would be good to be precise & consistent and use the term "PUBLIC" rather than "everyone", might lead to consistency in the user documentation which would be a good thing!
          Hide
          Satheesh Bandaram added a comment -

          I have updated Grant Revoke functional specification with newer version. Changes from previous version are highlighted in RED. Main changes from previous version are:

          1) Added a new table showing detailed information on system routines... Who can execute and what other privileges are required. I am in two minds about whether everyone (who has owns their tables) should be able to invoke import/export routines or not, BY DEFAULT. Since import/export routines access input/output files on the server-side, I am not sure if everyone should be able to execute these, BY DEFAULT. So I am currently proposing DBA needs to grant EXECUTE privilege to be able to invoke them.

          2) Clarified what was discussed on the list about who owns system schemas. I am going to be changing authorizationID of system schemas from psuedo user 'DBA' to authorizationID of database creator or to the authorizationID of the user who invokes full upgrade.

          3) Previous version of the spec was wrong about default EXTERNAL SECURITY clause and what SQL Standard says about it. SQL standard says default EXTERNAL SECURITY, if not specified, is implementation defined. Previous version of the spec and myself thought default value as specified by SQL standard was DEFINER, so I proposed adding DEFINER/INVOKER clause to routines now so we don't introduce forward compatibility issues by keeping INVOKER model. But the spec is very clear about default value of SECURITY, so there is no need to implement that feature NOW.

          Let me know if anyone have any comments.

          Show
          Satheesh Bandaram added a comment - I have updated Grant Revoke functional specification with newer version. Changes from previous version are highlighted in RED. Main changes from previous version are: 1) Added a new table showing detailed information on system routines... Who can execute and what other privileges are required. I am in two minds about whether everyone (who has owns their tables) should be able to invoke import/export routines or not, BY DEFAULT. Since import/export routines access input/output files on the server-side, I am not sure if everyone should be able to execute these, BY DEFAULT. So I am currently proposing DBA needs to grant EXECUTE privilege to be able to invoke them. 2) Clarified what was discussed on the list about who owns system schemas. I am going to be changing authorizationID of system schemas from psuedo user 'DBA' to authorizationID of database creator or to the authorizationID of the user who invokes full upgrade. 3) Previous version of the spec was wrong about default EXTERNAL SECURITY clause and what SQL Standard says about it. SQL standard says default EXTERNAL SECURITY, if not specified, is implementation defined. Previous version of the spec and myself thought default value as specified by SQL standard was DEFINER, so I proposed adding DEFINER/INVOKER clause to routines now so we don't introduce forward compatibility issues by keeping INVOKER model. But the spec is very clear about default value of SECURITY, so there is no need to implement that feature NOW. Let me know if anyone have any comments.
          Hide
          Satheesh Bandaram added a comment -

          Updated Part II Grant and Revoke patch... Synced to latest with more tests and some minor code changes. I have rerun the tests.

          I have added changeDescription to the patch.

          Show
          Satheesh Bandaram added a comment - Updated Part II Grant and Revoke patch... Synced to latest with more tests and some minor code changes. I have rerun the tests. I have added changeDescription to the patch.
          Hide
          Satheesh Bandaram added a comment -

          I would like to submit this patch over the weekend. Appreciate any comments before.

          Show
          Satheesh Bandaram added a comment - I would like to submit this patch over the weekend. Appreciate any comments before.
          Hide
          Satheesh Bandaram added a comment -

          Dan asked:
          Another quote from the spec
          <quote>
          A table may only be created or dropped by the owner of the table's schema. Table creation
          permission is not grantable. (This is the SQL2003 spec)
          </quote>
          Is there a reference, page number section number, for the comment about the SQL2003 spec?

          This is the best reference I can find in SQL 2003 spec. It is indirectly implied.... says persistent objects described by the (schema) descriptors are said to be owned by or to have been created by the authorizationID of the schema. Also, I couldn't find a privilege that can be granted to create tables.

          4.20 SQL-schemas
          An SQL-schema is a persistent descriptor that includes:
          ? The name of the SQL-schema.
          ? The <authorization identifier> of the owner of the SQL-schema.
          ...........................................
          In this part of ISO/IEC 9075, the term "schema" is used only in the sense of SQL-schema. The persistent objects
          described by the descriptors are said to be owned by or to have been created by the <authorization identifier>
          of the schema. Each component descriptor is one of:
          ? A domain descriptor.
          ? A base table descriptor.
          ? A view descriptor.
          ? A constraint descriptor.

          Show
          Satheesh Bandaram added a comment - Dan asked: Another quote from the spec <quote> A table may only be created or dropped by the owner of the table's schema. Table creation permission is not grantable. (This is the SQL2003 spec) </quote> Is there a reference, page number section number, for the comment about the SQL2003 spec? This is the best reference I can find in SQL 2003 spec. It is indirectly implied.... says persistent objects described by the (schema) descriptors are said to be owned by or to have been created by the authorizationID of the schema. Also, I couldn't find a privilege that can be granted to create tables. 4.20 SQL-schemas An SQL-schema is a persistent descriptor that includes: ? The name of the SQL-schema. ? The <authorization identifier> of the owner of the SQL-schema. ........................................... In this part of ISO/IEC 9075, the term "schema" is used only in the sense of SQL-schema. The persistent objects described by the descriptors are said to be owned by or to have been created by the <authorization identifier> of the schema. Each component descriptor is one of: ? A domain descriptor. ? A base table descriptor. ? A view descriptor. ? A constraint descriptor.
          Hide
          Daniel John Debrunner added a comment -

          Sorry, I see there is a TRIGGER privilege in the spec, so my concern on that goes away. It's kind of hard to read the spec to see what permissions are required to execute each operation when they are scattered around the document.

          Show
          Daniel John Debrunner added a comment - Sorry, I see there is a TRIGGER privilege in the spec, so my concern on that goes away. It's kind of hard to read the spec to see what permissions are required to execute each operation when they are scattered around the document.
          Hide
          Daniel John Debrunner added a comment -

          Another quote from the spec
          <quote>
          A table may only be created or dropped by the owner of the table's schema. Table creation permission is not grantable. (This is the SQL2003 spec)
          </quote>
          Is there a reference, page number section number, for the comment about the SQL2003 spec?

          Show
          Daniel John Debrunner added a comment - Another quote from the spec <quote> A table may only be created or dropped by the owner of the table's schema. Table creation permission is not grantable. (This is the SQL2003 spec) </quote> Is there a reference, page number section number, for the comment about the SQL2003 spec?
          Hide
          Daniel John Debrunner added a comment -

          To quote the func spec:
          <quote>
          Views and triggers may only be created or dropped by the owner of the view or trigger's schema.
          </quote>

          Allowing anyone apart from the owner to create a trigger on a table seems too permissive to me:

          Postgres requires an TRIGGER privilege on the table to create a trigger

          DB2 requires ALTERIN or CONTROL on the table depending on the trigger type

          Oracle requires CREATE_TRIGGER privilege

          I assume that the owner of a table can always create triggers on the table.

          Views seem ok to allow anyone to create one, as long as they have access to the underlying objects as the spec says.

          Show
          Daniel John Debrunner added a comment - To quote the func spec: <quote> Views and triggers may only be created or dropped by the owner of the view or trigger's schema. </quote> Allowing anyone apart from the owner to create a trigger on a table seems too permissive to me: Postgres requires an TRIGGER privilege on the table to create a trigger DB2 requires ALTERIN or CONTROL on the table depending on the trigger type Oracle requires CREATE_TRIGGER privilege I assume that the owner of a table can always create triggers on the table. Views seem ok to allow anyone to create one, as long as they have access to the underlying objects as the spec says.
          Hide
          Satheesh Bandaram added a comment -

          I am attaching Grant and Revoke, Part II patch to implement authorization scheme. This patch enforces permission checks that part I patch records in system catalogs. I am still adding more test cases and need to update functional spec with some review comments. Let me know if anyone has any comments or have trouble applying the patch. I moved some grant revoke tests, so may confuse patch program.

          Next I will work on implementing authorization for Trigger, View and Constraints, followed by some upgrade, migration and metadata changes.

          Show
          Satheesh Bandaram added a comment - I am attaching Grant and Revoke, Part II patch to implement authorization scheme. This patch enforces permission checks that part I patch records in system catalogs. I am still adding more test cases and need to update functional spec with some review comments. Let me know if anyone has any comments or have trouble applying the patch. I moved some grant revoke tests, so may confuse patch program. Next I will work on implementing authorization for Trigger, View and Constraints, followed by some upgrade, migration and metadata changes.
          Hide
          Satheesh Bandaram added a comment -

          Thanks for your comments, Dan. I will update the spec, but some answers below:

          "This property could be set either as a system property in derby.properties file or as application property." This needs re-wording, system properties are set as JVM system proiperties, application properties is what is used to describe properties in derby.properties. You could just say 'this is a standard Derby property"

          OK... Tuning guide refers to both as System-wide properties... System-wide properties set programmatically and System-wide properties set in derby.properties.

          • The design doesn't contain any mention of modifications to the statement dependencies. It may be (after thinking about it for 10 seconds) that any grant/revoke statement does not need to invalidate any DML compiled statements. It would be good to state this and the reasons why.

          Right... Grant and Revoke statements don't need to invalidate any existing compiled statements. This is because the compiled plan doesn't assume if any permissions are granted or not... Compilation only notes required object accesses to execute a statement. Only at runtime, these checks are performed. I will add more info to design section.

          • I couldn't see where you are storing the owner of the database

          Any ideas where it could be? I haven't coded that part. I was thinking of using an internal property to store the database owner. Are there any internal use only properties in Derby currently? I thought boot password is kind of stored like this?

          • Can you expand on the design around the StatementPermission class and its sub-classes. Which methods are invovled in making permission checks., The Javadoc for the check method in StatementPermission doesn't say anything. The methods have some funky equals methods with no javadoc, without understanding more it looks like equals is being overloaded for no good reason.

          StatementPermission has the following hieraracy.. not sure if this will show correctly in text

          StatementPermission

          ----- StatementTablePermission

          ------ StatementColumnPermission

          ------ StatementRoutinePermission

          ------ StatementSchemaPermission

          All implement check() interface that is used to invoke permission checking for that access descriptor. Access descriptors already know what they need to check for and are passed current user authorizationId.

          The equals() method is used to check if an access descriptor is already created for the specific access. For example, a query may have multiple references to same table. No need to create multiple access descriptors for the same table for the same kind of access. This becomes more important as each and every column referenced may try to add an access descriptor for the table in question.

          I will add these details to the design part of the spec.

          Show
          Satheesh Bandaram added a comment - Thanks for your comments, Dan. I will update the spec, but some answers below: "This property could be set either as a system property in derby.properties file or as application property." This needs re-wording, system properties are set as JVM system proiperties, application properties is what is used to describe properties in derby.properties. You could just say 'this is a standard Derby property" OK... Tuning guide refers to both as System-wide properties... System-wide properties set programmatically and System-wide properties set in derby.properties. The design doesn't contain any mention of modifications to the statement dependencies. It may be (after thinking about it for 10 seconds) that any grant/revoke statement does not need to invalidate any DML compiled statements. It would be good to state this and the reasons why. Right... Grant and Revoke statements don't need to invalidate any existing compiled statements. This is because the compiled plan doesn't assume if any permissions are granted or not... Compilation only notes required object accesses to execute a statement. Only at runtime, these checks are performed. I will add more info to design section. I couldn't see where you are storing the owner of the database Any ideas where it could be? I haven't coded that part. I was thinking of using an internal property to store the database owner. Are there any internal use only properties in Derby currently? I thought boot password is kind of stored like this? Can you expand on the design around the StatementPermission class and its sub-classes. Which methods are invovled in making permission checks., The Javadoc for the check method in StatementPermission doesn't say anything. The methods have some funky equals methods with no javadoc, without understanding more it looks like equals is being overloaded for no good reason. StatementPermission has the following hieraracy.. not sure if this will show correctly in text StatementPermission ----- StatementTablePermission ------ StatementColumnPermission ------ StatementRoutinePermission ------ StatementSchemaPermission All implement check() interface that is used to invoke permission checking for that access descriptor. Access descriptors already know what they need to check for and are passed current user authorizationId. The equals() method is used to check if an access descriptor is already created for the specific access. For example, a query may have multiple references to same table. No need to create multiple access descriptors for the same table for the same kind of access. This becomes more important as each and every column referenced may try to add an access descriptor for the table in question. I will add these details to the design part of the spec.
          Hide
          Daniel John Debrunner added a comment -

          Thanks for the updated spec - some comments:

          • "This property could be set either as a system property in derby.properties file or as application property." This needs re-wording, system properties are set as JVM system proiperties, application properties is what is used to describe properties in derby.properties. You could just say 'this is a standard Derby property"
          • The design doesn't contain any mention of modifications to the statement dependencies. It may be (after thinking about it for 10 seconds) that any grant/revoke statement does not need to invalidate any DML compiled statements. It would be good to state this and the reasons why.
          • I couldn't see where you are storing the owner of the database
          • Can you expand on the design around the StatementPermission class and its sub-classes. Which methods are invovled in making permission checks., The Javadoc for the check method in StatementPermission doesn't say anything. The methods have some funky equals methods with no javadoc, without understanding more it looks like equals is being overloaded for no good reason.
          Show
          Daniel John Debrunner added a comment - Thanks for the updated spec - some comments: "This property could be set either as a system property in derby.properties file or as application property." This needs re-wording, system properties are set as JVM system proiperties, application properties is what is used to describe properties in derby.properties. You could just say 'this is a standard Derby property" The design doesn't contain any mention of modifications to the statement dependencies. It may be (after thinking about it for 10 seconds) that any grant/revoke statement does not need to invalidate any DML compiled statements. It would be good to state this and the reasons why. I couldn't see where you are storing the owner of the database Can you expand on the design around the StatementPermission class and its sub-classes. Which methods are invovled in making permission checks., The Javadoc for the check method in StatementPermission doesn't say anything. The methods have some funky equals methods with no javadoc, without understanding more it looks like equals is being overloaded for no good reason.
          Hide
          Satheesh Bandaram added a comment -

          I have updated Grant & Revoke specification. I tried to address some of Dan's questions from earlier and also changed the spec to reflect Dan's suggestion about adding new property derby.database.sqlAuthorization. Changes are high lighted in RED. Let me know if anyone have any questions or comments. I will submit Grant and Revoke, Phase II code soon.

          Show
          Satheesh Bandaram added a comment - I have updated Grant & Revoke specification. I tried to address some of Dan's questions from earlier and also changed the spec to reflect Dan's suggestion about adding new property derby.database.sqlAuthorization. Changes are high lighted in RED. Let me know if anyone have any questions or comments. I will submit Grant and Revoke, Phase II code soon.
          Hide
          Daniel John Debrunner added a comment -

          spec> The SELECTPRIV column has value "Y" if the SELECT permission was granted, or "N" if the select permission was not granted. Similarly with the DELETEPRIV, INSERTPRIV, UPDATEPRIV, REFERENCESPRIV, and TRIGGERPRIV columns.

          Sound like BOOLEAN columns to me.

          Show
          Daniel John Debrunner added a comment - spec> The SELECTPRIV column has value "Y" if the SELECT permission was granted, or "N" if the select permission was not granted. Similarly with the DELETEPRIV, INSERTPRIV, UPDATEPRIV, REFERENCESPRIV, and TRIGGERPRIV columns. Sound like BOOLEAN columns to me.
          Hide
          Daniel John Debrunner added a comment -

          Some minor comments on the design spec:

          spec> DDL statements directly invoke executing mechanism simplify the implementation at the cost of a small increase in execution time.

          I don't think that's true. DDL''s are not slower due to using a constant action. That's not the reason for the way they are handled. I think the basic idea is that DDL's are limited in structure compared to DML and can easily be represented by an object that represents an action and a set of constants. DDL did generate a java class to execute the constant action but I cleaned that up to use a fixed implementation (sub-class) of BaseActivation , as otherwise each DDL would be generating an identical class. This was wasteful when most DDL is discarded after a single execution.

          In various tables you have the user name represented (I think) as VARCHAR(30) (GRANTEE, GRANTOR columns). Why 30? In the existing SYSSCHEMAs the user identifer is VARCHAR(128), I think 128 is a better limit for user names.

          spec> Permission checking is done at execution time.

          Could you expand on that? Are you going to match the current scheme, permissions checked when an internal ResultSet is created? Something else?

          spec> The permission checks could be done at compilation time.

          For the reasons given I think this is a bad idea, the statement cache. I do think we can set it up so that permissions are checked when the statement is prepared by the application, even when it picks up an existing compiled plan. This would be better than per execution of the statement. That's probably a separate discussion once you reveal what you are currently planning.

          spec > The permission checks could be done at compilation time. PreparedStatements would have to be re-compiled when permissions are revoked, as they are recompiled when tables are altered.

          Don't you have to recompile statement plans when permissions are revoked or granted? Even if you are checking permissions at execution time?

          Can you document the locking plans (e.g. what locks does grant get) and the interaction with the dependency system. I think these are key to getting the design right and secure.

          Show
          Daniel John Debrunner added a comment - Some minor comments on the design spec: spec> DDL statements directly invoke executing mechanism simplify the implementation at the cost of a small increase in execution time. I don't think that's true. DDL''s are not slower due to using a constant action. That's not the reason for the way they are handled. I think the basic idea is that DDL's are limited in structure compared to DML and can easily be represented by an object that represents an action and a set of constants. DDL did generate a java class to execute the constant action but I cleaned that up to use a fixed implementation (sub-class) of BaseActivation , as otherwise each DDL would be generating an identical class. This was wasteful when most DDL is discarded after a single execution. In various tables you have the user name represented (I think) as VARCHAR(30) (GRANTEE, GRANTOR columns). Why 30? In the existing SYSSCHEMAs the user identifer is VARCHAR(128), I think 128 is a better limit for user names. spec> Permission checking is done at execution time. Could you expand on that? Are you going to match the current scheme, permissions checked when an internal ResultSet is created? Something else? spec> The permission checks could be done at compilation time. For the reasons given I think this is a bad idea, the statement cache. I do think we can set it up so that permissions are checked when the statement is prepared by the application, even when it picks up an existing compiled plan. This would be better than per execution of the statement. That's probably a separate discussion once you reveal what you are currently planning. spec > The permission checks could be done at compilation time. PreparedStatements would have to be re-compiled when permissions are revoked, as they are recompiled when tables are altered. Don't you have to recompile statement plans when permissions are revoked or granted? Even if you are checking permissions at execution time? Can you document the locking plans (e.g. what locks does grant get) and the interaction with the dependency system. I think these are key to getting the design right and secure.
          Hide
          Satheesh Bandaram added a comment -

          I have submitted Grant and Revoke, Part I to trunk. Let me know if anyone would like to join developing remaining parts. It is possible to coordinate development using a Wiki.

          This Phase I implements:

          • Grant/Revoke DDL parsing and execution
          • Addition of several new system tables to hold the system metadata. I will update my spec to include detailed schema for new system tables, so that they can be included in 10.2 documentation.
          • Enhancing the syntax for routine creation to include external-security clause
          • Very simple tests to cover only the DDL. I would be expanding on the testing in the later submissions, including a JUnit test suite.
          • Grant/Revoke DDL is only supported if derby.database.defaultConnectionMode property is set to 'sqlStandard'.

          Pending items from Phase I:

          1. dblook needs to be enhanced to emit DDL for grant statements.
          2. Enhanced JUnit based test suite with many more tests.
          3. Some implementation improvements possible with the current patch. It should be possible to combine several new nodes being added, to reduce number of nodes and hence foot print. Also, the patch adds a Java object to new system tables. While Derby already has some java objects in its system tables, I think, we should discourage adding new java objects to catalogs. Since Java objects can't be used in SQL easily, this makes metadata harder to use. I will explore rewriting SYSCOLPERMS and SYSREQUIREDPERM to not use FormattableBitSet type. This can be done by having multiple entries in these catalogs for each column referenced.
          4. Updating specification to include schema for new system tables.
          5. Need to change how property defaultConnectionMode is set and/or used.
          6. Enhance system tables to store external security clause specification.

          I am also working on Grant and Revoke, Phase II. This will implement permission checking for DML statement. Hopefully I will have something to submit by end of January to complete Phase I and Phase II.

          Also need to support upgrade and migration of legacy databases and update JDBC metadata.

          Let me know if I missed anything else.

          Show
          Satheesh Bandaram added a comment - I have submitted Grant and Revoke, Part I to trunk. Let me know if anyone would like to join developing remaining parts. It is possible to coordinate development using a Wiki. This Phase I implements: Grant/Revoke DDL parsing and execution Addition of several new system tables to hold the system metadata. I will update my spec to include detailed schema for new system tables, so that they can be included in 10.2 documentation. Enhancing the syntax for routine creation to include external-security clause Very simple tests to cover only the DDL. I would be expanding on the testing in the later submissions, including a JUnit test suite. Grant/Revoke DDL is only supported if derby.database.defaultConnectionMode property is set to 'sqlStandard'. Pending items from Phase I: 1. dblook needs to be enhanced to emit DDL for grant statements. 2. Enhanced JUnit based test suite with many more tests. 3. Some implementation improvements possible with the current patch. It should be possible to combine several new nodes being added, to reduce number of nodes and hence foot print. Also, the patch adds a Java object to new system tables. While Derby already has some java objects in its system tables, I think, we should discourage adding new java objects to catalogs. Since Java objects can't be used in SQL easily, this makes metadata harder to use. I will explore rewriting SYSCOLPERMS and SYSREQUIREDPERM to not use FormattableBitSet type. This can be done by having multiple entries in these catalogs for each column referenced. 4. Updating specification to include schema for new system tables. 5. Need to change how property defaultConnectionMode is set and/or used. 6. Enhance system tables to store external security clause specification. I am also working on Grant and Revoke, Phase II. This will implement permission checking for DML statement. Hopefully I will have something to submit by end of January to complete Phase I and Phase II. Also need to support upgrade and migration of legacy databases and update JDBC metadata. Let me know if I missed anything else.
          Hide
          Francois Orsini added a comment -

          The way I implememted users in Cloudscape originally was done in a "Cloudscape running Embedded" mindset rather than anything else - in a similar way we what ww have done for permissions via properties - defining users is one thing, authenticating them via various schemes in another - For instance today, users defined at the System level, not database one, do not have their password encrypted for the built-in authentication scheme. I agree that users can be defined outside of Derby but we can't assume all organizations have an LDAP server out there - in fact, a lot if not most of them still don't have one.

          What I have in mind for Derby defined users is the following:

          • Users should be defined at the System level and added to databases as required (Grant access to a database)
          • The notion of users defined at the system level is NOT just for Derby's built-in authentication scheme - it is there so that we can better link and integrate users from an outside realm than Derby's one - Sysusers would be a great start and I would hope to use VTI internally to materialize (known) users for a particular instance of Derby whether the built-in authentication scheme is used or not..and I agree that Derby should not care as far as how the user has been authenticated.
          • Most database systems out there have DDL to define users managed by the RDBMS, we don't have this at the moment - some are using a syntax such as:
          • Grant Connet to username Identified by xyz;
            or
          • Create User username Identified by xyz;

          as well as DDL for altering users.

          • Introduce a rea and internal notion of a User object (descriptor) in Derby - there is none today

          Now, this could be done as part of this charter or not (separate JIRA) but there would be dependencies at least how a user is instantiated internally (see my last point) and the grant/revoke implementation at the user descriptor level.

          Show
          Francois Orsini added a comment - The way I implememted users in Cloudscape originally was done in a "Cloudscape running Embedded" mindset rather than anything else - in a similar way we what ww have done for permissions via properties - defining users is one thing, authenticating them via various schemes in another - For instance today, users defined at the System level, not database one, do not have their password encrypted for the built-in authentication scheme. I agree that users can be defined outside of Derby but we can't assume all organizations have an LDAP server out there - in fact, a lot if not most of them still don't have one. What I have in mind for Derby defined users is the following: Users should be defined at the System level and added to databases as required (Grant access to a database) The notion of users defined at the system level is NOT just for Derby's built-in authentication scheme - it is there so that we can better link and integrate users from an outside realm than Derby's one - Sysusers would be a great start and I would hope to use VTI internally to materialize (known) users for a particular instance of Derby whether the built-in authentication scheme is used or not..and I agree that Derby should not care as far as how the user has been authenticated. Most database systems out there have DDL to define users managed by the RDBMS, we don't have this at the moment - some are using a syntax such as: Grant Connet to username Identified by xyz; or Create User username Identified by xyz; as well as DDL for altering users. Introduce a rea and internal notion of a User object (descriptor) in Derby - there is none today Now, this could be done as part of this charter or not (separate JIRA) but there would be dependencies at least how a user is instantiated internally (see my last point) and the grant/revoke implementation at the user descriptor level.
          Hide
          Satheesh Bandaram added a comment -

          Good point, Francois, about adding comments in JIRA for this one. I will add comments here.

          I agree ROLES would be another great addition to Derby. Like I mentioned, there are many other potential enhancements possible in access control and security areas. I usually propose ideas that I can implement and want to implement in reasonable timeframe. Incremental enhancements is the prefered way in open source. Like Rick mentioned, ROLES could be developed in parallel.

          I am not sure about CREATE USER/DROP USER capabilities though. Databases are not the ideal places to manage users. Derby also provides several ways to authenticate and/or manage users, including LDAP. The property based user management is only one of these options. See: http://db.apache.org/derby/docs/10.1/devguide/cdevcsecure37817.html

          Show
          Satheesh Bandaram added a comment - Good point, Francois, about adding comments in JIRA for this one. I will add comments here. I agree ROLES would be another great addition to Derby. Like I mentioned, there are many other potential enhancements possible in access control and security areas. I usually propose ideas that I can implement and want to implement in reasonable timeframe. Incremental enhancements is the prefered way in open source. Like Rick mentioned, ROLES could be developed in parallel. I am not sure about CREATE USER/DROP USER capabilities though. Databases are not the ideal places to manage users. Derby also provides several ways to authenticate and/or manage users, including LDAP. The property based user management is only one of these options. See: http://db.apache.org/derby/docs/10.1/devguide/cdevcsecure37817.html
          Hide
          Francois Orsini added a comment -

          Agreed, CREATE/DROP ROLE as well as CREATE/DROP USER are there on my list...ROLE is frequently used out there along with Grant/Revoke - There is usually Built-in Roles and user-defined ones.

          I would also like to have a CREATE/DROP USER to enhance Derby useability for managing users rather than what we have now which is property-based and was done in the past with the notion of running Cloudscape/Derby embedded.

          Note that you can still Grant/Revoke at the user level.

          Grant/Revoke is a major feature and effort - it needs to be stagged in several phases as Saheesh mentioned.

          I could see the following major phases/steps: (which can be done in-parallel to some degree)

          • Create/Drop User / Role DDL (along with metadata / system catalogs {sysusers, sysroles)
          • Grant / Revoke privileges DDL (system and object ones) (sysprivileges, syspermissions)
          • Grant / Revoke runtime execution (processing)
          • Built-in Roles, SA user, Supoport for additional system privileges and built-in roles.

          at a minimum.

          We need to list the admin and object privileges we want to support in Derby and implement them in several phases.

          Show
          Francois Orsini added a comment - Agreed, CREATE/DROP ROLE as well as CREATE/DROP USER are there on my list...ROLE is frequently used out there along with Grant/Revoke - There is usually Built-in Roles and user-defined ones. I would also like to have a CREATE/DROP USER to enhance Derby useability for managing users rather than what we have now which is property-based and was done in the past with the notion of running Cloudscape/Derby embedded. Note that you can still Grant/Revoke at the user level. Grant/Revoke is a major feature and effort - it needs to be stagged in several phases as Saheesh mentioned. I could see the following major phases/steps: (which can be done in-parallel to some degree) Create/Drop User / Role DDL (along with metadata / system catalogs {sysusers, sysroles) Grant / Revoke privileges DDL (system and object ones) (sysprivileges, syspermissions) Grant / Revoke runtime execution (processing) Built-in Roles, SA user, Supoport for additional system privileges and built-in roles. at a minimum. We need to list the admin and object privileges we want to support in Derby and implement them in several phases.
          Hide
          Rick Hillegas added a comment -

          At the risk of yanking on the ball of yarn: I think it would be very useful if this effort included CREATE/DROP ROLE as well. There's a real opportunity for parallel development here: one engineer can get started on GRANT/REVOKE and the other can implement roles. I question the utility of GRANT/REVOKE without roles. Administering permissions without roles lies somewhere on the continuum from brittle to nightmarish.

          Show
          Rick Hillegas added a comment - At the risk of yanking on the ball of yarn: I think it would be very useful if this effort included CREATE/DROP ROLE as well. There's a real opportunity for parallel development here: one engineer can get started on GRANT/REVOKE and the other can implement roles. I question the utility of GRANT/REVOKE without roles. Administering permissions without roles lies somewhere on the continuum from brittle to nightmarish.
          Hide
          Satheesh Bandaram added a comment -

          Thanks for your interest, Francois... It will be good to collaborate on this important and rather large effort with you. Hope there is more interest in the community to join us.

          Show
          Satheesh Bandaram added a comment - Thanks for your interest, Francois... It will be good to collaborate on this important and rather large effort with you. Hope there is more interest in the community to join us.
          Hide
          Francois Orsini added a comment -

          Grant/Revoke capability is a rather important and high-priority feature IMHO - I'm interested in contributing to such an effort.

          Show
          Francois Orsini added a comment - Grant/Revoke capability is a rather important and high-priority feature IMHO - I'm interested in contributing to such an effort.

            People

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

              Dates

              • Created:
                Updated:
                Resolved:

                Development