Create User in Oracle SQL

Create User in Oracle SQL with Complete Grants:

* CREATE USER scott --Assign User name
* IDENTIFIED BY tiger -- Assign password
DEFAULT TABLESPACE tools -- Assign space for table and index segments
TEMPORARY TABLESPACE temp; -- Assign sort space

DROP USER scott CASCADE; -- Remove user

GRANT CONNECT, RESOURCE TO scott;

GRANT DBA TO scott; -- Make user a DB Administrator

Remember to give the user some space quota on its tablespaces:

ALTER USER scott QUOTA UNLIMITED ON tools;

Oracle user accounts can be locked, unlocked, forced to choose new passwords, etc. For example, all accounts except SYS and SYSTEM will be locked after creating an Oracle9iDB database using the DB Configuration Assistant (dbca). DBA's must unlock these accounts to make them available to users.

Look at these examples:

ALTER USER scott ACCOUNT LOCK -- lock a user account
ALTER USER scott ACCOUNT UNLOCK; -- unlocks a locked users account
ALTER USER scott PASSWORD EXPIRE; -- Force user to choose a new password

--------------------

SQL> grant connect to alfredo;
sql> alter user alfredo account unlock;