User Creation

SQL> create tablespace myuser datafile '/u01/oradata/mysid/myuser01.dbf' size 100M;

Tablespace created.

SQL> create user myuser identified by "mypassword" default tablespace myuser;

User created.

SQL> alter user myuser quota unlimited on myuser;

User altered.

SQL> grant connect, create table, create view, select any table to myuser;

Grant succeeded.

SQL>

---

to let the user login

SQL> GRANT CREATE SESSION TO salapati;
Grant succeeded.
SQL>

assign quota to user on users tablespace for the usre to be able to create tables

SQL> ALTER USER salapati
 2 QUOTA 100M ON users;
User altered.
SQL>

To give user unlimited space usage on all tablespaces:

SQL> GRANT UNLIMITED TABLESPACE TO salapati;
Grant succeeded.
SQL>

example showing how to create a user by explicitly specifying the default tablespaces (temporary and permanent). The GRANT QUOTA clause gives the user a 500MB space allocation in the USERS tablespace so the user can create objects there:

SQL> CREATE USER salapati IDENTIFIED BY sammyy1
     TEMPORARY TABLESPACE TEMPTBS01
     DEFAULT TABLESPACE USERS
     GRANT QUOTA 500M ON USERS;
User created.
SQL>

change user's password

SQL> ALTER USER salapati IDENTIFIED BY sammyy1;
User altered.
SQL>

To drop a user, you use the DROP USER statement, as shown here:

SQL> DROP USER salapati;
User Dropped.
SQL>

The DROP USER command will remove not only the user, but all objects owned by the user as well. If other objects in the database depend on this user, you won’t be able to use the simple DROP USER command—you must use the DROP USER . . . CASCADE statement, which drops the user, the user’s schema objects, and any dependent objects as well. Here’s an example:

SQL> DROP USER salapati CASCADE;
User Dropped.
SQL>

or simply leave the user and the user’s schema intact, but deny the user access to the database by using the following command:

SQL> REVOKE CREATE SESSION FROM salapati;
Revoke succeeded.
SQL>

In a case like this, you can change the password of a user temporarily and use the new pass- word to get in as that user. Before you change a user’s password, get the original encrypted password, which you can use to reset the user’s password back after you’re done. Here’s an example:

SQL> SELECT 'alter user tester identified by values '||password||';'
 2 FROM dba_users
 3* WHERE username='TESTER';
'ALTERUSERTESTERIDENTIFIED
---------------------------------------------------------
alter user tester identified by values 1825ACAA229030F1;
SQL>

Now change the password of user tester so you can log in as that user:

SQL> ALTER USER tester IDENTIFIED BY newpassword;

When you’re done using the tester user account, use the ALTER USER statement again to change user tester’s password back to its original value. Make sure you enclose the encrypted password in single quotes.

SQL> ALTER USER tester IDENTIFIED BY VALUES '1825ACAA229030F1';
User altered.
SQL>