You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Satheesh Bandaram (JIRA)" <de...@db.apache.org> on 2005/07/16 02:50:09 UTC

[jira] Created: (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.

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

         Key: DERBY-464
         URL: http://issues.apache.org/jira/browse/DERBY-464
     Project: Derby
        Type: New Feature
  Components: SQL  
    Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0    
 Environment: generic
    Reporter: Satheesh Bandaram


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.


-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


Re: [jira] Updated: (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.

Posted by Satheesh Bandaram <ba...@gmail.com>.
Thanks for contributing the test. It does need some time to make it work on
Derby by modifying some of the items I mentioned earlier. Also need to setup
users referenced in the test and enable authentication. I am not sure when I
will be able to get to this.

I did work on getting nist suite to pass with SQL authorization enabled.
Once I confirm test changes I made, I will submit that.

Satheesh

On 3/28/06, Michelle Caisse (JIRA) <de...@db.apache.org> wrote:
>
>      [ http://issues.apache.org/jira/browse/DERBY-464?page=all ]
>
> Michelle Caisse updated DERBY-464:
> ----------------------------------
>
>     Attachment: Privileges2.java
>
> 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.
>
> > 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.
> >
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> >
> >          Key: DERBY-464
> >          URL: http://issues.apache.org/jira/browse/DERBY-464
> >      Project: Derby
> >         Type: New Feature
> >   Components: SQL
> >     Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0
> >  Environment: generic
> >     Reporter: Satheesh Bandaram
> >     Assignee: Satheesh Bandaram
> >  Attachments: GrantRevokePartII.stat, GrantRevokePartII.txt,
> GrantRevokePartII.txt, Privileges.java, Privileges2.java,
> changeDescriptionPartII, grantRevoke.patch.Dec5, grantRevoke.stat.Dec5,
> grantRevokeSpec.html, grantRevokeSpec_v2.html
> >
> > 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.
>
> --
> This message is automatically generated by JIRA.
> -
> If you think it was sent incorrectly contact one of the administrators:
>    http://issues.apache.org/jira/secure/Administrators.jspa
> -
> For more information on JIRA, see:
>    http://www.atlassian.com/software/jira
>
>

Re: [jira] Commented: (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.

Posted by Francois Orsini <fr...@gmail.com>.
Thanks Satheesh - much appreciated ;)

On 12/16/05, Satheesh Bandaram <sa...@sourcery.org> wrote:
>
> Hi Francois,
>
> I will update functional specification with system table schema. I will
> also add a high level design section there with some details. I am little
> pressed for time right now, but will get this out next week. (happens to be
> my off week, but open source world never sleeps, right?)
>
> Satheesh
>
> Francois Orsini wrote:
>
> Hi Satheesh,
>
> I'm reviewing the part I changes you checked-in - there is a lot of
> changes and it is somewhat tedious to understand some of the decisions that
> have been made at the implementation level - not saying anything is wrong -
> it is just hard to follow certain paths without a minimum of high-level
> technical details about the implementation (i.e. design specs) and mostly
> because of the amount of changes - there are classes without headers, which
> makes it difficult to understand the purpose of a class - sure, one can
> always spend a long time trying to understand things but I think for patches
> with such amount of changes, some high-level design details would be
> appreciated by the reviewers...I know you intended to post details about the
> system catalogs but right now one has to review the changes without a lot of
> details besides the functional specs...a 1-2 pager high-level implementation
> details would not just help reviewers to do a better appreciation of the
> changes but also incentivize more reviewers to look at the changes...
>
> Btw, these changes cross a few of the Derby subsystems and am hoping
> someone can review the query tree and nodes generation (compiler) stuffs...
>
> Thanks,
>
> --francois
>
> On 12/11/05, Satheesh Bandaram (JIRA) <de...@db.apache.org> wrote:
> >
> >     [
> > http://issues.apache.org/jira/browse/DERBY-464?page=comments#action_12360186]
> >
> > Satheesh Bandaram commented on DERBY-464:
> > -----------------------------------------
> >
> > 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.
> >
> >
> > > 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.
> > >
> > -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> >
> > >
> > >          Key: DERBY-464
> > >          URL: http://issues.apache.org/jira/browse/DERBY-464
> > >      Project: Derby
> > >         Type: New Feature
> > >   Components: SQL
> > >     Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0
> > >  Environment: generic
> > >     Reporter: Satheesh Bandaram
> > >     Assignee: Satheesh Bandaram
> > >  Attachments: grant.html, grantRevoke.patch.Dec5,
> > grantRevoke.stat.Dec5
> > >
> > > 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.
> >
> > --
> > This message is automatically generated by JIRA.
> > -
> > If you think it was sent incorrectly contact one of the administrators:
> >    http://issues.apache.org/jira/secure/Administrators.jspa
> > -
> > For more information on JIRA, see:
> >    http://www.atlassian.com/software/jira
> >
> >
>

Re: [jira] Commented: (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.

Posted by Francois Orsini <fr...@gmail.com>.
Hi Satheesh,

I'm reviewing the part I changes you checked-in - there is a lot of changes
and it is somewhat tedious to understand some of the decisions that have
been made at the implementation level - not saying anything is wrong - it is
just hard to follow certain paths without a minimum of high-level technical
details about the implementation (i.e. design specs) and mostly because of
the amount of changes - there are classes without headers, which makes it
difficult to understand the purpose of a class - sure, one can always spend
a long time trying to understand things but I think for patches with such
amount of changes, some high-level design details would be appreciated by
the reviewers...I know you intended to post details about the system
catalogs but right now one has to review the changes without a lot of
details besides the functional specs...a 1-2 pager high-level implementation
details would not just help reviewers to do a better appreciation of the
changes but also incentivize more reviewers to look at the changes...

Btw, these changes cross a few of the Derby subsystems and am hoping someone
can review the query tree and nodes generation (compiler) stuffs...

Thanks,

--francois

On 12/11/05, Satheesh Bandaram (JIRA) <de...@db.apache.org> wrote:
>
>     [
> http://issues.apache.org/jira/browse/DERBY-464?page=comments#action_12360186]
>
> Satheesh Bandaram commented on DERBY-464:
> -----------------------------------------
>
> 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.
>
>
> > 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.
> >
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> >
> >          Key: DERBY-464
> >          URL: http://issues.apache.org/jira/browse/DERBY-464
> >      Project: Derby
> >         Type: New Feature
> >   Components: SQL
> >     Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0
> >  Environment: generic
> >     Reporter: Satheesh Bandaram
> >     Assignee: Satheesh Bandaram
> >  Attachments: grant.html, grantRevoke.patch.Dec5, grantRevoke.stat.Dec5
> >
> > 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.
>
> --
> This message is automatically generated by JIRA.
> -
> If you think it was sent incorrectly contact one of the administrators:
>    http://issues.apache.org/jira/secure/Administrators.jspa
> -
> For more information on JIRA, see:
>    http://www.atlassian.com/software/jira
>
>

Re: [jira] Updated: (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.

Posted by Francois Orsini <fr...@gmail.com>.
Hi Sateesh,

Great document - thanks for posting it - I was thinking that we could have a
page for this feature on the WIKI site so that we can disuss and comment
about the proposal but am perfectly ok to do it within the JIRA as well.

Cheers,

--francois

[jira] Updated: (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.

Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
     [ http://issues.apache.org/jira/browse/DERBY-464?page=all ]

Satheesh Bandaram updated DERBY-464:
------------------------------------

    Attachment: grant.html

> 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.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>          Key: DERBY-464
>          URL: http://issues.apache.org/jira/browse/DERBY-464
>      Project: Derby
>         Type: New Feature
>   Components: SQL
>     Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0
>  Environment: generic
>     Reporter: Satheesh Bandaram
>     Assignee: Satheesh Bandaram
>  Attachments: grant.html
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Commented: (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.

Posted by "Mamta A. Satoor (JIRA)" <de...@db.apache.org>.
    [ http://issues.apache.org/jira/browse/DERBY-464?page=comments#action_12428758 ] 
            
Mamta A. Satoor commented on DERBY-464:
---------------------------------------

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

> 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.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-464
>                 URL: http://issues.apache.org/jira/browse/DERBY-464
>             Project: Derby
>          Issue Type: New Feature
>          Components: SQL
>    Affects Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0
>         Environment: generic
>            Reporter: Satheesh Bandaram
>         Assigned To: Satheesh Bandaram
>             Fix For: 10.2.0.0
>
>         Attachments: changeDescriptionPartII, grantRevoke.patch.Dec5, grantRevoke.stat.Dec5, GrantRevokePartII.stat, GrantRevokePartII.txt, GrantRevokePartII.txt, grantRevokeSpec.html, grantRevokeSpec_v2.html, grantRevokeSpec_v3.html, grantRevokeSpec_v4.html, grantRevokeSpec_v4_1.html, grantRevokeSpec_v5.html, Privileges.java, Privileges2.java
>
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

Re: [jira] Commented: (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.

Posted by Daniel John Debrunner <dj...@apache.org>.
Satheesh Bandaram (JIRA) wrote:


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

Since g/r is a language issue, it seems like the check() method should
take the LCC and not the transaction controller. Then the authorization
id would be available in the LCC and not have the potential to be
different. Or can it be different, again comments needed here.

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

And to the java code, right?

Dan.



[jira] Commented: (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.

Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
    [ http://issues.apache.org/jira/browse/DERBY-464?page=comments#action_12366700 ] 

Satheesh Bandaram commented on DERBY-464:
-----------------------------------------

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.

> 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.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>          Key: DERBY-464
>          URL: http://issues.apache.org/jira/browse/DERBY-464
>      Project: Derby
>         Type: New Feature
>   Components: SQL
>     Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0
>  Environment: generic
>     Reporter: Satheesh Bandaram
>     Assignee: Satheesh Bandaram
>  Attachments: grantRevoke.patch.Dec5, grantRevoke.stat.Dec5, grantRevokeSpec.html
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Updated: (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.

Posted by "Mamta A. Satoor (JIRA)" <de...@db.apache.org>.
     [ http://issues.apache.org/jira/browse/DERBY-464?page=all ]

Mamta A. Satoor updated DERBY-464:
----------------------------------

    Attachment: grantRevokeSpec_v4_1.html

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

> 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.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-464
>                 URL: http://issues.apache.org/jira/browse/DERBY-464
>             Project: Derby
>          Issue Type: New Feature
>          Components: SQL
>    Affects Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0
>         Environment: generic
>            Reporter: Satheesh Bandaram
>         Assigned To: Satheesh Bandaram
>             Fix For: 10.2.0.0
>
>         Attachments: changeDescriptionPartII, grantRevoke.patch.Dec5, grantRevoke.stat.Dec5, GrantRevokePartII.stat, GrantRevokePartII.txt, GrantRevokePartII.txt, grantRevokeSpec.html, grantRevokeSpec_v2.html, grantRevokeSpec_v3.html, grantRevokeSpec_v4.html, grantRevokeSpec_v4_1.html, Privileges.java, Privileges2.java
>
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

[jira] Commented: (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.

Posted by "Francois Orsini (JIRA)" <de...@db.apache.org>.
    [ http://issues.apache.org/jira/browse/DERBY-464?page=comments#action_12356023 ] 

Francois Orsini commented on DERBY-464:
---------------------------------------

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.

> 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.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>          Key: DERBY-464
>          URL: http://issues.apache.org/jira/browse/DERBY-464
>      Project: Derby
>         Type: New Feature
>   Components: SQL
>     Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0
>  Environment: generic
>     Reporter: Satheesh Bandaram
>     Assignee: Satheesh Bandaram
>  Attachments: grant.html
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


Re: Re: [jira] Commented: (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.

Posted by Yip Ng <yi...@gmail.com>.
Thanks Andrew.

On 7/19/06, Andrew McIntyre <mc...@gmail.com> wrote:
>
> On 7/19/06, Yip Ng <yi...@yahoo.com> wrote:
> > Thanks Dan for the informative links.  I'll definitely
> > take a look at them to start my Derby journey.  =)
> >
> > I opened DERBY-1538 to track the issue I have found
> > but
> > since I don't have developer access to JIRA, I can't
> > link it to DERBY-464...
>
> Hi Yip,
>
> I've added you to the derby-developers JIRA group. You should now be
> able to link issues.
>
> andrew
>

Re: Re: [jira] Commented: (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.

Posted by Andrew McIntyre <mc...@gmail.com>.
On 7/19/06, Yip Ng <yi...@yahoo.com> wrote:
> Thanks Dan for the informative links.  I'll definitely
> take a look at them to start my Derby journey.  =)
>
> I opened DERBY-1538 to track the issue I have found
> but
> since I don't have developer access to JIRA, I can't
> link it to DERBY-464...

Hi Yip,

I've added you to the derby-developers JIRA group. You should now be
able to link issues.

andrew

Re: [jira] Commented: (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.

Posted by Yip Ng <yi...@yahoo.com>.
Thanks Dan for the informative links.  I'll definitely
take a look at them to start my Derby journey.  =)

I opened DERBY-1538 to track the issue I have found
but
since I don't have developer access to JIRA, I can't
link it to DERBY-464...


--- Daniel John Debrunner <dj...@apache.org> wrote:

> Yip Ng (JIRA) wrote:
> 
> > 
> >   My name is Yip Ng and I would like to contribute
> to the Apache Derby project. 
> 
> Welcome to the project Yip. I just tried to make the
> information for new
> devevlopers easier to navigate on the wiki, at
> 
> http://wiki.apache.org/db-derby/DerbyDev
> 
> I'd appreciate any feedback on it.
> 
> [grant/revoke tests & bugs snipped]
> 
> It would be great if you could add these bugs as
> Jira issues separate to
> DERBY-464 and link them to DERBY-464.
> 
> http://db.apache.org/derby/DerbyBugGuidelines.html
> 
> Thanks,
> Dan.
> 
> 


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

Re: [jira] Commented: (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.

Posted by Daniel John Debrunner <dj...@apache.org>.
Yip Ng (JIRA) wrote:

> 
>   My name is Yip Ng and I would like to contribute to the Apache Derby project. 

Welcome to the project Yip. I just tried to make the information for new
devevlopers easier to navigate on the wiki, at

http://wiki.apache.org/db-derby/DerbyDev

I'd appreciate any feedback on it.

[grant/revoke tests & bugs snipped]

It would be great if you could add these bugs as Jira issues separate to
DERBY-464 and link them to DERBY-464.

http://db.apache.org/derby/DerbyBugGuidelines.html

Thanks,
Dan.


[jira] Commented: (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.

Posted by "Yip Ng (JIRA)" <de...@db.apache.org>.
    [ http://issues.apache.org/jira/browse/DERBY-464?page=comments#action_12422067 ] 
            
Yip Ng commented on DERBY-464:
------------------------------

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.
      

> 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.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-464
>                 URL: http://issues.apache.org/jira/browse/DERBY-464
>             Project: Derby
>          Issue Type: New Feature
>          Components: SQL
>    Affects Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0
>         Environment: generic
>            Reporter: Satheesh Bandaram
>         Assigned To: Satheesh Bandaram
>         Attachments: changeDescriptionPartII, grantRevoke.patch.Dec5, grantRevoke.stat.Dec5, GrantRevokePartII.stat, GrantRevokePartII.txt, GrantRevokePartII.txt, grantRevokeSpec.html, grantRevokeSpec_v2.html, Privileges.java, Privileges2.java
>
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

[jira] Updated: (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.

Posted by "Michelle Caisse (JIRA)" <de...@db.apache.org>.
     [ http://issues.apache.org/jira/browse/DERBY-464?page=all ]

Michelle Caisse updated DERBY-464:
----------------------------------

    Attachment: Privileges.java

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

> 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.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>          Key: DERBY-464
>          URL: http://issues.apache.org/jira/browse/DERBY-464
>      Project: Derby
>         Type: New Feature
>   Components: SQL
>     Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0
>  Environment: generic
>     Reporter: Satheesh Bandaram
>     Assignee: Satheesh Bandaram
>  Attachments: GrantRevokePartII.stat, GrantRevokePartII.txt, GrantRevokePartII.txt, Privileges.java, changeDescriptionPartII, grantRevoke.patch.Dec5, grantRevoke.stat.Dec5, grantRevokeSpec.html, grantRevokeSpec_v2.html
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Assigned: (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.

Posted by "Mike Matrigali (JIRA)" <de...@db.apache.org>.
     [ http://issues.apache.org/jira/browse/DERBY-464?page=all ]

Mike Matrigali reassigned DERBY-464:
------------------------------------

    Assignee:     (was: Satheesh Bandaram)

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.


> 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.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-464
>                 URL: http://issues.apache.org/jira/browse/DERBY-464
>             Project: Derby
>          Issue Type: New Feature
>          Components: SQL
>    Affects Versions: 10.0.2.1, 10.1.1.0, 10.2.1.0
>         Environment: generic
>            Reporter: Satheesh Bandaram
>             Fix For: 10.2.1.0
>
>         Attachments: changeDescriptionPartII, grantRevoke.patch.Dec5, grantRevoke.stat.Dec5, GrantRevokePartII.stat, GrantRevokePartII.txt, GrantRevokePartII.txt, grantRevokeSpec.html, grantRevokeSpec_v2.html, grantRevokeSpec_v3.html, grantRevokeSpec_v4.html, grantRevokeSpec_v4_1.html, grantRevokeSpec_v5.html, Privileges.java, Privileges2.java
>
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

[jira] Commented: (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.

Posted by "Francois Orsini (JIRA)" <de...@db.apache.org>.
    [ http://issues.apache.org/jira/browse/DERBY-464?page=comments#action_12316108 ] 

Francois Orsini commented on DERBY-464:
---------------------------------------

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

> 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.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>          Key: DERBY-464
>          URL: http://issues.apache.org/jira/browse/DERBY-464
>      Project: Derby
>         Type: New Feature
>   Components: SQL
>     Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0
>  Environment: generic
>     Reporter: Satheesh Bandaram

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

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Updated: (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.

Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
     [ http://issues.apache.org/jira/browse/DERBY-464?page=all ]

Satheesh Bandaram updated DERBY-464:
------------------------------------

    Attachment: grantRevokeSpec_v3.html

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.
 

> 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.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-464
>                 URL: http://issues.apache.org/jira/browse/DERBY-464
>             Project: Derby
>          Issue Type: New Feature
>          Components: SQL
>    Affects Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0
>         Environment: generic
>            Reporter: Satheesh Bandaram
>         Assigned To: Satheesh Bandaram
>         Attachments: changeDescriptionPartII, grantRevoke.patch.Dec5, grantRevoke.stat.Dec5, GrantRevokePartII.stat, GrantRevokePartII.txt, GrantRevokePartII.txt, grantRevokeSpec.html, grantRevokeSpec_v2.html, grantRevokeSpec_v3.html, Privileges.java, Privileges2.java
>
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

[jira] Updated: (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.

Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
     [ http://issues.apache.org/jira/browse/DERBY-464?page=all ]

Satheesh Bandaram updated DERBY-464:
------------------------------------

    Derby Info:   (was: [Patch Available])

> 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.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>          Key: DERBY-464
>          URL: http://issues.apache.org/jira/browse/DERBY-464
>      Project: Derby
>         Type: New Feature

>   Components: SQL
>     Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0
>  Environment: generic
>     Reporter: Satheesh Bandaram
>     Assignee: Satheesh Bandaram
>  Attachments: GrantRevokePartII.stat, GrantRevokePartII.txt, GrantRevokePartII.txt, Privileges.java, Privileges2.java, changeDescriptionPartII, grantRevoke.patch.Dec5, grantRevoke.stat.Dec5, grantRevokeSpec.html, grantRevokeSpec_v2.html
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Commented: (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.

Posted by "Yip Ng (JIRA)" <de...@db.apache.org>.
    [ http://issues.apache.org/jira/browse/DERBY-464?page=comments#action_12428743 ] 
            
Yip Ng commented on DERBY-464:
------------------------------

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.

> 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.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-464
>                 URL: http://issues.apache.org/jira/browse/DERBY-464
>             Project: Derby
>          Issue Type: New Feature
>          Components: SQL
>    Affects Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0
>         Environment: generic
>            Reporter: Satheesh Bandaram
>         Assigned To: Satheesh Bandaram
>             Fix For: 10.2.0.0
>
>         Attachments: changeDescriptionPartII, grantRevoke.patch.Dec5, grantRevoke.stat.Dec5, GrantRevokePartII.stat, GrantRevokePartII.txt, GrantRevokePartII.txt, grantRevokeSpec.html, grantRevokeSpec_v2.html, grantRevokeSpec_v3.html, grantRevokeSpec_v4.html, grantRevokeSpec_v4_1.html, grantRevokeSpec_v5.html, Privileges.java, Privileges2.java
>
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

[jira] Resolved: (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.

Posted by "Mike Matrigali (JIRA)" <de...@db.apache.org>.
     [ http://issues.apache.org/jira/browse/DERBY-464?page=all ]

Mike Matrigali resolved DERBY-464.
----------------------------------

    Resolution: Fixed

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.

> 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.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-464
>                 URL: http://issues.apache.org/jira/browse/DERBY-464
>             Project: Derby
>          Issue Type: New Feature
>          Components: SQL
>    Affects Versions: 10.0.2.1, 10.1.1.0, 10.2.1.0
>         Environment: generic
>            Reporter: Satheesh Bandaram
>             Fix For: 10.2.1.0
>
>         Attachments: changeDescriptionPartII, grantRevoke.patch.Dec5, grantRevoke.stat.Dec5, GrantRevokePartII.stat, GrantRevokePartII.txt, GrantRevokePartII.txt, grantRevokeSpec.html, grantRevokeSpec_v2.html, grantRevokeSpec_v3.html, grantRevokeSpec_v4.html, grantRevokeSpec_v4_1.html, grantRevokeSpec_v5.html, Privileges.java, Privileges2.java
>
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

[jira] Updated: (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.

Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
     [ http://issues.apache.org/jira/browse/DERBY-464?page=all ]

Satheesh Bandaram updated DERBY-464:
------------------------------------

    Attachment: GrantRevokePartII.txt

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.


> 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.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>          Key: DERBY-464
>          URL: http://issues.apache.org/jira/browse/DERBY-464
>      Project: Derby
>         Type: New Feature
>   Components: SQL
>     Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0
>  Environment: generic
>     Reporter: Satheesh Bandaram
>     Assignee: Satheesh Bandaram
>  Attachments: GrantRevokePartII.txt, grantRevoke.patch.Dec5, grantRevoke.stat.Dec5, grantRevokeSpec.html
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Commented: (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.

Posted by "Yip Ng (JIRA)" <de...@db.apache.org>.
    [ http://issues.apache.org/jira/browse/DERBY-464?page=comments#action_12423574 ] 
            
Yip Ng commented on DERBY-464:
------------------------------

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?

> 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.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-464
>                 URL: http://issues.apache.org/jira/browse/DERBY-464
>             Project: Derby
>          Issue Type: New Feature
>          Components: SQL
>    Affects Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0
>         Environment: generic
>            Reporter: Satheesh Bandaram
>         Assigned To: Satheesh Bandaram
>             Fix For: 10.2.0.0
>
>         Attachments: changeDescriptionPartII, grantRevoke.patch.Dec5, grantRevoke.stat.Dec5, GrantRevokePartII.stat, GrantRevokePartII.txt, GrantRevokePartII.txt, grantRevokeSpec.html, grantRevokeSpec_v2.html, grantRevokeSpec_v3.html, grantRevokeSpec_v4.html, grantRevokeSpec_v4_1.html, Privileges.java, Privileges2.java
>
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

[jira] Commented: (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.

Posted by "Daniel John Debrunner (JIRA)" <de...@db.apache.org>.
    [ http://issues.apache.org/jira/browse/DERBY-464?page=comments#action_12367075 ] 

Daniel John Debrunner commented on DERBY-464:
---------------------------------------------

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.

> 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.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>          Key: DERBY-464
>          URL: http://issues.apache.org/jira/browse/DERBY-464
>      Project: Derby
>         Type: New Feature
>   Components: SQL
>     Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0
>  Environment: generic
>     Reporter: Satheesh Bandaram
>     Assignee: Satheesh Bandaram
>  Attachments: GrantRevokePartII.txt, grantRevoke.patch.Dec5, grantRevoke.stat.Dec5, grantRevokeSpec.html
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Updated: (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.

Posted by "Daniel John Debrunner (JIRA)" <de...@db.apache.org>.
     [ http://issues.apache.org/jira/browse/DERBY-464?page=all ]

Daniel John Debrunner updated DERBY-464:
----------------------------------------

    Derby Info:   (was: [Patch Available])

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

> 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.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-464
>                 URL: http://issues.apache.org/jira/browse/DERBY-464
>             Project: Derby
>          Issue Type: New Feature
>          Components: SQL
>    Affects Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0
>         Environment: generic
>            Reporter: Satheesh Bandaram
>         Assigned To: Satheesh Bandaram
>             Fix For: 10.2.0.0
>
>         Attachments: changeDescriptionPartII, grantRevoke.patch.Dec5, grantRevoke.stat.Dec5, GrantRevokePartII.stat, GrantRevokePartII.txt, GrantRevokePartII.txt, grantRevokeSpec.html, grantRevokeSpec_v2.html, grantRevokeSpec_v3.html, grantRevokeSpec_v4.html, grantRevokeSpec_v4_1.html, Privileges.java, Privileges2.java
>
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

[jira] Updated: (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.

Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
     [ http://issues.apache.org/jira/browse/DERBY-464?page=all ]

Satheesh Bandaram updated DERBY-464:
------------------------------------

    Attachment:     (was: grant.html)

> 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.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>          Key: DERBY-464
>          URL: http://issues.apache.org/jira/browse/DERBY-464
>      Project: Derby
>         Type: New Feature
>   Components: SQL
>     Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0
>  Environment: generic
>     Reporter: Satheesh Bandaram
>     Assignee: Satheesh Bandaram
>  Attachments: grantRevoke.patch.Dec5, grantRevoke.stat.Dec5, grantRevokeDec23.html
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Updated: (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.

Posted by "Andrew McIntyre (JIRA)" <de...@db.apache.org>.
     [ http://issues.apache.org/jira/browse/DERBY-464?page=all ]

Andrew McIntyre updated DERBY-464:
----------------------------------

    Other Info:   (was: [Patch available])
    Derby Info: [Patch Available]

> 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.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>          Key: DERBY-464
>          URL: http://issues.apache.org/jira/browse/DERBY-464
>      Project: Derby
>         Type: New Feature
>   Components: SQL
>     Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0
>  Environment: generic
>     Reporter: Satheesh Bandaram
>     Assignee: Satheesh Bandaram
>  Attachments: GrantRevokePartII.stat, GrantRevokePartII.txt, GrantRevokePartII.txt, Privileges.java, Privileges2.java, changeDescriptionPartII, grantRevoke.patch.Dec5, grantRevoke.stat.Dec5, grantRevokeSpec.html, grantRevokeSpec_v2.html
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Updated: (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.

Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
     [ http://issues.apache.org/jira/browse/DERBY-464?page=all ]

Satheesh Bandaram updated DERBY-464:
------------------------------------

    Attachment:     (was: grantRevokeDec23.html)

> 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.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>          Key: DERBY-464
>          URL: http://issues.apache.org/jira/browse/DERBY-464
>      Project: Derby
>         Type: New Feature
>   Components: SQL
>     Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0
>  Environment: generic
>     Reporter: Satheesh Bandaram
>     Assignee: Satheesh Bandaram
>  Attachments: grantRevoke.patch.Dec5, grantRevoke.stat.Dec5
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Commented: (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.

Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
    [ http://issues.apache.org/jira/browse/DERBY-464?page=comments#action_12316143 ] 

Satheesh Bandaram commented on DERBY-464:
-----------------------------------------

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. 

> 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.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>          Key: DERBY-464
>          URL: http://issues.apache.org/jira/browse/DERBY-464
>      Project: Derby
>         Type: New Feature
>   Components: SQL
>     Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0
>  Environment: generic
>     Reporter: Satheesh Bandaram

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

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Commented: (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.

Posted by "Daniel John Debrunner (JIRA)" <de...@db.apache.org>.
    [ http://issues.apache.org/jira/browse/DERBY-464?page=comments#action_12376518 ] 

Daniel John Debrunner commented on DERBY-464:
---------------------------------------------

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?

> 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.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>          Key: DERBY-464
>          URL: http://issues.apache.org/jira/browse/DERBY-464
>      Project: Derby
>         Type: New Feature

>   Components: SQL
>     Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0
>  Environment: generic
>     Reporter: Satheesh Bandaram
>     Assignee: Satheesh Bandaram
>  Attachments: GrantRevokePartII.stat, GrantRevokePartII.txt, GrantRevokePartII.txt, Privileges.java, Privileges2.java, changeDescriptionPartII, grantRevoke.patch.Dec5, grantRevoke.stat.Dec5, grantRevokeSpec.html, grantRevokeSpec_v2.html
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Updated: (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.

Posted by "Mamta A. Satoor (JIRA)" <de...@db.apache.org>.
     [ http://issues.apache.org/jira/browse/DERBY-464?page=all ]

Mamta A. Satoor updated DERBY-464:
----------------------------------

    Derby Info: [Patch Available]

> 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.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-464
>                 URL: http://issues.apache.org/jira/browse/DERBY-464
>             Project: Derby
>          Issue Type: New Feature
>          Components: SQL
>    Affects Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0
>         Environment: generic
>            Reporter: Satheesh Bandaram
>         Assigned To: Satheesh Bandaram
>             Fix For: 10.2.0.0
>
>         Attachments: changeDescriptionPartII, grantRevoke.patch.Dec5, grantRevoke.stat.Dec5, GrantRevokePartII.stat, GrantRevokePartII.txt, GrantRevokePartII.txt, grantRevokeSpec.html, grantRevokeSpec_v2.html, grantRevokeSpec_v3.html, grantRevokeSpec_v4.html, Privileges.java, Privileges2.java
>
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

[jira] Commented: (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.

Posted by "Daniel John Debrunner (JIRA)" <de...@db.apache.org>.
    [ http://issues.apache.org/jira/browse/DERBY-464?page=comments#action_12367077 ] 

Daniel John Debrunner commented on DERBY-464:
---------------------------------------------

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.

> 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.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>          Key: DERBY-464
>          URL: http://issues.apache.org/jira/browse/DERBY-464
>      Project: Derby
>         Type: New Feature
>   Components: SQL
>     Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0
>  Environment: generic
>     Reporter: Satheesh Bandaram
>     Assignee: Satheesh Bandaram
>  Attachments: GrantRevokePartII.txt, grantRevoke.patch.Dec5, grantRevoke.stat.Dec5, grantRevokeSpec.html
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Commented: (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.

Posted by "Yip Ng (JIRA)" <de...@db.apache.org>.
    [ http://issues.apache.org/jira/browse/DERBY-464?page=comments#action_12428760 ] 
            
Yip Ng commented on DERBY-464:
------------------------------

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

> 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.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-464
>                 URL: http://issues.apache.org/jira/browse/DERBY-464
>             Project: Derby
>          Issue Type: New Feature
>          Components: SQL
>    Affects Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0
>         Environment: generic
>            Reporter: Satheesh Bandaram
>         Assigned To: Satheesh Bandaram
>             Fix For: 10.2.0.0
>
>         Attachments: changeDescriptionPartII, grantRevoke.patch.Dec5, grantRevoke.stat.Dec5, GrantRevokePartII.stat, GrantRevokePartII.txt, GrantRevokePartII.txt, grantRevokeSpec.html, grantRevokeSpec_v2.html, grantRevokeSpec_v3.html, grantRevokeSpec_v4.html, grantRevokeSpec_v4_1.html, grantRevokeSpec_v5.html, Privileges.java, Privileges2.java
>
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

[jira] Commented: (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.

Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
    [ http://issues.apache.org/jira/browse/DERBY-464?page=comments#action_12370427 ] 

Satheesh Bandaram commented on DERBY-464:
-----------------------------------------

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

> 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.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>          Key: DERBY-464
>          URL: http://issues.apache.org/jira/browse/DERBY-464
>      Project: Derby
>         Type: New Feature
>   Components: SQL
>     Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0
>  Environment: generic
>     Reporter: Satheesh Bandaram
>     Assignee: Satheesh Bandaram
>  Attachments: GrantRevokePartII.stat, GrantRevokePartII.txt, GrantRevokePartII.txt, changeDescriptionPartII, grantRevoke.patch.Dec5, grantRevoke.stat.Dec5, grantRevokeSpec.html, grantRevokeSpec_v2.html
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Commented: (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.

Posted by "Rajesh Kartha (JIRA)" <de...@db.apache.org>.
    [ http://issues.apache.org/jira/browse/DERBY-464?page=comments#action_12423418 ] 
            
Rajesh Kartha commented on DERBY-464:
-------------------------------------

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. 

> 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.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-464
>                 URL: http://issues.apache.org/jira/browse/DERBY-464
>             Project: Derby
>          Issue Type: New Feature
>          Components: SQL
>    Affects Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0
>         Environment: generic
>            Reporter: Satheesh Bandaram
>         Assigned To: Satheesh Bandaram
>             Fix For: 10.2.0.0
>
>         Attachments: changeDescriptionPartII, grantRevoke.patch.Dec5, grantRevoke.stat.Dec5, GrantRevokePartII.stat, GrantRevokePartII.txt, GrantRevokePartII.txt, grantRevokeSpec.html, grantRevokeSpec_v2.html, grantRevokeSpec_v3.html, grantRevokeSpec_v4.html, Privileges.java, Privileges2.java
>
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

[jira] Updated: (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.

Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
     [ http://issues.apache.org/jira/browse/DERBY-464?page=all ]

Satheesh Bandaram updated DERBY-464:
------------------------------------

    Attachment: changeDescriptionPartII
                GrantRevokePartII.stat
                GrantRevokePartII.txt

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.

> 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.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>          Key: DERBY-464
>          URL: http://issues.apache.org/jira/browse/DERBY-464
>      Project: Derby
>         Type: New Feature
>   Components: SQL
>     Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0
>  Environment: generic
>     Reporter: Satheesh Bandaram
>     Assignee: Satheesh Bandaram
>  Attachments: GrantRevokePartII.stat, GrantRevokePartII.txt, GrantRevokePartII.txt, changeDescriptionPartII, grantRevoke.patch.Dec5, grantRevoke.stat.Dec5, grantRevokeSpec.html
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Updated: (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.

Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
     [ http://issues.apache.org/jira/browse/DERBY-464?page=all ]

Satheesh Bandaram updated DERBY-464:
------------------------------------

    Attachment: grantRevokeSpec_v2.html

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.
 

> 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.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>          Key: DERBY-464
>          URL: http://issues.apache.org/jira/browse/DERBY-464
>      Project: Derby
>         Type: New Feature
>   Components: SQL
>     Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0
>  Environment: generic
>     Reporter: Satheesh Bandaram
>     Assignee: Satheesh Bandaram
>  Attachments: GrantRevokePartII.stat, GrantRevokePartII.txt, GrantRevokePartII.txt, changeDescriptionPartII, grantRevoke.patch.Dec5, grantRevoke.stat.Dec5, grantRevokeSpec.html, grantRevokeSpec_v2.html
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Updated: (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.

Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
     [ http://issues.apache.org/jira/browse/DERBY-464?page=all ]

Satheesh Bandaram updated DERBY-464:
------------------------------------

    Fix Version/s: 10.2.0.0

> 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.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-464
>                 URL: http://issues.apache.org/jira/browse/DERBY-464
>             Project: Derby
>          Issue Type: New Feature
>          Components: SQL
>    Affects Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0
>         Environment: generic
>            Reporter: Satheesh Bandaram
>         Assigned To: Satheesh Bandaram
>             Fix For: 10.2.0.0
>
>         Attachments: changeDescriptionPartII, grantRevoke.patch.Dec5, grantRevoke.stat.Dec5, GrantRevokePartII.stat, GrantRevokePartII.txt, GrantRevokePartII.txt, grantRevokeSpec.html, grantRevokeSpec_v2.html, grantRevokeSpec_v3.html, Privileges.java, Privileges2.java
>
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

[jira] Updated: (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.

Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
     [ http://issues.apache.org/jira/browse/DERBY-464?page=all ]

Satheesh Bandaram updated DERBY-464:
------------------------------------

    Attachment: grantRevoke.stat.Dec5

> 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.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>          Key: DERBY-464
>          URL: http://issues.apache.org/jira/browse/DERBY-464
>      Project: Derby
>         Type: New Feature
>   Components: SQL
>     Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0
>  Environment: generic
>     Reporter: Satheesh Bandaram
>     Assignee: Satheesh Bandaram
>  Attachments: grant.html, grantRevoke.patch.Dec5, grantRevoke.stat.Dec5
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Commented: (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.

Posted by "Daniel John Debrunner (JIRA)" <de...@db.apache.org>.
    [ http://issues.apache.org/jira/browse/DERBY-464?page=comments#action_12367076 ] 

Daniel John Debrunner commented on DERBY-464:
---------------------------------------------

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?

> 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.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>          Key: DERBY-464
>          URL: http://issues.apache.org/jira/browse/DERBY-464
>      Project: Derby
>         Type: New Feature
>   Components: SQL
>     Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0
>  Environment: generic
>     Reporter: Satheesh Bandaram
>     Assignee: Satheesh Bandaram
>  Attachments: GrantRevokePartII.txt, grantRevoke.patch.Dec5, grantRevoke.stat.Dec5, grantRevokeSpec.html
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Commented: (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.

Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
    [ http://issues.apache.org/jira/browse/DERBY-464?page=comments#action_12423197 ] 
            
Satheesh Bandaram commented on DERBY-464:
-----------------------------------------

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.



> 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.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-464
>                 URL: http://issues.apache.org/jira/browse/DERBY-464
>             Project: Derby
>          Issue Type: New Feature
>          Components: SQL
>    Affects Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0
>         Environment: generic
>            Reporter: Satheesh Bandaram
>         Assigned To: Satheesh Bandaram
>             Fix For: 10.2.0.0
>
>         Attachments: changeDescriptionPartII, grantRevoke.patch.Dec5, grantRevoke.stat.Dec5, GrantRevokePartII.stat, GrantRevokePartII.txt, GrantRevokePartII.txt, grantRevokeSpec.html, grantRevokeSpec_v2.html, grantRevokeSpec_v3.html, Privileges.java, Privileges2.java
>
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

[jira] Commented: (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.

Posted by "Rick Hillegas (JIRA)" <de...@db.apache.org>.
    [ http://issues.apache.org/jira/browse/DERBY-464?page=comments#action_12356020 ] 

Rick Hillegas commented on DERBY-464:
-------------------------------------

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.

> 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.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>          Key: DERBY-464
>          URL: http://issues.apache.org/jira/browse/DERBY-464
>      Project: Derby
>         Type: New Feature
>   Components: SQL
>     Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0
>  Environment: generic
>     Reporter: Satheesh Bandaram
>     Assignee: Satheesh Bandaram
>  Attachments: grant.html
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Assigned: (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.

Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
     [ http://issues.apache.org/jira/browse/DERBY-464?page=all ]

Satheesh Bandaram reassigned DERBY-464:
---------------------------------------

    Assign To: Satheesh Bandaram

> 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.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>          Key: DERBY-464
>          URL: http://issues.apache.org/jira/browse/DERBY-464
>      Project: Derby
>         Type: New Feature
>   Components: SQL
>     Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0
>  Environment: generic
>     Reporter: Satheesh Bandaram
>     Assignee: Satheesh Bandaram

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

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Commented: (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.

Posted by "Yip Ng (JIRA)" <de...@db.apache.org>.
    [ http://issues.apache.org/jira/browse/DERBY-464?page=comments#action_12422235 ] 
            
Yip Ng commented on DERBY-464:
------------------------------

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

> 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.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-464
>                 URL: http://issues.apache.org/jira/browse/DERBY-464
>             Project: Derby
>          Issue Type: New Feature
>          Components: SQL
>    Affects Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0
>         Environment: generic
>            Reporter: Satheesh Bandaram
>         Assigned To: Satheesh Bandaram
>         Attachments: changeDescriptionPartII, grantRevoke.patch.Dec5, grantRevoke.stat.Dec5, GrantRevokePartII.stat, GrantRevokePartII.txt, GrantRevokePartII.txt, grantRevokeSpec.html, grantRevokeSpec_v2.html, Privileges.java, Privileges2.java
>
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

[jira] Commented: (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.

Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
    [ http://issues.apache.org/jira/browse/DERBY-464?page=comments#action_12356027 ] 

Satheesh Bandaram commented on DERBY-464:
-----------------------------------------

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



> 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.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>          Key: DERBY-464
>          URL: http://issues.apache.org/jira/browse/DERBY-464
>      Project: Derby
>         Type: New Feature
>   Components: SQL
>     Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0
>  Environment: generic
>     Reporter: Satheesh Bandaram
>     Assignee: Satheesh Bandaram
>  Attachments: grant.html
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Commented: (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.

Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
    [ http://issues.apache.org/jira/browse/DERBY-464?page=comments#action_12367255 ] 

Satheesh Bandaram commented on DERBY-464:
-----------------------------------------

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.

> 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.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>          Key: DERBY-464
>          URL: http://issues.apache.org/jira/browse/DERBY-464
>      Project: Derby
>         Type: New Feature
>   Components: SQL
>     Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0
>  Environment: generic
>     Reporter: Satheesh Bandaram
>     Assignee: Satheesh Bandaram
>  Attachments: GrantRevokePartII.txt, grantRevoke.patch.Dec5, grantRevoke.stat.Dec5, grantRevokeSpec.html
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Updated: (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.

Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
     [ http://issues.apache.org/jira/browse/DERBY-464?page=all ]

Satheesh Bandaram updated DERBY-464:
------------------------------------

    Attachment: grantRevokeSpec_v5.html

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.


> 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.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-464
>                 URL: http://issues.apache.org/jira/browse/DERBY-464
>             Project: Derby
>          Issue Type: New Feature
>          Components: SQL
>    Affects Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0
>         Environment: generic
>            Reporter: Satheesh Bandaram
>         Assigned To: Satheesh Bandaram
>             Fix For: 10.2.0.0
>
>         Attachments: changeDescriptionPartII, grantRevoke.patch.Dec5, grantRevoke.stat.Dec5, GrantRevokePartII.stat, GrantRevokePartII.txt, GrantRevokePartII.txt, grantRevokeSpec.html, grantRevokeSpec_v2.html, grantRevokeSpec_v3.html, grantRevokeSpec_v4.html, grantRevokeSpec_v4_1.html, grantRevokeSpec_v5.html, Privileges.java, Privileges2.java
>
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

[jira] Commented: (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.

Posted by "Daniel John Debrunner (JIRA)" <de...@db.apache.org>.
    [ http://issues.apache.org/jira/browse/DERBY-464?page=comments#action_12370303 ] 

Daniel John Debrunner commented on DERBY-464:
---------------------------------------------

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!




> 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.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>          Key: DERBY-464
>          URL: http://issues.apache.org/jira/browse/DERBY-464
>      Project: Derby
>         Type: New Feature
>   Components: SQL
>     Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0
>  Environment: generic
>     Reporter: Satheesh Bandaram
>     Assignee: Satheesh Bandaram
>  Attachments: GrantRevokePartII.stat, GrantRevokePartII.txt, GrantRevokePartII.txt, changeDescriptionPartII, grantRevoke.patch.Dec5, grantRevoke.stat.Dec5, grantRevokeSpec.html, grantRevokeSpec_v2.html
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


Re: [jira] Commented: (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.

Posted by "David W. Van Couvering" <Da...@Sun.COM>.
Thanks, Sateesh.  Sorry, I missed the request for review comments.  No 
need; I personally am not able at this time to review your changes.  I 
just thought this was the first time we saw these changes.  My mistake.

David

Satheesh Bandaram wrote:
> Hi David,
> 
> I did post the patch for review... I followed up the post after few days
> to invite anyone to review again. That time I said I could wait for
> review comments or submit then address comments. I also said I would
> submit the patch over the next weekend. Since no one replied that either
> they want more time or going to review before submission, I submitted
> the change.
> 
> http://article.gmane.org/gmane.comp.apache.db.derby.devel/11113
> http://article.gmane.org/gmane.comp.apache.db.derby.devel/11012
> 
> I am willing to address any review comments anyone has. Should I have
> waited for more time? I wanted to submit the changes over the weekend to
> minimize any disruptions...
> 
> Satheesh
> 
> David W. Van Couvering wrote:
> 
> 
>>Hi, Satheesh.  I am still learning the Apache Way, so I wanted to get
>>some clarity on how things are generally done.
>>
>>I know that committers have the merit and trust to commit what they
>>want.  I had generally assumed, however, that a large change like this
>>should be posted as a patch for review before being committed.
>>
>>Is the approach that we do an svn diff of the change revision and we
>>can send you comments, and you can make changes in response?
>>
>>Thanks,
>>
>>David
>>
>>Satheesh Bandaram (JIRA) wrote:
>>
>>
>>>    [
>>>http://issues.apache.org/jira/browse/DERBY-464?page=comments#action_12360186
>>>]
>>>Satheesh Bandaram commented on DERBY-464:
>>>-----------------------------------------
>>>
>>>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.
>>>
>>>
>>>
>>>
>>>>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.
>>>>-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>>>>
>>>>
>>>>        Key: DERBY-464
>>>>        URL: http://issues.apache.org/jira/browse/DERBY-464
>>>>    Project: Derby
>>>>       Type: New Feature
>>>> Components: SQL
>>>>   Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0
>>>>Environment: generic
>>>>   Reporter: Satheesh Bandaram
>>>>   Assignee: Satheesh Bandaram
>>>>Attachments: grant.html, grantRevoke.patch.Dec5, grantRevoke.stat.Dec5
>>>>
>>>>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.
>>>
>>>
>>>
> 

Re: [jira] Commented: (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.

Posted by Satheesh Bandaram <sa...@Sourcery.Org>.
Hi David,

I did post the patch for review... I followed up the post after few days
to invite anyone to review again. That time I said I could wait for
review comments or submit then address comments. I also said I would
submit the patch over the next weekend. Since no one replied that either
they want more time or going to review before submission, I submitted
the change.

http://article.gmane.org/gmane.comp.apache.db.derby.devel/11113
http://article.gmane.org/gmane.comp.apache.db.derby.devel/11012

I am willing to address any review comments anyone has. Should I have
waited for more time? I wanted to submit the changes over the weekend to
minimize any disruptions...

Satheesh

David W. Van Couvering wrote:

> Hi, Satheesh.  I am still learning the Apache Way, so I wanted to get
> some clarity on how things are generally done.
>
> I know that committers have the merit and trust to commit what they
> want.  I had generally assumed, however, that a large change like this
> should be posted as a patch for review before being committed.
>
> Is the approach that we do an svn diff of the change revision and we
> can send you comments, and you can make changes in response?
>
> Thanks,
>
> David
>
> Satheesh Bandaram (JIRA) wrote:
>
>>     [
>> http://issues.apache.org/jira/browse/DERBY-464?page=comments#action_12360186
>> ]
>> Satheesh Bandaram commented on DERBY-464:
>> -----------------------------------------
>>
>> 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.
>>
>>
>>
>>> 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.
>>> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>>>
>>>
>>>         Key: DERBY-464
>>>         URL: http://issues.apache.org/jira/browse/DERBY-464
>>>     Project: Derby
>>>        Type: New Feature
>>>  Components: SQL
>>>    Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0
>>> Environment: generic
>>>    Reporter: Satheesh Bandaram
>>>    Assignee: Satheesh Bandaram
>>> Attachments: grant.html, grantRevoke.patch.Dec5, grantRevoke.stat.Dec5
>>>
>>> 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.
>>
>>
>>


Re: [jira] Commented: (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.

Posted by "David W. Van Couvering" <Da...@Sun.COM>.
Hi, Satheesh.  I am still learning the Apache Way, so I wanted to get 
some clarity on how things are generally done.

I know that committers have the merit and trust to commit what they 
want.  I had generally assumed, however, that a large change like this 
should be posted as a patch for review before being committed.

Is the approach that we do an svn diff of the change revision and we can 
send you comments, and you can make changes in response?

Thanks,

David

Satheesh Bandaram (JIRA) wrote:
>     [ http://issues.apache.org/jira/browse/DERBY-464?page=comments#action_12360186 ] 
> 
> Satheesh Bandaram commented on DERBY-464:
> -----------------------------------------
> 
> 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.
> 
> 
> 
>>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.
>>-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>>
>>         Key: DERBY-464
>>         URL: http://issues.apache.org/jira/browse/DERBY-464
>>     Project: Derby
>>        Type: New Feature
>>  Components: SQL
>>    Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0
>> Environment: generic
>>    Reporter: Satheesh Bandaram
>>    Assignee: Satheesh Bandaram
>> Attachments: grant.html, grantRevoke.patch.Dec5, grantRevoke.stat.Dec5
>>
>>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.
> 
> 

[jira] Commented: (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.

Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
    [ http://issues.apache.org/jira/browse/DERBY-464?page=comments#action_12360186 ] 

Satheesh Bandaram commented on DERBY-464:
-----------------------------------------

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.


> 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.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>          Key: DERBY-464
>          URL: http://issues.apache.org/jira/browse/DERBY-464
>      Project: Derby
>         Type: New Feature
>   Components: SQL
>     Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0
>  Environment: generic
>     Reporter: Satheesh Bandaram
>     Assignee: Satheesh Bandaram
>  Attachments: grant.html, grantRevoke.patch.Dec5, grantRevoke.stat.Dec5
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Commented: (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.

Posted by "Yip Ng (JIRA)" <de...@db.apache.org>.
    [ http://issues.apache.org/jira/browse/DERBY-464?page=comments#action_12428349 ] 
            
Yip Ng commented on DERBY-464:
------------------------------

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

> 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.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-464
>                 URL: http://issues.apache.org/jira/browse/DERBY-464
>             Project: Derby
>          Issue Type: New Feature
>          Components: SQL
>    Affects Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0
>         Environment: generic
>            Reporter: Satheesh Bandaram
>         Assigned To: Satheesh Bandaram
>             Fix For: 10.2.0.0
>
>         Attachments: changeDescriptionPartII, grantRevoke.patch.Dec5, grantRevoke.stat.Dec5, GrantRevokePartII.stat, GrantRevokePartII.txt, GrantRevokePartII.txt, grantRevokeSpec.html, grantRevokeSpec_v2.html, grantRevokeSpec_v3.html, grantRevokeSpec_v4.html, grantRevokeSpec_v4_1.html, grantRevokeSpec_v5.html, Privileges.java, Privileges2.java
>
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

[jira] Updated: (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.

Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
     [ http://issues.apache.org/jira/browse/DERBY-464?page=all ]

Satheesh Bandaram updated DERBY-464:
------------------------------------

    Attachment: grantRevokeDec23.html

> 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.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>          Key: DERBY-464
>          URL: http://issues.apache.org/jira/browse/DERBY-464
>      Project: Derby
>         Type: New Feature
>   Components: SQL
>     Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0
>  Environment: generic
>     Reporter: Satheesh Bandaram
>     Assignee: Satheesh Bandaram
>  Attachments: grant.html, grantRevoke.patch.Dec5, grantRevoke.stat.Dec5, grantRevokeDec23.html
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


Re: [jira] Updated: (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.

Posted by Daniel John Debrunner <dj...@apache.org>.
Satheesh Bandaram (JIRA) wrote:

>      [ http://issues.apache.org/jira/browse/DERBY-464?page=all ]
> 
> Satheesh Bandaram updated DERBY-464:
> ------------------------------------
> 
>     Other Info: [Patch available]
> 
> I would like to submit this patch over the weekend. Appreciate any comments before.

Seems like a big patch, any pointers on where to start, what exactly
it's addressing in more detail?

Dan.



[jira] Updated: (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.

Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
     [ http://issues.apache.org/jira/browse/DERBY-464?page=all ]

Satheesh Bandaram updated DERBY-464:
------------------------------------

    Other Info: [Patch available]

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

> 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.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>          Key: DERBY-464
>          URL: http://issues.apache.org/jira/browse/DERBY-464
>      Project: Derby
>         Type: New Feature
>   Components: SQL
>     Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0
>  Environment: generic
>     Reporter: Satheesh Bandaram
>     Assignee: Satheesh Bandaram
>  Attachments: GrantRevokePartII.txt, grantRevoke.patch.Dec5, grantRevoke.stat.Dec5, grantRevokeSpec.html
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Updated: (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.

Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
     [ http://issues.apache.org/jira/browse/DERBY-464?page=all ]

Satheesh Bandaram updated DERBY-464:
------------------------------------

    Attachment: grantRevoke.patch.Dec5

> 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.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>          Key: DERBY-464
>          URL: http://issues.apache.org/jira/browse/DERBY-464
>      Project: Derby
>         Type: New Feature
>   Components: SQL
>     Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0
>  Environment: generic
>     Reporter: Satheesh Bandaram
>     Assignee: Satheesh Bandaram
>  Attachments: grant.html, grantRevoke.patch.Dec5, grantRevoke.stat.Dec5
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Updated: (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.

Posted by "Mamta A. Satoor (JIRA)" <de...@db.apache.org>.
     [ http://issues.apache.org/jira/browse/DERBY-464?page=all ]

Mamta A. Satoor updated DERBY-464:
----------------------------------


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

> 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.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-464
>                 URL: http://issues.apache.org/jira/browse/DERBY-464
>             Project: Derby
>          Issue Type: New Feature
>          Components: SQL
>    Affects Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0
>         Environment: generic
>            Reporter: Satheesh Bandaram
>         Assigned To: Satheesh Bandaram
>             Fix For: 10.2.0.0
>
>         Attachments: changeDescriptionPartII, grantRevoke.patch.Dec5, grantRevoke.stat.Dec5, GrantRevokePartII.stat, GrantRevokePartII.txt, GrantRevokePartII.txt, grantRevokeSpec.html, grantRevokeSpec_v2.html, grantRevokeSpec_v3.html, grantRevokeSpec_v4.html, grantRevokeSpec_v4_1.html, Privileges.java, Privileges2.java
>
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

[jira] Updated: (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.

Posted by "Michelle Caisse (JIRA)" <de...@db.apache.org>.
     [ http://issues.apache.org/jira/browse/DERBY-464?page=all ]

Michelle Caisse updated DERBY-464:
----------------------------------

    Attachment: Privileges2.java

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.

> 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.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>          Key: DERBY-464
>          URL: http://issues.apache.org/jira/browse/DERBY-464
>      Project: Derby
>         Type: New Feature
>   Components: SQL
>     Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0
>  Environment: generic
>     Reporter: Satheesh Bandaram
>     Assignee: Satheesh Bandaram
>  Attachments: GrantRevokePartII.stat, GrantRevokePartII.txt, GrantRevokePartII.txt, Privileges.java, Privileges2.java, changeDescriptionPartII, grantRevoke.patch.Dec5, grantRevoke.stat.Dec5, grantRevokeSpec.html, grantRevokeSpec_v2.html
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Updated: (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.

Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
     [ http://issues.apache.org/jira/browse/DERBY-464?page=all ]

Satheesh Bandaram updated DERBY-464:
------------------------------------

    Attachment: grantRevokeSpec.html

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. 

> 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.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>          Key: DERBY-464
>          URL: http://issues.apache.org/jira/browse/DERBY-464
>      Project: Derby
>         Type: New Feature
>   Components: SQL
>     Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0
>  Environment: generic
>     Reporter: Satheesh Bandaram
>     Assignee: Satheesh Bandaram
>  Attachments: grantRevoke.patch.Dec5, grantRevoke.stat.Dec5, grantRevokeSpec.html
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


Re: [jira] Commented: (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.

Posted by Daniel John Debrunner <dj...@debrunners.com>.
Francois Orsini (JIRA) wrote:

>     [ http://issues.apache.org/jira/browse/DERBY-464?page=comments#action_12356032 ] 
> 
> Francois Orsini commented on DERBY-464:
> ---------------------------------------
> 
> 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:

Probably good to move this to a different topic, rather than clutter up
the grant/revoke issue.

Dan.


Re: [jira] Commented: (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.

Posted by Francois Orsini <fr...@gmail.com>.
Derby's Built-in authentication can be used in a client-server mode today
along with DRDA's secure transport mechanisms of users' credential across
the wire. Originally, we had no cloudscape network driver besides 3rd party
ones which were NOT providing secure transport of user credentials across
the wire besides using SSL (which encrypts everything not just credentials -
overkill if you don't need it) - I don't see why we could not improve the
way user & passwords are stored at the system level to be as secure as the
ones defined at the database level. This is internal afterall - then, I
would like to see a unified way of defining users at the database and system
level when using built-in authentication - a simple 'CREATE USER' command
for instance (and am just taking a quick example) would do it and improve
useability at the same time - implementation details (storage) are internal
as it is done at the database level...I believe that Derby will be used more
and more in a client-server mode, along with built-in authentication way of
storing credentials (SHA-1 single-hashed) which is fairly secure already (at
the database level) - yes a few bits could be improved but the storage of
credentials is actually pretty good at the database level.

It does not make sense anymore to have to have 2 ways to define users for a
particular authentication scheme which is the built-in database system one.

This is exactly in-line as far as why we would implement grant/revoke now
since we didn't think we needed it when we implemented the other and simpler
scheme via properties back then as we were thinking embedded (which lead us
to piggy-back on using java 'properties' to define ACLs as we did for users)

Authorization is different than authentication, hence I agree with the
second part of your statement.

I will post a proposal once am done and the community can comment on it.

--francois

On 10/27/05, Satheesh Bandaram <sa...@sourcery.org> wrote:
>
> Why exactly would we want to strengthen builtin-authentication scheme when
> all of us agreed it was for simple embedded application use? I am not sure
> how useful access control is for embedded usages.
>
> But I will hold off on any more questions and wait for your proposal.
>
> Satheesh
>
> Francois Orsini wrote:
>
> I'm all for having a homogeneous and unified way to manage (create, drop,
> alter, etc) users in Derby and specifically for the built-in authentication
> scheme which is what I was referring to. Today we simply don't have that.
>
> More to follow as am starting to feel itchy ;)
>
> --francois
>
> On 10/26/05, Daniel John Debrunner <dj...@debrunners.com> wrote:
> >
> > Francois Orsini wrote:
> >
> > > Agreed since we always made it clear that users could be defined at
> > the
> > > system and/or database level ;)
> > >
> > > However, even as of today, databases can be dependent on users defined
> >
> > > at the system level if you have 'derby.database.propertiesOnly' set to
> > > false which is the default I believe ;)
> > >
> > > What I meant to say is: (and this was in the context of Grant&Revoke
> > > access to database(s) when users are defined at the system level in my
> >
> > > case which I think we'll be the most popular choice - 80/20 rule)
> >
> > Yep, flexibility is good. As long as we continue to support
> > self-contained databases. A system database would be a significant new
> > feature.
> >
> > Of course, I'm unclear on exactly what you are proposing, is it a new
> > authentication scheme or something else? I eagerly await the functional
> > spec/proposal. :-)
> >
> > Dan.
> >
> >
> >
>

Re: [jira] Commented: (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.

Posted by Francois Orsini <fr...@gmail.com>.
I never mentioned "system database" per-se - I specifically mentioned
"system Level" which is different

Yes I will scratch my own itch - at least I will try.... ;)

Sysusers just to name one entity does not have to reside in a system
database - hence why I had mentioned VTI before.

I'm all for having a homogeneous and unified way to manage (create, drop,
alter, etc) users in Derby and specifically for the built-in authentication
scheme which is what I was referring to. Today we simply don't have that.

More to follow as am starting to feel itchy ;)

--francois

On 10/26/05, Daniel John Debrunner <dj...@debrunners.com> wrote:
>
> Francois Orsini wrote:
>
> > Agreed since we always made it clear that users could be defined at the
> > system and/or database level ;)
> >
> > However, even as of today, databases can be dependent on users defined
> > at the system level if you have 'derby.database.propertiesOnly' set to
> > false which is the default I believe ;)
> >
> > What I meant to say is: (and this was in the context of Grant&Revoke
> > access to database(s) when users are defined at the system level in my
> > case which I think we'll be the most popular choice - 80/20 rule)
>
> Yep, flexibility is good. As long as we continue to support
> self-contained databases. A system database would be a significant new
> feature.
>
> Of course, I'm unclear on exactly what you are proposing, is it a new
> authentication scheme or something else? I eagerly await the functional
> spec/proposal. :-)
>
> Dan.
>
>
>

Re: [jira] Commented: (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.

Posted by Daniel John Debrunner <dj...@debrunners.com>.
Francois Orsini wrote:

> Agreed since we always made it clear that users could be defined at the
> system and/or database level ;)
> 
> However, even as of today, databases can be dependent on users defined
> at the system level if you have 'derby.database.propertiesOnly' set to
> false which is the default I believe ;)
> 
> What I meant to say is: (and this was in the context of Grant&Revoke
> access to database(s) when users are defined at the system level in my
> case which I think we'll be the most popular choice - 80/20 rule)

Yep, flexibility is good. As long as we continue to support
self-contained databases. A system database would be a significant new
feature.

Of course, I'm unclear on exactly what you are proposing, is it a new
authentication scheme or something else? I eagerly await the functional
spec/proposal. :-)

Dan.



Re: [jira] Commented: (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.

Posted by Francois Orsini <fr...@gmail.com>.
Agreed since we always made it clear that users could be defined at the
system and/or database level ;)

However, even as of today, databases can be dependent on users defined at
the system level if you have 'derby.database.propertiesOnly' set to false
which is the default I believe ;)

What I meant to say is: (and this was in the context of Grant&Revoke access
to database(s) when users are defined at the system level in my case which I
think we'll be the most popular choice - 80/20 rule)

It's ok to have a user being defined as a legitimate user of a database
(sysusers catalog in the database for instance - never meant to remove a
notion like that since we allow something similar today) - but on the other
hands, one should not have to define users at the database level if they
don't want to. Today we can have a user password being encrypted at the
database level but not at the system one - hence the motivation for
improving this at the system level in the case of Derby's built-in
authentication (password-based) - we should not require to have to define
users' credentials in every database they can access because we think a
database should be independent of the system level (one) - it is great of
course but there could be user management useability issues having to manage
a user across several databases if one does not plug into a directory
service (i.e. LDAP)...User definitions and therefore credentials at the
System level are too exposed right now and we know why - user definition at
the database level is more secure than at the system level and I believe
this should be fixed/addressed (am not a big fan of exposed system
properties either and especially when passwords are involved) but this is
the path we had decided as you said...

I'm just guessing and I may be wrong that most DBA's will either want to
define users at the system level _or_ have Derby authenticate against their
directory service (users' credentials managed outside then).

So yes, we could have a Sysusers (virtual or not) table at the database and
system level to define user access for the database or system-wide, as well
as providing a more secure storage for encrypted passwords (and especially
for the users defined at the system level). This would provide a unified way
of storing user credentials for the built-in scheme at least as well as
looking at current defined users (i.e. common sysusers interface)

--francois

On 10/26/05, Daniel John Debrunner <dj...@debrunners.com> wrote:
>
> Francois Orsini (JIRA) wrote:
>
> > [
> http://issues.apache.org/jira/browse/DERBY-464?page=comments#action_12356032]
> >
> > Francois Orsini commented on DERBY-464:
> > ---------------------------------------
> >
> > 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)
>
> That, to my mind would be a bad step. Currently Derby databases are
> independent of any system, they are self contained. Thus they can be
> copied anywhere and continue to work. Adding a dependency on a system
> database just seems wrong.
>
> I've often thought that one mistake made in the early days was to have
> the concept of a system, the single derby.properties, derby.log file, or
> reading system properties.
>
>
> Dan.
>
>
>

Re: [jira] Commented: (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.

Posted by Daniel John Debrunner <dj...@debrunners.com>.
Francois Orsini (JIRA) wrote:

>     [ http://issues.apache.org/jira/browse/DERBY-464?page=comments#action_12356032 ] 
> 
> Francois Orsini commented on DERBY-464:
> ---------------------------------------
> 
> 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)

That, to my mind would be a bad step. Currently Derby databases are
independent of any system, they are self contained. Thus they can be
copied anywhere and continue to work. Adding a dependency on a system
database just seems wrong.

I've often thought that one mistake made in the early days was to have
the concept of a system, the single derby.properties, derby.log file, or
reading system properties.


Dan.



[jira] Commented: (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.

Posted by "Francois Orsini (JIRA)" <de...@db.apache.org>.
    [ http://issues.apache.org/jira/browse/DERBY-464?page=comments#action_12356032 ] 

Francois Orsini commented on DERBY-464:
---------------------------------------

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.

> 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.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>          Key: DERBY-464
>          URL: http://issues.apache.org/jira/browse/DERBY-464
>      Project: Derby
>         Type: New Feature
>   Components: SQL
>     Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0
>  Environment: generic
>     Reporter: Satheesh Bandaram
>     Assignee: Satheesh Bandaram
>  Attachments: grant.html
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Updated: (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.

Posted by "Mamta A. Satoor (JIRA)" <de...@db.apache.org>.
     [ http://issues.apache.org/jira/browse/DERBY-464?page=all ]

Mamta A. Satoor updated DERBY-464:
----------------------------------

    Attachment: grantRevokeSpec_v4.html

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


> 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.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-464
>                 URL: http://issues.apache.org/jira/browse/DERBY-464
>             Project: Derby
>          Issue Type: New Feature
>          Components: SQL
>    Affects Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0
>         Environment: generic
>            Reporter: Satheesh Bandaram
>         Assigned To: Satheesh Bandaram
>             Fix For: 10.2.0.0
>
>         Attachments: changeDescriptionPartII, grantRevoke.patch.Dec5, grantRevoke.stat.Dec5, GrantRevokePartII.stat, GrantRevokePartII.txt, GrantRevokePartII.txt, grantRevokeSpec.html, grantRevokeSpec_v2.html, grantRevokeSpec_v3.html, grantRevokeSpec_v4.html, Privileges.java, Privileges2.java
>
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

Re: [jira] Commented: (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.

Posted by Daniel John Debrunner <dj...@debrunners.com>.
Satheesh Bandaram wrote:
> Some answers below....
> 
> Daniel John Debrunner (JIRA) wrote:
> 
>>    [ http://issues.apache.org/jira/browse/DERBY-464?page=comments#action_12361221 ] 
>>
>>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.
>>  
>>
> One side-effect of directly executing DDLs is that they need to be
> compiled everytime... I think that is what I was refering to.. though I
> don't see many useful cases where sharing a DDL plan would be useful. I
> will modify the line.

I don't think that's the case. DDL statements are cached like any other
statement. The exact same mechanism is used to exeucte constant action
statements as other statements.

Dan.


[jira] Commented: (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.

Posted by "Daniel John Debrunner (JIRA)" <de...@db.apache.org>.
    [ http://issues.apache.org/jira/browse/DERBY-464?page=comments#action_12361221 ] 

Daniel John Debrunner commented on DERBY-464:
---------------------------------------------

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. 



> 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.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>          Key: DERBY-464
>          URL: http://issues.apache.org/jira/browse/DERBY-464
>      Project: Derby
>         Type: New Feature
>   Components: SQL
>     Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0
>  Environment: generic
>     Reporter: Satheesh Bandaram
>     Assignee: Satheesh Bandaram
>  Attachments: grant.html, grantRevoke.patch.Dec5, grantRevoke.stat.Dec5, grantRevokeDec23.html
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Commented: (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.

Posted by "Daniel John Debrunner (JIRA)" <de...@db.apache.org>.
    [ http://issues.apache.org/jira/browse/DERBY-464?page=comments#action_12366670 ] 

Daniel John Debrunner commented on DERBY-464:
---------------------------------------------

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.
 

> 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.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>          Key: DERBY-464
>          URL: http://issues.apache.org/jira/browse/DERBY-464
>      Project: Derby
>         Type: New Feature
>   Components: SQL
>     Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0
>  Environment: generic
>     Reporter: Satheesh Bandaram
>     Assignee: Satheesh Bandaram
>  Attachments: grantRevoke.patch.Dec5, grantRevoke.stat.Dec5, grantRevokeSpec.html
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Commented: (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.

Posted by "Daniel John Debrunner (JIRA)" <de...@db.apache.org>.
    [ http://issues.apache.org/jira/browse/DERBY-464?page=comments#action_12361222 ] 

Daniel John Debrunner commented on DERBY-464:
---------------------------------------------

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.

> 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.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>          Key: DERBY-464
>          URL: http://issues.apache.org/jira/browse/DERBY-464
>      Project: Derby
>         Type: New Feature
>   Components: SQL
>     Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0
>  Environment: generic
>     Reporter: Satheesh Bandaram
>     Assignee: Satheesh Bandaram
>  Attachments: grant.html, grantRevoke.patch.Dec5, grantRevoke.stat.Dec5, grantRevokeDec23.html
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Updated: (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.

Posted by "Rick Hillegas (JIRA)" <de...@db.apache.org>.
     [ http://issues.apache.org/jira/browse/DERBY-464?page=all ]

Rick Hillegas updated DERBY-464:
--------------------------------

    Urgency: Normal

> 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.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-464
>                 URL: http://issues.apache.org/jira/browse/DERBY-464
>             Project: Derby
>          Issue Type: New Feature
>          Components: SQL
>    Affects Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0
>         Environment: generic
>            Reporter: Satheesh Bandaram
>         Assigned To: Satheesh Bandaram
>             Fix For: 10.2.0.0
>
>         Attachments: changeDescriptionPartII, grantRevoke.patch.Dec5, grantRevoke.stat.Dec5, GrantRevokePartII.stat, GrantRevokePartII.txt, GrantRevokePartII.txt, grantRevokeSpec.html, grantRevokeSpec_v2.html, grantRevokeSpec_v3.html, grantRevokeSpec_v4.html, grantRevokeSpec_v4_1.html, Privileges.java, Privileges2.java
>
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

Re: [jira] Commented: (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.

Posted by Daniel John Debrunner <dj...@apache.org>.
Kristian Waagan wrote:

> Daniel John Debrunner (JIRA) wrote:
> 
>>     [
>> http://issues.apache.org/jira/browse/DERBY-464?page=comments#action_12423202
>> ]             Daniel John Debrunner commented on DERBY-464:
>> ---------------------------------------------
>>
>> 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.
> 
> 
> Have I missed something, or can you use the 'Move this sub-task' to do
> that?
> I haven't tried it, but it seems to be possible to change the parent task.

I must have missed that, I know I ran into problems when I wanted to
change the type of a sub-task to be its own issue. I didn't think about
changing the parent to a different (new) parent.

Thanks,
Dan.


Re: [jira] Commented: (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.

Posted by Kristian Waagan <Kr...@Sun.COM>.
Daniel John Debrunner (JIRA) wrote:
>     [ http://issues.apache.org/jira/browse/DERBY-464?page=comments#action_12423202 ] 
>             
> Daniel John Debrunner commented on DERBY-464:
> ---------------------------------------------
> 
> 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.

Have I missed something, or can you use the 'Move this sub-task' to do that?
I haven't tried it, but it seems to be possible to change the parent task.


-- 
Kristian

> 
[ snip ]

[jira] Commented: (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.

Posted by "Daniel John Debrunner (JIRA)" <de...@db.apache.org>.
    [ http://issues.apache.org/jira/browse/DERBY-464?page=comments#action_12423202 ] 
            
Daniel John Debrunner commented on DERBY-464:
---------------------------------------------

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.


> 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.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-464
>                 URL: http://issues.apache.org/jira/browse/DERBY-464
>             Project: Derby
>          Issue Type: New Feature
>          Components: SQL
>    Affects Versions: 10.0.2.1, 10.1.1.0, 10.2.0.0
>         Environment: generic
>            Reporter: Satheesh Bandaram
>         Assigned To: Satheesh Bandaram
>             Fix For: 10.2.0.0
>
>         Attachments: changeDescriptionPartII, grantRevoke.patch.Dec5, grantRevoke.stat.Dec5, GrantRevokePartII.stat, GrantRevokePartII.txt, GrantRevokePartII.txt, grantRevokeSpec.html, grantRevokeSpec_v2.html, grantRevokeSpec_v3.html, Privileges.java, Privileges2.java
>
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira