Oracle CREATE USER Syntax and Examples

As a database administrator, you may need to give someone else access to the database without giving them full control. For instance, you hire developers to maintain your databases, but you don’t want to give them the ability to access, delete or modify sensitive information.

In this case, you must give them access to the database through a non-administrative account. This way, you will be able to keep track of what developers can and cannot do with your data.

In this guide, you’ll learn how to create a user in Oracle Database and grant him privileges to connect to the database. We’ll cover different user creation options and we’ll also provide some illustration examples..

Oracle CREATE USER Syntax

To create a new user in Oracle, you must be a DBA or be granted the CREATE USER system privilege.

Syntax

CREATE USER username 
IDENTIFIED BY password
[DEFAULT TABLESPACE tablespace_name]
[TEMPORARY TABLESPACE temp_ts_name]
[QUOTA UNLIMITED|size ON tablespace_name]
[PROFILE user_profile]
[PASSWORD EXPIRE]
[ACCOUNT LOCK|UNLOCK];

Note: Attributes in brackets are optional and are assigned default values when omitted.

Oracle CREATE USER attributes

AttributeDescriptionDefault value
IDENTIFIED BYallows Oracle to authenticate the user with a password to grant him access to the databaseN/A
DEFAULT TABLESPACEindicates the tablespace segments in which Oracle stores the user datausers
TEMPORARY TABLESPACEspecifies a temporary tablespace segments for the usertemp
QUOTAallows the user to store his data in others tablespace with a well-defined quota, in addition to his own tablespaceUNLIMITED (on user’s own tablespace)
PROFILEdatabase profile you want to assign to the userDEFAULT
PASSWORD EXPIREforces the user to change his password before Oracle grants him access to the databaseIf omitted, password doesn’t expire
ACCOUNT LOCK|UNLOCKtells Oracle if the user can access his account or notUNLOCK

Oracle CREATE USER Examples

Let’s illustrate with some examples.

Example 1:

The following example creates a database user account alekciss with  password OraclE19$.

CREATE USER alekciss 
IDENTIFIED BY OraclE19$;

Oracle Database Create User - Simple Mode

Note: Any missing attribute takes a default value.

Example 2:

The following example creates a user account and specifies the user password, default tablespace and tablespace quotas. Moreover, we force the user to change his password at the first successful login attempt.

CREATE USER aciss 
IDENTIFIED BY devApp$14
DEFAULT TABLESPACE dev_ts
QUOTA 10M ON dev_ts
QUOTA 700K on users
PASSWORD EXPIRE;

Oracle Database Create User - Advanced Mode

Now let’s try to connect to the database with user aciss.

Database login attempt

The database login attempt returns the following error message:

ORA-01045: user username lacks CREATE SESSION PRIVILEGE; logon denied.

Let’s fix it quickly.

Granting privileges to a user

The scope of a user in Oracle can be very limited. In fact, a newly created user cannot perform any action on the database, even connect to his own account.

As a DBA, you must assign a number of privileges to the user to allow him to interact with part of the database. This efficient privilege system allows you to control user actions on the database.

To assign a privilege to a user in Oracle, you can use the GRANT statement, followed by the list of privileges to be granted.

Example:

After creating a user, you may want to grant him the user the CREATE SESSION privilege, to log in to a database session.

GRANT CREATE SESSION TO aciss ;

Grant Create Session to a User

Let’s try again to connect to the database with the user aciss.

Login attempt 2

Altering a user in Oracle Database

You can easily alter an Oracle user account by modifying its attributes with the ALTER USER system privilege. Changing user domain affects the future user sessions, not current sessions.

The following example modify the user’s password, tablespace, quotas and lock the account.

ALTER USER aciss 
IDENTIFIED BY FormerDevApp$14
DEFAULT TABLESPACE users
QUOTA 1M ON dev_ts
ACCOUNT LOCK;

Dropping a user in Oracle Database

In some occasions, you may need to remove a user account from the database. To do so, you must have the DROP USER system privilege.

When you drop a user account, Oracle Database removes the user account and associated schema from the data dictionary. It also immediately drops all schema objects contained in the user schema, if any.

Example

DROP USER aciss CASCADE;

You might also like: Install Oracle Database Client Step by Step

Leave a Comment

Your email address will not be published.