PKG_NKW_USR.SPB
Source Package Body
Gerenciamento de usuários.
create or replace package body pkg_nkw_usr
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 --------------------------
------------------------------------------------------------------
------------------------------------------------------------------
------------------------ Public Section --------------------------
------------------------------------------------------------------
------------------------------------------------------------------
-- SET_PASSWORD
------------------------------------------------------------------
procedure set_password(fv_username in dba_users.username%TYPE,
fv_password in varchar2)
is
begin
if (fv_username is null or fv_password is null or upper(fv_username) in ('VIP','NK','SYS','SYSTEM')) then
return;
end if;
begin
pkg_nkw_rpl.run_code('alter user ' || fv_username || ' identified by ' || fv_password);
exception
when pkg_nkw_exc.no_role or pkg_nkw_exc.no_user then
raise_application_error(-20000,'Usuário inexistente.|');
when pkg_nkw_exc.no_password then
raise_application_error(-20000,'Senha ausente ou inválida.|');
when others then
raise_application_error(-20000,'Where''s Nulaya ? ' || chr(10) ||
'PKG_NKW_USR.SET_PASSWORD' || chr(10) || sqlerrm);
end;
exception
when others then raise;
end set_password;
------------------------------------------------------------------
procedure set_password(fv_password in varchar2)
is
lv_stmt varchar2(4096);
begin
if (fv_password is not null) then
begin
pkg_nkw_rpl.run_code('alter user ' || user || ' identified by ' || fv_password);
exception
when pkg_nkw_exc.no_role or pkg_nkw_exc.no_user then
raise_application_error(-20000,'Vocę năo existe.'); -- :-)
when others then
raise;
end;
end if;
exception
when others then raise;
end set_password;
------------------------------------------------------------------
-- RESET_PASSWORD
------------------------------------------------------------------
procedure reset_password(fv_username in dba_users.username%TYPE)
is
begin
if (fv_username is null or upper(fv_username) in ('VIP','NK','SYS','SYSTEM')) then
return;
end if;
begin
pkg_nkw_rpl.run_code('alter user ' || fv_username || ' identified by ' || fv_username);
exception
when pkg_nkw_exc.no_role or pkg_nkw_exc.no_user then
raise_application_error(-20000,'Usuário inexistente.|');
when pkg_nkw_exc.no_password then
raise_application_error(-20000,'Senha ausente ou inválida.|');
when others then
raise_application_error(-20000,'Where''s Nulaya ? ' || chr(10) ||
'PKG_NKW_USR.RESET_PASSWORD' || chr(10) || sqlerrm);
end;
exception
when others then raise;
end reset_password;
------------------------------------------------------------------
-- GRANT_SESSION
------------------------------------------------------------------
procedure grant_session(fv_username in dba_users.username%TYPE)
is
begin
if (fv_username is null or upper(fv_username) in ('VIP','NK','SYS','SYSTEM')) then
return;
end if;
begin
pkg_nkw_rpl.run_code('grant create session to ' || fv_username);
exception
when pkg_nkw_exc.no_role or pkg_nkw_exc.no_user then
raise_application_error(-20000,'Usuário inexistente.|');
when others then
raise_application_error(-20000,'Where''s Nulaya ? ' || chr(10) ||
'PKG_NKW_USR.GRANT_SESSION' || chr(10) || sqlerrm);
end;
exception
when others then raise;
end grant_session;
------------------------------------------------------------------
-- REVOKE_SESSION
------------------------------------------------------------------
procedure revoke_session(fv_username in dba_users.username%TYPE)
is
begin
if (fv_username is null or upper(fv_username) in ('VIP','NK','SYS','SYSTEM')) then
return;
end if;
begin
pkg_nkw_rpl.run_code('revoke create session from ' || fv_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 name does not exist.|');
when others then
raise_application_error(-20000,'Where''s Nulaya ? ' || chr(10) ||
'PKG_NKW_USR.REVOKE_SESSION' || chr(10) || sqlerrm);
end;
exception
when others then raise;
end revoke_session;
------------------------------------------------------------------
-- SET_SESSION
------------------------------------------------------------------
procedure set_session(ft_session in gt_session%TYPE)
is
i binary_integer;
begin
i := ft_session.first;
while (i is not null) loop
if (upper(ft_session(i).session_flag) = 'Y') then
grant_session(ft_session(i).username);
elsif (upper(ft_session(i).session_flag) = 'N') then
revoke_session(ft_session(i).username);
end if;
i := ft_session.next(i);
end loop;
exception
when others then raise;
end set_session;
------------------------------------------------------------------
-- CREATE_USER
------------------------------------------------------------------
procedure create_user(fv_username in dba_users.username%TYPE,
fv_password in varchar2 := NULL)
is
begin
if (fv_username is null) then
raise_application_error(-20000,'Nome do usuário năo especificado.|');
end if;
begin
pkg_nkw_rpl.run_code('create user ' || fv_username || ' identified by ' || nvl(fv_password, fv_username) ||
' default tablespace ' || pkg_nkw_env.gv_dflt_ts ||
' temporary tablespace ' || pkg_nkw_env.gv_dflt_tts);
exception
when pkg_nkw_exc.yet_user then
raise_application_error(-20000,'Usuário já cadastrado.|');
when pkg_nkw_exc.invalid_username then
raise_application_error(-20000,'Nome de inválido de usuário.|');
when pkg_nkw_exc.no_password then
raise_application_error(-20000,'Senha inválida.|');
when others then
raise_application_error(-20000,'Where''s Nulaya ? ' || chr(10) ||
'PKG_NKW_USR.CREATE_USER' || chr(10) || sqlerrm);
end;
grant_session(fv_username);
exception
when others then raise;
end create_user;
------------------------------------------------------------------
-- DROP_USER
------------------------------------------------------------------
procedure drop_user(fv_username in dba_users.username%TYPE)
is
begin
if (fv_username is null or upper(fv_username) in ('VIP','NK','SYS','SYSTEM')) then
return;
end if;
begin
pkg_nkw_rpl.run_code('drop user ' || fv_username || ' cascade');
exception
when pkg_nkw_exc.is_connected then
raise_application_error(-20000,'Usuário está em sessăo neste momento.|');
when pkg_nkw_exc.no_role or pkg_nkw_exc.no_user then
raise_application_error(-20000,'Usuário inexistente.|');
when others then
raise_application_error(-20000,'Where''s Nulaya ? ' || chr(10) ||
'PKG_NKW_USR.DROP_USER' || chr(10) || sqlerrm);
end;
exception
when others then raise;
end drop_user;
------------------------------------------------------------------
-- SET_PREFERENCES
------------------------------------------------------------------
procedure set_preferences(fr_prefs in gr_prefs%TYPE)
is
begin
if (fr_prefs.va is not null) then
begin
begin
update nkw_usr_prefs a
set a.va = upper(fr_prefs.va)
where (a.username = user);
end;
if (SQL%ROWCOUNT = 0) then
begin
insert into nkw_usr_prefs(username, va, flag)
values(user, fr_prefs.va, fr_prefs.flag);
end;
end if;
end;
end if;
exception
when others then raise;
end set_preferences;
------------------------------------------------------------------
-- GET_PREFERENCES
------------------------------------------------------------------
function get_preferences
return gr_prefs%TYPE
is
retrec gr_prefs%TYPE;
begin
begin
declare cursor get_cur is
select a.*
from nkw_usr_prefs a
where (a.username = user);
begin
if (not get_cur%ISOPEN) then
open get_cur;
end if;
fetch get_cur into retrec;
if (get_cur%ISOPEN) then
close get_cur;
end if;
end;
end;
return retrec;
exception
when others then raise;
end get_preferences;
end pkg_nkw_usr;
|
