db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta Satoor" <msat...@gmail.com>
Subject Looking for more information on SYS.SYSREQUIREDPERM?
Date Tue, 28 Feb 2006 19:27:02 GMT
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

Mime
View raw message