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-1686) Grant/Revoke: Attempt to GRANT access to another user on a VIEW, created by the current user with only SELECT privilege on the base table does not fail
Date Fri, 01 Sep 2006 19:59:23 GMT
    [ http://issues.apache.org/jira/browse/DERBY-1686?page=comments#action_12432223 ] 
            
Yip Ng commented on DERBY-1686:
-------------------------------

Dan, let me try to summarize and clarify what this patch is doing.  

First of all, at this point, we clearly understand that from the SQL:2003 spec, for the problem
statement, the 
system needs to raise an error  when an owner of a view attempts to grant the view to others
for which
he does not have all the required privilege(s)  (WITH GRANT OPTIONS) from the underlying objects.
The view owner has only privilege to select from the view but he does not have grantable select
privilege;
therefore, he does not have the rights to grant to others.

With that said, let's move to the patch implementation details.  In order to resolve this
issue, during the
bind phase of GRANT statement (see TablePriviegeNode's bindPrivilegeForView for details),
the logic
is to retrieve the view relevent providers and check against if the current user is the owner
of the schema
for those objects.  By relevent I mean we only check for the following providers:  TableDescriptor,
ViewDescriptor 
and AliasDescriptor (for routine).  If the check fails, we will mark the flag for execution
later to indicate the
object is grantable or not.  We do not fail this at bind time since this statement may be
in the statement
cache for reuse, so we need to make the final check at execution time.  Thus, at execution
time (see 
TablePrivilegeInfo's checkOwnership method for details), we determine here if the object is
grantable iff:

1)  The current user is the database owner.
2)  The current user is not the database owner but is the schema owner of the object to be
granted
3)  For view, we do an additional check to see if the grantable flag that was set in bind
time is true
4)  If the current user is not the owner, the system needs to do another check to see if the
grantor 
      has grantable privilege(s) on that object.    This is not implemented yet since Derby
doesn't support 
      FOR GRANT OPTIONS clause currently.

So in order for the logic above to work, one other thing needs to be resolved which was not
quite clear in 
the design specification - the permission cache.  I stumbled into this problem when I need
to get all the
providers of the dependable (the view in this case) from the dependency manager(DM).  There
are 2
ways to obtain a particular permission descriptor.  Let's take TablePermissionDescriptor for
example.
To retrieve this descriptor, one way is to supply grantee + tableUUID as a key or another
way is to use
the table permission UUID (this one is mainly used by the DM to instantiate the respective
descriptor object)
The problem with the permission cache is, only the grantee and tableUUID is in the equations
of computing
the hashCode and equals method but not the table perms UUID.  

So when the DM attempts to load the provider UUID for the various permission descriptors in
SYSDEPENDS, 
there is a problem of instantiating the permission descriptor since the equals and hashCode
method used
to set the identity of the object uses nothing of perms UUID, this eventually will lead to
a NPE in the permission
caching logic.  To resolve this, the getXXXPermission(UUID) form no longer interacts with
the permission cache.  
But for the other normal case where we know the grantee and the tableUUID from compilation
time, the system will 
still make use of this permission cache.  I think that is about it from the implementation
perspective.

The latest patch for this jira is derby1686-trunk-diff06.txt.  Appreciate any suggestions
and comments.

Note:  I was reviewing the Grant/Revoke code, I noticed that for various access violations,
the system is
           throwing SQLSTATE with class code 28, shouldn't it be class code 42 instead?  e.g.:
 

Class code - 28
invalid authorization specification 28 (no subclass) 000

Class code - 42
syntax error or access rule violation 42 (no subclass) 000



 

> Grant/Revoke: Attempt to GRANT access to another user on a VIEW, created by the current
user with only SELECT privilege on the base table does not fail
> -------------------------------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-1686
>                 URL: http://issues.apache.org/jira/browse/DERBY-1686
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.2.1.0
>         Environment: Any
>            Reporter: Rajesh Kartha
>         Assigned To: Yip Ng
>             Fix For: 10.2.1.0
>
>         Attachments: derby1686-trunk-diff01.txt, derby1686-trunk-diff02.txt, derby1686-trunk-diff03.txt,
derby1686-trunk-diff04.txt, derby1686-trunk-diff05.txt, derby1686-trunk-diff06.txt, derby1686-trunk-stat01.txt,
derby1686-trunk-stat02.txt, derby1686-trunk-stat03.txt, derby1686-trunk-stat04.txt, derby1686-trunk-stat05.txt,
derby1686-trunk-stat06.txt, select_table_no_privilege.sql
>
>
> With authentication on, attempting to execute a GRANT privilege  to 'user3' on a VIEW
created by the 'user2' - who has only SELECT privilege
> on the base table created by 'user1' does not fail. This results in 'user3' getting access
to the table created by 'user1' through the view.
> I remember a discussion on the list to raise an error when an attempt is execute a GRANT
on the view, until WITH GRANT option is implemented.
> Here is the repro:
> java -cp derby.jar;.\derbytools.jar -Dderby.database.sqlAuthorization=true -Dij.exceptionTrace=true
  org.apache.derby.tools.ij select_table_no_privilege.sql
