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;
/
show errors