In oracle you can create a new user by following the syntax shown below:
Oracle Create User Syntax
Create User user_name Identified by password|Globally|externally Default Tablespace name1 Temporary Tablespace name2 Quota Size On name1 Password expire ACCOUNT Lock|Unlock;
NOTE: After creating a User you should provide at least CREATE SESSION privileges to it.
The keyword is used to create a new user in the Oracle database.
Identified By clause:
- In the above syntax Identify By Clause followed by password is used to set the login password for the user to access the database.
- Identified By Clause followed by externally states that, the access of the user is handled by any third party. However, when followed by Globally keyword states that it has global access.
- Default Tablespace name1 in the above syntax is a space that is generated for storing the object that will be created by the user.
- Temporary Tablespace name2 in the above syntax is the space that is meant to store the temporary object that will be created.
- Note: By default, the default tablespace name is set as users ie. name1=users.
- QUOTA SIZE on name1 keyword in the above syntax is used to allocate the maximum amount of space that can be used by the user. Quota is followed by size in M(megabyte) or you can simply write unlimited.
- Make sure to always use the name1 that is the name of the default tablespace with Quota.
- This statement helps the user to change the password just before login into the database.
- This statement summarises the status of the Account that is created. ACCOUNT UNLOCK is used to unlock the account if it is locked.
Create User in Oracle Example
You want to create a new user named ANEW. Follow the syntax to perform the same task:
Create User ANEW Identified by manager Default Tablespace Users Temporary Tablespace temp Quota 10M On Users Password expire ACCOUNT UNLOCK;