Home » Other » General » How to find user with Insert/update privilage but not select (Oracle 10g)
How to find user with Insert/update privilage but not select [message #532458] Tue, 22 November 2011 15:25 Go to next message
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?
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 Go to previous messageGo to next message
Littlefoot
Messages: 21592
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Try to query DBA_TAB_PRIVS.
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 Go to previous messageGo to next message
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')
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
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');


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 Go to previous messageGo to next message
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)
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 Go to previous messageGo to next message
BlackSwan
Messages: 26733
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> 

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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
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';

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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
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>

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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
I should probably point out that this is just privileges granted directly, not through roles.
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 Go to previous message
OracleDBA2010
Messages: 16
Registered: November 2011
Location: Indianapolis
Junior Member
Thanks you every one. Its working now.

Greatly appriciated all the responses.
Previous Topic: Oracle Licensing - core factor
Next Topic: Database configuration
Goto Forum:
  


Current Time: Sun Sep 20 23:10:26 CDT 2020