db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dag H. Wanvik (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-3877) SQL roles: build support for dblook
Date Thu, 25 Sep 2008 20:45:44 GMT

    [ https://issues.apache.org/jira/browse/DERBY-3877?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12634608#action_12634608

Dag H. Wanvik commented on DERBY-3877:

Thanks for looking at this patch, Rick!

> The description section of this JIRA says that you need
> multiple connections in order to faithfully recreate a database using
> the script generated by dblook. Is this problem introduced by roles or
> does the problem exist in earlier Derby versions like 10.4?

No, I was thinking of the situation after SQL authorization was added
(>=10.2).  I think many objects could be created by the DBO by
creating the schemas for other users with an explicit authorization
clause and then creating the objects from the DBO connection. The
objects would then belong to that user.

But there is still the question of objects that in the original db
would depend on privileges to other objects that are tracked using the
dependency system. For example, is user X creates a view X_view that
depends on a select privilege to a table belonging to a user Y, then I
think that view would need to be created by a connection with user
authentication set to X in order for the correct dependency to be
created and tracked (if the DBO creates this view, a privilege would
not be required I assume). If the select privilege is later revoked,
we woul dget the wrong behavior if this is not tracked; the view would
not be impacted.

Adding roles just adds more of the same problems. If the DBO had some
way of setting effective authentication id (think "su(1)") the dblook
script would be runnable from the dbo connection (although the order
in which operations would need to be performed needs a topological
sort: privilege grants and object creations need to be interleaved the
right way).

I can't find any existing tests for dblook and db with sqlAuthorization set.

> Thanks for the patch, Dag. One small hygiene comment:
>     - You may want to close rs before assigning it to a new query result

Thanks, will do.

> SQL roles: build support for dblook
> -----------------------------------
>                 Key: DERBY-3877
>                 URL: https://issues.apache.org/jira/browse/DERBY-3877
>             Project: Derby
>          Issue Type: Sub-task
>          Components: Tools
>            Reporter: Dag H. Wanvik
>            Assignee: Dag H. Wanvik
>             Fix For:
>         Attachments: derby-3877-1.diff, derby-3877-1.stat
> dblook does have support for grant/revoke although I am unsure of its quality, cf, my
> in DERBY-3868. Nevertheless, I choose to build support for roles for dblook.
> Since role creation and the granting of roles is only allowed for the data base owner
in the first basic
> roles implementation, expanding the existing dblook tests to just dump the roles and
roles grants should be easy.
> In general though, the dblook output file, in order to receate a database, must potentially
use several connection, so the objects will be owned by the original owner. [The model for
running the presently generated script run seems to be within one connection.] Setting roles
properly to be able to recreate a view with the correct owner, for example, would be part
of such a more advanced script which would be object owner aware.
> My intention with this issue is just to add support for dumping roles and role grants.

This message is automatically generated by JIRA.
You can reply to this email to add a comment to the issue online.

View raw message