User management is done by this code

CREATE OR REPLACE procedure sp_chk_pwd_lock_time_setting(p_db_name in varchar2) as


— use of ref cursor is required to avoid ORA-04054 error

TYPE RefCurType1 IS REF CURSOR;
sql_cur RefCurType1;

TYPE RefCurType2 IS REF CURSOR;
sql_cur_1 RefCurType2;

v_pwd_lock_time sys.dba_profiles.limit%type;
v_value_str varchar2(1024);
v_value_str_1 varchar2(1024);
v_sql varchar2(1024);
v_sql_1 varchar2(1024);
v_dbid fusa.db_names.dbid%type;
begin

select dbid
into v_dbid
from fusa.db_names
where db_link_name = upper(p_db_name);

v_value_str:=null;

v_sql:=’select distinct profile from dba_profiles@’|| p_db_name ||’where
resource_name=’||”’FAILED_LOGIN_ATTEMPTS”’||’and limit
<>’||”’UNLIMITED”’;

OPEN sql_cur FOR v_sql;
loop
fetch sql_cur into v_value_str;
exit when sql_cur%notfound;

v_sql_1:=’select limit from dba_profiles@’||p_db_name||’where profile =
‘||v_value_str;

OPEN sql_cur_1 FOR v_sql_1;
loop
fetch sql_cur_1 into v_value_str_1;
exit when sql_cur%notfound;

if v_value_str_1 = ‘UNLIMITED’ then

update fusa.security_check_report
set chk_27 =’Y’,
Report_date_time = sysdate
where DBID= v_dbid;

else

update fusa.security_check_report
set chk_27 =’N’,
Report_date_time = sysdate
where DBID= v_dbid;

end if;
end loop;
end loop;

commit;
end;
/

CREATE OR REPLACE package pkg_user_manager as

procedure sp_create_user (p_user_name in varchar2,
p_default_tbsp in varchar2,
p_temp_tbsp in varchar2,
p_default_profile in varchar2 default null,
p_grant_quota in varchar2 default null,
p_quota in char default null,
p_quota_tbsp in varchar2 default null
);
procedure sp_grants_to_user (p_grants in varchar2,
p_username in varchar2 );

procedure sp_reset_password (p_username in varchar2,
p_old_password in varchar2
);

procedure sp_drop_user (p_user in varchar2);

procedure sp_report_90days_old;

procedure sp_lock_user (p_user in varchar2);

procedure sp_unlock_user (p_user in varchar2);

procedure sp_who_locked_my_account (p_user in varchar2);

procedure sp_failed_login;


end;
/

CREATE OR REPLACE package body pkg_user_manager as

— pre-requisite conditions
— grant to the owner select privilege on dba_users if
o7_dictionary_accessibility is set to false
— grant select privileges on dba_objects to the owner of this package if
o7_dictionary_accessibility is set to false

procedure sp_create_user (p_user_name in varchar2,
p_default_tbsp in varchar2,
p_temp_tbsp in varchar2,
p_default_profile in varchar2 default null,
p_grant_quota in varchar2 default null,
p_quota in char default null,
p_quota_tbsp in varchar2 default null
)
is
cursor users is
select username
from sys.dba_users;

sql_str varchar2(4000);
v_password varchar2(7);
p_password varchar2(7);

begin

v_password:= rand_pw( p_password );

— check if a user exists with the name proposed to create a user
for users_rec in users loop
if users_rec.username=p_user_name then
dbms_output.put_line(‘With this name the User is already in existance.’);
else
— if no user by the proposed name exits
sql_str:=’create user ‘||p_user_name||’ identified by ‘||v_password||’
default tablespace ‘||p_default_tbsp||’ temporary tablespace ‘||p_temp_tbsp;
— if profile is proposed for the new user then give that profile
if p_default_profile <> null then
sql_str:=sql_str||’ profile ‘||p_default_profile;
— if no profile is proposed
else
sql_str:=sql_str;
end if;
— if quota is proposed on a tablespace for the proposed user then
if p_grant_quota <> null then
sql_str:=sql_str||’ quota ‘;
if UPPER(p_quota) = ‘UNLIMITED’ or to_number(p_quota) > 0 then
sql_str:=sql_str||p_quota ||’ on ‘||p_quota_tbsp;
end if;
else
sql_str:=sql_str;
end if;
execute immediate sql_str;
dbms_output.put_line(‘ The user ‘||p_user_name||’ is created with the
password ‘||v_password );
end if;
end loop;
end sp_create_user;

— this procedure deals with the grants to the created user
— this is useful onl;y to grant roles to the users.
— separate the roles by using (,) comma.

procedure sp_grants_to_user (p_grants in varchar2, p_username in varchar2
)
as
sql_str varchar2(4000);
begin
sql_str:=’ grant ‘||p_grants ||’ to ‘||p_username;
execute immediate sql_str;
dbms_output.put_line(‘ The user ‘||p_username||’ is granted ‘||p_grants);
end sp_grants_to_user;

