Re: Looking for more information on SYS.SYSREQUIREDPERM, SYS.SYSTABLEPERMS?

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

Thanks, Satheesh, for info on EXECUTE privilege.

Moving on to SYSTABLEPERMS, shouldn't rows from SYSTABLEPERMS be deleted
when the object to which it applies get dropped. For instance, on a
new 10.2db with grant/revoke enabled,
select * from sys.systableperms; -- will return 0 rows
select * from sys.systables where tablename = 'T1'; -- will return 0 rows
create table t1(c11 int);
select * from sys.systableperms; -- will still return 0 rows, so no grant on
t1 yet
 select * from sys.systables where tablename = 'T1'; -- will return 1 row
grant select on t1 to public;
select * from sys.systableperms; -- will return 1 row since there was a
grant made on t1
select * from sys.systables where tablename = 'T1'; -- will return 1 row
drop table t1;
 select * from sys.systableperms; -- still returns 1, had expected no row
for t1 at this point
select * from sys.systables where tablename = 'T1'; -- will return 0 rows as
expected

Is this the expected behavior? Thanks.
Mamta



On 2/28/06, Satheesh Bandaram <sa...@sourcery.org> wrote:
>
>
> Mamta Satoor wrote:
>
> > Hi,
> >
> > I am looking for information on SYSREQUIREDPERM. The grant revoke spec
> > says following for it
>
> This part of the functionality has not been implemented yet.... You want
> to work in it? :-)
>
> As for your question about EXECUTE privilege, yes, this table should
> have a row to describe the routine that view definition depends on.
>
> Satheesh
>
> > ============= start of text from grant revoke spec
> > "The SYS.SYSREQUIREDPERM table keeps track of the permissions required
> > by views, triggers, and constraints. It is used in the revoke
> > statement to find views, triggers, and constraints that have to be
> > dropped because they no longer have their required permissions. The
> > schema is:
> >
> > create table SYS.SYSREQUIREDPERM
> > (
> >     OPERATOR char(36) not null,
> >     OPERATORTYPE char(1) not null,
> >     PERMTYPE char(1) not null,
> >     OBJECT char(36) not null,
> >     COLUMNS org.apache.derby.iapi.services.io.FormatableBitSet
> > )
> >
> > The OPERATOR column contains the ID of the view, trigger, or
> > constraint. The OPERATORTYPE column has value 'V' for view, 'T' for
> > trigger, or 'C' for constraint. The PERMTYPE column indicates the type
> > of the permission required. It has value 'S' for SELECT, 'D' for
> > DELETE, 'I' for INSERT, 'U' for UPDATE, or 'E' for EXECUTE. The OBJECT
> > contains the ID of the object of the required permission. It is a
> > reference to the SYS.SYSALIASES table if PERMTYPE = 'E', or to the
> > SYS.SYSTABLES table otherwise. The COLUMNS column indicates the
> > columns for which permission is required. It is ignored for EXECUTE,
> > INSERT, and DELETE permissions.
> >
> > ============= end of text from grant revoke spec
> >
> > I created a view as follows
> > ij> create table t1(i int);
> > 0 rows inserted/updated/deleted
> > ij> insert into t1 values(1),(2),(3);
> > 3 rows inserted/updated/deleted
> > ij> create view v2 as select * from t1;
> > 0 rows inserted/updated/deleted
> > ij> select * from sys.SYSREQUIREDPERM;
> > OPERATOR
> > |&|&|OBJECT                              |COLUMNS
> >
> ----------------------------------------------------------------------------------------------------------
> >
> > 0 rows selected
> >
> > At the end of the view creation, I thought there will be a row
> > inserted in SYSREQUIREDPERM which will indicate that view requires
> > permission on t1. May be I don't understand the table correctly. I am
> > also interested in finding out more about 'E' for EXECUTE for
> > PERMTYPE. Will it be set if say the view has a function invokation
> > underneath it. eg
> >
> >  CREATE FUNCTION F_ABS(P1 INT)
> >  RETURNS INT NO SQL
> >  RETURNS NULL ON NULL INPUT
> >  EXTERNAL NAME 'java.lang.Math.abs'
> >  EXTERNAL SECURITY DEFINER
> >  LANGUAGE JAVA PARAMETER STYLE JAVA;
> > create view v1(c11) as values f_abs(1);
> >
> > thanks,
> > Mamta
> >
>
>