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 <[email protected]>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 >> <[email protected]<mailto: >> [email protected]>> 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: [email protected]; Mobile: +94 77 633 3491 Blog: http://techfeast-hiranya.blogspot.com
