db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: Roles Support for dblook
Date Mon, 27 Jul 2009 14:01:36 GMT
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)?

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.

Thanks,
-Rick

Mime
View raw message