Re: How can I get enabled roles list ?

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Wed, 3 Nov 1999 16:43:28 +0100
Message-ID: <7vpl9r$62u$1@oceanite.cybercable.fr>

You can use the sys tables directly, something like:

select lpad(' ',2*(r.lvl-1))||u2.name
from sys.user$ u2,

     (select level lvl, sa.privilege# priv
      from sys.sysauth$ sa
      connect by sa.grantee# = prior sa.privilege#
      start with sa.grantee# in
         ( select u1.user# from sys.user$ u1 where u1.name = user )) r

where r.priv = u2.user#
/

LPAD('',2*(R.LVL-1))||U2.NAME

---

DBA
  EXP_FULL_DATABASE
  IMP_FULL_DATABASE
  MONITORER 4 rows selected.

--
Have a nice day
Michel

Igor V. Podolsky <igoryok_at_soft-review.kiev.ua> a écrit dans le message : ABpP48uyWB_at_soft-review.kiev.ua...
> Hi all !
>
> Can anyone give me an answer for the following quesion:
> I need to get a full list of roles, enabled for an user or role, including
> roles that granted directly or via another roles in role hierarchy as well. I
> need something about
>
> select *
> from
> dba_role_privs
> start with grantee = user
> connect by prior grantee = granted_role
>
> But it doesn't work. It causes 'ORA-01472: cannot use CONNECT BY on view with
> DISTINCT, GROUP BY, etc.'. Any ideas ?
>
> P.S. Duplicate answers ( if any :( ) to my e-mail please...
>
> --
> Is There A God Or Any Kind Of Justice Under The Sky... (Queen'91)
>
> Igor V. Podolsky (igoryok_at_soft-review.kiev.ua)
>
>
Received on Wed Nov 03 1999 - 09:43:28 CST