Oracle Create User

Users in Oracle databases are useful as they allow a specific account to connect to the database instance and perform various operations. They also enable database administrators to limit the permissions of a given user account.

This can then determine what functions an account can achieve, the objects they can access on the database, etc. Additionally, users can help prevent database compromise in case of a security exploit.

In this article, we will learn the usage of the CREATE USER statement in the Oracle database to create a new user.

Oracle Create User Statement

As stated, this statement allows you to create a database user who can log in and perform set actions.

The syntax of the CREATE USER command is as shown:

The above statement describes the options for creating a database user in Oracle Server.

Let us break down the provided statement to understand what each clause does.

CREATE USER

We start with the CREATE USER clause followed by the username we wish to create. This must be a unique value that conforms to the naming conventions in Oracle. Next, ensure that the target username does not exist.

IDENTIFIED BY PASSWORD

The IDENTIFIED BY clause enables to specify of the login password for the defined user. In addition, Oracle allows you to create an external or global user.

Check out our tutorials on the topic to learn more.

DEFAULT TABLESPACE

This section allows you to specify the tablespace of the objects which the defined user can create. If this value is not determined, the objects created by the user will be stored in the default tablespace, called users. Oracle will default to the system tablespace if there is no default tablespace.

TEMPORARY TABLESPACE

This defines the tablespace or tablespace group for the user’s temporary segments.

QUOTA size_clause

The QUOTA clause defines the maximum space in the specified tablespace that the determined user can allocate. To set an unrestricted size, set the quota size to UNLIMITED.

You can also specify multiple quota clauses for multiple tablespaces. However, remember that you cannot size the quota limit on a temporary tablespace.

PROFILE

This defines the user profile you wish to assign to the created user. In addition, the profile limits the resources a user can use on the target database. If this clause is missing, Oracle will assign the DEFAULT profile to the target user.

PASSWORD EXPIRE

This clause forces the user’s password to expire, forcing them to change it before logging into the database. This is an outstanding feature as a database administrator, as it prevents users from re-using passwords.

ACCOUNT LOCK | UNLOCK

This clause is used to define the state and access of the created account. Then, use the ACCOUNT LOCK clause to lock the user’s account and deny all access. Otherwise, to unlock the account and allow access, use ACCOUNT UNLOCK.

Before creating any users in the database using the CREATE USER statement, you need to have sufficient privileges on the database.

Oracle Create User Example Illustrations

The following are some examples of the CREATE USER statement in Oracle Databases:

Example 1 – Oracle Create Local User

The following example demonstrates creating a local user with a username and password:

The statement above should create a local user with the username linuxhint and password of ‘password.’

Example 2 – Oracle Create User with Unlimited Quota

To create a user with unlimited quota, run the command:

The above query should allocate unlimited space on the native tablespace.

Example 3 – Oracle Create User with Password Expire

In the example below, we demonstrate how to use the PASSWORD EXPIRE clause to force the user to change the password on login.

In this case, the password is set to expire.

Example 4 – Oracle Create User in Locked State

To create a user account in a locked state, we can run a query as shown:

Closing

This post taught you how to use the CREATE USER statement in Oracle to create new database users.