How To Create Oracle Account

Oracle Create User Statement with Example by Manish Sharma

Create a new user using Create user statement.

If you have ever worked with Oracle database then you must have come across different types of user accounts such as sys, hr, OE, Scott and many more. These are the user accounts which Oracle creates for you. What if you want to create your own user account? In this tutorial, you will learn how to use Oracle Create User Statement to create a new user account in the Oracle Database

Among several different ways, creating a user account using the Oracle Create User statement is the most common one. Using the Oracle "create user" statement you can create and configure new database user and by using this database user you can log on to your database.

Syntax of The Oracle Create User statement

CREATE USER username IDENTIFIED BY password/externally/globally
DEFAULT TABLESPACE tablespace_name TEMPERORY TABLESPACE tablespace_name
QUOTA size/unlimited ON tablespace_name
PROFILE profile_name
PASSWORD expire
ACCOUNT lock/unlock;

Create and User both are the keywords followed by the username. As username, you can give any name of your choice. After that, we have IDENTIFIED BY clause which helps the Oracle engine in authenticating the user. There are three ways of authenticating a user

Password: Password clause lets you create a local user and indicates that the user must specify a password to log on to the database. Remember here: passwords are case sensitive. Passwords can consist of only single-byte characters from your database character set regardless of whether the character set also has multi-byte characters.
Externally: This clause helps you in creating an external user. These external users and their authentication are managed by external services such as the operating system. The user who is authenticated by the operating system can easily access the database without being requested for a password. External users are classic regular database users (non-database administrators) who are assigned standard database roles (such as CONNECT and RESOURCE), but no SYSDBA (database administrator) or SYSOPER (database operator) privilege.
Globally: Through Global users, you can keep the login or authentication information at a Central Oracle Security Server rather than having passwords in every database to be accessed. Moreover, on the assignment of a global user, the authorization information can be assigned globally as well. This means that you can assign global roles to global users in the central Oracle Security Server, instead of assigning the roles in each database.

Default Tablespace

In the default tablespace, you have to specify the name of tablespace where this user will create its object. By objects I mean tables, indexes, synonyms etc. This is an optional clause, if you omit this clause then all the objects created by this user gets stored in the database default tablespace which is "users" tablespace in most cases.

Temporary Tablespace

All the temporary segments created by this user get stored here. Temporary segment such as temporary tables; Similar to default tablespace you have to specify the name of the temporary tablespace here. But make sure the tablespace you are going to specify here must be temporary tablespace. This is an optional clause so if you omit this clause then all the temporary objects created by this user get stored in the database default temporary tablespace which is "temp" tablespace.
Always remember the name of tablespace you will specify here must be a temporary tablespace and should have a standard block size. Also, it should not be an undo tablespace or a tablespace with automatic segment-space management

Quota Clause

Quota clause can help you in specifying the maximum amount of space that the user can allocate in the tablespace. In single create user statement you can have multiple quota clause. As you can see here our quota keyword is followed by size. You can either specify a limited size such as 50M or 100M, M stands for Megabyte here or just write UNLIMITED for allocating space without any limits. After the size, you have to specify the name of tablespace using ON clause.

Profile Clause

Using profile clause you can specify the profile you want to assign to the user. By Assigning profile you can limit the amount of database resources the user can use.
It's also an optional clause hence if you omit this clause database will assign a default profile to your user.

Password Expire Clause

This is the kind of settings which force your user to change the password on the first login attempt or say before the user can log in to the database. This is also an optional clause.

Account Clause

Using Account clause you can set the status of your user account. If you set the status on the lock then Oracle will create your user account but disable its access. This means that you will have your user account but you cannot use it. If you set the status on UNLOCK then you are all free to use it. Unlock means status will be open. By default its set on UNLOCK option.
It's again optional if you omit this clause. In case you do omit it then the Oracle will set the status of your account to default state on UNLOCK.

Example for Creating A New User Account In Oracle

How to create a user account with whole manual settings

Let's say you want to create a user by the name of Jarvis with authentication type password and want to allot 20MB space on users tablespace which will be its default tablespace and the temporary tablespace will be temp.

CREATE USER ironman IDENTIFIED BY jarvis
DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp
QUOTA 20M ON users PROFILE default ACCOUNT UNLOCK;

Above query will create a new user account by the name of ironman but you will not be able to log on to your database with this user because a newly created user cannot connect to the database until granted the CREATE SESSION system privilege.
So for that, you have to grant the CREATE SESSION system privilege to this user account – Ironman.

Here's how to do this.

Step 1: if on Windows machine just set your Oracle SID to your database in which you have created this user

C:/> set ORACLE_SID=orcl

If on Linux machine then first export your SID and then set it

$export ORACLE_SID=orcl
$set ORACLE_SID=orcl

Step 2:Log on to your database using sys user account with sysdba privileges.

C:/> sqlplus sys/password as sysdba

Step 3:grant the create session privilege

SQL>grant connect session to ironman;

That's all you have to do, your account is now created and ready to connect to your database.

How to create user account with whole default settings

You can create a new user account with all oracle default settings by using just a small very simple query.

CREATE USER ironman IDENTIFIED BY Jarvis;

That's all, just execute the above query and Oracle will create you a new user account with all the default settings.

Another way to Create A User In Oracle Database is through SQL Developer. We have discussed it in the next tutorial. Click here to check that out

That's it for this tutorial. Hope you enjoyed reading and learnt something new. In case if you have any doubts then feel free to drop me a message on my Facebook or Twitter. Thanks, and have a great day!