procedure sp_reset_password (p_username in varchar2,
p_old_password in varchar2
)
is
v_password varchar2(7);
p_password varchar2(7);
sql_str varchar2(4000);
begin

v_password:= rand_pw( p_password );
if p_old_password <> v_password then
sql_str:=’alter user ‘||p_username||’ identified by ‘||v_password;
execute immediate sql_str;
dbms_output.put_line(‘ The user ‘||p_username||’ is reset with the new
password ‘||v_password );
else
dbms_output.put_line(‘Old password can not be used ‘);
end if;

end sp_reset_password;

procedure sp_drop_user (p_user in varchar2)
is
sql_str varchar2(4000);
v_obj_count number;
cursor users is
select username
from sys.dba_users;

begin
— check if a user exists with the name proposed to drop a user
for users_rec in users loop
if users_rec.username=p_user then

select count(*)
into v_obj_count
from dba_objects
where owner=p_user;

if nvl(v_obj_count, 0)=0 then
sql_str:=’drop user ‘||p_user;
execute immediate sql_str;
dbms_output.put_line(‘The user ‘||p_user||’ has been dropped’);
else
sql_str:=’ drop user ‘||p_user||’ cascade’;
execute immediate sql_str;
dbms_output.put_line(‘The user ‘||p_user||’ has been dropped’);
end if;
else
dbms_output.put_line(‘There is no such user to drop’);
end if;
end loop;
end sp_drop_user;

procedure sp_report_90days_old
is
v_input_file_dir varchar2(1024);
v_input_file utl_file.file_type;
v_new_line varchar2(1024);
sql_str varchar2(1024);

— identify the default database users and exclude them from being
identified as older users
— for security purposes

cursor ninty_days_old is
select username,created, expiry_date
from dba_users
where expiry_date < sysdate – 90
and username not in (‘SYS’,’SYSTEM’,’OUTLN’,’DBSNMP’);

begin

for ninty_days_old_rec in ninty_days_old loop
if ninty_days_old_rec.username <> null then
v_new_line:=ninty_days_old_rec.username||’ created on
‘||ninty_days_old_rec.created||’ expired ‘||ninty_days_old_rec.expiry_date;

v_input_file:=utl_file.fopen(v_input_file_dir,’Expired_users’||to_char(sysdate,’mmddyyyyhh24miss’)||’.log’,’w’);
UTL_FILE.put_line(v_input_file,v_new_line);
else
dbms_output.put_line(‘There are no expired users ‘);
end if;
end loop;
utl_file.fclose(v_input_file);
end sp_report_90days_old;

procedure sp_lock_user (p_user in varchar2)
is
sql_str varchar2(4000);
begin
sql_str:=’alter user ‘||p_user||’ account lock’;
execute immediate sql_str;
end sp_lock_user;

procedure sp_unlock_user (p_user in varchar2)
is
sql_str varchar2(4000);
begin
sql_str:=’alter user ‘||p_user||’ account unlock’;
execute immediate sql_str;
end sp_unlock_user;

— for this procedure aelect privilege is to be granted on sys.aud$,
v$sessions, v$process tables
procedure sp_who_locked_my_account (p_user in varchar2)
is
cursor who_locked is
select timestamp#,
userid,
terminal,
action#,
returncode,
spare1
from sys.aud$
where userid = p_user;

v_input_file_dir varchar2(1024);
v_input_file utl_file.file_type;
v_new_line varchar2(1024);
sql_str varchar2(1024);
begin

for who_locked_rec in who_locked loop

if who_locked_rec.userid <> null then
v_new_line:=who_locked_rec.timestamp#||’ ‘||who_locked_rec.userid||’
‘||who_locked_rec.terminal||’ ‘||who_locked_rec.action#||’
‘||who_locked_rec.returncode||’ ‘||who_locked_rec.spare1;

v_input_file:=utl_file.fopen(v_input_file_dir,’who_locked_me’||to_char(sysdate,’mmddyyyyhh24miss’)||’.log’,’w’);
UTL_FILE.put_line(v_input_file,v_new_line);
else
dbms_output.put_line(‘No body ‘);
end if;
end loop;
utl_file.fclose(v_input_file);
end sp_who_locked_my_account;

procedure sp_failed_login
is
cursor failed_logins is
select timestamp#,
userid
from sys.aud$
where returncode=1017;

v_input_file_dir varchar2(1024);
v_input_file utl_file.file_type;
v_new_line varchar2(1024);
sql_str varchar2(1024);


begin

for failed_logins_rec in failed_logins loop
if failed_logins_rec.userid <> null then
v_new_line:=failed_logins_rec.userid||’ failed on
‘||failed_logins_rec.timestamp#;

v_input_file:=utl_file.fopen(v_input_file_dir,’failed_logins’||to_char(sysdate,’mmddyyyyhh24miss’)||’.log’,’w’);
UTL_FILE.put_line(v_input_file,v_new_line);
else
dbms_output.put_line(‘No body ‘);
end if;
end loop;
utl_file.fclose(v_input_file);

end sp_failed_login;

end pkg_user_manager;
/