Re: Issue with using dependency manager for table level permission tracking

[Mamta Satoor]

Hi,

 

I wondered if anyone got a chance to go over my earlier mail and if they think of an alternative way to solve the problem than the one suggested by me. If I don't hear anything in a day or so, then I will go ahead and change SYSTABLEPERMS to look like SYSCOlPERMS. After that change, I will be able to associate a view/trigger/constraint's dependnecy on the exact privilege type for a give table.

 

thanks,

Mamta

 

On 6/28/06, Mamta Satoor <[EMAIL PROTECTED]> wrote:

Hi,

 

I recently submitted a patch ( http://www.nabble.com/-PATCH-DERBY-1330-Collect-privilege-requirements-for-views-tf1858264.html#a5075008 ) that tracks a view's(view descriptor) dependency on different privileges (PermissionsDescriptor) using the dependency manager which in turn saves these dependencies in SYSDEPENDS.

 

Currently, Derby 10.2 codeline tracks table level privileges per user in SYSTABLEPERMS. If a user has one/more privileges on a table, they are all tracked in one row in SYSTABLEPERMS. The structure of SYSTABLEPRMS looks as follows

create table SYS.SYSREQUIREDPERM
(
    GRANTEE varchar(128) not null,
    GRANTOR varchar(128) not null,
    TABLEID char(36) not null,
    SELECTPRIV char(1) not null,
    DELETEPRIV char(1) not null,
    INSERTPRIV char(1) not null,
    UPDATEPRIV char(1) not null,
    REFERENCESPRIV char(1) not null,
    TRIGGERPRIV char(1) not null,
    primary key( GRANTEE, TABLEID),
    foreign key( TABLEID references SYS.SYSTABLES)
)

 

Following eg shows how table level privileges are tracked in SYSTABLEPERMS and the problem at hand

user1 connects

create table t1

grant select on t1 to user2

--Then at this point, there is one row in SYSTABLEPERMS  for t1 and user2 and that row has SELECTPRIV column set to true.

user2 connects

create view v1 as select * from user1.t1

-- view above depends on SELECTPRIV on user1.t1 and dependency manager keeps track of that dependency by inserting a row for viewdescriptor depending on permissiondescriptor(the uuid column in SYSTABLEPERMS). Notice that there is no differentiation here which says that the view depends only on the SELECTPRIV privilege type on table t1. We just know that view is dependent on SOME privilege type on table t1

user1 connects

grant insert on t1 to user2

--Then at this point, we still have one row in SYSTABLEPERMS for t1 and user2 and that row has both SELECTPRIV and INSERTPRIV set to true.

user2 connects

create table t2

create insert trigger tr1 on t2 which inserts into user1.t1

 All the table level privileges are kept in

-- trigger above depends on INSERTPRIV on user1.t1 and dependency manager keeps track of that dependency by inserting a row for triggerdescriptor depending on permissiondescriptor(the uuid column in SYSTABLEPERMS). Notice that there is no differentiation here which says that the trigger depends only on the INSERTPRIV privilege type on table t1. We just know that trigger is dependent on SOME privilege type on table t1

user1 connects

revoke select on t1 from user2

-- at this point, only view user2.v1 should get dropped because it depends on the SELECTPRIV on t1. But dependency manager has no way to know that user2.v1 needs only SELECTPRIV and hence only object affected by this revoke in user2.v1 and there should be no impact on user2.tr2. That's because user2.tr2 needs INSERTPRIV on user1.t1 and that is still in place

 

So, my question is how can I fine tune the dependency manager to know that user2.v1 needs SELECTPRIV on user1.t1? And hence, revoke above should result in dropping user2.v1 but should not touch user2.tr2

 

One solution that comes to my mind is to have one row for each privilege type for a given user in SYSTABLEPERMS. ie in the example above

grant select on t1 to user2

--There should be one row in SYSTABLEPERMS  for t1 and user2 with a privilege type column set to S(for select)

grant insert on t1 to user2

--There should be another row in SYSTABLEPERMS for t1 and user2 with a privilege type column set to I(for insert). ie at the end of the above 2 grant statements, there should be rows in SYSTABLEPERMS for table t1 and user user2.

 

This is what we do for column level privileges in SYSCOLPERMS. That is, there is one row for every type of privilege that is granted on columns level for a given user. If we did the same thing for SYSTABLEPERMS, it will solve my problem. Maybe there is another way to solve the table level privilege type dependency which I am overlooking and would appreciate any suggestion from the community.

 

thanks,

Mamta