[
http://issues.apache.org/jira/browse/DERBY-1686?page=comments#action_12431443 ]
Yip Ng commented on DERBY-1686:
-------------------------------
Dan, here are the two references from the SQL:2003 spec that I have read to
deduce the grant behavior for the stated problem of this jira:
In section 12.1 <grant statement>, Under general rules item 4:
4) For every table T specified by some involved privilege descriptor and for
each view V owned by some
involved grantee G such that T or some column CT of T is referenced in the
<query expression> QE of V,
or T is a supertable of the scoped table of a <reference resolution>
contained in QE, let RTi, for i ranging
from 1 (one) to the number of tables identified by the <table reference>s
contained in QE, be the <table name>s
of those tables. For every column CV of V:
a) Let CRTi,j, for j ranging from 1 (one) to the number of columns of RTi
that are underlying columns of
CV, be the <column name>s of those columns.
b) If, following successful execution of the <grant statement>, all of
the following are true:
i) The applicable privileges for G include grantable SELECT
privileges on all of the columns CRTi,j.
ii) The applicable privileges for G include grantable EXECUTE
privileges on all SQL-invoked
routines that are subject routines of <routine invocation>s
contained in QE.
...
And in section 12.2 <grant privilege statement>, Under general rules item 6:
6) If WITH GRANT OPTION was specified, then each privilege descriptor also
indicates that the privilege
is grantable.
If I interpret the above correctly, then by enforcing the semantics of
grantable privilege -
granting select privilege to a view owned by user A who have non-grantable
select privileges
on the underlying objects should fail. Comments?
> 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-stat01.txt,
> derby1686-trunk-stat02.txt, derby1686-trunk-stat03.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
