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: Looking for more information on SYS.SYSREQUIREDPERM, SYS.SYSTABLEPERMS?
Date Wed, 01 Mar 2006 07:03:29 GMT
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<body bgcolor="#ffffff" text="#000000">
This still needs to be done. (Number 2, on my list) Here is my current
list of known changes yet to be implemented, if that helps.<br>
  <li>Implement <b>dblook </b>changes</li>
  <li>Clean up permission descriptors as objects are dropped. Currently
new system tables have only primary key index. I would need to add
another index to be able to search on objectId so I can drop the
  <li>Test JDBC metadata changes.</li>
  <li>Add new property sqlAuthorization and remove changes made to
  <li>Implement trigger, view and constraint authorization model. <b>Biggest
    </b>item left, I think.</li>
  <li>Implement DEFINER model of execution. <b>You are currently
working on this</b>.</li>
  <li>Add mechanism to save database owner and implement schema
creation privilege checks.</li>
  <li>Add&nbsp; RoutinePermDescriptors for system routines that everyone can
  <li>Add warning if sqlAuthorization is on and authentication is OFF.
Add warning if grant/revoke is issued without sqlAuthorization on.<br>
  <li>Add some concurrent multi-user tests.</li>
These should keep me busy for next few weeks.<br>
Mamta Satoor wrote:
  <div>Thanks, Satheesh, for info on EXECUTE privilege. </div>
  <div>Moving on to SYSTABLEPERMS,&nbsp;shouldn't rows from SYSTABLEPERMS be
deleted when the object to which it applies get dropped. For instance,
on a new 10.2 db with grant/revoke enabled,</div>
  <div>select * from sys.systableperms; -- will return 0 rows</div>
  <div>select * from sys.systables where tablename = 'T1'; -- will
return 0 rows</div>
  <div>create table t1(c11 int);</div>
  <div>select * from sys.systableperms; -- will still return 0 rows, so
no grant on t1 yet</div>
  <div>select * from sys.systables where tablename = 'T1'; -- will
return 1 row</div>
grant select on t1 to public;</div>
  <div>select * from sys.systableperms; -- will return 1 row since
there was a grant made on t1</div>
  <div>select * from sys.systables where tablename = 'T1'; -- will
return 1 row</div>
  <div>drop table t1;</div>
  <div>select * from sys.systableperms; -- still&nbsp;returns 1, had
expected no&nbsp;row for t1 at this point</div>
  <div>select * from sys.systables where tablename = 'T1'; -- will
return&nbsp;0 rows as expected</div>
  <div>Is this the expected behavior? Thanks.</div>
  <div><span class="gmail_quote">On 2/28/06, <b
 class="gmail_sendername">Satheesh Bandaram</b> &lt;<a
  <blockquote class="gmail_quote"
 style="border-left: 1px solid rgb(204, 204, 204); margin: 0px 0px 0px 0.8ex; padding-left:
Mamta Satoor wrote:<br>
&gt; Hi,<br>
&gt; I am looking for information on SYSREQUIREDPERM. The grant revoke
&gt; says following for it<br>
This part of the functionality has not been implemented yet.... You want<br>
to work in it? :-)<br>
As for your question about EXECUTE privilege, yes, this table should<br>
have a row to describe the routine that view definition depends on.
&gt; ============= start of text from grant revoke spec<br>
&gt; "The SYS.SYSREQUIREDPERM table keeps track of the permissions
&gt; by views, triggers, and constraints. It is used in the revoke
&gt; statement to find views, triggers, and constraints that have to be<br>
&gt; dropped because they no longer have their required permissions. The<br>
&gt; schema is:<br>
&gt; create table SYS.SYSREQUIREDPERM<br>
&gt; (<br>
&gt;&nbsp;&nbsp;&nbsp;&nbsp; OPERATOR char(36) not null,<br>
&gt;&nbsp;&nbsp;&nbsp;&nbsp; OPERATORTYPE char(1) not null,<br>
&gt;&nbsp;&nbsp;&nbsp;&nbsp; PERMTYPE char(1) not null,<br>
&gt;&nbsp;&nbsp;&nbsp;&nbsp; OBJECT char(36) not null,<br>
&gt;&nbsp;&nbsp;&nbsp;&nbsp; COLUMNS org.apache.derby.iapi.services.io.FormatableBitSet
&gt; )<br>
&gt; The OPERATOR column contains the ID of the view, trigger, or<br>
&gt; constraint. The OPERATORTYPE column has value 'V' for view, 'T' for<br>
&gt; trigger, or 'C' for constraint. The PERMTYPE column indicates the
&gt; of the permission required. It has value 'S' for SELECT, 'D' for<br>
&gt; DELETE, 'I' for INSERT, 'U' for UPDATE, or 'E' for EXECUTE. The
&gt; contains the ID of the object of the required permission. It is a
&gt; reference to the SYS.SYSALIASES table if PERMTYPE = 'E', or to the<br>
&gt; SYS.SYSTABLES table otherwise. The COLUMNS column indicates the<br>
&gt; columns for which permission is required. It is ignored for
&gt; INSERT, and DELETE permissions.<br>
&gt; ============= end of text from grant revoke spec<br>
&gt; I created a view as follows<br>
&gt; ij&gt; create table t1(i int);<br>
&gt; 0 rows inserted/updated/deleted
&gt; ij&gt; insert into t1 values(1),(2),(3);<br>
&gt; 3 rows inserted/updated/deleted<br>
&gt; ij&gt; create view v2 as select * from t1;<br>
&gt; 0 rows inserted/updated/deleted<br>
&gt; ij&gt; select * from sys.SYSREQUIREDPERM
&gt; OPERATOR<br>
&gt; |&amp;|&amp;|OBJECT&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;|COLUMNS<br>
&gt; 0 rows selected
&gt; At the end of the view creation, I thought there will be a row<br>
&gt; inserted in SYSREQUIREDPERM which will indicate that view requires<br>
&gt; permission on t1. May be I don't understand the table correctly. I
&gt; also interested in finding out more about 'E' for EXECUTE for<br>
&gt; PERMTYPE. Will it be set if say the view has a function invokation<br>
&gt; underneath it. eg<br>
&gt;&nbsp;&nbsp;CREATE FUNCTION F_ABS(P1 INT)<br>
&gt;&nbsp;&nbsp;RETURNS INT NO SQL<br>
&gt;&nbsp;&nbsp;RETURNS NULL ON NULL INPUT<br>
&gt;&nbsp;&nbsp;EXTERNAL NAME 'java.lang.Math.abs'<br>
&gt; create view v1(c11) as values f_abs(1);
&gt; thanks,<br>
&gt; Mamta<br>

View raw message