Compare two Oracle database roles

I'm trying to re-engineer my corporates user managament and want to compare all the roles with each other to find duplicate ones. Doing it by hand is really tiring and I need to compare like 150 roles. I wrote the following script:

DEFINE ROLE_1 = &INPUT_1
DEFINE ROLE_2 = &INPUT_2

SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING ON
SET LINESIZE 140

COLUMN PRIVILEGE    FORMAT A20    HEADING "PRIVILEGE"
COLUMN TABLE_NAME   FORMAT A30    HEADING "TABLE_NAME"
COLUMN GRANTABLE    FORMAT A5     HEADING "GRANTABLE"

PROMPT
PROMPT OVERLAPPINGS BETWEEN 1st ROLE AND 2nd ROLE
PROMPT

SELECT
    PRIVILEGE,
    TABLE_NAME,
    GRANTABLE
FROM
    SYS.DBA_TAB_PRIVS
WHERE
    GRANTEE IN ( '&ROLE_1' )

INTERSECT

SELECT
    PRIVILEGE,
    TABLE_NAME,
    GRANTABLE
FROM
    SYS.DBA_TAB_PRIVS
WHERE
    GRANTEE IN ( '&ROLE_2' )
ORDER BY TABLE_NAME, PRIVILEGE ASC
/

PROMPT
PROMPT PRIVILEGES IN 1st ROLE BUT NOT IN 2nd ROLE
PROMPT

SELECT
    PRIVILEGE,
    TABLE_NAME,
    GRANTABLE
FROM
    SYS.DBA_TAB_PRIVS
WHERE
    GRANTEE IN ( '&ROLE_1' )

MINUS

SELECT
    PRIVILEGE,
    TABLE_NAME,
    GRANTABLE
FROM
    SYS.DBA_TAB_PRIVS
WHERE
    GRANTEE IN ( '&ROLE_2' )
ORDER BY TABLE_NAME, PRIVILEGE ASC
/

PROMPT
PROMPT PRIVILEGES IN 2nd ROLE BUT NOT IN 1st ROLE
PROMPT

SELECT
    PRIVILEGE,
    TABLE_NAME,
    GRANTABLE
FROM
    SYS.DBA_TAB_PRIVS
WHERE
    GRANTEE IN ( '&ROLE_2' )

MINUS

SELECT
    PRIVILEGE,
    TABLE_NAME,
    GRANTABLE
FROM
    SYS.DBA_TAB_PRIVS
WHERE
    GRANTEE IN ( '&ROLE_1' )
ORDER BY TABLE_NAME, PRIVILEGE ASC
/

It works all fine, but for some reason it doesn't display the table head.

The real problem why I'm asking is, is there a more comfortable way to automatically compare all roles to one another? Some neat way to do all this? I'm quite new to PL/SQL and don't really know how to loop this thing up. Is there even maybe a possibly way to display the corresponding column of the tables?

Here is a query that finds duplicate role_tab_privs. It uses Oracle LISTAGG aggregate function to create a list of privileges for each role, owner, table.

SELECT arole, brole, aowner, atable, aprivlist
FROM 
      --join to each grouped view by role, owner, table
      (
      SELECT A.ROLE AS arole, A.owner AS aowner, A.table_name AS atable,
                  --one field for all privileges concatenated with | separator
                  listagg(a.privilege, ' | ') WITHIN GROUP(ORDER BY a.privilege) AS aprivlist
      FROM role_tab_privs a
      GROUP BY A.ROLE, A.owner, A.table_name
      )
JOIN 
      (
      SELECT b.ROLE as brole, b.owner as bowner, b.table_name as btable,
                  listagg(b.privilege, ' | ') WITHIN GROUP(ORDER BY b.privilege) AS bprivlist
      FROM role_tab_privs b
      GROUP BY b.ROLE, b.owner, b.table_name
      )
  -- the roles cannot be equal but the owner, table, and privileges must be
  ON AROLE != bROLE
 AND aowner = bowner
 AND atable = btable
 AND aprivlist = bprivlist
ORDER BY aowner, atable, arole 
;

Note that the LISTAGG is used only on the privileges within a role, owner, table group. The result of a `LISTAGG' function must fit within the max varchar(4000) or the error:

ORA-01489: result of string concatenation is too long

This query gives a good view of how the table privileges relate.

StackExchange Database Administrators Q#46494, answer score: 2