[
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.
