Review Oracle user privilege

set echo off
set verify off
set lines 1000
set pages 999
set head on
set feed on
set serveroutput on size 1000000 format wrap
set trimspool on
-- clear col
-- clear break
-- clear computes
set arrays 1
set long 10000

declare
ls_sql_text varchar2( 2000 );
ls_link_text varchar2( 2000 );
ls_sel_text varchar2( 2000 );
ls_drop_link varchar2( 2000 );
ls_this_sid varchar2( 20 );
ls_sqlerr number;
ls_sqlcode varchar2( 2000 );
ln_dcount number;
ln_numlinks number;
ls_db_link varchar2( 2000 );
ln_no_nu number := 0;
ln_no_users number := 0;
ls_privs varchar2( 2000 );
ls_out_text varchar2( 2000 );

cursor c_users is
select * from dba_users
order by username;

cursor c_sys_privs( x_user in varchar2 ) is
select *
from dba_sys_privs
where grantee = x_user
order by privilege;

begin
ls_sql_text := 'alter session set global_names=false';
execute immediate ls_sql_text;

select global_name into ls_this_sid from global_name;
ls_db_link := 'TEMPLINK' || substr( ls_this_sid, instr( ls_this_sid, '.' ) );
ls_this_sid := substr( ls_this_sid, 1, instr( ls_this_sid, '.' ) - 1 );

dbms_output.put_line( 'SID : ' || ls_this_sid );
dbms_output.put_line( 'Temporary Link : ' || ls_db_link );
dbms_output.put_line( rpad( '-', 100, '-' ) );
dbms_output.put_line( 'Note : privileges shown are only those directly granted. They do not include any inherited via roles.' || chr( 10 ) );

for i in c_users loop
ln_no_users := ln_no_users + 1;
begin
select count(*) into ln_numlinks from user_db_links where db_link = ls_db_link;
if ln_numlinks > 0 then
ls_drop_link := 'drop database link templink';
execute immediate ls_drop_link;
end if;

if i.account_status = 'LOCKED' then
dbms_output.put_line( 'User ' || rpad( i.username, 31 ) || ' STATUS = LOCKED' );
else
ls_link_text := 'create database link templink connect to ' || i.username || ' identified by ' || i.username || ' using ' || '''' || ls_this_sid || '''';
execute immediate ls_link_text;

ls_sel_text := 'select count(*) from dual@templink';
execute immediate ls_sel_text into ln_dcount;

ls_drop_link := 'drop database link templink';
execute immediate ls_drop_link;

ls_privs := ' ';
for j in c_sys_privs( i.username ) loop
ls_privs := ls_privs || ' * ' || j.privilege;
end loop;
ls_out_text := 'User ' || rpad( i.username, 31 ) || ' is a naughty user.' || ls_privs;
if length( ls_out_text ) > 254 then
dbms_output.put_line( substr( ls_out_text, 1, 240 ) || ' and more ...' );
else
dbms_output.put_line( ls_out_text );
end if;
ln_no_nu := ln_no_nu + 1;
end if;

exception
when others then
ls_sqlerr := sqlcode;
ls_sqlcode := sqlerrm;
if ls_sqlerr = -2024
or ls_sqlerr = -1017
then
null;
else
dbms_output.put_line( rpad( i.username, 31 ) || ls_sqlerr || ' ' || ls_sqlcode );
end if;
end;
end loop;
dbms_output.put_line( rpad( '-', 100, '-' ) );
dbms_output.put_line( 'Number of users : ' || ln_no_users );
dbms_output.put_line( 'Number of naughty users : ' || ln_no_nu );
dbms_output.put_line( 'Percentage : ' || to_char( ( ln_no_nu / ln_no_users ) * 100, '999.99' ) );
dbms_output.put_line( rpad( '-', 100, '-' ) );
end;
/