create or replace package body pkg_nkw_grv timestamp '2006-07-07:10:10:10' is ------------------------------------------------------------------ -- 2006 DataPRO Developers - m@urelio ------------------------------------------------------------------ -- Version: 2.0.2 ------------------------------------------------------------------ -- Collections, Records, Variables, Constants, Exceptions, Cursors ------------------------------------------------------------------ ------------------------------------------------------------------ ----------------------- Private Section -------------------------- ------------------------------------------------------------------ ------------------------------------------------------------------ -- GRANT_REVOKE ------------------------------------------------------------------ procedure grant_revoke(ft_privileges in gt_privileges%TYPE) is lv_stmt varchar2(80); lv_nxtflw boolean := (TRUE); i binary_integer; begin begin i := ft_privileges.first; << privs_loop >> while (i is not null) loop lv_nxtflw := (TRUE); if (ft_privileges(i).username is null or ft_privileges(i).object_name is null or ft_privileges(i).object_type not in ('TABLE','VIEW','PACKAGE','PROCEDURE','FUNCTION')) then lv_nxtflw := (FALSE); end if; if (upper(ft_privileges(i).object_name) in ('PKG_NKW_USR','PKG_NKW_GRV')) then lv_nxtflw := (FALSE); end if; if (upper(ft_privileges(i).username) in ('NK','SYS','SYSTEM')) then lv_nxtflw := (FALSE); end if; if (ft_privileges(i).object_type in ('PACKAGE','PROCEDURE','FUNCTION')) then if (nvl(ft_privileges(i).select_flag,'N') || nvl(ft_privileges(i).insert_flag,'N') || nvl(ft_privileges(i).update_flag,'N') || nvl(ft_privileges(i).delete_flag,'N') <> 'NNNN') then lv_nxtflw := (FALSE); end if; else if (nvl(ft_privileges(i).execute_flag,'N') <> 'N') then lv_nxtflw := (FALSE); end if; end if; if (lv_nxtflw) then begin pkg_nkw_rpl.run_code('revoke all on ' || ft_privileges(i).object_name || ' from ' || ft_privileges(i).username); exception when pkg_nkw_exc.no_grant then null; when pkg_nkw_exc.no_role or pkg_nkw_exc.no_user then raise_application_error(-20000,'User ' || ft_privileges(i).username || ' does not exists.|'); when pkg_nkw_exc.no_object then raise_application_error(-20000,'Object ' || ft_privileges(i).object_name || ' does not exists.|'); when others then raise_application_error(-20000,'Where''s Nulaya ?' || chr(10) || 'PKG_NKW_GRV.GRANT_REVOKE' || chr(10) || sqlerrm); end; if (nvl(ft_privileges(i).execute_flag,'N') = 'Y') then lv_stmt := 'execute, '; end if; if (nvl(ft_privileges(i).select_flag,'N') = 'Y') then lv_stmt := lv_stmt || 'select, '; end if; if (nvl(ft_privileges(i).insert_flag,'N') = 'Y') then lv_stmt := lv_stmt || 'insert, '; end if; if (nvl(ft_privileges(i).update_flag,'N') = 'Y') then lv_stmt := lv_stmt || 'update, '; end if; if (nvl(ft_privileges(i).delete_flag,'N') = 'Y') then lv_stmt := lv_stmt || 'delete, '; end if; if (lv_stmt is not null) then lv_stmt := substr(lv_stmt, 1, (length(lv_stmt) - 2)); begin pkg_nkw_rpl.run_code('grant ' || lv_stmt || ' on ' || ft_privileges(i).object_name || ' to ' || ft_privileges(i).username); exception when pkg_nkw_exc.no_grant then null; when pkg_nkw_exc.no_role or pkg_nkw_exc.no_user then raise_application_error(-20000,'User ' || ft_privileges(i).username || ' does not exists.|'); when pkg_nkw_exc.no_object then raise_application_error(-20000,'Object ' || ft_privileges(i).object_name || ' does not exists.|'); when others then raise_application_error(-20000,'Where''s Nulaya ?' || chr(10) || 'PKG_NKW_GRV.GRANT_REVOKE' || chr(10) || sqlerrm); end; end if; lv_stmt := null; end if; i := ft_privileges.next(i); end loop privs_loop; end; exception when others then raise; end grant_revoke; ------------------------------------------------------------------ -- GET_OBJECTS_PRIVILEGES ------------------------------------------------------------------ procedure get_objects_privileges(fv_username in dba_users.username%TYPE, fv_type in user_objects.object_type%TYPE, ft_privileges in out gt_privileges%TYPE) is lv_owner# dba_users.user_id%TYPE; lv_user# dba_users.user_id%TYPE; lv_type sys.obj$.type#%TYPE; begin begin ft_privileges.delete; lv_owner# := pkg_nkw_sys.get_nkid; lv_user# := pkg_nkw_sys.get_schemaid(fv_username); lv_type := pkg_nkw_sys.get_object_type(fv_type); end; begin declare cursor obj_cur is select a.name, a.obj# from sys.obj$ a where (a.owner# = lv_owner#) and (a.type# = lv_type) order by a.name; obj_row obj_cur%ROWTYPE; i binary_integer := 1; begin if (not obj_cur%ISOPEN) then open obj_cur; end if; << obj_loop >> loop fetch obj_cur into obj_row; exit obj_loop when obj_cur%NOTFOUND; begin declare cursor priv_cur is select max(decode(a.privilege#,12,'Y','N')) execute_flag, max(decode(a.privilege#, 9,'Y','N')) select_flag, max(decode(a.privilege#, 6,'Y','N')) insert_flag, max(decode(a.privilege#,10,'Y','N')) update_flag, max(decode(a.privilege#, 3,'Y','N')) delete_flag from sys.objauth$ a where (a.obj# = obj_row.obj#) and (a.grantor# = lv_owner#) and (a.grantee# = lv_user#); priv_row priv_cur%ROWTYPE; begin if (not priv_cur%ISOPEN) then open priv_cur; end if; fetch priv_cur into priv_row; begin ft_privileges(i).username := upper(fv_username); ft_privileges(i).object_id := obj_row.obj#; ft_privileges(i).object_name := obj_row.name; ft_privileges(i).object_type := upper(fv_type); ft_privileges(i).execute_flag := nvl(priv_row.execute_flag,'N'); ft_privileges(i).select_flag := nvl(priv_row.select_flag,'N'); ft_privileges(i).insert_flag := nvl(priv_row.insert_flag,'N'); ft_privileges(i).update_flag := nvl(priv_row.update_flag,'N'); ft_privileges(i).delete_flag := nvl(priv_row.delete_flag,'N'); i := nvl(i,0) + 1; end; if (priv_cur%ISOPEN) then close priv_cur; end if; end; end; end loop obj_loop; if (obj_cur%ISOPEN) then close obj_cur; end if; end; end; exception when others then raise; end get_objects_privileges; ------------------------------------------------------------------ ------------------------ Public Section -------------------------- ------------------------------------------------------------------ -------------------------------------------------------------------- -- GET_PRIVILEGE -------------------------------------------------------------------- function get_privilege(fv_user# in sys.user$.user#%TYPE, fv_obj# in sys.obj$.obj#%TYPE, fv_privilege# in sys.objauth$.privilege#%TYPE) return varchar2 is retval varchar2(1) := 'N'; begin begin declare cursor priv_cur is select 'Y' from sys.objauth$ a where (a.obj# = fv_obj#) and (a.grantor# = pkg_nkw_sys.get_nkid) and (a.grantee# = fv_user#) and (a.privilege# = fv_privilege#) and (a.col# is null); begin if (not priv_cur%ISOPEN) then open priv_cur; end if; fetch priv_cur into retval; if (priv_cur%ISOPEN) then close priv_cur; end if; end; end; return retval; exception when others then raise; end get_privilege; ------------------------------------------------------------------ -- GET_USERS_PRIVILEGES ------------------------------------------------------------------ procedure get_users_privileges(fv_object in user_objects.object_name%TYPE, fv_type in user_objects.object_type%TYPE, ft_privileges in out gt_privileges%TYPE) is lt_users pkg_nkw_sys.gt_users%TYPE; lv_nkid# sys.user$.user#%TYPE; lv_user# sys.user$.user#%TYPE; lv_obj# sys.obj$.obj#%TYPE; i binary_integer; begin ft_privileges.delete; begin pkg_nkw_sys.get_users(lt_users); exception when others then raise; end; begin i := lt_users.first; << user_loop >> while (i is not null) loop begin lv_nkid# := pkg_nkw_sys.get_schemaid('NK'); lv_user# := pkg_nkw_sys.get_schemaid(lt_users(i).name); lv_obj# := pkg_nkw_sys.get_object_id(fv_object); end; begin declare cursor priv_cur is select max(decode(a.privilege#,12,'Y','N')) execute_flag, max(decode(a.privilege#, 9,'Y','N')) select_flag, max(decode(a.privilege#, 6,'Y','N')) insert_flag, max(decode(a.privilege#,10,'Y','N')) update_flag, max(decode(a.privilege#, 3,'Y','N')) delete_flag from sys.objauth$ a where (a.obj# = lv_obj#) and (a.grantor# = lv_nkid#) and (a.grantee# = lv_user#); priv_row priv_cur%ROWTYPE; begin if not priv_cur%ISOPEN then open priv_cur; end if; fetch priv_cur into priv_row; begin ft_privileges(i).username := lt_users(i).name; ft_privileges(i).object_id := lv_obj#; ft_privileges(i).object_name := fv_object; ft_privileges(i).object_type := fv_type; ft_privileges(i).execute_flag := nvl(priv_row.execute_flag,'N'); ft_privileges(i).select_flag := nvl(priv_row.select_flag,'N'); ft_privileges(i).insert_flag := nvl(priv_row.insert_flag,'N'); ft_privileges(i).update_flag := nvl(priv_row.update_flag,'N'); ft_privileges(i).delete_flag := nvl(priv_row.delete_flag,'N'); end; if priv_cur%ISOPEN then close priv_cur; end if; end; end; i := lt_users.next(i); end loop user_loop; end; exception when others then raise; end get_users_privileges; ------------------------------------------------------------------ -- GET_PACKAGES_PRIVILEGES ------------------------------------------------------------------ procedure get_packages_privileges(fv_username in dba_users.username%TYPE, ft_privileges in out gt_privileges%TYPE) is lv_username dba_users.username%TYPE := fv_username; lt_privileges gt_privileges%TYPE; begin begin get_objects_privileges(lv_username, 'PACKAGE', lt_privileges); exception when others then raise; end; ft_privileges := lt_privileges; exception when others then raise; end get_packages_privileges; ------------------------------------------------------------------ -- GET_PROCEDURES_PRIVILEGES ------------------------------------------------------------------ procedure get_procedures_privileges(fv_username in dba_users.username%TYPE, ft_privileges in out gt_privileges%TYPE) is lv_username dba_users.username%TYPE := fv_username; lt_privileges gt_privileges%TYPE; begin begin get_objects_privileges(lv_username, 'PROCEDURE', lt_privileges); exception when others then raise; end; ft_privileges := lt_privileges; exception when others then raise; end get_procedures_privileges; ------------------------------------------------------------------ -- GET_FUNCTIONS_PRIVILEGES ------------------------------------------------------------------ procedure get_functions_privileges(fv_username in dba_users.username%TYPE, ft_privileges in out gt_privileges%TYPE) is lv_username dba_users.username%TYPE := fv_username; lt_privileges gt_privileges%TYPE; begin begin get_objects_privileges(lv_username, 'FUNCTION', lt_privileges); exception when others then raise; end; ft_privileges := lt_privileges; exception when others then raise; end get_functions_privileges; ------------------------------------------------------------------ -- GET_TABLES_PRIVILEGES ------------------------------------------------------------------ procedure get_tables_privileges(fv_username in dba_users.username%TYPE, ft_privileges in out gt_privileges%TYPE) is lv_username dba_users.username%TYPE := fv_username; lt_privileges gt_privileges%TYPE; begin begin get_objects_privileges(lv_username, 'TABLE', lt_privileges); exception when others then raise; end; ft_privileges := lt_privileges; exception when others then raise; end get_tables_privileges; ------------------------------------------------------------------ -- GET_VIEWS_PRIVILEGES ------------------------------------------------------------------ procedure get_views_privileges(fv_username in dba_users.username%TYPE, ft_privileges in out gt_privileges%TYPE) is lv_username dba_users.username%TYPE := fv_username; lt_privileges gt_privileges%TYPE; begin begin get_objects_privileges(lv_username, 'VIEW', lt_privileges); exception when others then raise; end; ft_privileges := lt_privileges; exception when others then raise; end get_views_privileges; ------------------------------------------------------------------ -- SET_PRIVILEGES ------------------------------------------------------------------ procedure set_privileges(ft_privileges in gt_privileges%TYPE) is begin begin grant_revoke(ft_privileges); exception when others then raise; end; exception when others then raise; end set_privileges; end pkg_nkw_grv; / show errors