Re: Nested roles - PL/SQL proc

From: Steve Howard <stephen.howard_at_us.pwcglobal.com>
Date: 5 Jun 2003 08:13:13 -0700
Message-ID: <6d8b7216.0306050713.321fd6f9@posting.google.com>

jeffbb88_at_hotmail.com (Jeff Bock) wrote in message news:<5b8b1052.0306040615.8f1bac6_at_posting.google.com>...
> 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

Hi Jeff,

If I understand you correctly, you want the following:

create role new_role;
grant dba to new_role;
create user t identified by t;
grant new_role to t;

You want to know if user t has the dba role and associated privileges indirectly granted to him? You can probably use a "connect by" using the dictionary views referenced earlier, other than the PL/SQL routine you mentioned.

No, I have never written this, but I can see it being useful. Received on Thu Jun 05 2003 - 10:13:13 CDT