Oracle Roles and Users audit report

Posted By Sagar Patil

I have often used following procedure to create list of roles & corresponding users mapped into it. This was then used to email business owners as well as for user accesss auditing.

-- Package Name : users_granted_role
-- This package was created to spool user and their respective Privs from oracle data Dictionary.
CREATE OR REPLACE PACKAGE users_granted_role
IS
 PROCEDURE write_op (pv_str IN VARCHAR2);
 FUNCTION user_or_role(pv_grantee IN dba_users.username%TYPE) RETURN VARCHAR2;
 FUNCTION role_pwd(pv_role IN dba_roles.role%TYPE)   RETURN VARCHAR2;
 PROCEDURE get_users(pv_grantee IN dba_roles.role%TYPE,pv_tabstop IN OUT NUMBER);
 PROCEDURE get_role (pv_role IN VARCHAR2);
 PROCEDURE extract_user_role_details;
END users_granted_role;
/
CREATE OR REPLACE PACKAGE BODY users_granted_role
IS
 output_method VARCHAR2(1) :='S';
 skip_user VARCHAR2(1) := 'N';
 user_to_skip VARCHAR2(20) :='TEST%';
 --
 lg_fptr UTL_FILE.file_type;
 lv_file_or_screen VARCHAR2(1):='S';
 v_tag      VARCHAR2(8);
 v_filename VARCHAR2(30);
 v_today    VARCHAR2(8);
 --
 CURSOR find_all_roles IS
 SELECT    role
 FROM    dba_roles;
 --
PROCEDURE write_op (pv_str IN VARCHAR2) IS
 BEGIN
 v_today    := TO_CHAR(SYSDATE,'YYYYMMDD');
 v_tag      := 'UserPriv';
 v_filename := 'User_Privileges'|| v_today;
 
 IF lv_file_or_screen='S' THEN
 DBMS_OUTPUT.put_line(v_tag || v_filename||' '||pv_str);
 ELSE
 UTL_FILE.put_line(lg_fptr,pv_str);
 END IF;
 EXCEPTION
 WHEN UTL_FILE.invalid_path  THEN
 DBMS_OUTPUT.put_line('invalid path');
 WHEN UTL_FILE.invalid_mode  THEN
 DBMS_OUTPUT.put_line('invalid mode');
 WHEN UTL_FILE.invalid_filehandle  THEN
 DBMS_OUTPUT.put_line('invalid filehandle');
 WHEN UTL_FILE.invalid_operation  THEN
 DBMS_OUTPUT.put_line('invalid operation');
 WHEN UTL_FILE.read_error  THEN
 DBMS_OUTPUT.put_line('read error');
 WHEN UTL_FILE.write_error  THEN
 DBMS_OUTPUT.put_line('write error');
 WHEN UTL_FILE.internal_error  THEN
 DBMS_OUTPUT.put_line('internal error');
 WHEN OTHERS THEN
 DBMS_OUTPUT.put_line('ERROR (write_op) => '||SQLCODE);
 DBMS_OUTPUT.put_line('MSG (write_op) => '||SQLERRM);
 
END write_op;
 --
FUNCTION user_or_role(pv_grantee IN dba_users.username%TYPE) RETURN VARCHAR2 IS
 --
 CURSOR c_use (cp_grantee IN dba_users.username%TYPE) IS
 SELECT  'USER' userrole
 FROM    dba_users u
 WHERE   u.username=cp_grantee
 UNION
 SELECT  'ROLE' userrole
 FROM    dba_roles r
 WHERE   r.role=cp_grantee;
 --
 lv_use c_use%ROWTYPE;
 --
 BEGIN
 OPEN c_use(pv_grantee);
 FETCH c_use INTO lv_use;
 CLOSE c_use;
 RETURN lv_use.userrole;
 EXCEPTION
 WHEN OTHERS THEN
 DBMS_OUTPUT.put_line('ERROR (user_or_role) => '||SQLCODE);
 DBMS_OUTPUT.put_line('MSG (user_or_role) => '||SQLERRM);
END user_or_role;
 -----------------
