Create a New User

--+------------------------------------------------------------
--| CrUser.Sql
--| Script To Create New User
--+------------------------------------------------------------
Accept UserName Prompt 'Enter name of user to create: '

Drop User &UserName Cascade;

Create User &UserName 
   Identified By &UserName
   Default Tablespace Users
   --Temporary Tablespace Temp  /* automatic in 10 and 11 */
   Quota 0 On System
   Quota Unlimited On Users
   --Quota Unlimited On Indx
   Password Expire;

--+------------------------------------------------------------
--| Allows User To Logon
--+------------------------------------------------------------
Grant Connect To &UserName;    

--+------------------------------------------------------------
--| Provides User With Tablespace
--+------------------------------------------------------------
Grant Resource To &UserName;  

--+------------------------------------------------------------
--| Get connected to system instead of typing connect user/user
--+------------------------------------------------------------
Grant Create Session To &UserName; 

Basic code received from anonymous DBA