Another question on grant/revoke functional spec

[Mamta Satoor <ms...@gmail.com>]

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:

   - ownership of schema s (so that she can create something in it),
   - ownership of table t1 (so that she can allow others to see columns
   in it),
   - select permission on t2.c1 and t2.c1, and
   - execute permission on f.

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