Hi Satheesh,
 
Pardon me if I keep asking questions on areas that are work in progress. But I just want to be clear on things as I do my work on EXTERNAL SECURITY.
 
I am copying following from the spec
******************** start of text from functional spec
CREATE PROCEDURE SALES.TOTAL_REVENUE(IN S_MONTH INTEGER,
                                     IN S_YEAR INTEGER,
                                     OUT TOTAL DECIMAL(10,2))
  PARAMETER STYLE JAVA
  READS SQL DATA
  LANGUAGE JAVA
  EXTERNAL NAME 'com.acme.sales.calculateRevenueByMonth'
  EXTERNAL SECURITY INVOKER
This specifies that procedure sales.total_revenue can only read columns that the invoker can read directly. If instead the definition of sales.total_revenue used EXTERNAL SECURITY DEFINER, or it did not have an external security clause, then the procedure can only read columns that the creator of sales.total_revenue is permitted to read. It then may be able to access data that the invoker of sales.total_revenue is not permitted to read directly.
******************** end of text from functional spec
 
If I read the above text correctly, then invoker can only read columns that it has access to. But, I think it contradicts with what functional spec says about permissions(it's talking about triggers etc, but I think it applies to functions/procedures too)
 
******************** start of text from functional spec
  CREATE VIEW s.v(vc1,vc2,vc3)
    AS SELECT t1.c1,t1.c2,f(t1.c3) FROM t1 JOIN t2 ON t1.c1 = t2.c1 WHERE t2.c2 = 5

Jane needs the following permissions in order to create the view:

When the view is created only jane has select permission on it. Jane can grant select permission on any or all of the columns of s.v to anyone, even to users who do not have select permission t1 or t2 or execute permission on f. Suppose jane grants select permission on s.v to harry. When Derby executes a select on s.v on behalf of harry, Derby only checks that harry has select permission on s.v; it does not check that harry has select permission on t1, or t2 or execute permission on f.

Similarly with triggers and constraints: a trigger or constraint may operate on columns for which the current user does not have the appropriate permissions. It is only required that the owner of the trigger or constraint have the appropriate permissions.

******************** end of text from functional spec

I wrote a test case to see exactly what happens
-- user "mamtano" doesn't have access to table t1 but it can execute a function which uses t1
-- That function was granted to "mamtano" by "mamta"
connect 'jdbc:derby:c:/dellater/db1sqlStandardNewCol;create=true' user 'mamta';
create table t1(c11 int);
insert into t1 values(1),(2);
CREATE FUNCTION COUNT_ROWS(P1 VARCHAR(128), P2 VARCHAR(128)) RETURNS INT
READS SQL DATA
EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.ProcedureTest.countRows '
LANGUAGE JAVA PARAMETER STYLE JAVA;
values count_rows('mamta','t1');
grant execute on function count_rows to public;
connect 'jdbc:derby:c:/dellater/db1sqlStandardNewCol;create=true' user 'mamtano';
values mamta.count_rows('mamta','t1'); -- ran fine even though no permission on mamta.t1
select * from mamta.t1; -- fails because no permissions on mamta.t1
 
Code for org.apache.derbyTesting.functionTests.util.ProcedureTest.countRows
 public static int countRows(String schema, String table) throws SQLException
 {
  Connection conn = DriverManager.getConnection("jdbc:default:connection");
  Statement s = conn.createStatement();
  ResultSet rs = s.executeQuery("SELECT COUNT(*) FROM " + schema + "." + table);
  rs.next();
  int count = rs.getInt(1);
  rs.close();
  s.close();
  conn.close();
  return count;
 }
 
As per the spec, it seems like "mamtano" shouldn't have been able to call count_rows successfully because it doesn't have access to table t1. But I didn't get any error in the example above when "mamtano" executed count_rows. Is that right?
 
thanks,
Mamta