db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Yip Ng (JIRA)" <derby-...@db.apache.org>
Subject [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.
Date Wed, 19 Jul 2006 09:24:14 GMT
    [ 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

        

Mime
View raw message