db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Satheesh Bandaram <sathe...@Sourcery.Org>
Subject Re: Another question on grant/revoke functional spec
Date Wed, 01 Mar 2006 22:33:54 GMT
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<br>
<br>
Mamta Satoor wrote:
<blockquote
 cite="midd9619e4a0603011214q191893b1l5a68e1f30eb50412@mail.gmail.com"
 type="cite">
  <div>Hi Satheesh,</div>
</blockquote>
The difference in behavior is seen because in one case you are doing
"select * from mamta.t1" and in another you have "select count(*) from
mamta.t1". If you change the second one to "select * from mamta.t1",
you should see the same error.<br>
<br>
Now, we have a special situation in select count(*) from mamta.t1 and
similar queries that don't actually read any column data from the
table. (like select 1+1 from mamta.t1) Looks like current code misses
checking for SELECT privilege on the table.<br>
<br>
Satheesh<br>
<blockquote
 cite="midd9619e4a0603011214q191893b1l5a68e1f30eb50412@mail.gmail.com"
 type="cite">
  <div>&nbsp;</div>
  <div>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. </div>
  <div>&nbsp;</div>
  <div>I am copying following from the spec</div>
  <div>******************** start of text from functional spec</div>
  <div>CREATE PROCEDURE SALES.TOTAL_REVENUE(IN S_MONTH INTEGER, <br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
IN S_YEAR INTEGER, <br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
OUT TOTAL DECIMAL(10,2)) <br>
&nbsp; PARAMETER STYLE JAVA <br>
&nbsp; READS SQL DATA <br>
&nbsp; LANGUAGE JAVA <br>
&nbsp; EXTERNAL NAME 'com.acme.sales.calculateRevenueByMonth' <br>
&nbsp; EXTERNAL SECURITY INVOKER </div>
  <div>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. </div>
  <div>
  <div>********************&nbsp;end of text from functional spec</div>
  <div>&nbsp;</div>
  <div>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&nbsp;about&nbsp;permissions(it's talking about triggers etc,
but I think it applies to functions/procedures too)
  </div>
  <div>
  <div>&nbsp;</div>
  <div>******************** start of text from functional spec</div>
  <div>&nbsp; CREATE VIEW s.v(vc1,vc2,vc3) <br>
&nbsp;&nbsp;&nbsp; AS SELECT t1.c1,t1.c2,f(t1.c3) FROM t1 JOIN t2 ON t1.c1 = t2.c1
WHERE t2.c2 = 5 </div>
  <p>Jane needs the following permissions in order to create the view: </p>
  <ul>
    <li>ownership of schema s (so that she can create something in it),
    </li>
    <li>ownership of table t1 (so that she can allow others to see
columns in it), </li>
    <li>select permission on t2.c1 and t2.c1, and </li>
    <li>execute permission on f. </li>
  </ul>
When the view is created only <i>jane</i> 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 <i>jane</i> grants select permission
on s.v to <i>harry</i>. When Derby executes a select on s.v on behalf
of <i>harry</i>, Derby only checks that <i>harry</i> has select
permission on s.v; it does not check that <i>harry
  </i> has select permission on t1, or t2 or execute permission on f.
  <p>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.
  </p>
  <p>********************&nbsp;end of text from functional spec</p>
  </div>
  <div>I wrote a test case to see exactly what happens</div>
  <div>-- user "mamtano" doesn't have access to table t1 but it can
execute a function which uses&nbsp;t1<br>
--&nbsp;That function was granted to "mamtano"&nbsp;by "mamta"<br>
connect 'jdbc:derby:c:/dellater/db1sqlStandardNewCol;create=true' user
'mamta';
  <br>
create table t1(c11 int);<br>
insert into t1 values(1),(2);<br>
CREATE FUNCTION COUNT_ROWS(P1 VARCHAR(128), P2 VARCHAR(128)) RETURNS INT<br>
READS SQL DATA<br>
EXTERNAL NAME
'org.apache.derbyTesting.functionTests.util.ProcedureTest.countRows
'<br>
LANGUAGE JAVA PARAMETER STYLE JAVA;<br>
values count_rows('mamta','t1'); </div>
  <div>grant execute on function count_rows to public;<br>
connect 'jdbc:derby:c:/dellater/db1sqlStandardNewCol;create=true' user
'mamtano';<br>
values mamta.count_rows('mamta','t1'); -- ran fine even though no
permission on mamta.t1<br>
select * from mamta.t1; -- fails because no permissions on mamta.t1<br>
&nbsp;</div>
  <div>Code for
org.apache.derbyTesting.functionTests.util.ProcedureTest.countRows</div>
  <div>&nbsp;public static int countRows(String schema, String table) throws
SQLException<br>
&nbsp;{<br>
&nbsp;&nbsp;Connection conn =
DriverManager.getConnection("jdbc:default:connection");<br>
&nbsp;&nbsp;Statement s = conn.createStatement();
  <br>
&nbsp;&nbsp;ResultSet rs = s.executeQuery("SELECT COUNT(*) FROM " + schema + "."
+ table);<br>
&nbsp;&nbsp;rs.next();<br>
&nbsp;&nbsp;int count = rs.getInt(1);<br>
&nbsp;&nbsp;rs.close();<br>
&nbsp;&nbsp;s.close();<br>
&nbsp;&nbsp;conn.close();<br>
&nbsp;&nbsp;return count;
  <br>
&nbsp;}<br>
&nbsp;</div>
  <div>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?
  </div>
  <div>&nbsp;</div>
  <div>thanks,</div>
  <div>Mamta</div>
  </div>
</blockquote>
</body>
</html>


Mime
View raw message