db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Hiranya Jayathilaka <hiranya...@gmail.com>
Subject Re: Roles Support for dblook
Date Tue, 28 Jul 2009 12:21:27 GMT
Hi Rick,

Thanks for the great explanation and the sample script. It totally helped me
to get a clear picture of the requirements. See my comments inline.

On Mon, Jul 27, 2009 at 7:31 PM, Rick Hillegas <Richard.Hillegas@sun.com>wrote:

> Hi Hiranya,
>
> Thanks for continuing to push this project forward. Some comments inline...
>
> Hiranya Jayathilaka wrote:
>
>> Hi Rick,
>>
>> Thanks for the explanation. See my comments inline.
>>
>> On Mon, Jul 20, 2009 at 9:45 PM, Rick Hillegas <Richard.Hillegas@sun.com<mailto:
>> Richard.Hillegas@sun.com>> wrote:
>>
>>    Hiranya Jayathilaka wrote:
>>
>>        One more question.
>>        We initially intended to use a dependency graph to capture the
>>        dependencies among different roles. But looking at the
>>        SYSROLES table and the existing role handling code I'm not
>>        sure whether we really need that. Isn't creating all the roles
>>        in one swoop and then performing all the grants (also recorded
>>        in SYSROLES table) sufficient? I can't seem to realize how
>>        this will break any inter-dependencies among roles.
>>
>>    Hi Hiranya,
>>
>>    I think that the creating of roles and granting of roles to roles
>>    can all be done at once.
>>
>> +1. This is handled by the existing code as well (According to DERBY-3877
>> it was implemented by Dag). So I should be able to reuse it as it is. All
>> the role creation and granting of roles will be carried out as the DBO.
>>
>>    It is the granting of privileges to the roles that has to be
>>    interleaved with the creation of other objects.
>>
>> I'm a little bit confused about this requirement. From the discussion in
>> DERBY-3877 I got the impression that if all the objects are created by its
>> actual owner with the proper roles set, then object should get created
>> correctly with all dependencies intact. Isn't that sufficient?
>>
> Or the confusion may be mine. To help us understand one another, I am
> attaching a script. In this script:
>
> 1) the select_role is granted select privilege on table t1
> 2) alice sets herself to select_role in order to create a view on t1
>
> At the end of the script, I revoke the select privilege from the role. This
> causes alice's view to be dropped. I am including this step because we may
> need to explore this issue later on. For the moment, assume that the last
> set of actions by the administrator don't happen. That is, lop off the end
> of the script so that the view is not dropped and it must be recreated by
> dblook.
>
> Here is what I don't understand about your proposal: In the dblook script,
> where did you envision doing step (1)?


OK I think I get it now. I have already implemented the logic to perform all
the grants related to objects soon after each object is created. For example
as soon as a table is created all the permission grants related to that
table will be carried out. I fetch the necessary information from
SYSTABLEPERMS and SYSCOLPERMS tables to do this. What I didn't realize
earlier is that these grant operations also take care of granting
permissions to roles. So since my code already handles this I think I just
need to focus on writing the logic to create the roles at the begining and
then set the roles as necessary in the middle of the script.


>
> Use this command to run the script:
>
> java -\
>  -Dderby.connection.requireAuthentication=true  \
>  -Dderby.authentication.provider=BUILTIN \
>  -Dderby.user.admin=adminpassword \
>  -Dderby.user.alice=alicepassword \
>  -Dderby.user.ruth=ruthpassword \
>  -Dderby.database.sqlAuthorization=true \
>  org.apache.derby.tools.ij script.sql
>
> Here is script.sql:
>
> --
> -- Original script to create a view which depends on a role grant.
> --
> connect
> 'jdbc:derby:memory:temp;create=true;user=admin;password=adminpassword' as
> admin_conn;
>
> create role select_role;
>
> grant select_role to ruth;
> grant select_role to alice;
>
> connect 'jdbc:derby:memory:temp;user=ruth;password=ruthpassword' as
> ruth_conn;
>
> create table t1( a int );
> insert into t1( a ) values ( 1 );
>
> connect 'jdbc:derby:memory:temp;user=alice;password=alicepassword' as
> alice_conn;
>
> -- fails because alice does not have select privilege on t1
> create view v1 ( a ) as select * from ruth.t1;
>
> set connection ruth_conn;
>
> grant select on t1 to select_role;
>
> set connection alice_conn;
>
> set role select_role;
>
> -- now works because alice has set herself to a role which
> -- has the correct privilege
> create view v1 ( a ) as select * from ruth.t1;
>
> set role none;
>
> --
> -- This is the optional end of the script.
> --
> set connection admin_conn;
>
> -- as a super user, the admin can select from the view
> select * from alice.v1;
>
> -- now revoke alice's rights. this drops the view.
> revoke select_role from alice;
>
> -- note that the view has been dropped
> select * from alice.v1;
>
>
>  Do I need to explicitly grant privileges to roles at different stages of
>> the script? Where in the system tables these privileges are recorded so I
>> can retrieve them in dblook?
>>
>>  The privileges (including grants to roles) are recorded in a number of
> system tables: SYSCOLPERMS, SYSROUTINEPERMS, SYSTABLEPERMS. In all of these
> tables, the grantee could be a role or a user.


+1. I think this is what lead to my confusion earlier. Thanks for clarifying
this.

Thanks,
Hiranya


>
>
> Thanks,
> -Rick
>



-- 
Hiranya Jayathilaka
Software Engineer;
WSO2 Inc.;  http://wso2.org
E-mail: hiranya@wso2.com;  Mobile: +94 77 633 3491
Blog: http://techfeast-hiranya.blogspot.com

Mime
View raw message