REM
REM DBAToolZ NOTE:
REM	This script was obtained from DBAToolZ.com
REM	It's configured to work with SQL Directory (SQLDIR).
REM	SQLDIR is a utility that allows easy organization and
REM	execution of SQL*Plus scripts using user-friendly menu.
REM	Visit DBAToolZ.com for more details and free SQL scripts.
REM
REM 
REM File:
REM 	c_user_rebuild.sql
REM
REM <SQLDIR_GRP>USER MAINT</SQLDIR_GRP>
REM 
REM Author:
REM 	Vitaliy Mogilevskiy (vit100gain@earthlink.net)
REM 
REM Purpose:
REM	<SQLDIR_TXT>
REM	Drops [CASCADE] and recreates a USER with original password
REM	granting ONLY CONNECT and RESOURCE roles
REM	</SQLDIR_TXT>
REM
REM Usage:
REM	c_user_rebuild.sql (prompts for username_to_drop, default_ts, quota_in_MB)
REM 
REM Example:
REM	c_user_rebuild.sql
REM
REM
REM History:
REM	08-01-1998	VMOGILEV	Created
REM
REM

accept dummy prompt "WARNING this script will drop a user of your choice!"
accept dummy2 prompt "Press Enter to continue or ctl-C to cancel "
accept dummy2 prompt "Are you sure? "

-- col password new_value x noprint
col password new_value x

select password
from dba_users
where username = upper('&&username_to_drop');

drop user &&username_to_drop cascade;

create user &&username_to_drop identified by VALUES '&x'
default tablespace &&default_ts temporary tablespace temp;

grant connect, resource to &&username_to_drop;

revoke unlimited tablespace from &&username_to_drop;


alter user &&username_to_drop quota &quota_in_MB on &&default_ts ;

undefine username_to_drop
undefine x