[
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
