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.
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
