[ http://issues.apache.org/jira/browse/DERBY-1686?page=comments#action_12431953 ] Daniel John Debrunner commented on DERBY-1686: ----------------------------------------------
I think from the standard a GRANT statement has three possible outcomes: A) Failed execution with SQL state 42xxx - Access Rule violation B) Successful execution - no privileges granted - warning raised 01007 - privilege not granted C) Successful execution - at least one privilege granted For the examples above 1) GRANT SELECT, INSERT ON T TO FRED - I think it is outcome C), ie. no warning As for case 2) Yip says: "According to 12.1 GR 4), the above statement should only be executed successfully when all of its underlying objects (be that tables or views) have grantable select privilege as well. " So can you quote any piece of text in 12.1 GR 4) that indicates a possible outcome is a failure? The standard indicates failure by saying "then an exception condition is raised:<description of error>" Note there are no Access Rules for 12.1, the only access rules that apply here is from 12.2. I think that 12.1 GR 4) is simply stating that if a creator of a view following *any* GRANT statement has grantable privileges for all the privileges the view requires, then the creator of the view automatically gains grantable SELECT on the view. So for the example in the description, if user1 subseqeuntly executes (after the view was created) GRANT SELECT ON user.t1 to user2 WITH GRANT OPTION then this statement is implicitly executed GRANT SELECT ON user2.v1 to user2 WITH GRANT OPTION GRANTED BY _SYSTEM So I think case 2) here is a B - warning raised. > 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