> ij version 10.2
> ij> --
> --create db as user1
> --
> connect 'jdbc:derby:grntrevokedb;create=true' user 'user1';
> WARNING 01J14: SQL authorization is being used without first enabling authentication.
> ij> create table t1(id int);
> 0 rows inserted/updated/deleted
> ij> insert into t1 values(100);
> 1 row inserted/updated/deleted
> ij> insert into t1 values(200);
> 1 row inserted/updated/deleted
> ij> --
> --Grant select to user2
> --
> grant select on t1 to user2;
> 0 rows inserted/updated/deleted
> ij> --
> --Connect as user2
> --
> connect 'jdbc:derby:grntrevokedb;create=true' user 'user2';
> WARNING 01J01: Database 'grntrevokedb' not created, connection made to existingdatabase
instead.
> WARNING 01J14: SQL authorization is being used without first enabling authentication.
> ij(CONNECTION1)> select * from user1.t1;
> ID
> -----------
> 100
> 200
> 2 rows selected
> ij(CONNECTION1)> --
> --Create view
> --
> create view v1 as select * from user1.t1;
> 0 rows inserted/updated/deleted
> ij(CONNECTION1)> select * from v1;
> ID
> -----------
> 100
> 200
> 2 rows selected
> ij(CONNECTION1)> --
> --Grant select on view to user3. With the WITH GRANT option this should have failed
> --
> grant select on v1 to user3;
> 0 rows inserted/updated/deleted
> ij(CONNECTION1)> --
> --Connect as user3
> --
> connect 'jdbc:derby:grntrevokedb;create=true' user 'user3';
> WARNING 01J01: Database 'grntrevokedb' not created, connection made to existing
> database instead.
> WARNING 01J14: SQL authorization is being used without first enabling authentication.
> ij(CONNECTION2)> --
> --No select privilege on base table user1.t1, hence will FAIL
> --
> select * from user1.t1;
> ERROR 28508: User 'USER3' does not have select permission on column 'ID' of table 'USER1'.'T1'.
> ERROR 28508: User 'USER3' does not have select permission on column 'ID' of table 'USER1'.'T1'.
>         at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
>         at org.apache.derby.iapi.sql.dictionary.StatementColumnPermission.check(Unknown
Source)
>         at org.apache.derby.impl.sql.conn.GenericAuthorizer.authorize(Unknown Source)
>         at org.apache.derby.exe.ac295dc08bx010dx00a2x500ax00000011df100.fillResultSet(Unknown
Source)
>         at org.apache.derby.exe.ac295dc08bx010dx00a2x500ax00000011df100.execute(Unknown
Source)
>         at org.apache.derby.impl.sql.GenericActivationHolder.execute(Unknown Source)
>         at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
>         at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
>         at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
>         at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
>         at org.apache.derby.impl.tools.ij.ij.executeImmediate(Unknown Source)
>         at org.apache.derby.impl.tools.ij.utilMain.doCatch(Unknown Source)
>         at org.apache.derby.impl.tools.ij.utilMain.runScriptGuts(Unknown Source)
>         at org.apache.derby.impl.tools.ij.utilMain.go(Unknown Source)
>         at org.apache.derby.impl.tools.ij.Main.go(Unknown Source)
>         at org.apache.derby.impl.tools.ij.Main.mainCore(Unknown Source)
>         at org.apache.derby.impl.tools.ij.Main14.main(Unknown Source)
>         at org.apache.derby.tools.ij.main(Unknown Source)
> ij(CONNECTION2)> --
> --Select from the view on the base table should also FAIL, but does not
> --
> select * from user2.v1;
> ID
> -----------
> 100
> 200
> 2 rows selected
> ij(CONNECTION2)>

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