db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta A. Satoor (JIRA)" <derby-...@db.apache.org>
Subject [jira] Commented: (DERBY-1646) Documentation to address Grant/Revoke Authorization for views/triggers/constraints/routines(DERBY-1330)
Date Thu, 24 Aug 2006 06:51:05 GMT
    [ http://issues.apache.org/jira/browse/DERBY-1646?page=comments#action_12430172 ] 
            
Mamta A. Satoor commented on DERBY-1646:
----------------------------------------

One final area that requires documentation has to do with revoke privilege on table/column
levels. 

Currently, all the privilege types (SELECT, DELETE, INSERT, UPDATE, REFERENCES, TRIGGER) for
a given grantee and tableid are kept in one row in SYSTABLEPERMS. ie say user user2 has SELECT
and DELETE privileges on table user1.t1, then the row in SYSTABLEPERMS for grantee user2 and
table user1.t1 will have SELECTPRIV and DELETEPRIV set to "Y" but the rest of the privilege
type fields, INSERTPRIV, UPDATEPRIV,  REFERENCESPRIV and TRIGGERPRIV will be set to "N". 
Later, when an object is created which relies on any granted privilege type for a given tableid
and grantee, the Derby engine tracks the dependency of the newly created object on the specific
row in SYSTABLEPERMS ie the dependency manager only knows that the object is dependent on
some privilege type in that specific row but it doesn't know exactly what privilege type.

Later, when a revoke table level privilege is issued for the given grantee and tableid, all
the dependents of grantee and tableid get notified. All the dependents might not need the
specific privilege type being revoked for the given grantee and tableid, but the dependents
of that row in SYSTABLEPERMS will still end up dropping themselves. This behavior of Derby
should be enhanced in future so that real dependents of a privilege type on a given grantee,
tableid get notified but until then, we should document this behavior.
Following is an example showing the current behavior for privilege granted at table level.
user1
create table t1
grant select, delete on t1 to user2 -- one row in SYSTABLEPERMS for grantee(user2), table(t1)
with SELECTPRIV and DELETEPRIV set to "Y"
user2
create view v1 as select * from user1.t1 -- dependency manager tracks dependency of v1 on
row in SYSTABLEPERMS for grantee(user2), table(t1)
-- Note that dependency manager does not track dependency on specific privilege type for this
row which in this case is SELECTPRIV
-- Note that view v1 has no requirement for DELETEPRIV
user1
revoke delete on t1 from user2 -- since row in SYSTABLEPERMS for grantee(user2), table(t1)
got modified because of this revoke, 
-- dependency manager sends a revoke invalidation message to view user2.v1 and the view ends
up dropping itself eventhough it was
-- never dependent on DELETEPRIV
-- Derby behavior should be modified in future such that the revoke delete above does not
end up dropping view user2.v1
-- View user2.v1 should get dropped automatically, only if revoke select on t1 from user2
is issued. Until we implement this behavior,
-- we should document the current behavior somewhere.

Similar behavior exists for column level privileges. Currently, if a privilege is granted
at columns level, then we create a row in SYSCOLPERMS for grantee, tableid, columns on which
permission is granted and permission type. ie say user user2 has SELECT privilege on table
user1.t1's columns c11, c12 but not on columns c13, c14, then the row in SYSCOLPERMS will
have privilege type as SELECT for grantee user2, table user1.t1 and column bits set for columns
c11, c12. 
Later, when an object is created which relies on a granted privilege type for a given grantee
and subset of columns in a tableid, the Derby engine tracks the dependency of the newly created
object on the specific row in SYSCOLPERMS ie the dependency manager only knows that the object
is dependent on some columns in that specific row but it doesn't know exactly which columns.

Later, when a revoke column level privilege is issued for the given grantee and tableid and
privilege type, all the dependents of grantee, tableid and privilege type get notified. All
the dependents might not need the specific columns whose privilege is being revoked for the
given grantee and tableid and privilege type, but the dependents of that row in SYSCOLPERMS
will still end up dropping themselves. This behavior of Derby should be enhanced in future
so that real dependents of the column list for a privilege type on a given grantee, tableid
get notified but until then, we should document this behavior.
Following is an example showing the current behavior for privilege granted at column level.
user1
create table t1(c11, c12, c13, c14)
grant select(c11, c12) on t1 to user2 -- one row in SYSCOLPERMS for grantee(user2), table(t1),
columns(c11, c12) and privilege type(SELECT)
user2
create view v1 as select c11 from user1.t1 -- dependency manager tracks dependency of v1 on
row in SYSCOLPERMS for grantee(user2), table(t1)
-- and privilege type SELECT. Note that dependency manager does not track dependency on specific
column list of table t1 in SYSCOLPERMS/
-- Note that view v1 has no requirements for SELECT privilege on column c12 in user1.t1
user1
revoke select(c12) on t1 from user2 -- since row in SYSCOLPERMS for grantee(user2), table(t1),
privilege type(SELECT) got modified because
-- of this revoke, dependency manager send a revoke invalidation message to view user2.v1
and view ends up dropping itself even though it
-- was never dependent on SELECT privilege on column c12 of table user1.t1
-- Derby behavior should be modified in future such that the revoke select on column c12 above
does not end up dropping view user2.v1
-- View user2.v1 should get dropped automatically only if revoke select on column c11 on t1
from user2 is ussed. But until this behavior
-- is implemented, we should document the current behavior somewhere.

Laura, please let me know if this is unclear. An understanding of the structure on SYSTABLEPERMS
and SYSCOLPERMS might make it easier
to understand the current behavior and ideal behavior.



> Documentation to address Grant/Revoke Authorization for views/triggers/constraints/routines(DERBY-1330)
> -------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-1646
>                 URL: http://issues.apache.org/jira/browse/DERBY-1646
>             Project: Derby
>          Issue Type: New Feature
>          Components: Documentation
>    Affects Versions: 10.2.1.0
>            Reporter: Mamta A. Satoor
>         Assigned To: Laura Stewart
>
> Creating a separate jira entry for documentation of Grant/Revoke Authorization for views/triggers/constraints/routines(Engine
changes are going as part of DERBY-1330).
> Will link this jira entry to DERBY-1330

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

Mime
View raw message