Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • New OFA for 11g Oracle
  • scp with ssh2 Linux/Unix
  • DETERMINING WHICH INSTANCE OWNS WHICH SHARED MEMORY & SEMAPHORE SEGMENTS Oracle
  • Implementation of key based authentications Linux/Unix
  • New Latest Param.sql for finding all hidden parameters also Oracle
  • process id based files and processes Linux/Unix
  • Implementing Listener Security Oracle
  • block_ident.sql Oracle
  • arch_configUOCIOTTO.ora Oracle
  • Giving Grant on v$DATABASE Oracle
  • OPENING A STANDBY DATABASE IN READ-ONLY MODE Oracle
  • Goog notes on X$ tables Oracle
  • telnet listening Linux/Unix
  • Good Doc 28-JUN-2006 Oracle
  • Specify the Rollback segment to use in Transaction Oracle

Find all users who have DML privileges

Posted on 05-Sep-202305-Sep-2023 By Admin No Comments on Find all users who have DML privileges

Run following query as sysdba.

alter session set nls_date_format=’DD-MON-RRRR:HH24:MI:SS’;

alter session set nls_date_format='DD-MON-RRRR:HH24:MI:SS';


select username, created, account_status   /* Users who granted role with DML privs */ 
  from dba_users 
 where oracle_maintained = 'N' 
   and username in 
         (select grantee from dba_role_privs 
           where granted_role in 
                (select role 
                  from dba_roles 
                 where role in (select GRANTEE from dba_tab_privs where privilege in ('INSERT','UPDATE', 'DELETE') ) ) )
 Union
 select username, created, account_status   /* Users who granted direct DML privs */ 
  from dba_users 
 where oracle_maintained = 'N' 
  and username in (select GRANTEE from dba_tab_privs where privilege in ('INSERT','UPDATE', 'DELETE') );
 
Oracle, SQL scripts

Post navigation

Previous Post: Oracle GoldenGate lag monitoring shell script
Next Post: Convert multiple rows to single column

Related Posts

  • DETERMINING WHICH INSTANCE OWNS WHICH SHARED MEMORY & SEMAPHORE SEGMENTS Oracle
  • Adding Datafile on Primary Server and Impact on Standby Server Oracle
  • Monitor Long Running Job Oracle
  • Consolidated Reference List Of Notes For Migration / Upgrade Service Requests -ID 762540.1 Oracle
  • Oracle Material from OTN Oracle
  • In Addition to previous note, following grants needed on PERFSTAT user. Oracle

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Categories

  • AWS (2)
  • Azure (1)
  • Linux/Unix (149)
  • Oracle (388)
  • PHP/MYSQL/Wordpress (10)
  • Power-BI (0)
  • Python/PySpark (7)
  • RAC (17)
  • rman-dataguard (26)
  • shell (149)
  • SQL scripts (337)
  • Uncategorized (0)
  • Videos (0)

Recent Posts

  • findinfo.sql (SQL for getting CPU and Active session info)27-May-2025
  • SQL Tracker by SID sqltrackerbysid.sql22-Apr-2025
  • How to connect to Oracle Database with Wallet with Python.21-Mar-2025
  • JSON/XML Types in Oracle18-Mar-2025
  • CPU Core related projections12-Mar-2025
  • Exadata Basics10-Dec-2024
  • Reading config file from other folder inside class24-Sep-2024
  • Python class import from different folders22-Sep-2024
  • Transfer SQL Profiles from One database to other database.05-Sep-2024
  • Load testing on Oracle 19C RAC with HammerDB18-Jan-2024

Archives

  • 2025
  • 2024
  • 2023
  • 2010
  • 2009
  • 2008
  • 2007
  • 2006
  • 2005
  • FRA Information. Oracle
  • compile_inv.sql Oracle
  • SYSOPER Mystery Oracle
  • sess1.sql Oracle
  • Standby Database File Management in 10g with STANDBY_FILE_MANAGEMENT Oracle
  • Settting up get_vmstat.sh for colletinf CPU Usage. Oracle
  • Useful Solaris Commands on 28-SEP-2005 Linux/Unix
  • scripts to take listener.log backup Linux/Unix

Copyright © 2025 pvmehta.com.

Powered by PressBook News WordPress theme