News

  1. Home
  2. All posts
All posts, PL/SQL by Team Ajara

Report generation with Database user details.

Report generation with Database user details.

Requirement: Report should contain below fields, in the last field all privileges and roles are separated by comma(,)

S.No USERNAME CREATED_DATE STATUS PRIVILIGES/Roles

Requirement is to generate the Oracle DB user report with the above given fields.

 

Anonymous block:

declare
q clob;
i number:=0;
id varchar2(20);
begin
for x in (select username,created,account_status from dba_users where username not in (‘SYS’ ,’SYSTEM’) )
loop
i:=i+1;
dbms_output.put_line(”);
dbms_output.put(”||i||’||’||x.username);
dbms_output.put(‘||’||x.created);
dbms_output.put(‘||’||x.account_status);

for y in ( select LISTAGG(PRIVILEGE,’,’) within group (order by PRIVILEGE) as priv from dba_sys_privs where grantee=x.username )
loop
dbms_output.put(‘||’||y.priv);
end loop;

for z in (select LISTAGG(granted_role,’,’) within group (order by granted_role) as role from dba_role_privs where grantee =x.username)
loop
dbms_output.put(‘,’||z.role);
end loop;

dbms_output.put_line(”);

end loop;
end;
/

 

How to execute the above PL/SQL block:

Step 1) save the block in file and run the script.

Step 2) For example save into anonymous_block.sql
Step 3) connect to database
Step 4) SQL>set lines 300
SQL> set serveroutput on
SQL>@anonymous_block.sql

Sample output:

1||DBSNMP||07-JUL-14||OPEN||CREATE PROCEDURE,CREATE TABLE,INHERIT ANY PRIVILEGES,SELECT ANY DICTIONARY,UNLIMITED TABLESPACE,CDB_DBA,DV_MONITOR,OEM_MONITOR
2||SCOTT||02-JUL-20||EXPIRED & LOCKED||UNLIMITED TABLESPACE,CONNECT,RESOURCE
3||TEST||23-MAR-23||OPEN||CONNECT,RESOURCE

 

Note: There are many ways to generate this report. This is one simple report. May need to fix small filed lenghts and formatting output can be placed in the excel sheet.

 

See Also: