[ http://issues.apache.org/jira/browse/DERBY-1686?page=comments#action_12431141 ] Mamta A. Satoor commented on DERBY-1686: ----------------------------------------
Yip, I thought more about this issue and I think we might be able to get away with an easier solution. With SQL authorization mode, for a view, in sys.sysdepends table, there will be zero/more rows for privilege dependencies. If the database owner is the owner of the view, then there will be no privilege dependencies rows in sys.sysdepends table. For a view whose owner is not the database owner, there will be no privilege dependencies rows in sys.sysdepends table if the view is not accessing any objects outside of the schema owned by the view owner. So, no privilege dependencies rows in sys.sysdepends table for a view would mean that the view owner can grant access to the view to other users. If there are privilege dependencies rows in sys.sysdepends table for a view, then that would mean that the view is relying on privileges on external objects and hence, the view owner will not be able to grant access to the view to other users (the exception to this rule would be if the database owner is trying to grant access to any view to any user. That should work no matter if there are zero or more privilege dependencies rows in sys.sysdepends table for the view). So, rather than building a list of providers for a view, we can simply have a flag which will be set to true at compile time if there are privilege dependencies rows in sys.sysdepends table for the given view. At execute time, if the grantor is the database owner, then we wil not worry about the flag and will succesfully grant the view access to the grantee. But if the grantor is the view owner and the flag is set to true, then we should throw an exception because the view owner does not own all the objects accessed by the view. I might not have thought over everything, so do let me know, if you find something in my proposed logic that might not work. > 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
