Oracle Privilages

SQL> SELECT username, privilege FROM USER_SYS_PRIVS;

USERNAME                       PRIVILEGE
------------------------------ --------------------------------
OE                             QUERY REWRITE
OE                             UNLIMITED TABLESPACE
OE                             CREATE SYNONYM
OE                             CREATE DATABASE LINK
OE                             CREATE MATERIALIZED VIEW
OE                             CREATE SESSION
OE                             CREATE VIEW

7 rows selected. 

SQL>  SELECT GRANTEE, PRIVILEGE FROM DBA_SYS_PRIVS
  2    WHERE GRANTEE = 'SH';

GRANTEE                        PRIVILEGE
------------------------------ ----------------------------------------
SH                             CREATE SEQUENCE
SH                             ALTER SESSION
SH                             CREATE SYNONYM
SH                             CREATE TABLE
SH                             UNLIMITED TABLESPACE
SH                             CREATE SESSION
SH                             CREATE DATABASE LINK
SH                             CREATE CLUSTER
SH                             CREATE DIMENSION
SH                             CREATE VIEW
SH                             QUERY REWRITE

--Example: List all privilege that have “TABLE” keyword in it.

SQL> select * from system_privilege_map where NAME like '%TABLE%';

 PRIVILEGE NAME                                       PROPERTY
---------- ---------------------------------------- ----------
       -10 CREATE TABLESPACE                                 0
       -11 ALTER TABLESPACE                                  0
       -12 MANAGE TABLESPACE                                 0
       -13 DROP TABLESPACE                                   0
       -15 UNLIMITED TABLESPACE                              0
       -40 CREATE TABLE                                      0
       -41 CREATE ANY TABLE                                  0
       -42 ALTER ANY TABLE                                   0
       -43 BACKUP ANY TABLE                                  0
       -44 DROP ANY TABLE                                    0
       -45 LOCK ANY TABLE                                    0

 PRIVILEGE NAME                                       PROPERTY
---------- ---------------------------------------- ----------
       -46 COMMENT ANY TABLE                                 0
       -47 SELECT ANY TABLE                                  0
       -48 INSERT ANY TABLE                                  0
       -49 UPDATE ANY TABLE                                  0
       -50 DELETE ANY TABLE                                  0
      -213 UNDER ANY TABLE                                   0
      -243 FLASHBACK ANY TABLE                               0

18 rows selected.

---Users to roles and system privileges

select
  lpad(' ', 2*level) || granted_role "User, his roles and privileges"
from
  (
  /* THE USERS */
    select 
      null     grantee, 
      username granted_role
    from 
      dba_users
    where
      username like upper('%&enter_username%')
  /* THE ROLES TO ROLES RELATIONS */ 
  union
    select 
      grantee,
      granted_role
    from
      dba_role_privs
  /* THE ROLES TO PRIVILEGE RELATIONS */ 
  union
    select
      grantee,
      privilege
    from
      dba_sys_privs
  )
start with grantee is null
connect by grantee = prior granted_role;

---System privileges to roles and users

select
  lpad(' ', 2*level) || c "Privilege, Roles and Users"
from
  (
  /* THE PRIVILEGES */
    select 
      null   p, 
      name   c
    from 
      system_privilege_map
    where
      name like upper('%&enter_privliege%')
  /* THE ROLES TO ROLES RELATIONS */ 
  union
    select 
      granted_role  p,
      grantee       c
    from
      dba_role_privs
  /* THE ROLES TO PRIVILEGE RELATIONS */ 
  union
    select
      privilege     p,
      grantee       c
    from
      dba_sys_privs
  )
start with p is null
connect by p = prior c; 

---OBJECT

select
  case when level = 1 then own || '.' || obj || ' (' || typ || ')' else
  lpad (' ', 2*(level-1)) || obj || nvl2 (typ, ' (' || typ || ')', null)
  end
from
  (
  /* THE OBJECTS */
    select 
      null          p1, 
      null          p2,
      object_name   obj,
      owner         own,
      object_type   typ
    from 
      dba_objects
    where
       owner not in 
        ('SYS', 'SYSTEM', 'WMSYS', 'SYSMAN','MDSYS','ORDSYS','XDB', 'WKSYS', 'EXFSYS', 
         'OLAPSYS', 'DBSNMP', 'DMSYS','CTXSYS','WK_TEST', 'ORDPLUGINS', 'OUTLN')
      and object_type not in ('SYNONYM', 'INDEX')
  /* THE OBJECT TO PRIVILEGE RELATIONS */ 
  union
    select
      table_name p1,
      owner      p2,
      grantee,
      grantee,
      privilege
    from
      dba_tab_privs
  /* THE ROLES TO ROLES/USERS RELATIONS */ 
  union
    select 
      granted_role  p1,
      granted_role  p2,
      grantee,
      grantee,
      null
    from
      dba_role_privs
  )
start with p1 is null and p2 is null
connect by p1 = prior obj and p2 = prior own;