How to Create Users in Oracle: Step-by-Step Guide

Creating users in Oracle Database is a fundamental skill for managing access control and security in your database environment. This tutorial will guide you through the process of creating users, assigning privileges, and ensuring your Oracle database remains secure.

Prerequisites

Access to an Oracle Database instance with administrative privileges (typically SYSTEM or SYS user).
SQL*Plus, SQL Developer, or any database client tool that allows you to run SQL commands.
Basic understanding of SQL syntax and Oracle user concepts.

Understanding Oracle Users

In Oracle, a user is essentially a schema owner and an account through which you connect to the database. Users can be granted privileges to perform actions such as creating tables, querying data, or administering the system.

Step 1: Connect to Oracle as an Administrator

The first step is to connect to your Oracle database as a user with the necessary privileges to create other users. For example, you can connect using SQL*Plus as SYS or SYSTEM:

sqlplus system/password@your_db

Step 2: Create a New User

Use the CREATE USER statement to create a new user. You need to specify the username and password. Optionally, you can assign default tablespaces and quotas.

CREATE USER new_username IDENTIFIED BY new_password
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON users;

Example:

CREATE USER hr_admin IDENTIFIED BY HrAdmin2025
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON users;

Step 3: Grant Required Privileges

After creating the user, grant the necessary system and object privileges so the user can perform actions in the database. Common grants include:

CREATE SESSION – permits the user to connect to the database
CREATE TABLE, CREATE VIEW, CREATE PROCEDURE – allow creating database objects
Other system privileges or roles depending on the user’s responsibilities

Example grants:

GRANT CREATE SESSION TO hr_admin;
GRANT CREATE TABLE, CREATE VIEW, CREATE PROCEDURE TO hr_admin;

For administrative users, you might grant additional roles such as DBA:

GRANT DBA TO hr_admin;

Note: Grant privileges cautiously to avoid security risks.

Step 4: Verify User Creation and Privileges

To verify the user was created successfully, query the data dictionary views. Connect as an administrative user and execute:

SELECT username, account_status FROM dba_users WHERE username = 'HR_ADMIN';

This will show the user’s account status (e.g., OPEN, LOCKED).

Step 5: Testing the New User

Test connecting with the new user credentials using your preferred database tool:

sqlplus hr_admin/HrAdmin2025@your_db

After connecting, test performing permitted operations, such as creating a table (if granted).

Troubleshooting Common Issues

ORA-01920: user name conflicts with another user’s or role’s name: Choose a unique username not used elsewhere.
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER: Ensure you connect with correct admin privileges.
User cannot connect: Verify that CREATE SESSION privilege is granted.
Insufficient privileges errors: Revisit granted privileges and roles to ensure coverage.

Security Best Practices

Grant least privilege necessary for the user’s tasks.
Assign strong passwords and consider expiry policies.
Regularly audit user accounts and privileges.
Use roles to simplify privilege management.

Summary Checklist

Connect as an administrative user
Create the user with a secure password and default tablespace
Grant appropriate privileges like CREATE SESSION
Verify the user exists and is enabled
Test login and permissions
Maintain security hygiene through audits and roles management

For more advanced Oracle tutorials, you might find useful reading our guide on how to install Oracle XE.

Creating and managing users correctly is crucial for effective database administration and security. Use these steps to confidently add and maintain users in your Oracle database.