Create view from DBA_ROLES for non-privileged users?

[dan...@yahoo.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).

[dan...@yahoo.com]

Anyone?

[Ana C. Dent]

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

My reaction is that a stored procedure owned by some appropriately
privileged schema that could be invoked by PUBLIC might be a better choice.

[dan...@yahoo.com]

On Jun 12, 9:18 pm, "Ana C. Dent" <anaced...@hotmail.com> wrote:
> My reaction is that a stored procedure owned by some appropriately

> privileged schema that could be invoked by PUBLIC might be a better choice.- Hide quoted text -

Thanks Ana. They prefer to not use stored procedures in my
environment, but I will look into it.
Any other thoughts about the original approach? Why specifically might
your solution be preferable?

Thanks again.

Dana

[gazzag]

On 13 Jun, 13:02, dana...@yahoo.com wrote:
> Thanks Ana. They prefer to not use stored procedures in my
> environment, but I will look into it.

Out of interest, why?

> Any other thoughts about the original approach? Why specifically might
> your solution be preferable?
>
> Thanks again.
>
> Dana

-g

[dan...@yahoo.com]

On Jun 13, 9:10 am, gazzag <gar...@jamms.org> wrote:
> On 13 Jun, 13:02, dana...@yahoo.com wrote:
>
> > Thanks Ana. They prefer to not use stored procedures in my
> > environment, but I will look into it.
>
> Out of interest, why?

I believe it has something to do with:

1) A standard from higher up in the org to keep all business logic, as
far as possible, out of the data tier and on an application tier

2) A standard from higher up in the org to lessen vendor lock-in by
reducing the use of vendor-specific database features.

They're not my standards and it's all highly debatable.

Dana