You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by Bryan Pendleton <bp...@amberpoint.com> on 2006/09/09 19:27:09 UTC
Question about GRANT and SYSDEPENDS
Hi all,
In the context of DERBY-1489, I am trying to understand a point about
the system tables. In particular, I am trying to understand what entries
are created in the system tables by the GRANT statement.
It appears to me that GRANTing a column permission adds a row to
SYS.SYSCOLPERMS, and GRANTint a table permission adds a row to
SYS.SYSTABLEPERMS.
However, I don't see any rows added to SYS.SYSDEPENDS to indicate a
dependency that the column permission or table permission has on the
underlying column or table (see ij session below).
Is this intentional? If so, when dropping a column from a table, how should
we identify which column permissions are affected? I see that when dropping
the entire table, DropTableConstantAction calls
DataDictionary.dropAllTableAndColPermDescriptors directly; i.e., this
doesn't seem to be handled by using the dependency manager.
I guess I'm groping for some generic help regarding SYS.SYSDEPENDS and
when it should be used and when it shouldn't be used.
thanks,
bryan
ij> create table t (a int, b int);
0 rows inserted/updated/deleted
ij> grant select(b) on t to bryan;
0 rows inserted/updated/deleted
ij> select * from sys.sysdepends;
DEPENDENTID |DEPENDENTFINDER|PROVIDERID
|PROVIDERFINDER
--------------------------------------------------------------------------------
-------------------------
0 rows selected
ij> select * from sys.syscolperms;
COLPERMSID |GRANTEE
|GRANTOR
|TABLEID
|&|COLUMNS
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-----------------------------
6839c016-010d-938b-c2aa-000000131c08|BRYAN
|APP
|a02ac013-010d-938b-c2aa-0
00000131c08|s|{1}
1 row selected
ij> grant update on t to bryan;
0 rows inserted/updated/deleted
ij> select * from sys.systableperms;
TABLEPERMSID |GRANTEE
|GRANTOR
|TABLEID
|&|&|&|&|&|&
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-----------------------
004b0019-010d-938b-c2aa-000000131c08|BRYAN
|APP
|a02ac013-010d-938b-c2aa-0
00000131c08|N|N|N|y|N|N
1 row selected
ij> select count(*) from sys.sysdepends;
1
-----------
0
1 row selected
Re: Question about GRANT and SYSDEPENDS
Posted by Mamta Satoor <ms...@gmail.com>.
Hi Bryan,
Maybe someone with more knowledge of dependency manager can provide more
information but I think the reason we are handling views through dependency
system is that there might be other objects(say another view, prepared
statement) dependent on that view and they all need to be notified of the
view invalidation. As for the trigger, there can't be any other object that
can depend on that trigger and hence we can bypass the dependency system and
go straight to DropTableConstantAction code.
Mamta
On 9/11/06, Bryan Pendleton <bp...@amberpoint.com> wrote:
>
> > Satheesh had added some code as part of "(DERBY-1543) Address two
> > remaining issues with GRANT/REVOKE functionality: 1) Add warning when
> > sqlAuthorization is on with authentication off 2) Drop permission
> > descriptors when objects they cover are dropped." to cleanup 3
> > permissions related system tables when a table/routine is dropped. Maybe
> > something similar is required when a column is dropped from a table.
>
> Thanks, Mamta! Yes, this is a very interesting and relevant change for
> the issue I'm struggling with. It seems like DERBY-1543 could have been
> implemented using the dependency manager, but was not.
>
> I will investigate extending the techniques used by DERBY-1543 to cover
> dropping a column, as you suggest.
>
> In the meantime, I'm still rather puzzled regarding the more generic
> question of when the various data dictionary objects use the SYSDEPENDS
> table and the Dependency Manager routines, and when they do not. If
> anybody
> has insight into the history or architecture here, that would be greatly
> appreciated. For example, why is dropping a dependent VIEW driven out of
> ViewDescriptor.makeInvalid, but dropping a dependent TRIGGER is driven by
> direct calls in the DropTableConstantAction code?
>
> There's a definite difference in the overall technique, and I'm not
> grasping the idea about when to use the one and when to use the other.
>
> thanks,
>
> bryan
>
>
>
Re: Question about GRANT and SYSDEPENDS
Posted by Bryan Pendleton <bp...@amberpoint.com>.
> Satheesh had added some code as part of "(DERBY-1543) Address two
> remaining issues with GRANT/REVOKE functionality: 1) Add warning when
> sqlAuthorization is on with authentication off 2) Drop permission
> descriptors when objects they cover are dropped." to cleanup 3
> permissions related system tables when a table/routine is dropped. Maybe
> something similar is required when a column is dropped from a table.
Thanks, Mamta! Yes, this is a very interesting and relevant change for
the issue I'm struggling with. It seems like DERBY-1543 could have been
implemented using the dependency manager, but was not.
I will investigate extending the techniques used by DERBY-1543 to cover
dropping a column, as you suggest.
In the meantime, I'm still rather puzzled regarding the more generic
question of when the various data dictionary objects use the SYSDEPENDS
table and the Dependency Manager routines, and when they do not. If anybody
has insight into the history or architecture here, that would be greatly
appreciated. For example, why is dropping a dependent VIEW driven out of
ViewDescriptor.makeInvalid, but dropping a dependent TRIGGER is driven by
direct calls in the DropTableConstantAction code?
There's a definite difference in the overall technique, and I'm not
grasping the idea about when to use the one and when to use the other.
thanks,
bryan
Re: Question about GRANT and SYSDEPENDS
Posted by Mamta Satoor <ms...@gmail.com>.
Hi Bryan,
For grant/revoke functionality, views/triggers/conststraints use SYSDEPENDS
table to track their dependency on various privileges that they need.
GRANT/REVOKE statements in particular touch SYSTABLEPERMS, SYSCOLPERMS and
SYSROUTINEPERMS. A REVOKE might indirectly cause rows deletion in SYSDEPENDS
if there are views/triggers/constraints dependent on the privilege being
revoked.
Satheesh had added some code as part of "(DERBY-1543) Address two remaining
issues with GRANT/REVOKE functionality: 1) Add warning when sqlAuthorization
is on with authentication off 2) Drop permission descriptors when objects
they cover are dropped." to cleanup 3 permissions related system tables when
a table/routine is dropped. Maybe something similar is required when a
column is dropped from a table.
Hope this helps some,
Mamta
On 9/9/06, Bryan Pendleton <bp...@amberpoint.com> wrote:
>
> Hi all,
>
> In the context of DERBY-1489, I am trying to understand a point about
> the system tables. In particular, I am trying to understand what entries
> are created in the system tables by the GRANT statement.
>
> It appears to me that GRANTing a column permission adds a row to
> SYS.SYSCOLPERMS, and GRANTint a table permission adds a row to
> SYS.SYSTABLEPERMS.
>
> However, I don't see any rows added to SYS.SYSDEPENDS to indicate a
> dependency that the column permission or table permission has on the
> underlying column or table (see ij session below).
>
> Is this intentional? If so, when dropping a column from a table, how
> should
> we identify which column permissions are affected? I see that when
> dropping
> the entire table, DropTableConstantAction calls
> DataDictionary.dropAllTableAndColPermDescriptors directly; i.e., this
> doesn't seem to be handled by using the dependency manager.
>
> I guess I'm groping for some generic help regarding SYS.SYSDEPENDS and
> when it should be used and when it shouldn't be used.
>
> thanks,
>
> bryan
>
> ij> create table t (a int, b int);
> 0 rows inserted/updated/deleted
> ij> grant select(b) on t to bryan;
> 0 rows inserted/updated/deleted
> ij> select * from sys.sysdepends;
> DEPENDENTID |DEPENDENTFINDER|PROVIDERID
> |PROVIDERFINDER
> --------------------------------------------------------------------------------
>
> -------------------------
>
> 0 rows selected
> ij> select * from sys.syscolperms;
> COLPERMSID |GRANTEE
>
> |GRANTOR
> |TABLEID
> |&|COLUMNS
>
> --------------------------------------------------------------------------------
>
> --------------------------------------------------------------------------------
> --------------------------------------------------------------------------------
>
>
> --------------------------------------------------------------------------------
> -----------------------------
> 6839c016-010d-938b-c2aa-000000131c08|BRYAN
>
> |APP
> |a02ac013-010d-938b-c2aa-0
>
> 00000131c08|s|{1}
>
> 1 row selected
> ij> grant update on t to bryan;
> 0 rows inserted/updated/deleted
> ij> select * from sys.systableperms;
> TABLEPERMSID |GRANTEE
>
> |GRANTOR
> |TABLEID
> |&|&|&|&|&|&
>
> --------------------------------------------------------------------------------
> --------------------------------------------------------------------------------
>
>
> --------------------------------------------------------------------------------
>
> --------------------------------------------------------------------------------
> -----------------------
> 004b0019-010d-938b-c2aa-000000131c08|BRYAN
>
> |APP
>
> |a02ac013-010d-938b-c2aa-0
> 00000131c08|N|N|N|y|N|N
>
> 1 row selected
> ij> select count(*) from sys.sysdepends;
> 1
> -----------
> 0
>
> 1 row selected
>
>