FUNCTION role_pwd(pv_role IN dba_roles.role%TYPE)   RETURN VARCHAR2 IS
 --
 CURSOR c_role(cp_role IN dba_roles.role%TYPE) IS
 SELECT    r.password_required
 FROM    dba_roles r
 WHERE    r.role=cp_role;
 --
 lv_role c_role%ROWTYPE;
 --
 BEGIN
 OPEN c_role(pv_role);
 FETCH c_role INTO lv_role;
 CLOSE c_role;
 RETURN lv_role.password_required;
 EXCEPTION       
 WHEN OTHERS THEN NULL;
 --dbms_output.put_line('ERROR (role_pwd) => '||sqlcode);
 --dbms_output.put_line('MSG (role_pwd) => '||sqlerrm);
END role_pwd;
 --
PROCEDURE get_users(pv_grantee IN dba_roles.role%TYPE,pv_tabstop IN OUT NUMBER) IS
 --
 lv_tab VARCHAR2(50):='';
 lv_loop NUMBER;
 lv_user_or_role dba_users.username%TYPE;
 --
 CURSOR c_user (cp_username IN dba_role_privs.grantee%TYPE) IS
 SELECT  d.grantee,
 d.admin_option
 FROM    dba_role_privs d
 WHERE   d.granted_role=cp_username;
 --
 BEGIN
 pv_tabstop:=pv_tabstop+1;
 FOR lv_loop IN 1..pv_tabstop LOOP
 lv_tab:=lv_tab||CHR(9);
 END LOOP;
 
 FOR lv_user IN c_user(pv_grantee) LOOP
 lv_user_or_role:=user_or_role(lv_user.grantee);
 IF lv_user_or_role = 'ROLE' THEN
 IF lv_user.grantee = 'PUBLIC' THEN
 write_op(lv_tab||'Role => '||lv_user.grantee
 ||' (ADM = '||lv_user.admin_option
 ||'|PWD = '||role_pwd(lv_user.grantee)||')');
 ELSE
 write_op(lv_tab||'Role => '||lv_user.grantee
 ||' (ADM = '||lv_user.admin_option
 ||'|PWD = '||role_pwd(lv_user.grantee)||')'
 ||' which is granted to =>');
 END IF;
 get_users(lv_user.grantee,pv_tabstop);
 ELSE
 IF UPPER(skip_user) = 'Y' AND lv_user.grantee LIKE UPPER(user_to_skip) THEN
 NULL;
 ELSE
 write_op(lv_tab||'User => '||lv_user.grantee
 ||' (ADM = '||lv_user.admin_option||')');
 END IF;
 END IF;
 END LOOP;
 pv_tabstop:=pv_tabstop-1;
 lv_tab:='';
 EXCEPTION
 WHEN OTHERS THEN
 DBMS_OUTPUT.put_line('ERROR (get_users) => '||SQLCODE);
 DBMS_OUTPUT.put_line('MSG (get_users) => '||SQLERRM);       
END get_users;
----
PROCEDURE get_role (pv_role IN VARCHAR2) IS
 --
 CURSOR c_main (cp_role IN VARCHAR2) IS
 SELECT    p.grantee,
 p.admin_option
 FROM    dba_role_privs p
 WHERE    p.granted_role=cp_role;
 --
 lv_userrole dba_users.username%TYPE;
 lv_tabstop NUMBER;
 --
 --
BEGIN
 lv_tabstop:=1;
 FOR lv_main IN c_main(pv_role) LOOP   
 lv_userrole:=user_or_role(lv_main.grantee);
 IF lv_userrole='USER' THEN
 IF UPPER(skip_user) = 'Y' AND lv_main.grantee LIKE UPPER(user_to_skip) THEN
 NULL;
 ELSE
 write_op(CHR(9)||'User => '||lv_main.grantee
 ||' (ADM = '||lv_main.admin_option||')');
 END IF;
 ELSE
 IF lv_main.grantee='PUBLIC' THEN
 write_op(CHR(9)||'Role => '||lv_main.grantee
 ||' (ADM = '||lv_main.admin_option
 ||'|PWD = '||role_pwd(lv_main.grantee)||')');
 ELSE
 write_op(CHR(9)||'Role => '||lv_main.grantee
 ||' (ADM = '||lv_main.admin_option
 ||'|PWD = '||role_pwd(lv_main.grantee)||')'
 ||' which is granted to =>');
 END IF;
 get_users(lv_main.grantee,lv_tabstop);
 END IF;
 END LOOP;
 EXCEPTION
 WHEN OTHERS THEN
 DBMS_OUTPUT.put_line('ERROR (get_role) => '||SQLCODE);
 DBMS_OUTPUT.put_line('MSG (get_role) => '||SQLERRM);
