G-1120

with
   upriv as (
      -- system privileges granted to users
      select u.username, p.privilege as priv
        from dba_sys_privs p
       inner join dba_users u
          on u.username = p.grantee
      union all
      -- roles granted to users
      select u.username, p.granted_role as priv
        from dba_role_privs p
       inner join dba_users u
          on u.username = p.grantee
   ),
   rpriv as (
      -- roles without parent (=roots)
      select role as priv, null as parent_priv
        from dba_roles
       where role not in (
                select rrp.granted_role
                  from role_role_privs rrp
             )
      union all
      -- roles granted to roles
      select granted_role as priv,
             grantee as parent_priv
        from dba_role_privs
      union all
      -- system privileges granted to roles
      select privilege as priv,
             grantee as parent_priv
        from dba_sys_privs
       where grantee in (
                select r.role
                  from dba_roles r
             )
   ),
   rtree as (
      -- provides priv_path and is_leaf for every role/priv in the hierarchy
      select priv,
             parent_priv,
             sys_connect_by_path(priv, '/') || '/' as priv_path,
             case
                when connect_by_isleaf = '0' then
                   0
                else
                   1
             end as is_leaf
        from rpriv
     connect by prior priv = parent_priv
   ),
   rgraph as (
      -- provides per priv all its possible parent privs or in other words
      -- provides per parent priv all its possible child privs
      -- this allows to join with parent_priv to get all privs
      select priv,
             substr(priv_path, 2, instr(priv_path, '/', 2) - 2) as parent_priv,
             is_leaf
        from rtree
   ),
   ugraph as (
      -- extends the rgraph by users and their privs
      -- distinct is required since a priv can be part of multiple roles
      select distinct
             upriv.username,
             rgraph.priv,
             rgraph.is_leaf
        from upriv
        join rgraph
          on upriv.priv = rgraph.parent_priv
   )
-- main
select username || '.' || priv as identifier,
       'Connect user ' || username || ' has ' || priv || ' privilege.' as message
  from ugraph
 where username in (#ConnectUsers#)
   and priv not in (#AllowedSystemPrivileges#)
   and is_leaf = 1
   and priv not in (select r.role from dba_roles r)
 order by identifier