Return-Path: Delivered-To: apmail-db-derby-dev-archive@www.apache.org Received: (qmail 84222 invoked from network); 1 Mar 2006 20:14:04 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 1 Mar 2006 20:14:04 -0000 Received: (qmail 72479 invoked by uid 500); 1 Mar 2006 20:14:50 -0000 Delivered-To: apmail-db-derby-dev-archive@db.apache.org Received: (qmail 72447 invoked by uid 500); 1 Mar 2006 20:14:50 -0000 Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: Delivered-To: mailing list derby-dev@db.apache.org Received: (qmail 72438 invoked by uid 99); 1 Mar 2006 20:14:50 -0000 Received: from asf.osuosl.org (HELO asf.osuosl.org) (140.211.166.49) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 01 Mar 2006 12:14:50 -0800 X-ASF-Spam-Status: No, hits=0.0 required=10.0 tests=HTML_MESSAGE,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (asf.osuosl.org: domain of msatoor@gmail.com designates 64.233.182.196 as permitted sender) Received: from [64.233.182.196] (HELO nproxy.gmail.com) (64.233.182.196) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 01 Mar 2006 12:14:49 -0800 Received: by nproxy.gmail.com with SMTP id k27so157697nfc for ; Wed, 01 Mar 2006 12:14:28 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:mime-version:content-type; b=dcjic7QocuqksvwENM2kyZyDQktRgqZmLN7kuej1qRhbO2Ac+vErXrMNOqgvytDCjNVUE+hwQUaQANusRvuAEwHS6Aa0tT0NtrclfloNKZ45zWZbk2vl30C6/USnaX/J0vDGi9db6x56O5vsdl1dN1vcLQSJw7WS6+6l4+IDdGM= Received: by 10.49.56.6 with SMTP id i6mr284229nfk; Wed, 01 Mar 2006 12:14:28 -0800 (PST) Received: by 10.49.22.19 with HTTP; Wed, 1 Mar 2006 12:14:28 -0800 (PST) Message-ID: Date: Wed, 1 Mar 2006 12:14:28 -0800 From: "Mamta Satoor" To: "Derby Development" Subject: Another question on grant/revoke functional spec MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_1285_27774233.1141244068104" X-Virus-Checked: Checked by ClamAV on apache.org X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N ------=_Part_1285_27774233.1141244068104 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Hi Satheesh, Pardon me if I keep asking questions on areas that are work in progress. Bu= t 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 tha= t 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 rea= d 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 =3D t2.c1 WHERE t2.c2 =3D 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 o= n 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 *harr= y * 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 operat= e on columns for which the current user does not have the appropriate permissions. It is only required that the owner of the trigger or constrain= t 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=3Dtrue' 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=3Dtrue' user 'mamtano'; values mamta.count_rows('mamta','t1'); -- ran fine even though no permissio= n 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 =3D DriverManager.getConnection("jdbc:default:connection"= ); Statement s =3D conn.createStatement(); ResultSet rs =3D s.executeQuery("SELECT COUNT(*) FROM " + schema + "." + table); rs.next(); int count =3D 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 ------=_Part_1285_27774233.1141244068104 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
Hi Satheesh,
 
Pardon me if I keep asking questions on areas that are work in progres= s. But I just want to be clear on things as I do my work on EXTERNAL SECURI= TY.
 
I am copying following from the spec
******************** start of text from functional spec
CREATE PROCEDURE SALES.TOTAL_REVENUE(IN S_MONTH INTEGER,
 &nb= sp;            =             &nb= sp;          IN S_YEAR INTEGER= ,
           &nb= sp;            =              OU= T TOTAL DECIMAL(10,2))
  PARAMETER STYLE JAVA
  READS SQL= DATA=20
  LANGUAGE JAVA
  EXTERNAL NAME 'com.acme.sales.calculate= RevenueByMonth'
  EXTERNAL SECURITY INVOKER
This specifies that procedure sales.total_revenue can only read column= s that the invoker can read directly. If instead the definition of sales.to= tal_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=20 sales.total_revenue is permitted to read. It then may be able to access dat= a 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 sp= ec 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 t= 1.c1,t1.c2,f(t1.c3) FROM t1 JOIN t2 ON t1.c1 =3D t2.c1 WHERE t2.c2 =3D 5

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

  • ownership of schema s (so that she can create something in it),=20
  • ownership of table t1 (so that she can allow others to see columns in i= t),=20
  • select permission on t2.c1 and t2.c1, and=20
  • execute permission on f.
When the view is crea= ted only jane has select permission on it. Jane can grant select per= mission 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=20 jane grants select permission on s.v to harry. When Derby exe= cutes 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.=20

Similarly with triggers and constraints: a trigger or constraint may ope= rate on columns for which the current user does not have the appropriate pe= rmissions. 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=3Dtrue' user 'mamta';
create table t1(c11 int);
insert into t1 values(1),(2);
CREATE FU= NCTION COUNT_ROWS(P1 VARCHAR(128), P2 VARCHAR(128)) RETURNS INT
READS SQ= L DATA
EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.Procedu= reTest.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=3Dtrue' user 'mamtano';
values = mamta.count_rows('mamta','t1'); -- ran fine even though no permission on=20 mamta.t1
select * from mamta.t1; -- fails because no permissions on mamt= a.t1
 
Code for org.apache.derbyTesting.functionTests.util.ProcedureTest.coun= tRows
 public static int countRows(String schema, String table) throws = SQLException
 {
  Connection conn =3D DriverManager.ge= tConnection("jdbc:default:connection");
  Statement = s =3D conn.createStatement();
  ResultSet rs =3D s.executeQuery("SELECT COUNT(*) FROM = " + schema + "." + table);
  rs.next();
&nbs= p; int count =3D rs.getInt(1);
  rs.close();
 &nb= sp;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 tab= le t1. But I didn't get any error in the example above when "mamtano&q= uot; executed count_rows. Is that right?
 
thanks,
Mamta
------=_Part_1285_27774233.1141244068104--