Hi!

Connected as user x i like to know which rights user x has, related to all
objects in the database and what right is needed to create a user?

Greetings, Ralf

Ralf Schnickmann wrote:

> Hi!
>
> Connected as user x i like to know which rights user x has, related to all
> objects in the database and what right is needed to create a user?
>
> Greetings, Ralf

USER_SYS_PRIVS
USER_TAB_PRIVS
USER_TAB_PRIVS_RECD
USER_COL_PRIVS
USER_COL_PRIVS_RECD
USER_ROLE_PRIVS

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
http://www.outreach.washington.edu/extinfo/certprog/aoa/aoa_main.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

So I created this query:

SELECT TABLE_NAME, OWNER, PRIVILEGE
FROM USER_TAB_PRIVS
UNION
SELECT TABLE_NAME, TABLE_SCHEMA, PRIVILEGE
FROM USER_ROLE_PRIVS, ALL_TAB_PRIVS
WHERE GRANTED_ROLE = GRANTEE

In sql+ or in toad in can start this query correctly and it returns several
rows.

But starting this query in my c# application i got no rows returned.
The intresting thing is that i can start these two SELECT statements
seperatly with the correct result.
Only the union of these statements fails... no errors but no rows
returned...

Any ideas?

Geetings,
Ralf


"Daniel Morgan" <damorgan@exxesolutions.com> schrieb im Newsbeitrag
news:3F4D07F1.2718B2C3@exxesolutions.com...
> Ralf Schnickmann wrote:
>
> > Hi!
> >
> > Connected as user x i like to know which rights user x has, related to
all
> > objects in the database and what right is needed to create a user?
> >
> > Greetings, Ralf
>
> USER_SYS_PRIVS
> USER_TAB_PRIVS
> USER_TAB_PRIVS_RECD
> USER_COL_PRIVS
> USER_COL_PRIVS_RECD
> USER_ROLE_PRIVS
>
> --
> Daniel Morgan
> http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
> http://www.outreach.washington.edu/extinfo/certprog/aoa/aoa_main.asp
> damorgan@x.washington.edu
> (replace 'x' with a 'u' to reply)
>
>

Hi

You can use a script i created called find_all_privs.sql (it is
available from my site http://www.petefinnigan.com on the tools page) to
see all privileges granted to a user including those granted via a role.
The output is hierarchical. An example is as follows for the user
DBSNMP:

NAME OF USER TO CHECK   [ORCL]: DBSNMP
OUTPUT METHOD           [S/F]: S
FILE NAME FOR OUTPUT   [priv.lst]: 
OUTPUT DIRECTORY          [/tmp]: 

old 162:        lv_file_or_screen:='&&output_method';
new 162:        lv_file_or_screen:='S';
old 164:                open_file('&&file_name','&&output_dir');
new 164:                open_file('priv.lst','/tmp');
old 166:        get_privs('&&user_to_find',lv_tabs);
new 166:        get_privs('DBSNMP',lv_tabs);
....USER => DBSNMP has ROLE CONNECT which contains =>
.......SYS PRIV =>ALTER SESSION grantable => NO
.......SYS PRIV =>CREATE CLUSTER grantable => NO
.......SYS PRIV =>CREATE DATABASE LINK grantable => NO
.......SYS PRIV =>CREATE SEQUENCE grantable => NO
.......SYS PRIV =>CREATE SESSION grantable => NO
.......SYS PRIV =>CREATE SYNONYM grantable => NO
.......SYS PRIV =>CREATE TABLE grantable => NO
.......SYS PRIV =>CREATE VIEW grantable => NO
....SYS PRIV =>CREATE USER grantable => NO
....SYS PRIV =>SELECT ANY DICTIONARY grantable => NO

PL/SQL procedure successfully completed.

SQL> 

To do it as the connected user use the USER_% views, such as
user_sys_privs, user_role_privs and user_tab_privs etc.

The privilege needed to create a user is "create user".

hth

kind regards

Pete
-- 
Pete Finnigan
email:pete@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.