Oracle CREATE USER Tutorial With Examples

Whenever a person or a service connects to an Oracle Database instance, they need a way to identify and authenticate themselves. Usernames and passwords are a common way to do this.

In this article we are going to review the different types of Oracle database users and how to create them to grant access to the database.

In the article below we will walk through

• What a database user is used for
• Different roles and user types
• Syntax for Oracle CREATE USER
• CREATE USER permissions
• CREATE USER passwords, locking, and expiry
• EXTERNAL users
• GLOBAL users
• Finally, Dropping a user

What is a Database User?

A Database User or User Account is an Oracle object that primarily stores information about who can connect to the database and how they authenticate. An Oracle user includes:

• User name
• Authentication method
• Default tablespace and temporary tablespace
• Additional tablespaces and quotas on them
• User profile.
  A profile is a set of rules that limit the resources a user can consume, like the number of concurrent sessions or max CPU time per call. It also provides password related parameters, like the number of failed login attempts before locking the account, or the number of days a password is valid until it needs to be changed.
• User permissions / privileges

Permissions (or privileges) are divided into two main categories:

• System Privileges: General actions that the user can perform, like CREATE TABLE. They are usually granted by administrators.
• Objects Privileges: Actions that the user can perform on a specific objects they do not own, like a SELECT ON Sales.Invoices privilege given to a Reporting user. They can be granted by the user that owns the object or by an administrator.

User Roles

Oracle also has the concept of Roles. Roles are a group of privileges that can be granted as a unit to a user or to another role. They provide a simpler way to grant privileges to multiple users that require the same set of privileges, since we just need to grant the specific privileges to a role and then grant the single role to all users that require it. If later we need to add or remove a privilege, we just need to add it or remove it from the role rather than individually from each user. Roles are granted to a user or to another role in the same way as any other privilege.

Different User Types In Multitenant Deployments

Starting with Oracle 12c, Oracle database supports multitenant architecture with a top-level Container Database (known as CDB or root), which stores state accessible to all databases, along with multiple Pluggable Databases (known as PDBs). Oracle supports two types of users in this kind of environments:

• Common Users: Exist on the CDB and have access to the CDB plus all PDBs. This kind of user can switch from one DB to another. Common user names must begin with the string "C##" or "c##". Information about common users is stored in the root database, while specific permissions granted on each PDB are stored on each PDB.
• Local Users: Exist only on a particular PDB and have access only to that PDB resources. All information and permission granted to a loca user is stored on the PDB where it was created.

In the rest of this article we are going to focus on what are now called Local Users, how to create them and how to grant them privileges that allow them to connect and use a database.

Oracle CREATE USER Syntax

Basic syntax to create an Oracle user identified with a password (we will see additional authentication methos later) is shown:

CREATE USER username IDENTIFIED BY password
    [DEFAULT TABLESPACE tablespace]
    [TEMPORARY TABLESPACE tablespace]
    [QUOTA {size | UNLIMITED} ON tablespace]
    [PROFILE profile]
    [PASSWORD EXPIRE]
    [ACCOUNT {LOCK | UNLOCK}];

CREATE USER Options

We only need to provide a username and a password (note that they do not require any quotation), but we can also specify:

• The Default tablespace is where user objects will be stored. Also, a default temporary tablespace (to perform sort operations that cannot be handled in memory or create temporary objects) can be specified.
• A quota can be established on one or many tablespaces, allowing either a specific size or unlimited usage on each of them.
• A Profile can be specified for the user, that will limit the resources the user can consume. Users created without specifying a profile are automatically assigned the Default profile.
• Password Expiration forces the user to define a new password the first time they authenticate.
• Account Lock or Unlock (default) is the initial state of the account. You may want to create an account for a future feature but do not want it to be available immediately, in such a case you can create it with the LOCK option. Accounts can also be automatically locked after several login failures.

Local Users vs Local Users

Previously, this kind of user were sometimes referred as local users since information is stored locally in the database. This is now more confusing since Oracle 12c because the term local is used to referr to users on a PDB rather than common users who have access to the CDB and all PDBs.

