SPOOL pre_import.log append

-- Создаем роль kfm_role
var row_exists number;
begin
  SELECT count(*) into :row_exists FROM DBA_ROLES WHERE ROLE = 'KFM_ROLE';
  if :row_exists = 0 then
    execute immediate 'create role KFM_ROLE not identified';
  end if;
end;
/
-- Привилегии
  GRANT SELECT ON SYS.V_$SESSION TO KFM_ROLE;
  GRANT CREATE SESSION TO KFM_ROLE;
/

-- Пакет sys.pkg_systasks
CREATE OR REPLACE package SYS.PKG_SYSTasks
as

  function AddUser(pLoginName in varchar2, pPassword in varchar2) return number;
  function ChangePassword(pLoginName in varchar2, pPassword in varchar2) return number;

end PKG_SYSTasks;
/

CREATE OR REPLACE package body SYS.PKG_SYSTasks
as

function AddUser(pLoginName in varchar2, pPassword in varchar2) return number
is
 Result number default 0;
 vLoginName varchar2(30);
 vPassword varchar2(30);
 vCountUser number;

 vStrTmp varchar2(200);
begin

 vLoginName := pLoginName;
 vPassword := pPassword;

 select count(*)
 into vCountUser
 from all_users au
 where au.username = vLoginName;

 if vCountUser = 0 then
	 begin
		 vStrTmp := 'create user '||vLoginName||' identified by '||vPassword||' default tablespace KFM profile DEFAULT';
		 execute immediate vStrTmp;
		 vStrTmp := 'grant kfm_role to '||vLoginName;
		 execute immediate vStrTmp;
		 vStrTmp := 'grant connect to '||vLoginName;
		 execute immediate vStrTmp;
	 exception
		 when OTHERS then
				Return(-1);
	 end;
 end if;

 if vCountUser = 1 then
	 begin
		 vStrTmp := 'grant kfm_role to '||vLoginName;
		 execute immediate vStrTmp;
	 exception
		 when OTHERS then
				Return(-1);
	 end;
 end if;

 return(Result);
end;

function ChangePassword(pLoginName in varchar2, pPassword in varchar2) return number
is
 Result number default 0;
 vLoginName varchar2(30);
 vPassword varchar2(30);
 vCountUser number;
begin

 vLoginName := UPPER(pLoginName);
 vPassword := pPassword;

 select count(*)
 into vCountUser
 from all_users au
 where au.username = vLoginName;

 if vCountUser <> 1 then
  Return(-1);
 end if;

 begin
	 execute immediate 'alter user '||vLoginName||' identified by '||vPassword;
 exception
   when OTHERS then
/*	    dbms_output.put_line(SQLCODE);
	    dbms_output.put_line(SQLERRM);
*/      Return(-1);
 end;

 return(Result);
end;

end PKG_SYSTasks;
/

create or replace public synonym PKG_SYSTasks for SYS.PKG_SYSTasks;
/
GRANT EXECUTE ON SYS.PKG_SYSTASKS TO KFM_ROLE;
/
begin
  SELECT count(*) into :row_exists FROM DBA_USERS WHERE USERNAME = 'KFM';
  if :row_exists = 0 then
    execute immediate 'CREATE USER KFM IDENTIFIED BY kfm DEFAULT TABLESPACE KFM PROFILE DEFAULT ACCOUNT UNLOCK';
  else
    execute immediate 'ALTER USER KFM DEFAULT TABLESPACE KFM PROFILE DEFAULT ACCOUNT UNLOCK';
  end if;
end;
/

-- Для компиляции пакетов. См. http://docs.oracle.com/cd/B28359_01/network.111/b28531/authorization.htm#i1007304
GRANT SELECT ON SYS.V_$SESSION TO KFM;
GRANT EXECUTE ON SYS.PKG_SYSTASKS TO KFM;

GRANT MANAGE FILE GROUP TO KFM;
GRANT READ ANY FILE GROUP TO KFM;
ALTER USER KFM QUOTA UNLIMITED ON KFM;

GRANT KFM_ROLE TO KFM;
