Toad World® Forums

Is there any way to print or extract the privs of a role with embedded roles?

I'd like to see a create script on an existing role not only show me the embedded roles but the list of privs in each 1. when I choose editor as script I see an option to list grants to the role and this is checked. Why not have another option to allow me to check grants to all roles?

The closest we get to that are the "system privileges" and "object grants" tabs in the right hand side of the schema browser. That shows privs directly granted, as well as those granted through roles. It's just that object privs and system privs are not combined into a single display.

When I need a full, recursive, list of all privs granted to a user, I run this script: http://www.petefinnigan.com/find_all_privs.sql

Pete Finnegan, the author, says "This short script can be used to find all of the privileges granted to a particular user. It includes Roles, system privileges and object privileges. If a role is encountered then it recursively looks for the roles, system privileges and object privileges granted to the roles and so on.....
The output can be directed to either the 'S'creen or to a 'F'ile. This is prompted for at run time. If a 'F'ile is chosen then a file name and output directory are needed. If 'F'ile is chosen then the directory used needs to be enabled via utl_file_dir prior to 9iR2 and with a directory object after that."

I might have had a small hand in it too, back in the day! :wink:

HTH

Cheers,
Norm.

1 Like