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.
- Implement dblook changes
- 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
entries.
- Test JDBC metadata changes.
- Add new property sqlAuthorization and remove changes made to
defaultConnectionMode.
- Implement trigger, view and constraint authorization model. Biggest
item left, I think.
- Implement DEFINER model of execution. You are currently
working on this.
- Add mechanism to save database owner and implement schema
creation privilege checks.
- Add RoutinePermDescriptors for system routines that everyone can
execute.
- Add warning if sqlAuthorization is on and authentication is OFF.
Add warning if grant/revoke is issued without sqlAuthorization on.
- Add some concurrent multi-user tests.
These should keep me busy for next few weeks.
Satheesh
Mamta Satoor wrote:
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.2 db 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 <[EMAIL PROTECTED]>
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
>
|
