Script to Show All Users’ System Privileges

CREATE OR REPLACE VIEW DBA_USER_PRIVS (USERNAME, ROLENAME, PRIVILEGE) AS 
 SELECT DECODE(SA1.GRANTEE#, 1, 'PUBLIC', U1.NAME), SUBSTR(U2.NAME,1,20),  
   SUBSTR(SPM.NAME,1,27)  
FROM SYS.SYSAUTH$ SA1, SYS.SYSAUTH$ SA2, SYS.USER$ U1,  
     SYS.USER$ U2, SYS.SYSTEM_PRIVILEGE_MAP SPM 
WHERE SA1.GRANTEE# = U1.USER# 
  AND SA1.PRIVILEGE# = U2.USER# 
  AND U2.USER# = SA2.GRANTEE# 
  AND SA2.PRIVILEGE# = SPM.PRIVILEGE 
UNION 
 SELECT U.NAME, NULL, SUBSTR(SPM.NAME,1,27) 
FROM SYS.SYSTEM_PRIVILEGE_MAP SPM, SYS.SYSAUTH$ SA, SYS.USER$ U 
WHERE SA.GRANTEE#=U.USER#  
  AND SA.PRIVILEGE#=SPM.PRIVILEGE 
/ 

select * from DBA_USER_PRIVS where username not in('SYSTEM','SYS','XDB','WMSYS','SYSMAN','SPATIAL_WFS_ADMIN_USR');

USERNAME ROLENAME PRIVILEGE

------------------------------------- -----------
SOTT RESOURCE CREATE INDEXTYPE
SCOTT RESOURCE CREATE OPERATOR
SCOTT RESOURCE CREATE PROCEDURE
SCOTT RESOURCE CREATE SEQUENCE
SCOTT RESOURCE CREATE TABLE
SCOTT RESOURCE CREATE TRIGGER
SCOTT RESOURCE CREATE TYPE
SCOTT CREATE DATABASE LINK
SCOTT CREATE MATERIALIZED VIEW
SCOTT UNLIMITED TABLESPACE
SI_INFORMTN_SCHEMA UNLIMITED TABLESPACE
USERNAME ROLENAME PRIVILEGE
------------------------------ ---------------------------
SPATIAL_CSW_ADMIN_USR CONNECT CREATE SESSION
SPATIAL_CSW_ADMIN_USR RESOURCE CREATE CLUSTER
SPATIAL_CSW_ADMIN_USR RESOURCE CREATE INDEXTYPE
SPATIAL_CSW_ADMIN_USR RESOURCE CREATE OPERATOR
SPATIAL_CSW_ADMIN_USR RESOURCE CREATE PROCEDURE
SPATIAL_CSW_ADMIN_USR RESOURCE CREATE SEQUENCE
SPATIAL_CSW_ADMIN_USR RESOURCE CREATE TABLE
SPATIAL_CSW_ADMIN_USR RESOURCE CREATE TRIGGER
SPATIAL_CSW_ADMIN_USR RESOURCE CREATE TYPE
SPATIAL_CSW_ADMIN_USR ALTER ANY ROLE
SPATIAL_CSW_ADMIN_USR CREATE ANY DIRECTORY

 

Advertisements

About Sher khan

Senior Oracle DBA, Oracle 10g , Oracle 11g OCE ,Oracle 12C and Oracle 11g OPN certified Specialist, OCP Certified in Oracle 9i,10g , 11g and 12C. I am working in Oracle technology since 2004. Currently working in U.A.E Email: sher487@hotmail.com
This entry was posted in scripts. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s