You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Dag H. Wanvik (JIRA)" <ji...@apache.org> on 2008/10/29 14:05:44 UTC
[jira] Created: (DERBY-3930) SQL roles: Add VTI for CONTAINED_ROLES
SQL roles: Add VTI for CONTAINED_ROLES
--------------------------------------
Key: DERBY-3930
URL: https://issues.apache.org/jira/browse/DERBY-3930
Project: Derby
Issue Type: Sub-task
Reporter: Dag H. Wanvik
Assignee: Dag H. Wanvik
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3930) SQL roles: Add VTI for
CONTAINED_ROLES
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3930?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12643511#action_12643511 ]
Dag H. Wanvik commented on DERBY-3930:
--------------------------------------
Thanks for looking at this, Rick!
The "ij_show*" tests are driven from the JUnit test ToolScripts.java
I guess since this is partly ij tests, we do need to look at the output vs the canon.
Granted, the VTIs could be tested without using ij,
but the rest of the test (show [*_]roles) could not. It was just easier
to extend ToolScripts test with authorization (so that we can test the VTIs meaningfully)
than to extend SysDiagVTIMappingTest with authorization and users.
Do you think this acceptable?
> SQL roles: Add VTI for CONTAINED_ROLES
> --------------------------------------
>
> Key: DERBY-3930
> URL: https://issues.apache.org/jira/browse/DERBY-3930
> Project: Derby
> Issue Type: Sub-task
> Reporter: Dag H. Wanvik
> Assignee: Dag H. Wanvik
> Attachments: derby-3930-1.diff, derby-3930-1.stat
>
>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3930) SQL roles: Add VTI for
CONTAINED_ROLES
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3930?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12643483#action_12643483 ]
Dag H. Wanvik commented on DERBY-3930:
--------------------------------------
Notice that the result contains role names that are in case normal form (useful when matching against dictionary as in the shown example), but the argument is a SQL identifier (not CNF).
> SQL roles: Add VTI for CONTAINED_ROLES
> --------------------------------------
>
> Key: DERBY-3930
> URL: https://issues.apache.org/jira/browse/DERBY-3930
> Project: Derby
> Issue Type: Sub-task
> Reporter: Dag H. Wanvik
> Assignee: Dag H. Wanvik
> Attachments: derby-3930-1.diff, derby-3930-1.stat
>
>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3930) SQL roles: Add VTI for
CONTAINED_ROLES
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3930?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12643515#action_12643515 ]
Rick Hillegas commented on DERBY-3930:
--------------------------------------
Thanks for the explanation, Dag. This sounds ok to me.
> SQL roles: Add VTI for CONTAINED_ROLES
> --------------------------------------
>
> Key: DERBY-3930
> URL: https://issues.apache.org/jira/browse/DERBY-3930
> Project: Derby
> Issue Type: Sub-task
> Reporter: Dag H. Wanvik
> Assignee: Dag H. Wanvik
> Attachments: derby-3930-1.diff, derby-3930-1.stat
>
>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3930) SQL roles: Add VTI for
CONTAINED_ROLES
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3930?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12643481#action_12643481 ]
Dag H. Wanvik commented on DERBY-3930:
--------------------------------------
It can be useful to access the dictionary to see information about
what objects depend on granted roles (triggers, constraints,
views). An IDE might find it useful to show the user what roles
are contained in a given role (the grant closure). Since Derby does
not implement recursive queries, this cannot be done in a single
select without a VTI to get the closure. This issue tracks adding
SYSCS_DIAG.CONTAINED_ROLES. I propose to make a VTI with the following
API:
SYSCS_DIAG.CONTAINED_ROLES(<role identifier string> [, <integer: if != 0, compute inverse])
It will be inclusive, i.e. the given role will be part of the result
set. As an example, the following ij query would show all persistent
objects that rely on on a role:
prepare find_role_dependents as 'select objecttype, schemaname, tablename, name from
(
-- show all constraints that depend on role, directly or indirectly
select ''VIEW'', cast(s.schemaname as varchar(20)), cast(t.tablename as varchar(20)),
cast(null as varchar(20)), r.roleid from
sys.sysdepends d,
sys.sysroles r,
sys.systables t,
sys.sysschemas s where
r.isdef=''Y'' and
r.uuid=d.providerid and t.tableid=d.dependentid and
s.schemaid=t.schemaid
UNION
-- show all constraints that depend on role, directly or indirectly
select ''CONSTRAINT'', cast(s.schemaname as varchar(20)), cast(tab.tablename as varchar(20)),
cast(t.constraintname as varchar(20)), r.roleid from
sys.sysdepends d,
sys.sysroles r,
sys.sysconstraints t,
sys.systables tab,
sys.sysschemas s where
r.isdef=''Y'' and
r.uuid=d.providerid and t.constraintid=d.dependentid and
s.schemaid=t.schemaid and
tab.tableid = t.tableid
UNION
-- show all triggers that depend on role, directly or indirectly
select ''TRIGGER'', cast(s.schemaname as varchar(20)), cast(tab.tablename as varchar(20)),
cast(t.triggername as varchar(20)), r.roleid from
sys.sysdepends d,
sys.sysroles r,
sys.systriggers t,
sys.systables tab,
sys.sysschemas s where
r.isdef=''Y'' and
r.uuid=d.providerid and t.triggerid=d.dependentid and
s.schemaid=t.schemaid and
tab.tableid = t.tableid
) dt(objecttype, schemaname, tablename, name, roleid)
WHERE dt.roleid in (select roleid from table(syscs_diag.contained_roles(?, 1))rt)';
> SQL roles: Add VTI for CONTAINED_ROLES
> --------------------------------------
>
> Key: DERBY-3930
> URL: https://issues.apache.org/jira/browse/DERBY-3930
> Project: Derby
> Issue Type: Sub-task
> Reporter: Dag H. Wanvik
> Assignee: Dag H. Wanvik
>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Closed: (DERBY-3930) SQL roles: Add VTI for CONTAINED_ROLES
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3930?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik closed DERBY-3930.
--------------------------------
Resolution: Fixed
Committed derby-3930-2 as svn 709266, closing.
> SQL roles: Add VTI for CONTAINED_ROLES
> --------------------------------------
>
> Key: DERBY-3930
> URL: https://issues.apache.org/jira/browse/DERBY-3930
> Project: Derby
> Issue Type: Sub-task
> Reporter: Dag H. Wanvik
> Assignee: Dag H. Wanvik
> Attachments: derby-3930-1.diff, derby-3930-1.stat, derby-3930-2.diff, derby-3930-2.stat
>
>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-3930) SQL roles: Add VTI for CONTAINED_ROLES
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3930?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas updated DERBY-3930:
---------------------------------
Attachment: derby-3930-3-buildJars.diff
Attaching and committing derby-3930-3-buildJars.diff. This fixes an error which I saw when building the jars. Touches the following files:
M tools/jar/extraDBMSclasses.properties
Replaces EnabledRoles with ContainedRoles in the list of extra classes which should be pulled into derby.jar.
> SQL roles: Add VTI for CONTAINED_ROLES
> --------------------------------------
>
> Key: DERBY-3930
> URL: https://issues.apache.org/jira/browse/DERBY-3930
> Project: Derby
> Issue Type: Sub-task
> Reporter: Dag H. Wanvik
> Assignee: Dag H. Wanvik
> Attachments: derby-3930-1.diff, derby-3930-1.stat, derby-3930-2.diff, derby-3930-2.stat, derby-3930-3-buildJars.diff
>
>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-3930) SQL roles: Add VTI for CONTAINED_ROLES
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3930?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik updated DERBY-3930:
---------------------------------
Attachment: derby-3930-1.stat
derby-3930-1.diff
Uploading a patch for this, and tests. Regressions (sane jars) ran ok.
> SQL roles: Add VTI for CONTAINED_ROLES
> --------------------------------------
>
> Key: DERBY-3930
> URL: https://issues.apache.org/jira/browse/DERBY-3930
> Project: Derby
> Issue Type: Sub-task
> Reporter: Dag H. Wanvik
> Assignee: Dag H. Wanvik
> Attachments: derby-3930-1.diff, derby-3930-1.stat
>
>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3930) SQL roles: Add VTI for
CONTAINED_ROLES
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3930?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12643514#action_12643514 ]
Kim Haase commented on DERBY-3930:
----------------------------------
Would this involve a documentation change, specifically to http://db.apache.org/derby/docs/dev/ref/rrefsyscsdiagtables.html? If so, either it could be added to the DERBY-2207 spec, or one of us can file a separate JIRA.
> SQL roles: Add VTI for CONTAINED_ROLES
> --------------------------------------
>
> Key: DERBY-3930
> URL: https://issues.apache.org/jira/browse/DERBY-3930
> Project: Derby
> Issue Type: Sub-task
> Reporter: Dag H. Wanvik
> Assignee: Dag H. Wanvik
> Attachments: derby-3930-1.diff, derby-3930-1.stat
>
>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3930) SQL roles: Add VTI for
CONTAINED_ROLES
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3930?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12643532#action_12643532 ]
Kim Haase commented on DERBY-3930:
----------------------------------
Thank you, Dag -- looks like most of the info I would need is in this JIRA (3930), so I can work from this.
> SQL roles: Add VTI for CONTAINED_ROLES
> --------------------------------------
>
> Key: DERBY-3930
> URL: https://issues.apache.org/jira/browse/DERBY-3930
> Project: Derby
> Issue Type: Sub-task
> Reporter: Dag H. Wanvik
> Assignee: Dag H. Wanvik
> Attachments: derby-3930-1.diff, derby-3930-1.stat
>
>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-3930) SQL roles: Add VTI for CONTAINED_ROLES
Posted by "Myrna van Lunteren (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3930?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Myrna van Lunteren updated DERBY-3930:
--------------------------------------
Fix Version/s: 10.5.0.0
> SQL roles: Add VTI for CONTAINED_ROLES
> --------------------------------------
>
> Key: DERBY-3930
> URL: https://issues.apache.org/jira/browse/DERBY-3930
> Project: Derby
> Issue Type: Sub-task
> Reporter: Dag H. Wanvik
> Assignee: Dag H. Wanvik
> Fix For: 10.5.0.0
>
> Attachments: derby-3930-1.diff, derby-3930-1.stat, derby-3930-2.diff, derby-3930-2.stat, derby-3930-3-buildJars.diff
>
>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3930) SQL roles: Add VTI for
CONTAINED_ROLES
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3930?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12644063#action_12644063 ]
Rick Hillegas commented on DERBY-3930:
--------------------------------------
Hi Dag,
The second patch looks simpler and more elegant. +1
> SQL roles: Add VTI for CONTAINED_ROLES
> --------------------------------------
>
> Key: DERBY-3930
> URL: https://issues.apache.org/jira/browse/DERBY-3930
> Project: Derby
> Issue Type: Sub-task
> Reporter: Dag H. Wanvik
> Assignee: Dag H. Wanvik
> Attachments: derby-3930-1.diff, derby-3930-1.stat, derby-3930-2.diff, derby-3930-2.stat
>
>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3930) SQL roles: Add VTI for
CONTAINED_ROLES
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3930?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12643518#action_12643518 ]
Dag H. Wanvik commented on DERBY-3930:
--------------------------------------
Hi Kim, just uploaded version 10 of the spec to cover this addition.
> SQL roles: Add VTI for CONTAINED_ROLES
> --------------------------------------
>
> Key: DERBY-3930
> URL: https://issues.apache.org/jira/browse/DERBY-3930
> Project: Derby
> Issue Type: Sub-task
> Reporter: Dag H. Wanvik
> Assignee: Dag H. Wanvik
> Attachments: derby-3930-1.diff, derby-3930-1.stat
>
>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3930) SQL roles: Add VTI for
CONTAINED_ROLES
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3930?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12643489#action_12643489 ]
Rick Hillegas commented on DERBY-3930:
--------------------------------------
Hi Dag,
Thanks for the patch. It looks good to me. I noticed something which escaped my attention before: The is_show_roles_usr test is a recently added canon-based test. Is there any plan to replace this with a JUnit test?
Thanks,
-Rick
> SQL roles: Add VTI for CONTAINED_ROLES
> --------------------------------------
>
> Key: DERBY-3930
> URL: https://issues.apache.org/jira/browse/DERBY-3930
> Project: Derby
> Issue Type: Sub-task
> Reporter: Dag H. Wanvik
> Assignee: Dag H. Wanvik
> Attachments: derby-3930-1.diff, derby-3930-1.stat
>
>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3930) SQL roles: Add VTI for
CONTAINED_ROLES
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3930?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12644311#action_12644311 ]
Dag H. Wanvik commented on DERBY-3930:
--------------------------------------
Thanks, Rick!
> SQL roles: Add VTI for CONTAINED_ROLES
> --------------------------------------
>
> Key: DERBY-3930
> URL: https://issues.apache.org/jira/browse/DERBY-3930
> Project: Derby
> Issue Type: Sub-task
> Reporter: Dag H. Wanvik
> Assignee: Dag H. Wanvik
> Attachments: derby-3930-1.diff, derby-3930-1.stat, derby-3930-2.diff, derby-3930-2.stat, derby-3930-3-buildJars.diff
>
>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-3930) SQL roles: Add VTI for CONTAINED_ROLES
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3930?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik updated DERBY-3930:
---------------------------------
Attachment: derby-3930-2.stat
derby-3930-2.diff
Uploading version 2 of this patch, which
also removes SYSCS_DIAG.ENABLED_ROLES in favor of just
SYSCS_DIAG.CONTAINED_ROLES, since the former is equivalent to
SYSCS_DIAG.CONTAINED_ROLES(CURRENT_ROLE). To keep
API economy, I think it is better to make do with just the one VTI.
The ij command "show enabled_roles" is retained, but its implementation
now uses SYSCS_DIAG.CONTAINED_ROLES instead.
> SQL roles: Add VTI for CONTAINED_ROLES
> --------------------------------------
>
> Key: DERBY-3930
> URL: https://issues.apache.org/jira/browse/DERBY-3930
> Project: Derby
> Issue Type: Sub-task
> Reporter: Dag H. Wanvik
> Assignee: Dag H. Wanvik
> Attachments: derby-3930-1.diff, derby-3930-1.stat, derby-3930-2.diff, derby-3930-2.stat
>
>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.