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 <[EMAIL PROTECTED]
> 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
