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?
Date Tue, 28 Feb 2006 19:53:14 GMT

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
>


Mime
View raw message