PKG_NKW_GRV.SPB
Source Package Body
Concessão, revogação de privilégios em objetos.
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;
|
