Oracle Roles and Users audit report
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.