You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Yip Ng (JIRA)" <de...@db.apache.org> on 2006/09/01 04:26:23 UTC
[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
[ http://issues.apache.org/jira/browse/DERBY-1686?page=comments#action_12432028 ]
Yip Ng commented on DERBY-1686:
-------------------------------
Dan states:
1) GRANT SELECT, INSERT ON T TO FRED - I think it is outcome C), ie. no warning
Hmm... I found it abit strange if no warning is raised for the above case where
INSERT is non-grantable privilege and SELECT is grantable. The SELECT privilege
is granted(12.2 AR item 1) and since the INSERT privilege is non-grantable, the
system should raise a warning (12.1 GR item 8)
If no warning is raised, then how can a grantor can be informed by the system that all
the specified privileges he/she attempts to grant are all granted to the respective
grantee(s)? i.e.: If this very statement was issued by another user who happens to
have both SELECT and INSERT as grantable privileges. So shouldn't C) be
C) Successful execution - at least one privilege granted, warning issued
And an additional outcome D) where
D) Successful execution - all privileges granted, no warning issued
2) GRANT SELECT ON V TO FRED
I cannot find any piece of info that is in 12.1 GR 4) that explicitly states it should return
as failure or warning. All it states is that if the conditions in GR 4) are met,
then the grant statement is executed successfully. I was inferring this implicitly
that if it is not executed successfully then it is an error.
I think at least we agree on that the problem statement of this jira should not be granted
successfully, so now the question is - should it be raised as a warning or an error
in the "singular" privilege case? In either case, there must be references somewhere
in the spec that addresses this...
> 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-stat01.txt, derby1686-trunk-stat02.txt, derby1686-trunk-stat03.txt, derby1686-trunk-stat04.txt, derby1686-trunk-stat05.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
Re: [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
Posted by Yip Ng <yi...@gmail.com>.
>On 8/31/06, Daniel John Debrunner <dj...@apache.org> wrote:
>
>I think you are misreading it. It is saying if the GRANT statement is
>executed successfuly *AND* all of the conditions are met than an
>*additional* GRANT statement is executed.
You are right. I missed the additional GRANT statement part...
Yip
On 8/31/06, Daniel John Debrunner <dj...@apache.org> wrote:
>
> Yip Ng (JIRA) wrote:
>
> > 2) GRANT SELECT ON V TO FRED
> >
> > I cannot find any piece of info that is in 12.1 GR 4) that
> explicitly states it should return
> > as failure or warning. All it states is that if the conditions in
> GR 4) are met,
> > then the grant statement is executed successfully. I was inferring
> this implicitly
> > that if it is not executed successfully then it is an error.
>
> I think you are misreading it. It is saying if the GRANT statement is
> executed successfuly *AND* all of the conditions are met than an
> *additional* GRANT statement is executed.
>
> It is not saying that all of those conditions need to be met in order to
> execute the original GRANT.
>
> Dan.
>
>
>
Re: [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
Posted by Daniel John Debrunner <dj...@apache.org>.
Yip Ng (JIRA) wrote:
> 2) GRANT SELECT ON V TO FRED
>
> I cannot find any piece of info that is in 12.1 GR 4) that explicitly states it should return
> as failure or warning. All it states is that if the conditions in GR 4) are met,
> then the grant statement is executed successfully. I was inferring this implicitly
> that if it is not executed successfully then it is an error.
I think you are misreading it. It is saying if the GRANT statement is
executed successfuly *AND* all of the conditions are met than an
*additional* GRANT statement is executed.
It is not saying that all of those conditions need to be met in order to
execute the original GRANT.
Dan.