Looking for more information on SYS.SYSREQUIREDPERM?

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

Hi,

I am looking for information on SYSREQUIREDPERM. The grant revoke spec says
following for it
============= 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

Re: Looking for more information on SYS.SYSREQUIREDPERM?

[Satheesh Bandaram <sa...@Sourcery.Org>]

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
>