Hi all,

I need a query that will simply return all Oracle role/permissions for
all object in a database. (IE do they have SELECT, UPDATE, DELETE etc
etc permissions.

Thanks,
Derek Smigelski

"Derek Smigelski" <derek.smigelski@bestsoftware.com> wrote in message
news:820e9de3.0409030640.69a058d5@posting.google.c om...
| Hi all,
|
| I need a query that will simply return all Oracle role/permissions for
| all object in a database. (IE do they have SELECT, UPDATE, DELETE etc
| etc permissions.
|
| Thanks,
| Derek Smigelski


look at dba_tab_privs

++ mcs

Mark C. Stock wrote:

> "Derek Smigelski" <derek.smigelski@bestsoftware.com> wrote in message
> news:820e9de3.0409030640.69a058d5@posting.google.c om...
> | Hi all,
> |
> | I need a query that will simply return all Oracle role/permissions for
> | all object in a database. (IE do they have SELECT, UPDATE, DELETE etc
> | etc permissions.
> |
> | Thanks,
> | Derek Smigelski
>
>
> look at dba_tab_privs
>
> ++ mcs

Understanding that if someone also has, for example,
SELECT ANY TABLE
there will be no related records in DBA_TAB_PRIVS.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)

"Daniel Morgan" <damorgan@x.washington.edu> wrote in message
news:1094255690.866056@yasure...
| Mark C. Stock wrote:
|
| > "Derek Smigelski" <derek.smigelski@bestsoftware.com> wrote in message
| > news:820e9de3.0409030640.69a058d5@posting.google.c om...
| > | Hi all,
| > |
| > | I need a query that will simply return all Oracle role/permissions for
| > | all object in a database. (IE do they have SELECT, UPDATE, DELETE etc
| > | etc permissions.
| > |
| > | Thanks,
| > | Derek Smigelski
| >
| >
| > look at dba_tab_privs
| >
| > ++ mcs
|
| Understanding that if someone also has, for example,
| SELECT ANY TABLE
| there will be no related records in DBA_TAB_PRIVS.
| --
| Daniel A. Morgan
| University of Washington
| damorgan@x.washington.edu
| (replace 'x' with 'u' to respond)
|

in which case you will also need to look at DBA_SYS_PRIVS for users with
various '%ANY TABLE' privs (and perhaps also the 'EXECUTE ANY%' privs)

(good catch daniel)

plus, if you're trying to determine what object privs particular users have,
you'll also want to take a look at what roles they have, and they determine
what privs whose roles confer to them -- so likely you'll also be looking at
DBA_ROLE_PRIVS

++ mcs

derek.smigelski@bestsoftware.com (Derek Smigelski) wrote in message news:<820e9de3.0409030640.69a058d5@posting.google. com>...
> Hi all,
>
> I need a query that will simply return all Oracle role/permissions for
> all object in a database. (IE do they have SELECT, UPDATE, DELETE etc
> etc permissions.
>
> Thanks,
> Derek Smigelski

Derek,

I have once written a script that recursively (that is also via roles)
lists the object privileges. You find it on my homepage at:

http://www.adp-gmbh.ch/ora/misc/recu...privilege.html

(Named Object Privileges)


hth

Rene

--
Rene Nyffenegger
www.adp-gmbh.ch

Hi Derek,

Try my script find_all_privs.sql that is available on my tools page
http://www.petefinnigan.com/tools.htm that recursively lists all
privileges for a user for roles, system privileges and object
privileges.

hth

kind regards

Pete
--
Pete Finnigan
emailete@petefinnigan.com
Web site: http://www.petefinnigan.com - Oracle security audit specialists
Book:Oracle security step-by-step Guide - see http://store.sans.org for details.