create table users_permmisions  
( username  varchar2(20) ,
  privilge  varchar2(60)) ;

Insert into  users_permmisions values ('mike','grant select on table1');
Insert into  users_permmisions values ('mike','grant select on table2'); 
Insert into  users_permmisions values ('mike','grant select on table3');
Insert into  users_permmisions values ('rogger','grant select on table1');
Insert into  users_permmisions values ('rogger','grant select on table3'); 
Insert into  users_permmisions values ('rogger','grant select on table5'); 

Insert into  users_permmisions values ('rogger','grant select on table7'); 
Insert into  users_permmisions values ('rogger','grant select on table9');

SQL> select privilge from users_permmisions where username = 'mike'
  2  intersect
  3  select privilge from users_permmisions where username = 'rogger';

PRIVILGE
------------------------------------------------------------
grant select on table1
grant select on table3

2 rows selected.

SQL> 

username  privilges 

0      A
0      B
0      C
0      D
1      A
1      Z
1      F
1      E
1      G
2      A
2      B
2      C
2      D
3      A
4      U
4      L

create table test_050 (username number, priv varchar2(10));

insert into test_050 values (0      ,'A');
insert into test_050 values (0      ,'B');
insert into test_050 values (0      ,'C');
insert into test_050 values (0      ,'D');
insert into test_050 values (1      ,'A');
insert into test_050 values (1      ,'Z');
insert into test_050 values (1      ,'F');
insert into test_050 values (1      ,'E');
insert into test_050 values (1      ,'G');
insert into test_050 values (2      ,'A');
insert into test_050 values (2      ,'B');
insert into test_050 values (2      ,'C');
insert into test_050 values (2      ,'D');
insert into test_050 values (3      ,'A');
insert into test_050 values (4      ,'U');
insert into test_050 values (4      ,'L');

commit;

select count(t1.priv), count(t2.priv)
from   test_050 t1
      ,test_050 t2
where  t1.username = 1
and    t2.username(+) = 2
and    t1.priv = t2.priv(+);

SQL> select
  2    t1.username username1, t1.cnt privs1, t2.username username2, t2.cnt privs2
  3  from
  4    (select username, count(*) cnt from users_permmisions group by username) t1,
  5    (select username, count(*) cnt from users_permmisions group by username) t2
  6  where t1.username < t2.username
  7  /

USERNAME1                PRIVS1 USERNAME2                PRIVS2
-------------------- ---------- -------------------- ----------
mike                          3 rogger                        5

1 row selected.

SQL> 

SQL> select
  2    username1, username2, privs_both, privs1, privs2,
  3    privs_both / greatest( privs1, privs2 ) perc
  4  from (
  5    select
  6      up1.username username1, up1.privilge privilge1,
  7      up2.username username2, up2.privilge privilge2,
  8      count(distinct up1.privilge) over(partition by up1.username) privs1,
  9      count(distinct up2.privilge) over(partition by up2.username) privs2,
 10      count(case when up1.privilge = up2.privilge then 1 end)
 11        over(partition by up1.username, up2.username) privs_both
 12    from users_permmisions up1, users_permmisions up2
 13    where up1.username < up2.username )
 14  group by username1, username2, privs_both, privs1, privs2
 15  /

USERNAME1            USERNAME2            PRIVS_BOTH     PRIVS1     PRIVS2       PERC
-------------------- -------------------- ---------- ---------- ---------- ----------
mike                 rogger                        2          3          5         .4

1 row selected.

SQL> 

SQL> SELECT v1.username
  2        ,v2.priv_cnt
  3        ,v1.matched_username
  4        ,v1.match_cnt
  5  FROM (SELECT a.username
  6              ,b.username matched_username
  7              ,COUNT(*) match_cnt
  8        FROM test_050 a
  9        JOIN test_050 b ON b.priv = a.priv
 10                    AND b.username != a.username
 11        GROUP BY a.username, b.username) v1
 12  JOIN (SELECT username
 13              ,COUNT(*) priv_cnt
 14        FROM test_050
 15        GROUP BY username) v2 ON v2.username = v1.username
 16  WHERE match_cnt / priv_cnt > .5
 17  ORDER BY v1.username, v1.matched_username;
 
  USERNAME   PRIV_CNT MATCHED_USERNAME  MATCH_CNT
---------- ---------- ---------------- ----------
         0          4                2          4
         2          4                0          4
         3          1                0          1
         3          1                1          1
         3          1                2          1

