Oracle Users' System Privileges

Oracle Users' System Privileges
Oracle role PRIVILEGE

Oracle Role


set linesize 200
col PRIVILEGE format a30
col ROLENAME format a20



-- 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
/


USERNAME ROLENAME PRIVILEGE
------------------------------ -------------------- ------------------------------
VIHAAN RESOURCE CREATE INDEXTYPE
VIHAAN RESOURCE CREATE OPERATOR
VIHAAN RESOURCE CREATE PROCEDURE
VIHAAN RESOURCE CREATE SEQUENCE
VIHAAN RESOURCE CREATE TABLE
VIHAAN RESOURCE CREATE TRIGGER
VIHAAN RESOURCE CREATE TYPE
VIHAAN UNLIMITED TABLESPACE
WMSYS CONNECT CREATE SESSION