END get_role;
 
PROCEDURE extract_user_role_details IS
BEGIN
 write_op('Users_granted_role: Release 1.0 - Author : Sagar PATIL on '|| SYSDATE);
 FOR role_to_find IN find_all_roles LOOP
 lv_file_or_screen:= UPPER(output_method);
 write_op(CHR(10));
 write_op('Investigating Role => '||UPPER(role_to_find.role)||' (PWD = '
 ||role_pwd(UPPER(role_to_find.role))||') which is granted to =>');
 write_op('====================================================================');
 get_role(UPPER(role_to_find.role));
 END LOOP;
EXCEPTION
 WHEN OTHERS THEN
 DBMS_OUTPUT.put_line('ERROR (main) => '||SQLCODE);
 DBMS_OUTPUT.put_line('MSG (main) => '||SQLERRM);
END extract_user_role_details;
END;
/

Run it as below

SQL> spool list_of_users.lst
SQL> set serveroutput on size 20000;
SQL> exec users_granted_role.extract_user_role_details;

UserPrivUser_Privileges20101026 Investigating Role => CONNECT (PWD = NO) which is granted to =>
====================================================================
UserPrivUser_Privileges20101026         User => WMSYS (ADM = NO)
UserPrivUser_Privileges20101026         User => SPATIAL_CSW_ADMIN_USR (ADM = NO)
UserPrivUser_Privileges20101026         User => SYSMAN (ADM = NO)
UserPrivUser_Privileges20101026         User => GRIDCONTROL (ADM = NO)
UserPrivUser_Privileges20101026         User => RMAN (ADM = NO)
UserPrivUser_Privileges20101026         User => MDDATA (ADM = NO)
UserPrivUser_Privileges20101026         User => OWBSYS (ADM = YES)
UserPrivUser_Privileges20101026         User => SYSMAN_MDS (ADM = NO)
UserPrivUser_Privileges20101026         User => SYS (ADM = YES)
UserPrivUser_Privileges20101026         User => MDSYS (ADM = NO)
UserPrivUser_Privileges20101026         User => SPATIAL_WFS_ADMIN_USR (ADM = NO)
UserPrivUser_Privileges20101026         User => APEX_030200 (ADM = YES)
UserPrivUser_Privileges20101026         User => SCOTT (ADM = NO)
UserPrivUser_Privileges20101026

UserPrivUser_Privileges20101026 Investigating Role => RESOURCE (PWD = NO) which is granted to =>
====================================================================
UserPrivUser_Privileges20101026         User => WMSYS (ADM = NO)
UserPrivUser_Privileges20101026         User => SCOTT (ADM = NO)
UserPrivUser_Privileges20101026         User => SPATIAL_CSW_ADMIN_USR (ADM = NO)
UserPrivUser_Privileges20101026         User => RMAN (ADM = NO)
UserPrivUser_Privileges20101026         Role => LOGSTDBY_ADMINISTRATOR (ADM = NO|PWD =NO) which is granted to =>
UserPrivUser_Privileges20101026                 User => SYS (ADM = YES)
UserPrivUser_Privileges20101026         User => EXFSYS (ADM = NO)
UserPrivUser_Privileges20101026         User => SPATIAL_WFS_ADMIN_USR (ADM = NO)
UserPrivUser_Privileges20101026         User => CTXSYS (ADM = NO)
UserPrivUser_Privileges20101026         User => OLAPSYS (ADM = NO)
UserPrivUser_Privileges20101026         User => MDSYS (ADM = NO)
UserPrivUser_Privileges20101026         User => SYSMAN_MDS (ADM = NO)
UserPrivUser_Privileges20101026         User => XDB (ADM = NO)
UserPrivUser_Privileges20101026         User => APEX_030200 (ADM = YES)
UserPrivUser_Privileges20101026         User => SYS (ADM = YES)
UserPrivUser_Privileges20101026         User => SYSMAN (ADM = NO)
UserPrivUser_Privileges20101026         User => OUTLN (ADM = NO)
UserPrivUser_Privileges20101026         User => MDDATA (ADM = NO)
PL/SQL procedure successfully completed.
SQL> spool off;

Leave a Reply

You must be logged in to post a comment.

Top of Page

Top menu