SQL> SELECT *
  2  FROM (SELECT a.username
  3              ,a.priv_cnt
  4              ,b.username matched_username
  5              ,COUNT(*) match_cnt
  6        FROM (SELECT t.*
  7                    ,COUNT(*) OVER(PARTITION BY t.username) priv_cnt
  8              FROM test_050 t) a
  9        JOIN test_050 b ON b.priv = a.priv
 10                       AND b.username != a.username
 11        GROUP BY a.username, a.priv_cnt, b.username) v1
 12  WHERE match_cnt / priv_cnt > .5
 13  ORDER BY v1.username, v1.matched_username;
 
  USERNAME   PRIV_CNT MATCHED_USERNAME  MATCH_CNT
---------- ---------- ---------------- ----------
         0          4                2          4
         2          4                0          4
         3          1                0          1
         3          1                1          1
         3          1                2          1

drop table t purge;
create table t (username integer, priv varchar2(10));
insert into t values (0,'A');
insert into t values (0,'B');
insert into t values (0,'C');
insert into t values (0,'D');
insert into t values (1,'A');
insert into t values (1,'Z');
insert into t values (1,'F');
insert into t values (1,'E');
insert into t values (1,'G');
insert into t values (2,'A');
insert into t values (2,'B');
insert into t values (2,'C');
insert into t values (2,'D');
insert into t values (3,'A');
insert into t values (4,'U');
insert into t values (4,'L');
commit;

SQL> select * from t order by 1, 2;
  USERNAME PRIV
---------- ----------
         0 A
         0 B
         0 C
         0 D
         1 A
         1 E
         1 F
         1 G
         1 Z
         2 A
         2 B
         2 C
         2 D
         3 A
         4 L
         4 U

16 rows selected.

SQL> def threshold=50
SQL> with
  2    counts as (
  3      select t1.username user1, t2.username user2,
  4             greatest(count(distinct t1.priv),count(distinct t2.priv)) max_nb,
  5             count(decode(t1.priv,t2.priv,1)) nb_common
  6      from t t1, t t2
  7      where t2.username > t1.username
  8      group by t1.username, t2.username
  9    )
 10  select user1, user2, nb_common, max_nb, 
 11         round(100*nb_common/max_nb) pct
 12  from counts
 13  where 100*nb_common/max_nb > &threshold
 14  order by 1, 2
 15  /
     USER1      USER2  NB_COMMON     MAX_NB        PCT
---------- ---------- ---------- ---------- ----------
         0          2          4          4        100

1 row selected.

SQL> def threshold=20
SQL> /
     USER1      USER2  NB_COMMON     MAX_NB        PCT
---------- ---------- ---------- ---------- ----------
         0          2          4          4        100
         0          3          1          4         25
         2          3          1          4         25

3 rows selected.

SQL> def threshold=50
SQL> col common_privs format a20 heading "COMMON PRIVS AT &threshold.%"
SQL> with
  2    counts as (
  3      select t1.username user1, t2.username user2,
  4             greatest(count(distinct t1.priv),count(distinct t2.priv)) max_nb,
  5             count(decode(t1.priv,t2.priv,1)) nb_common,
  6             wm_concat(decode(t1.priv,t2.priv,t1.priv)) common_privs
  7      from t t1, t t2
  8      where t2.username > t1.username
  9      group by t1.username, t2.username
 10    )
 11  select user1, user2, common_privs
 12  from counts
 13  where 100*nb_common/max_nb > &threshold
 14  order by 1, 2
 15  /
     USER1      USER2 COMMON PRIVS AT 50%
---------- ---------- --------------------
         0          2 C,A,D,B

1 row selected.

SQL> def threshold=20
SQL> col common_privs format a20 heading "COMMON PRIVS AT &threshold.%"
SQL> /
     USER1      USER2 COMMON PRIVS AT 20%
---------- ---------- --------------------
         0          2 C,A,D,B
         0          3 A
         2          3 A

3 rows selected.

SQL> with
  2    counts as (
  3      select t1.username user1, t2.username user2,
  4             greatest(count(distinct t1.priv),count(distinct t2.priv)) max_nb,
  5             count(decode(t1.priv,t2.priv,1)) nb_common,
  6             wm_concat(decode(t1.priv,t2.priv,t1.priv)) common_privs
  7      from t t1, t t2
  8      where t2.username > t1.username
  9      group by t1.username, t2.username
 10    )
 11  select user1, user2, common_privs
 12  from counts
 13  where 100*nb_common/max_nb > &threshold
 14  order by 1, 2
 15  /