[ http://issues.apache.org/jira/browse/DERBY-1686?page=all ]
Yip Ng updated DERBY-1686:
--------------------------
Attachment: derby1686-trunk-stat01.txt
derby1686-trunk-diff01.txt
Attaching experimental patch for DERBY-1686. I ran derbyall on it and
grantRevokeDDL.sql is the only diff I see. It turns out that the master file
of this testcase have incorrect expected behavior of certain test scenarios. I
think this patch outputs the correct results but will need to double check the
grantRevokeDDL diffs. Meanwhile, I welcome any comments for this patch. It
would be nice if the reviewer can also double check the master result file of
this testcase.
The patch addresses the permissions cache problem that was mentioned in my
previous comments. When retrieving the permission descriptor via its UUID, it
will bypass the cache. For other case where it uses the grantee + other
related fields as the key, it remains the same as before and use the cache.
This is to avoid having two different hash codes for the same object.
Now, let's move on to the real problem of this jira. =) Before this patch,
when granting a view whose underlying objects are not owned by the grantor, the
grant will succeed which is not correct since the grantor does not have grant
option for those objects unless he is DBA or the objects granted to him is with
grant option). So to resolve this issue, Derby will now checks each of the
view's underlying object(s) to see if the grantor is the owner at execution
time of the grant statement. (additonal checks are needed in the future when
WITH GRANT OPTION is supported.) If not, the grant will fail with an
SQLException which is what the correct behavior should be.
> 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-stat01.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
