Re: Nested roles - PL/SQL proc

From: Jeff Bock <jeffbb88_at_hotmail.com>
Date: 4 Jun 2003 18:00:36 -0700
Message-ID: <5b8b1052.0306041700.72bf74b7@posting.google.com>

The link lists the following query:

create view user_role_hierarchy
as
select u2.name granted_role
  from ( select *

           from sys.sysauth$
        connect by prior privilege# = grantee#
          start with grantee# = uid or grantee# = 1) sa,
       sys.user$ u2

 where u2.user#=sa.privilege#
union all select user from dual
union all select 'PUBLIC' from dual
/

This view is similar to ROLE_ROLE_PRIVS in that it shows MY access level. I can modify it and replace uid with a query to get the uid number of a different user - but that user has to be logged in... That is why I need the recursive proc. To loop through DBA_ROLE_PRIVS and extract all the roles for any user.

• Jeff

"Oradba Linux" <oradba_linux_at_attbi.com> wrote in message news:<pan.2003.06.04.16.55.13.276912_at_attbi.com>...
> On Wed, 04 Jun 2003 07:15:20 +0000, Jeff Bock wrote:
>
> > Hi,
> >
> > I'm trying to determine what roles a user has been assigned (whether
> > directly or indirectly), and it seems the only proper way to do it is
> > through a recursive PL/SQL proc.
> >
> > I'm sure someone out there has already written one, and I would
> > appreciate it if you could post it.
> >
> > Many thanks,
> >
> > Jeff
>
> May be this link could help you ......
> http://asktom.oracle.com/pls/ask/f?p=4950:8:255024707240464514::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:853427230099,
Received on Wed Jun 04 2003 - 20:00:36 CDT