OraFAQ Forum: General » How to find user with Insert/update privilage but not select

Home » Other » General » How to find user with Insert/update privilage but not select (Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to find user with Insert/update privilage but not select [message #532458]

Tue, 22 November 2011 15:25

OracleDBA2010
Messages: 16
Registered: November 2011
Location: Indianapolis

Junior Member

Does any one know, how to find a user who HAS INSERT and UPDATE privilage but DOES NOT have SELECT privilage?

Report message to a moderator

Re: How to find user with Insert/update privilage but not select [message #532460 is a reply to message #532458]

Tue, 22 November 2011 15:30

Littlefoot
Messages: 21756
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Try to query DBA_TAB_PRIVS.

Report message to a moderator

Re: How to find user with Insert/update privilage but not select [message #532463 is a reply to message #532458]

Tue, 22 November 2011 15:42

OracleDBA2010
Messages: 16
Registered: November 2011
Location: Indianapolis

Junior Member

I'm not getting the correct output from following query.

select * from dba_tab_privs where PRIVILEGE IN ('INSERT','UPDATE') union select * from dba_tab_privs where PRIVILEGE NOT IN ('SELECT')

Report message to a moderator

Re: How to find user with Insert/update privilage but not select [message #532464 is a reply to message #532463]

Tue, 22 November 2011 16:02

Barbara Boehmer
Messages: 8933
Registered: November 2002
Location: California, USA

Senior Member

You will need to modify the below for specific tables and/or include ANY.

select distinct grantee
from   dba_tab_privs t1
where  exists
       (select *
        from   dba_tab_privs t2
        where  t2.grantee = t1.grantee
        and    t2.privilege = 'INSERT')
and    exists
       (select *
        from   dba_tab_privs t3
        where  t3.grantee = t1.grantee
        and    t3.privilege = 'UPDATE')
and    not exists
       (select *
        from   dba_tab_privs t3
        where  t3.grantee = t1.grantee
        and    t3.privilege = 'SELECT');

Report message to a moderator

Re: How to find user with Insert/update privilage but not select [message #532567 is a reply to message #532463]

Wed, 23 November 2011 09:01

OracleDBA2010
Messages: 16
Registered: November 2011
Location: Indianapolis

Junior Member

Thank you for this information. How every when I run the query its just hanging.

(I want ALL users who has that access in a database, on ANY table)

Report message to a moderator

Re: How to find user with Insert/update privilage but not select [message #532570 is a reply to message #532567]

Wed, 23 November 2011 09:12

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

depending upon DB it takes a while to run

SQL> set time on
07:11:03 SQL> set echo on term on
07:11:09 SQL> select distinct grantee
from   dba_tab_privs t1
where  exists
       (select *
        from   dba_tab_privs t2
        where  t2.grantee = t1.grantee
        and    t2.privilege = 'INSERT')
and    exists
       (select *
        from   dba_tab_privs t3
        where  t3.grantee = t1.grantee
        and    t3.privilege = 'UPDATE')
and    not exists
       (select *
        from   dba_tab_privs t3
        where  t3.grantee = t1.grantee
        and    t3.privilege = 'SELECT');
07:11:10   2  07:11:10   3  07:11:10   4  07:11:10   5  07:11:10   6  07:11:10   7  07:11:10   8  07:11:10   9  07:11:10  10  07:11:10  11  07:11:10  12  07:11:10  13  07:11:10  14  07:11:10  15  07:11:10  16  07:11:10  17  

GRANTEE
------------------------------
CTXAPP

07:12:00 SQL> 07:12:00 SQL>

Report message to a moderator

Re: How to find user with Insert/update privilage but not select [message #532573 is a reply to message #532567]

Wed, 23 November 2011 09:20

Barbara Boehmer
Messages: 8933
Registered: November 2002
Location: California, USA

Senior Member

You could try something like the following. If you have a lot of such privileges on your system, then it may be slow no matter what method you use. You could add a first_rows hint.

select t1.grantee
from   dba_tab_privs t1, dba_tab_privs t2
where  t1.grantee = t2.grantee
and    t1.privilege = 'INSERT'
and    t2.privilege = 'UPDATE'
MINUS
select grantee
from   dba_tab_privs 
where  privilege = 'SELECT';

Report message to a moderator

Re: How to find user with Insert/update privilage but not select [message #532580 is a reply to message #532573]

Wed, 23 November 2011 09:25

Barbara Boehmer
Messages: 8933
Registered: November 2002
Location: California, USA

Senior Member

Both are quick on my system, but it is a small system. The second one is faster.

SCOTT@orcl_11gR2> set	 timing on
SCOTT@orcl_11gR2> select distinct grantee
  2  from   dba_tab_privs t1
  3  where  exists
  4  	    (select *
  5  	     from   dba_tab_privs t2
  6  	     where  t2.grantee = t1.grantee
  7  	     and    t2.privilege = 'INSERT')
  8  and    exists
  9  	    (select *
 10  	     from   dba_tab_privs t3
 11  	     where  t3.grantee = t1.grantee
 12  	     and    t3.privilege = 'UPDATE')
 13  and    not exists
 14  	    (select *
 15  	     from   dba_tab_privs t3
 16  	     where  t3.grantee = t1.grantee
 17  	     and    t3.privilege = 'SELECT')
 18  /

GRANTEE
------------------------------
CTXAPP

1 row selected.

Elapsed: 00:00:02.10

SCOTT@orcl_11gR2> select t1.grantee
  2  from   dba_tab_privs t1, dba_tab_privs t2
  3  where  t1.grantee = t2.grantee
  4  and    t1.privilege = 'INSERT'
  5  and    t2.privilege = 'UPDATE'
  6  MINUS
  7  select grantee
  8  from   dba_tab_privs
  9  where  privilege = 'SELECT'
 10  /

GRANTEE
------------------------------
CTXAPP

1 row selected.

Elapsed: 00:00:00.71
SCOTT@orcl_11gR2>

Report message to a moderator

Re: How to find user with Insert/update privilage but not select [message #532581 is a reply to message #532580]

Wed, 23 November 2011 09:26

Barbara Boehmer
Messages: 8933
Registered: November 2002
Location: California, USA

Senior Member

I should probably point out that this is just privileges granted directly, not through roles.

Report message to a moderator

Re: How to find user with Insert/update privilage but not select [message #532583 is a reply to message #532570]

Wed, 23 November 2011 09:28

OracleDBA2010
Messages: 16
Registered: November 2011
Location: Indianapolis

Junior Member

Thanks you every one. Its working now.

Greatly appriciated all the responses.

Report message to a moderator

Previous Topic:	Oracle Licensing - core factor
Next Topic:	Database configuration

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jan 28 10:18:31 CST 2023