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)?
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
