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-3930) SQL roles: Add VTI for CONTAINED_ROLES
Date Wed, 29 Oct 2008 13:19:44 GMT

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


Mime
View raw message