CREATING A USER IN ORACLE

                     Here is an example for simple creation of a user in oracle 10g.

Purpose:

Create the user by using the CREATE USER  statement. It is an account through which the user can login to the database and access the data by the privileges he has.

SYNTAX:

SQL> CERATE USER username [IDENTIFIED BY password |EXTERNALLY]

 DEFAULT  TABLESPACE  tablespace 

TEMPORARY TABLESPACE  [tablespace|tablespace_group_name]

QUOTA [UNLIMITED|value] ON tablepsace PROFILE profile_name

PASSWORD EXPIRE

ACCOUNT  [UNLOCK|UNLOCK];

Username:

It specifies the name of the user account which is to be created.

IDENTIFIED BY:

It is that how the user will be identified i.e. how the oracle database authenticates the user account.

BY password:

It specifies that the user is a local database user, who can login into the database directly using the password

EXERNALLY:

It specifies that the user created as an external user. In this the user is authenticated by the operating system password file.

DEFAULT TABLESPACE:

This clause specifies the default storage of the objects created and owned by that user. If the default tablespace is not specified, then the objects that are owned by the user defaultly stored in the default tablespace of the database.

TEMPORARY TABLEPSACE:

It specifies the default temporary tablepsace storage. It is used to store the temporary(sorted) data during the sql operations done by the user. A singe tablespace or tablespace group can be assigned to the user.

QUOTA:

This clause specifies allocating specific amount of the space(quota) on different tablespaces. So, the user is restricted to use more than this quota size on that particular tablepsace.

PROFILE:

 This clause specifies to set the resource limits to the user such as database resources, password limits and OS resources.

PASSWORD EXPIRE:

It is to specify that the password created by the DBA in the identified clause expires. So that when the user login to the database, then he is prompted to give the new password to that account.

ACCOUNT:

It specifies the user account to unlock or lock. The user unable to access the account if the UNLOCK parameter is used for this. If the user

Example for creating a user:

SQL> CREATE USER sample IDENTIFIED BY sample  

DEFAULT TABLESPACE TBL_SAMPLE_DATA

QUOTA UNLIMITED ON IDX_SAMPLE_DATA

PROFILE default

PASSWORD EXPIRE

ACCOUNT UNLOCK;

Example for creating an external user:

In this example the authentication of the user is done by using the password file.

1. The password file should be created in the $ORACLE_HOME/dbs location

$ cd $ORACLE_HOME/dbs

$ orapwd file=orapwd<SID> password=<password> entries=<max_users>    

                  Ex: $ orapwd file=orapwdACDB2 password=oracle entries=5

2. Set the initialization parameter REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
3. Create the user sample

    SQL> CREATE USER SAMPLE IDENTIFIED EXTERNALLY;

Note: The password file users information can be viewed in the v$pwd_users view.