Re: Create view from DBA_ROLES for non-privileged users?

dananrg_at_yahoo.com wrote in news:b754c633-bfa5-4437-b9f7-e32b90b40809@ 27g2000hsf.googlegroups.com:

> Is it possible to create a view based off DBA_ROLES so that a non-DBA
> user can keep track of which users belong to roles associated with his
> department? In this case, all roles associated with the specific
> department are named with a common prefix, e.g. ABC_<rest of role
> name>.
>
> I'm thinking of something like the following:
>
> CREATE VIEW ABC_USER_ROLES as
> SELECT USERNAME, GRANTED_ROLE
> FROM DBA_ROLES
> WHERE GRANTED_ROLE LIKE '%ABC_%';
>
> Is there any technical or security issue in creating a view like this
> for the user.
>
> It would be a time-saver for all concerned to have the view (vs.
> generating static reports of what users are in which roles).

My reaction is that a stored procedure owned by some appropriately privileged schema that could be invoked by PUBLIC might be a better choice. Received on Thu Jun 12 2008 - 20:18:12 CDT