Naming things is hard I guess.

Oracle CREATE USER Example

In the following example, we are creating a user Reporting with a temporary tablespce and an account lock.

CREATE USER Reporting IDENTIFIED BY mYp4sSw0Rd 
    TEMPORARY TABLESPACE tbs_tmp ACCOUNT LOCK;

When we are ready to start using the account, we just need to unlock it with the syntax:

ALTER USER Reporting IDENTIFIED BY mYp4sSw0Rd ACCOUNT UNLOCK;

Note : You can unlock the user without providing a new password, the account will be unlocked but the password will remain expired.

CREATE USER With Privileges

If we try to connect to our database with the user we create above, we are going to receive the following error message:

ERROR: ORA-01045:
user Reporting lacks CREATE SESSION privilege; logon denied

The problem is that while we created the user, we didn’t give it permission to do anything! The minimum privilege required to connect to the database is the CREATE SESSION system privilege, and it must be granted this way:

GRANT CREATE SESSION TO Reporting;

Once done, the Reporting user should be able to connect to the database, but cannot yet do anything useful.

Next, we need to provide permissions to perform tasks like creating tables. This can be done by granting some of the existing roles on the database, like CONNECT and RESOURCE:

GRANT CONNECT, RESOURCE TO Reporting;

Prior to version 10, the CONNECT role included basic permissions like CREATE SESSION, ALTER SESSION, CREATE TABLE, CREATE SEQUENCE and CREATE VIEW. In Oracle 10 it was modified to only include CREATE SESSION, and in Oracle 12c it added the SET CONTAINER permission to allow a common user to change the CDB or PDB that they are connected to.

The RESOURCE role includes additional permissions like CREATE TRIGGER or CREATE PROCEDURE and UNLIMITED TABLESPACE.

Although those two roles still exist on Oracle, it is recommended to create your own roles and grant only the minimum required privileges based on your actual needs rather than using RESOURCE.

Other Authentication Methods

Oracle provides additional authentication methods besides using a password.

External Users

External users are authenticated “externally” (not by Oracle) either by the operating system or by a third-party service. In this set-up, Oracle database relies on it to perform the authentication.

CREATE USER username 
IDENTIFIED EXTERNALLY [AS 'certificate_DN' | AS 'kerberos_principal_name']

When relying on the operating system, the username must be preceded by a prefix defined in the parameter OS_AUTHENT_PREFIX. For example, if the parameter is set to “OPS$” (the default value), and you want to create an Oracle user for the operating system user JSMITH, you need to use this syntax:

CREATE USER OPS$JSMITH IDENTIFIED EXTERNALLY;

When using a third party service, you need to provide either a SSL certificate name or a Kerberos name.

• certificate_DN is used when signing in with a SSL-authenticated user, and it must be the distinguished name in the user’s PKI certificate available in the user’s wallet.
• Kerberos_principal_name is a name of an existing Kerberos-authenticated user.

Global Users

Global users are authenticated by the enterprise directory service (also known as the Oracle Internet Directory) using a string that includes the CN and any other attribute that are part of the user’s distinguished name (DN) in the directory:

CREATE USER username IDENTIFIED GLOBALLY [AS 'directory_DN']

This example creates a user name “global_user” that will be identified by the directory as an analyst working on the division1 unit in the organization Oracle:

CREATE USER global_user
    IDENTIFIED GLOBALLY AS 'CN=analyst, OU=division1, O=oracle, C=US';

Global users can also be created without a directory distinguished name and will rely on users roles defined on Oracle Internet Directory (which we are not going to discuss here).

Finally, Dropping a user

At the end of the day, you might need a user to go away, you can drop them (like many other database objects), fairly simply:

DROP USER Reporting [CASCADE];

If you specify CASCADE Oracle will drop all of the objects in the Reporting user schema before dropping the user.

Wrapping Up

By this point you should be able to use CREATE USER to create a new user in your Oracle Database and authenticate them either locally, via the OS, or via an external service.

We hope you try running some of these commands in Beekeeper Studio, probably the most pleasant to use Oracle client on the market (although we’re obviously biased 😁).