Checking User priviledges On your system for every user

set lines 110 pages 1000 ver off
col role for a16
col pv for a75 hea 'PRIVILEGE OR ROLE'
bre on role on type skip 1

SQL>  define usercheck ='COLLECTION'
SQL> select grantee
, 'ROL' type
, granted_role pv
from dba_role_privs
where grantee = '&usercheck'
union
select grantee
, 'PRV' type
, privilege pv
from dba_sys_privs
where grantee = '&usercheck' union
select grantee
, 'OBJ' type,
regexp_replace(max(decode(privilege,'WRITE','WRITE,'))||
max(decode(privilege,'READ','READ,'))||
max(decode(privilege,'EXECUTE','EXECUTE')),'WRITE,READ,EXECUTE','ALL')||
regexp_replace(max(decode(privilege,'SELECT','SELECT'))||
max(decode(privilege,'DELETE',',DELETE'))||
max(decode(privilege,'UPDATE',',UPDATE'))||
max(decode(privilege,'INSERT',',INSERT')),'SELECT,DELETE,UPDATE,INSERT','ALL')||
' ON '||object_type||' "'||a.owner||'"."'||table_name||'"' pv
from dba_tab_privs a
, dba_objects b
where a.owner=b.owner
and a.table_name = b.object_name
and a.grantee='&usercheck'
group by a.owner
, table_name
, object_type
, grantee
union
select grantee
, 'COL' type,
privilege||' ('||column_name||') ON "'||owner||'"."'||table_name||'"' pv
from dba_col_privs
where grantee='&usercheck'
union
select username grantee
, '---' type
, 'empty user ---' pv
from dba_users
where not username in (select distinct grantee from dba_role_privs)
and not username in (select distinct grantee from dba_sys_privs)
and not username in (select distinct grantee from dba_tab_privs)
and username like '%&usercheck%'
group by username
order by grantee
, type
, pv;

GRANTEE                        TYP PRIVILEGE OR ROLE
------------------------------ --- ---------------------------------------------------------------------------
COLLECTION                     OBJ SELECT ON TABLE "CTS.SERVICE_PARAMETER"
COLLECTION                         SELECT ON TABLE "SUB.ALLOWED_MSISDN"
COLLECTION                         SELECT ON VIEW "CTS.SERVICE_VIEW"

COLLECTION                     ROL CONNECT
COLLECTION                         DSXE
COLLECTION                         RESOURCE


6 rows selected.

SQL> SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE ='DSXE'
  2  ;

no rows selected

SQL> SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE ='RESOURCE';

ROLE             PRIVILEGE                                ADM
---------------- ---------------------------------------- ---
RESOURCE         CREATE CLUSTER                           NO
                 CREATE SEQUENCE                          NO
                 CREATE TRIGGER                           NO
                 CREATE TABLE                             NO
                 CREATE PROCEDURE                         NO
                 CREATE TYPE                              NO
                 CREATE OPERATOR                          NO
                 CREATE INDEXTYPE                         NO