Derby
  1. Derby
  2. DERBY-1646

Documentation to address Grant/Revoke Authorization for views/triggers/constraints/routines(DERBY-1330)

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.2.1.6
    • Fix Version/s: 10.2.1.6
    • Component/s: Documentation
    • Labels:
      None
    • Urgency:
      Urgent

      Description

      Creating a separate jira entry for documentation of Grant/Revoke Authorization for views/triggers/constraints/routines(Engine changes are going as part of DERBY-1330).

      Will link this jira entry to DERBY-1330

      1. derby1646_1.diff
        11 kB
        Laura Stewart
      2. derby1646_2.diff
        19 kB
        Laura Stewart
      3. derby1646_3.diff
        19 kB
        Laura Stewart
      4. derby1646_4.diff
        19 kB
        Laura Stewart
      5. derby1646_5.diff
        19 kB
        Laura Stewart
      6. derby1646_html1.zip
        11 kB
        Laura Stewart
      7. derby1646_html2.zip
        11 kB
        Laura Stewart
      8. derby1646_html3.zip
        11 kB
        Laura Stewart
      9. derby1646_html4.zip
        11 kB
        Laura Stewart
      10. derby1646_html5.zip
        11 kB
        Laura Stewart

        Issue Links

          Activity

          Hide
          Laura Stewart added a comment -

          In Derby-1330 I asked some questions about the properties in the spec AuthorizationMOdelFroDerbySQLStandardAuthorizationV2.html.

          I need some further clarification on these 2 properties and how we refer to them:

          derby.database.defaultConnectionMode
          derby.database.sqlAuthorization

          Questions:
          1. If the derby.database.defaultConnectionMode property is not set, the default authorization is fullAccess. The user does not have to set anything, correct?

          2. Does the derby.database.sqlAuthorization have a default setting?

          3. Is it important for the documentation to distingush between the "Derby Authorization model" and the "Derby SQL Standard Authorization model"?
          Are these the terms that should be externalized in the documentation?

          Thanks!

          Show
          Laura Stewart added a comment - In Derby-1330 I asked some questions about the properties in the spec AuthorizationMOdelFroDerbySQLStandardAuthorizationV2.html. I need some further clarification on these 2 properties and how we refer to them: derby.database.defaultConnectionMode derby.database.sqlAuthorization Questions: 1. If the derby.database.defaultConnectionMode property is not set, the default authorization is fullAccess. The user does not have to set anything, correct? 2. Does the derby.database.sqlAuthorization have a default setting? 3. Is it important for the documentation to distingush between the "Derby Authorization model" and the "Derby SQL Standard Authorization model"? Are these the terms that should be externalized in the documentation? Thanks!
          Hide
          Mamta A. Satoor added a comment -

          Laura, answers to your questions

          1)Yes

          2)false

          3)I think we should distinguish between the 2 models so we can define them in one place in the documentation and then refer to them when we talk about info specific to each of the 2 models. It will be good to know other people's opinion on this question.

          Show
          Mamta A. Satoor added a comment - Laura, answers to your questions 1)Yes 2)false 3)I think we should distinguish between the 2 models so we can define them in one place in the documentation and then refer to them when we talk about info specific to each of the 2 models. It will be good to know other people's opinion on this question.
          Hide
          Daniel John Debrunner added a comment -

          3) In one way there are not really two models, Laura you put it well in some other comment that said something like derby.database.sqlAuthorization=true allows the application to use grant/revoke. The existing coarse grained authorization continues to work (I hope and applications have the option of using the fine grained authorization (grant/revoke)

          The way there are two models is that default accessbility to objects is open without grant/revoke and restricted to the object's owner with grant/revoke.

          With derby.database.sqlAuthorization=false (or not set)

          If I create a table T then anyone else can access it in any way, including dropping it ,subject to their coarse grained authorization (noAccess, full or readonly).

          With derby.database.sqlAuthorization=true

          If I create a table T then no-one else has any access to it unless I grant them select/insert/update and/or delete access. No-one else
          can drop the table under any circumstance. For any other user with a granted privilege on T they are still subject to their coarse grained authorization (noAccess, full or readonly). So if I grant INSERT access to a user that only has read-only connection authorization, then they can not insert into the table.

          Show
          Daniel John Debrunner added a comment - 3) In one way there are not really two models, Laura you put it well in some other comment that said something like derby.database.sqlAuthorization=true allows the application to use grant/revoke. The existing coarse grained authorization continues to work (I hope and applications have the option of using the fine grained authorization (grant/revoke) The way there are two models is that default accessbility to objects is open without grant/revoke and restricted to the object's owner with grant/revoke. With derby.database.sqlAuthorization=false (or not set) If I create a table T then anyone else can access it in any way, including dropping it ,subject to their coarse grained authorization (noAccess, full or readonly). With derby.database.sqlAuthorization=true If I create a table T then no-one else has any access to it unless I grant them select/insert/update and/or delete access. No-one else can drop the table under any circumstance. For any other user with a granted privilege on T they are still subject to their coarse grained authorization (noAccess, full or readonly). So if I grant INSERT access to a user that only has read-only connection authorization, then they can not insert into the table.
          Hide
          Laura Stewart added a comment -

          Okay, I see what you mean.
          I am not sure that we want to use "coarse grained" and "fine grained" as descriptions for these modes.
          How about "levels of access" ?

          Show
          Laura Stewart added a comment - Okay, I see what you mean. I am not sure that we want to use "coarse grained" and "fine grained" as descriptions for these modes. How about "levels of access" ?
          Hide
          Laura Stewart added a comment -

          I was very unhappy with the content of the file that describes setting the default access mode.
          So based on your comments I reworded it. Please let me know if there is anything amiss...

          Setting the default access mode

          There are two properties that control the default access mode for database objects, the derby.database.defaultConnectionMode property and the derby.database.sqlAuthorization property.

          The default settings for these properties allow anyone to access and drop the database objects that you create. The default setting for the derby.database.defaultConnectionMode property is fullAccess and the default setting for the derby.database.sqlAuthorization property is FALSE. You can change the default access mode by specify different settings for these properties.

          These properties work together:

          When the derby.database.sqlAuthorization property is FALSE, the default access mode is determined by the setting for the derby.database.defaultConnectionMode property. If the derby.database.defaultConnectionMode property is set to readOnlyAccess, users can access (read) database all of the objects but they cannot update or drop the objects.

          When the derby.database.sqlAuthorization property is TRUE, the default access mode is restricted to the owner of the database objects. The owner must grant permission for others to access the database objects. No one but the owner of an object can drop the object.

          The access mode specified for the derby.database.sqlAuthorization property overrides the permissions that are granted by the owner of a database object. For example, if a user is granted INSERT privileges on a table but the user only has read-only connection authorization, the user cannot insert data into the table.

          Derby validates the database authorization properties when you set the properties. A user authorization exception is returned if you specify an invalid value when you set these properties.

          derby.database.defaultConnectionMode property
          The derby.database.defaultConnectionMode property controls the default authorization when users connect to the database.
          The valid settings for the derby.database.defaultConnectionMode property are:

          noAccess
          readOnlyAccess
          fullAccess

          The default value is fullAccess.

          derby.database.sqlAuthorization property
          The derby.database.sqlAuthorization property controls the ability for object owners to grant and revoke permission for users to perform actions on database objects.
          The valid settings for the derby.database.sqlAuthorization property are:

          TRUE
          FALSE

          The default value is FALSE.

          Show
          Laura Stewart added a comment - I was very unhappy with the content of the file that describes setting the default access mode. So based on your comments I reworded it. Please let me know if there is anything amiss... Setting the default access mode There are two properties that control the default access mode for database objects, the derby.database.defaultConnectionMode property and the derby.database.sqlAuthorization property. The default settings for these properties allow anyone to access and drop the database objects that you create. The default setting for the derby.database.defaultConnectionMode property is fullAccess and the default setting for the derby.database.sqlAuthorization property is FALSE. You can change the default access mode by specify different settings for these properties. These properties work together: When the derby.database.sqlAuthorization property is FALSE, the default access mode is determined by the setting for the derby.database.defaultConnectionMode property. If the derby.database.defaultConnectionMode property is set to readOnlyAccess, users can access (read) database all of the objects but they cannot update or drop the objects. When the derby.database.sqlAuthorization property is TRUE, the default access mode is restricted to the owner of the database objects. The owner must grant permission for others to access the database objects. No one but the owner of an object can drop the object. The access mode specified for the derby.database.sqlAuthorization property overrides the permissions that are granted by the owner of a database object. For example, if a user is granted INSERT privileges on a table but the user only has read-only connection authorization, the user cannot insert data into the table. Derby validates the database authorization properties when you set the properties. A user authorization exception is returned if you specify an invalid value when you set these properties. derby.database.defaultConnectionMode property The derby.database.defaultConnectionMode property controls the default authorization when users connect to the database. The valid settings for the derby.database.defaultConnectionMode property are: noAccess readOnlyAccess fullAccess The default value is fullAccess. derby.database.sqlAuthorization property The derby.database.sqlAuthorization property controls the ability for object owners to grant and revoke permission for users to perform actions on database objects. The valid settings for the derby.database.sqlAuthorization property are: TRUE FALSE The default value is FALSE.
          Hide
          Satheesh Bandaram added a comment -

          I think we need to document "Derby Authorization mode" and "SQL standard authorization mode" in Derby. That is we need to externalize the modes, as enabling GRANT/REVOKE while allows fine grained access, it also takes away something that used to work before. (like ability to create any number of schemas or ability to create objects in other schemas etc.)

          I think it is important to be consistant about defaultConnectionMode that controls access mode and sqlAuthorization that enables SQL standard authorization. Though both properties can interact with each other, I think, it may be best to document each independently and then may be have a section that covers what happens when both are set. I will reply to Laura's earlier post in another comment.

          Show
          Satheesh Bandaram added a comment - I think we need to document "Derby Authorization mode" and "SQL standard authorization mode" in Derby. That is we need to externalize the modes, as enabling GRANT/REVOKE while allows fine grained access, it also takes away something that used to work before. (like ability to create any number of schemas or ability to create objects in other schemas etc.) I think it is important to be consistant about defaultConnectionMode that controls access mode and sqlAuthorization that enables SQL standard authorization. Though both properties can interact with each other, I think, it may be best to document each independently and then may be have a section that covers what happens when both are set. I will reply to Laura's earlier post in another comment.
          Hide
          Satheesh Bandaram added a comment -

          I have rewriten Laura's comment earlier with some updates. This being a text window, I couldn't high-light changes, sorry. I think it is important not to mix up "access mode" with "authorization".

          Let me know if you need any further clarifications.

          Setting the default access mode

          There are two properties that control access to database objects for users. They are derby.database.defaultConnectionMode and derby.database.sqlAuthorization.

          The default settings for these properties allow anyone to access and drop the database objects that you create. The default setting for the derby.database.defaultConnectionMode property is fullAccess and the default setting for the derby.database.sqlAuthorization property is FALSE. You can change the default access mode by specify different settings for these properties.

          These properties work together:

          When the derby.database.sqlAuthorization property is FALSE, ability to read or write database objects is determained by the setting for the derby.database.defaultConnectionMode property. If the derby.database.defaultConnectionMode property is set to readOnlyAccess, users can access (read) database all of the objects but they cannot update or drop the objects.

          When the derby.database.sqlAuthorization property is TRUE, ability to read or write database objects is further restricted to the owner of the database objects. The owner must grant permission for others to access the database objects. No one but the owner of an object can drop the object. Note that derby.database.defaultConnectionMode, if set to readOnlyAccess, allows read only access to database objects even for the owner of an object.

          The access mode specified for the derby.database.defaultConnectionMode property overrides the permissions that are granted by the owner of a database object. For example, if a user is granted INSERT privileges on a table but the user only has read-only connection access, the user cannot insert data into the table.

          Derby validates the database properties when you set the properties. A user exception is returned if you specify an invalid value when you set these properties.

          derby.database.defaultConnectionMode property
          The derby.database.defaultConnectionMode property controls the default authorization when users connect to the database.
          The valid settings for the derby.database.defaultConnectionMode property are:

          noAccess
          readOnlyAccess
          fullAccess

          The default value is fullAccess.

          derby.database.sqlAuthorization property
          The derby.database.sqlAuthorization property provides the ability for object owners to grant and revoke permission for users to perform actions on database objects.
          The valid settings for the derby.database.sqlAuthorization property are:

          TRUE
          FALSE

          The default value is FALSE.

          Show
          Satheesh Bandaram added a comment - I have rewriten Laura's comment earlier with some updates. This being a text window, I couldn't high-light changes, sorry. I think it is important not to mix up "access mode" with "authorization". Let me know if you need any further clarifications. Setting the default access mode There are two properties that control access to database objects for users. They are derby.database.defaultConnectionMode and derby.database.sqlAuthorization. The default settings for these properties allow anyone to access and drop the database objects that you create. The default setting for the derby.database.defaultConnectionMode property is fullAccess and the default setting for the derby.database.sqlAuthorization property is FALSE. You can change the default access mode by specify different settings for these properties. These properties work together: When the derby.database.sqlAuthorization property is FALSE, ability to read or write database objects is determained by the setting for the derby.database.defaultConnectionMode property. If the derby.database.defaultConnectionMode property is set to readOnlyAccess, users can access (read) database all of the objects but they cannot update or drop the objects. When the derby.database.sqlAuthorization property is TRUE, ability to read or write database objects is further restricted to the owner of the database objects. The owner must grant permission for others to access the database objects. No one but the owner of an object can drop the object. Note that derby.database.defaultConnectionMode, if set to readOnlyAccess, allows read only access to database objects even for the owner of an object. The access mode specified for the derby.database.defaultConnectionMode property overrides the permissions that are granted by the owner of a database object. For example, if a user is granted INSERT privileges on a table but the user only has read-only connection access, the user cannot insert data into the table. Derby validates the database properties when you set the properties. A user exception is returned if you specify an invalid value when you set these properties. derby.database.defaultConnectionMode property The derby.database.defaultConnectionMode property controls the default authorization when users connect to the database. The valid settings for the derby.database.defaultConnectionMode property are: noAccess readOnlyAccess fullAccess The default value is fullAccess. derby.database.sqlAuthorization property The derby.database.sqlAuthorization property provides the ability for object owners to grant and revoke permission for users to perform actions on database objects. The valid settings for the derby.database.sqlAuthorization property are: TRUE FALSE The default value is FALSE.
          Hide
          John H. Embretsen added a comment -

          I noticed the following odd wording in the paragraph below "These properties work together":

          "... users can access (read) database all of the objects..."

          It should probably be "... users can access (read) all of the database objects...".

          Has the term "database object" been defined somewhere in the manuals, or should it be clear to everyone what that means (and that it has nothing to do with JDO )? No big deal, just wondering...

          Show
          John H. Embretsen added a comment - I noticed the following odd wording in the paragraph below "These properties work together": "... users can access (read) database all of the objects..." It should probably be "... users can access (read) all of the database objects...". Has the term "database object" been defined somewhere in the manuals, or should it be clear to everyone what that means (and that it has nothing to do with JDO )? No big deal, just wondering...
          Hide
          Daniel John Debrunner added a comment -

          I like it when Satheesh wrote:
          "I think it is important to be consistant about defaultConnectionMode that controls access mode and sqlAuthorization that enables SQL standard authorization"

          Use "authorization" for grant/revoke and "access " for the connection full/read/none access., maybe even clarify by calling it "connection access".

          This would need to follow through to Satheesh's re-write in the comment above (dated 5 Aug 12:14am)
          http://issues.apache.org/jira/browse/DERBY-1646#action_12425937

          E.g.
          Replace:
          The derby.database.defaultConnectionMode property controls the default authorization when users connect to the database.
          with
          The derby.database.defaultConnectionMode property controls the default connection access when users connect to the database.

          Maybe the whole first section could be re-written to not have defaultConnectionMode refer to database objects at all, if one just thinks about connection access then it would be described by:

          none - The user is not allowed to make a connection to the database
          read-only - the user's connection is in read-only mode and can not modify the database in any way . Connecttion.isReadOnly will return true and
          Connection.setReadOnly(false) will throw an exception
          fullAccess - The connection is in read-write mode, Connecttion.isReadOnly returns false, Connection.setReadOnly() may be used freely.

          Show
          Daniel John Debrunner added a comment - I like it when Satheesh wrote: "I think it is important to be consistant about defaultConnectionMode that controls access mode and sqlAuthorization that enables SQL standard authorization" Use "authorization" for grant/revoke and "access " for the connection full/read/none access., maybe even clarify by calling it "connection access". This would need to follow through to Satheesh's re-write in the comment above (dated 5 Aug 12:14am) http://issues.apache.org/jira/browse/DERBY-1646#action_12425937 E.g. Replace: The derby.database.defaultConnectionMode property controls the default authorization when users connect to the database. with The derby.database.defaultConnectionMode property controls the default connection access when users connect to the database. Maybe the whole first section could be re-written to not have defaultConnectionMode refer to database objects at all, if one just thinks about connection access then it would be described by: none - The user is not allowed to make a connection to the database read-only - the user's connection is in read-only mode and can not modify the database in any way . Connecttion.isReadOnly will return true and Connection.setReadOnly(false) will throw an exception fullAccess - The connection is in read-write mode, Connecttion.isReadOnly returns false, Connection.setReadOnly() may be used freely.
          Hide
          Satheesh Bandaram added a comment -

          Thanks Dan... I missed correcting that from Laura's original description. It was little too burried down, but glad you found it. Laura, let me know if you want me to rewrite it again or if you got all the comments.

          Show
          Satheesh Bandaram added a comment - Thanks Dan... I missed correcting that from Laura's original description. It was little too burried down, but glad you found it. Laura, let me know if you want me to rewrite it again or if you got all the comments.
          Hide
          Laura Stewart added a comment -

          Thank you for your comments.

          Before I start using "connection access" I want to make certain that we are not making this overly complex for Derby developers.
          In the security section of the Developers Guide (Security ->Working with user authenticaion and Security ->Users and authorization identifiers) the term "user authentication" is used pervasively and seems to coinside with the term you propose "connection access".
          I don't really want to introduce a new term or change an existing term that is pervasive....
          Or perhaps these sections are discussing something completely different?

          Show
          Laura Stewart added a comment - Thank you for your comments. Before I start using "connection access" I want to make certain that we are not making this overly complex for Derby developers. In the security section of the Developers Guide (Security ->Working with user authenticaion and Security ->Users and authorization identifiers) the term "user authentication" is used pervasively and seems to coinside with the term you propose "connection access". I don't really want to introduce a new term or change an existing term that is pervasive.... Or perhaps these sections are discussing something completely different?
          Hide
          Laura Stewart added a comment -

          I reread the sections on user authorization and user authentication... and I now see that I was confused.
          I will add the term "connection access" and clarify the topics so that others don't get confused as well

          Show
          Laura Stewart added a comment - I reread the sections on user authorization and user authentication... and I now see that I was confused. I will add the term "connection access" and clarify the topics so that others don't get confused as well
          Hide
          Laura Stewart added a comment -

          This documentation issue is tied to Derby1057.
          I believe that all of the documentation requirements for 1646
          have been addressed in the patches for 1057.
          I would like to close 1646.

          Show
          Laura Stewart added a comment - This documentation issue is tied to Derby1057. I believe that all of the documentation requirements for 1646 have been addressed in the patches for 1057. I would like to close 1646.
          Hide
          Mamta A. Satoor added a comment -

          One final area that requires documentation has to do with revoke privilege on table/column levels.

          Currently, all the privilege types (SELECT, DELETE, INSERT, UPDATE, REFERENCES, TRIGGER) for a given grantee and tableid are kept in one row in SYSTABLEPERMS. ie say user user2 has SELECT and DELETE privileges on table user1.t1, then the row in SYSTABLEPERMS for grantee user2 and table user1.t1 will have SELECTPRIV and DELETEPRIV set to "Y" but the rest of the privilege type fields, INSERTPRIV, UPDATEPRIV, REFERENCESPRIV and TRIGGERPRIV will be set to "N".
          Later, when an object is created which relies on any granted privilege type for a given tableid and grantee, the Derby engine tracks the dependency of the newly created object on the specific row in SYSTABLEPERMS ie the dependency manager only knows that the object is dependent on some privilege type in that specific row but it doesn't know exactly what privilege type.
          Later, when a revoke table level privilege is issued for the given grantee and tableid, all the dependents of grantee and tableid get notified. All the dependents might not need the specific privilege type being revoked for the given grantee and tableid, but the dependents of that row in SYSTABLEPERMS will still end up dropping themselves. This behavior of Derby should be enhanced in future so that real dependents of a privilege type on a given grantee, tableid get notified but until then, we should document this behavior.
          Following is an example showing the current behavior for privilege granted at table level.
          user1
          create table t1
          grant select, delete on t1 to user2 – one row in SYSTABLEPERMS for grantee(user2), table(t1) with SELECTPRIV and DELETEPRIV set to "Y"
          user2
          create view v1 as select * from user1.t1 – dependency manager tracks dependency of v1 on row in SYSTABLEPERMS for grantee(user2), table(t1)
          – Note that dependency manager does not track dependency on specific privilege type for this row which in this case is SELECTPRIV
          – Note that view v1 has no requirement for DELETEPRIV
          user1
          revoke delete on t1 from user2 – since row in SYSTABLEPERMS for grantee(user2), table(t1) got modified because of this revoke,
          – dependency manager sends a revoke invalidation message to view user2.v1 and the view ends up dropping itself eventhough it was
          – never dependent on DELETEPRIV
          – Derby behavior should be modified in future such that the revoke delete above does not end up dropping view user2.v1
          – View user2.v1 should get dropped automatically, only if revoke select on t1 from user2 is issued. Until we implement this behavior,
          – we should document the current behavior somewhere.

          Similar behavior exists for column level privileges. Currently, if a privilege is granted at columns level, then we create a row in SYSCOLPERMS for grantee, tableid, columns on which permission is granted and permission type. ie say user user2 has SELECT privilege on table user1.t1's columns c11, c12 but not on columns c13, c14, then the row in SYSCOLPERMS will have privilege type as SELECT for grantee user2, table user1.t1 and column bits set for columns c11, c12.
          Later, when an object is created which relies on a granted privilege type for a given grantee and subset of columns in a tableid, the Derby engine tracks the dependency of the newly created object on the specific row in SYSCOLPERMS ie the dependency manager only knows that the object is dependent on some columns in that specific row but it doesn't know exactly which columns.
          Later, when a revoke column level privilege is issued for the given grantee and tableid and privilege type, all the dependents of grantee, tableid and privilege type get notified. All the dependents might not need the specific columns whose privilege is being revoked for the given grantee and tableid and privilege type, but the dependents of that row in SYSCOLPERMS will still end up dropping themselves. This behavior of Derby should be enhanced in future so that real dependents of the column list for a privilege type on a given grantee, tableid get notified but until then, we should document this behavior.
          Following is an example showing the current behavior for privilege granted at column level.
          user1
          create table t1(c11, c12, c13, c14)
          grant select(c11, c12) on t1 to user2 – one row in SYSCOLPERMS for grantee(user2), table(t1), columns(c11, c12) and privilege type(SELECT)
          user2
          create view v1 as select c11 from user1.t1 – dependency manager tracks dependency of v1 on row in SYSCOLPERMS for grantee(user2), table(t1)
          – and privilege type SELECT. Note that dependency manager does not track dependency on specific column list of table t1 in SYSCOLPERMS/
          – Note that view v1 has no requirements for SELECT privilege on column c12 in user1.t1
          user1
          revoke select(c12) on t1 from user2 – since row in SYSCOLPERMS for grantee(user2), table(t1), privilege type(SELECT) got modified because
          – of this revoke, dependency manager send a revoke invalidation message to view user2.v1 and view ends up dropping itself even though it
          – was never dependent on SELECT privilege on column c12 of table user1.t1
          – Derby behavior should be modified in future such that the revoke select on column c12 above does not end up dropping view user2.v1
          – View user2.v1 should get dropped automatically only if revoke select on column c11 on t1 from user2 is ussed. But until this behavior
          – is implemented, we should document the current behavior somewhere.

          Laura, please let me know if this is unclear. An understanding of the structure on SYSTABLEPERMS and SYSCOLPERMS might make it easier
          to understand the current behavior and ideal behavior.

          Show
          Mamta A. Satoor added a comment - One final area that requires documentation has to do with revoke privilege on table/column levels. Currently, all the privilege types (SELECT, DELETE, INSERT, UPDATE, REFERENCES, TRIGGER) for a given grantee and tableid are kept in one row in SYSTABLEPERMS. ie say user user2 has SELECT and DELETE privileges on table user1.t1, then the row in SYSTABLEPERMS for grantee user2 and table user1.t1 will have SELECTPRIV and DELETEPRIV set to "Y" but the rest of the privilege type fields, INSERTPRIV, UPDATEPRIV, REFERENCESPRIV and TRIGGERPRIV will be set to "N". Later, when an object is created which relies on any granted privilege type for a given tableid and grantee, the Derby engine tracks the dependency of the newly created object on the specific row in SYSTABLEPERMS ie the dependency manager only knows that the object is dependent on some privilege type in that specific row but it doesn't know exactly what privilege type. Later, when a revoke table level privilege is issued for the given grantee and tableid, all the dependents of grantee and tableid get notified. All the dependents might not need the specific privilege type being revoked for the given grantee and tableid, but the dependents of that row in SYSTABLEPERMS will still end up dropping themselves. This behavior of Derby should be enhanced in future so that real dependents of a privilege type on a given grantee, tableid get notified but until then, we should document this behavior. Following is an example showing the current behavior for privilege granted at table level. user1 create table t1 grant select, delete on t1 to user2 – one row in SYSTABLEPERMS for grantee(user2), table(t1) with SELECTPRIV and DELETEPRIV set to "Y" user2 create view v1 as select * from user1.t1 – dependency manager tracks dependency of v1 on row in SYSTABLEPERMS for grantee(user2), table(t1) – Note that dependency manager does not track dependency on specific privilege type for this row which in this case is SELECTPRIV – Note that view v1 has no requirement for DELETEPRIV user1 revoke delete on t1 from user2 – since row in SYSTABLEPERMS for grantee(user2), table(t1) got modified because of this revoke, – dependency manager sends a revoke invalidation message to view user2.v1 and the view ends up dropping itself eventhough it was – never dependent on DELETEPRIV – Derby behavior should be modified in future such that the revoke delete above does not end up dropping view user2.v1 – View user2.v1 should get dropped automatically, only if revoke select on t1 from user2 is issued. Until we implement this behavior, – we should document the current behavior somewhere. Similar behavior exists for column level privileges. Currently, if a privilege is granted at columns level, then we create a row in SYSCOLPERMS for grantee, tableid, columns on which permission is granted and permission type. ie say user user2 has SELECT privilege on table user1.t1's columns c11, c12 but not on columns c13, c14, then the row in SYSCOLPERMS will have privilege type as SELECT for grantee user2, table user1.t1 and column bits set for columns c11, c12. Later, when an object is created which relies on a granted privilege type for a given grantee and subset of columns in a tableid, the Derby engine tracks the dependency of the newly created object on the specific row in SYSCOLPERMS ie the dependency manager only knows that the object is dependent on some columns in that specific row but it doesn't know exactly which columns. Later, when a revoke column level privilege is issued for the given grantee and tableid and privilege type, all the dependents of grantee, tableid and privilege type get notified. All the dependents might not need the specific columns whose privilege is being revoked for the given grantee and tableid and privilege type, but the dependents of that row in SYSCOLPERMS will still end up dropping themselves. This behavior of Derby should be enhanced in future so that real dependents of the column list for a privilege type on a given grantee, tableid get notified but until then, we should document this behavior. Following is an example showing the current behavior for privilege granted at column level. user1 create table t1(c11, c12, c13, c14) grant select(c11, c12) on t1 to user2 – one row in SYSCOLPERMS for grantee(user2), table(t1), columns(c11, c12) and privilege type(SELECT) user2 create view v1 as select c11 from user1.t1 – dependency manager tracks dependency of v1 on row in SYSCOLPERMS for grantee(user2), table(t1) – and privilege type SELECT. Note that dependency manager does not track dependency on specific column list of table t1 in SYSCOLPERMS/ – Note that view v1 has no requirements for SELECT privilege on column c12 in user1.t1 user1 revoke select(c12) on t1 from user2 – since row in SYSCOLPERMS for grantee(user2), table(t1), privilege type(SELECT) got modified because – of this revoke, dependency manager send a revoke invalidation message to view user2.v1 and view ends up dropping itself even though it – was never dependent on SELECT privilege on column c12 of table user1.t1 – Derby behavior should be modified in future such that the revoke select on column c12 above does not end up dropping view user2.v1 – View user2.v1 should get dropped automatically only if revoke select on column c11 on t1 from user2 is ussed. But until this behavior – is implemented, we should document the current behavior somewhere. Laura, please let me know if this is unclear. An understanding of the structure on SYSTABLEPERMS and SYSCOLPERMS might make it easier to understand the current behavior and ideal behavior.
          Hide
          Rick Hillegas added a comment -

          Assign to 10.2 and bump urgency.

          Show
          Rick Hillegas added a comment - Assign to 10.2 and bump urgency.
          Hide
          Laura Stewart added a comment -

          Mamta - I think that a new topic is needed to discuss this behavior.
          With a title of "Limitations of revoke privileges on tables and columns"
          I need your help in determining where to put this new topic.

          Show
          Laura Stewart added a comment - Mamta - I think that a new topic is needed to discuss this behavior. With a title of "Limitations of revoke privileges on tables and columns" I need your help in determining where to put this new topic.
          Hide
          Laura Stewart added a comment -

          One place that we could add this information is in the Developers Guide - the "SQL tips" section.
          Although we might want to change the title of the section to "Using SQL with Derby"

          Show
          Laura Stewart added a comment - One place that we could add this information is in the Developers Guide - the "SQL tips" section. Although we might want to change the title of the section to "Using SQL with Derby"
          Hide
          Mamta A. Satoor added a comment -

          Laura, I feel that it should as a topic in the same page as the exsiting revoke statement. This way, all the pertinent information about revoke is in one single place.

          If we do decide to put it under SQL tips section (with the new title "Using SQL with Derby"), then we should clear links from the existing revoke statement page so users will know to look there to understand the revoke functionality completely.

          Show
          Mamta A. Satoor added a comment - Laura, I feel that it should as a topic in the same page as the exsiting revoke statement. This way, all the pertinent information about revoke is in one single place. If we do decide to put it under SQL tips section (with the new title "Using SQL with Derby"), then we should clear links from the existing revoke statement page so users will know to look there to understand the revoke functionality completely.
          Hide
          Laura Stewart added a comment -

          None of the other SQL statements have separate topics that discuss limitations. Nor do they have sections within the statements that discuss limitations.... but in looking at the options of where to put this info, I think that we should add a section to the Revoke statement called "Limitations of tables and columns".

          Show
          Laura Stewart added a comment - None of the other SQL statements have separate topics that discuss limitations. Nor do they have sections within the statements that discuss limitations.... but in looking at the options of where to put this info, I think that we should add a section to the Revoke statement called "Limitations of tables and columns".
          Hide
          Mamta A. Satoor added a comment -

          That sounds fine, Laura.

          Show
          Mamta A. Satoor added a comment - That sounds fine, Laura.
          Hide
          Laura Stewart added a comment -

          Hi Mamta -

          I ahve drafted the text for the table-level limitation. Please look it over and see if I have captured what you want.

          Limitations

          Table-level privileges
          All of the table-level privilege types for a specified grantee and table ID are stored in one row in the SYSTABLEPERMS system table. For example, when user2 is granted the SELECT and DELETE privileges on table user1.t1, a row is added to the SYSTABLEPERMS table. The GRANTEE field contains user2 and the TABLEID contains user1.t1. The SELECTPRIV and DELETEPRIV fields are set to Y. The remaining privilege type fields are set to N.

          When a grantee creates an object that relies on one of the privilege types, the Derby engine tracks the dependency of the object on the specific row in the SYSTABLEPERMS table. For example, user2 creates the view v1 by using the statement SELECT * FROM user1.t1, the dependency manager tracks the dependency of view v1 on the row in SYSTABLEPERMS for GRANTEE(user2), TABLEID(user1.t1). The dependency manager knows only that the view is dependent on a privilege type in that specific row, but does not track exactly which privilege type the view is dependent on.

          When a REVOKE statement for a table-level privilege is issued for a grantee and table ID, all of the objects that are dependent on the grantee and table ID are dropped. For example, if user1 revokes the DELETE privilege on table t1 from user2, the row in SYSTABLEPERMS for GRANTEE(user2), TABLEID(user1.t1) is modified by the REVOKE statement. The dependency manager sends a revoke invalidation message to the view user2.v1 and the view is dropped even though the view is not dependent on the DELETE privilege for GRANTEE(user2), TABLEID(user1.t1).

          Show
          Laura Stewart added a comment - Hi Mamta - I ahve drafted the text for the table-level limitation. Please look it over and see if I have captured what you want. Limitations Table-level privileges All of the table-level privilege types for a specified grantee and table ID are stored in one row in the SYSTABLEPERMS system table. For example, when user2 is granted the SELECT and DELETE privileges on table user1.t1, a row is added to the SYSTABLEPERMS table. The GRANTEE field contains user2 and the TABLEID contains user1.t1. The SELECTPRIV and DELETEPRIV fields are set to Y. The remaining privilege type fields are set to N. When a grantee creates an object that relies on one of the privilege types, the Derby engine tracks the dependency of the object on the specific row in the SYSTABLEPERMS table. For example, user2 creates the view v1 by using the statement SELECT * FROM user1.t1, the dependency manager tracks the dependency of view v1 on the row in SYSTABLEPERMS for GRANTEE(user2), TABLEID(user1.t1). The dependency manager knows only that the view is dependent on a privilege type in that specific row, but does not track exactly which privilege type the view is dependent on. When a REVOKE statement for a table-level privilege is issued for a grantee and table ID, all of the objects that are dependent on the grantee and table ID are dropped. For example, if user1 revokes the DELETE privilege on table t1 from user2, the row in SYSTABLEPERMS for GRANTEE(user2), TABLEID(user1.t1) is modified by the REVOKE statement. The dependency manager sends a revoke invalidation message to the view user2.v1 and the view is dropped even though the view is not dependent on the DELETE privilege for GRANTEE(user2), TABLEID(user1.t1).
          Hide
          Mamta A. Satoor added a comment -

          Laura, that's great. Now, you just have to do your magic for column level privileges.

          Show
          Mamta A. Satoor added a comment - Laura, that's great. Now, you just have to do your magic for column level privileges.
          Hide
          Laura Stewart added a comment -

          Updated the following files:

          Filename Title Summary of update
          cdevcsecuregrantrevokeaccess SQL standard authorization Corrected typo
          derbydev.ditamap Updated links
          refderby.ditamap Updated links
          rrefsistabs12813 SYSDEPENDS Corrected typo
          rrefsqljrevoke REVOKE statement Added limitations for table/column privileges

          Show
          Laura Stewart added a comment - Updated the following files: Filename Title Summary of update cdevcsecuregrantrevokeaccess SQL standard authorization Corrected typo derbydev.ditamap Updated links refderby.ditamap Updated links rrefsistabs12813 SYSDEPENDS Corrected typo rrefsqljrevoke REVOKE statement Added limitations for table/column privileges
          Hide
          Mamta A. Satoor added a comment -

          Laura, thanks for working on the documentation part of grant revoke. I have following feedback

          ************************************************************
          Comments on page cdevcsecuregrantrevokeaccess.html
          a)User harry is referred to as "Harry" in somepleaces. We should maintain the case. Also, we should use the same font/boldness for user names on the page.
          b)Example 2
          We should modify the paragraph from
          "User anita creates table t1 and grants SELECT privileges to PUBLIC. User harry creates view v1 with the statement SELECT * from anita.t1. The view depends on the PUBLIC level privilege that user harry has on t1 since user harry does not have user-level privileges on table t1. Subsequently, user anita revokes SELECT privileges from PUBLIC on table t1. As a result, the view harry.v1 is dropped. "

          to something like following

          "User anita creates table t1 and grants SELECT privileges to PUBLIC. User harry creates view v1 with the statement SELECT * from anita.t1. The view depends on the PUBLIC level privilege that user harry has on t1 since user harry does not have user-level privileges on table t1 at this point. Subsequently, user anita grants SELECT privileges to user harry on table t1. Later, user anita revokes SELECT privileges from PUBLIC on table t1. As a result, the view harry.v1 is dropped. "
          ************************************************************

          ************************************************************
          Comments on page rrefsqljrevoke.html
          a)Minor typo in following paragraph
          "The privileges revoked from PUBLIC and to individual users are independent privileges"
          Should read as
          "The privileges revoked from PUBLIC and from individual users are independent privileges"

          b)User harry is referred to as "Harry" in somepleaces. We should maintain the case. Also, we should use the same font/boldness for user names on the page.

          c)The first line of the first paragraph for Column-level privileges does not have the correct information about SYSCOLPERMS table
          "All of the column-level privileges for a specified grantee and table ID are stored in one row in the SYSCOLPERMS system table. "
          Each row in SYSCOLPERMS table represents one granted privilege type for a given GRANTEE, TABLEID and columns on which that privilege type is granted. So, say table t1 has columns c11, c12, c13, c14 and SELECT privilege is granted to user2 on c11 and c12 and UPDATE privilege is granted to user2 on c12 and c13. Then there will be 2 rows in SYSCOLPERMS. One row for privilege type SELECT, GRANTEE(user2), TABLEID(t1), columns(c11, c12). Another row in SYSCOLPERMS will be for privilege type UPDATE, GRANTEE(user2), TABLEID(t1), columns (c12, c13).
          ************************************************************

          ************************************************************
          One final comment, do we need to write anything about warnings that get raised when revoke is trying to take away a permission that was never granted? Deepa worked on that enhancement as part of DERBY-1582
          ************************************************************

          Show
          Mamta A. Satoor added a comment - Laura, thanks for working on the documentation part of grant revoke. I have following feedback ************************************************************ Comments on page cdevcsecuregrantrevokeaccess.html a)User harry is referred to as "Harry" in somepleaces. We should maintain the case. Also, we should use the same font/boldness for user names on the page. b)Example 2 We should modify the paragraph from "User anita creates table t1 and grants SELECT privileges to PUBLIC. User harry creates view v1 with the statement SELECT * from anita.t1. The view depends on the PUBLIC level privilege that user harry has on t1 since user harry does not have user-level privileges on table t1. Subsequently, user anita revokes SELECT privileges from PUBLIC on table t1. As a result, the view harry.v1 is dropped. " to something like following "User anita creates table t1 and grants SELECT privileges to PUBLIC. User harry creates view v1 with the statement SELECT * from anita.t1. The view depends on the PUBLIC level privilege that user harry has on t1 since user harry does not have user-level privileges on table t1 at this point. Subsequently, user anita grants SELECT privileges to user harry on table t1. Later, user anita revokes SELECT privileges from PUBLIC on table t1. As a result, the view harry.v1 is dropped. " ************************************************************ ************************************************************ Comments on page rrefsqljrevoke.html a)Minor typo in following paragraph "The privileges revoked from PUBLIC and to individual users are independent privileges" Should read as "The privileges revoked from PUBLIC and from individual users are independent privileges" b)User harry is referred to as "Harry" in somepleaces. We should maintain the case. Also, we should use the same font/boldness for user names on the page. c)The first line of the first paragraph for Column-level privileges does not have the correct information about SYSCOLPERMS table "All of the column-level privileges for a specified grantee and table ID are stored in one row in the SYSCOLPERMS system table. " Each row in SYSCOLPERMS table represents one granted privilege type for a given GRANTEE, TABLEID and columns on which that privilege type is granted. So, say table t1 has columns c11, c12, c13, c14 and SELECT privilege is granted to user2 on c11 and c12 and UPDATE privilege is granted to user2 on c12 and c13. Then there will be 2 rows in SYSCOLPERMS. One row for privilege type SELECT, GRANTEE(user2), TABLEID(t1), columns(c11, c12). Another row in SYSCOLPERMS will be for privilege type UPDATE, GRANTEE(user2), TABLEID(t1), columns (c12, c13). ************************************************************ ************************************************************ One final comment, do we need to write anything about warnings that get raised when revoke is trying to take away a permission that was never granted? Deepa worked on that enhancement as part of DERBY-1582 ************************************************************
          Hide
          Laura Stewart added a comment -

          Hi Mamta -

          I have updated the patch and included the html files for you to look at.
          The mixing of names "harry" and "Harry" was unfortunate. "harry" was an attempt to refer to the userid or auth id and Harry as the person. In all cases I have changed them to "harry". I also fixed Anita as well.

          As for the warnings, as long as they are documented in the "Derby exception messages and SQL states" section in the Ref Manual, I don't think that we need to mention them here. If someone attemtps to revoke a userid that does not have the authorization, they will see the error message.

          Show
          Laura Stewart added a comment - Hi Mamta - I have updated the patch and included the html files for you to look at. The mixing of names "harry" and "Harry" was unfortunate. "harry" was an attempt to refer to the userid or auth id and Harry as the person. In all cases I have changed them to "harry". I also fixed Anita as well. As for the warnings, as long as they are documented in the "Derby exception messages and SQL states" section in the Ref Manual, I don't think that we need to mention them here. If someone attemtps to revoke a userid that does not have the authorization, they will see the error message.
          Hide
          Mamta A. Satoor added a comment -

          Somme feedback

                                                                                                                                • cdevcsecuregrantrevokeaccess.html
                                                                                                                                  1)Following line in Example 2 doesn't flow well
                                                                                                                                  "The view harry.v1 continues to depend on the is dependent on PUBLIC level privilege that user harry has on t1. "

                                                                                                                                • rrefsqljrevoke.html
                                                                                                                                  1) Column=level privileges says "All of the column-level privileges for a specified grantee and table ID are stored in one row in the SYSCOLPERMS system table. " That's not true. Only one type of privilege for all the columns for a specified grantee and table ID are stored in one row in the SYSCOLPERMS system table..

          Show
          Mamta A. Satoor added a comment - Somme feedback cdevcsecuregrantrevokeaccess.html 1)Following line in Example 2 doesn't flow well "The view harry.v1 continues to depend on the is dependent on PUBLIC level privilege that user harry has on t1. " rrefsqljrevoke.html 1) Column=level privileges says "All of the column-level privileges for a specified grantee and table ID are stored in one row in the SYSCOLPERMS system table. " That's not true. Only one type of privilege for all the columns for a specified grantee and table ID are stored in one row in the SYSCOLPERMS system table..
          Hide
          Laura Stewart added a comment -

          Hi Mamta
          In the following example, what is "f" as in f(T1.c3) ?
          function?

          CREATE VIEW s.v(vc1,vc2,vc3)
          AS SELECT t1.c1,t1.c2,f(t1.c3)
          FROM t1 JOIN
          t2 ON t1.c1 = t2.c1
          WHERE t2.c2 = 5

          Show
          Laura Stewart added a comment - Hi Mamta In the following example, what is "f" as in f(T1.c3) ? function? CREATE VIEW s.v(vc1,vc2,vc3) AS SELECT t1.c1,t1.c2,f(t1.c3) FROM t1 JOIN t2 ON t1.c1 = t2.c1 WHERE t2.c2 = 5
          Hide
          Laura Stewart added a comment -

          Updated the 2 files from Mamta's last comment. Included all of the files in
          a new patch diff #3 and also attached a new html.zip file #3 with all of the updates.

          Please review and comment.

          Show
          Laura Stewart added a comment - Updated the 2 files from Mamta's last comment. Included all of the files in a new patch diff #3 and also attached a new html.zip file #3 with all of the updates. Please review and comment.
          Hide
          Mamta A. Satoor added a comment -

          Laura, "f" is a name of a function in following sql
          CREATE VIEW s.v(vc1,vc2,vc3)
          AS SELECT t1.c1,t1.c2,f(t1.c3)
          FROM t1 JOIN
          t2 ON t1.c1 = t2.c1
          WHERE t2.c2 = 5

          Show
          Mamta A. Satoor added a comment - Laura, "f" is a name of a function in following sql CREATE VIEW s.v(vc1,vc2,vc3) AS SELECT t1.c1,t1.c2,f(t1.c3) FROM t1 JOIN t2 ON t1.c1 = t2.c1 WHERE t2.c2 = 5
          Hide
          Laura Stewart added a comment -

          Thank you for confirming that it is a function reference.
          Please do NOT review the #3 patch/htm files. i definately want to update this in the files
          and will post #4 very shortly.

          Show
          Laura Stewart added a comment - Thank you for confirming that it is a function reference. Please do NOT review the #3 patch/htm files. i definately want to update this in the files and will post #4 very shortly.
          Hide
          Laura Stewart added a comment -

          Here are the final updates, using #4 for the diff file and the html file.

          Show
          Laura Stewart added a comment - Here are the final updates, using #4 for the diff file and the html file.
          Hide
          Mamta A. Satoor added a comment -

          Laura, thanks for your patience. The changes look great and ready to be committed after we remove the line "When PUBLIC is specified, the privileges affect all current and future users." from the grantee section of the "REVOKE statement " page at rrefsqljrevoke.html. We should remove it because it is not valid and does not make sense for revoke. It was probably mistakenly copied from the grant statement page.

          Other than that, the changes look very good to me.

          Show
          Mamta A. Satoor added a comment - Laura, thanks for your patience. The changes look great and ready to be committed after we remove the line "When PUBLIC is specified, the privileges affect all current and future users." from the grantee section of the "REVOKE statement " page at rrefsqljrevoke.html. We should remove it because it is not valid and does not make sense for revoke. It was probably mistakenly copied from the grant statement page. Other than that, the changes look very good to me.
          Hide
          Laura Stewart added a comment -

          No worries Mamta. We want to get this correct

          I updated the file regarding PUBLIC with the REVOKE statement and have
          created a new patch file and html zip file (#5)

          Show
          Laura Stewart added a comment - No worries Mamta. We want to get this correct I updated the file regarding PUBLIC with the REVOKE statement and have created a new patch file and html zip file (#5)
          Hide
          Mamta A. Satoor added a comment -

          Laura, if that's the only change that went in #5 patch then +1 for commit from me. Thanks

          Show
          Mamta A. Satoor added a comment - Laura, if that's the only change that went in #5 patch then +1 for commit from me. Thanks
          Hide
          Andrew McIntyre added a comment -

          Committed derby1646_5 to trunk with revision 440851. Please resolve and close this issue if it is complete.

          Show
          Andrew McIntyre added a comment - Committed derby1646_5 to trunk with revision 440851. Please resolve and close this issue if it is complete.
          Hide
          Laura Stewart added a comment -

          Changing the status to Resolved until the patch is merged into the 10.2 branch by Rick.

          Show
          Laura Stewart added a comment - Changing the status to Resolved until the patch is merged into the 10.2 branch by Rick.

            People

            • Assignee:
              Laura Stewart
              Reporter:
              Mamta A. Satoor
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development