How to Create an Oracle Database User

Edited 1 week ago by ExtremeHow Editorial Team

Oracle DatabaseUser ManagementDatabase ManagementSecurityAdmin ToolsSystem ConfigurationPerformanceAccess ControlCloud ComputingEnterprise Solutions

Creating Oracle database users is an essential step for database administration. It allows you to manage who can access the database and what activities they can perform. In this guide, we will explore the step-by-step process of creating users in Oracle Database. We will cover various aspects including specifying user credentials, defining quotas, and assigning roles. This document is designed to provide a comprehensive understanding for users who are new to Oracle Database as well as those who want to sharpen their skills.

Understanding the basics

Before we get into the detailed procedure, let us get familiar with some basic concepts related to Oracle Database User:

• User: In Oracle, a user is essentially a named account that has certain rights and permissions to access the database.
• Schema: Oracle uses the term "schema" to refer to the collection of database objects, such as tables and indexes, that a user creates and maintains.
• Privileges: Privileges are special rights that allow users to execute particular SQL statements or to access and modify particular objects within a database.

Prerequisites

To create users in Oracle Database, you must have the necessary privileges. Generally, you need CREATE USER system privilege to create users and ALTER USER system privilege to modify existing users. Make sure you have these privileges before proceeding. It is also necessary to have access to Oracle SQL*Plus or another Oracle Database interface to execute SQL commands.

Step-by-step process to create an Oracle user

Now, let's look at the process of creating a user account in Oracle Database step by step. We will cover everything from defining the username to setting privileges and storage quotas.

1. Connect to Oracle Database

The first step is to connect to the Oracle database as a user that has the necessary privileges. You can use Oracle SQL*Plus or another database administration tool. Using SQL*Plus you can connect as follows:

sqlplus sys as sysdba

When you run the above command, you will be asked to enter your password. After entering the password, you will be connected to the database.

2. Create a user

Once connected, you can create a new user by executing CREATE USER statement. Here is the basic syntax:

CREATE USER <username> IDENTIFIED BY <password>;

<username> is the name of the new user, and <password> is the password for the new user account. Here's an example:

CREATE USER john_doe IDENTIFIED BY StrongPassword123;

In this example, a new user named john_doe is created with the password StrongPassword123. This password should be a good balance of complexity, involvement of alphanumeric characters, and no dictionary words for security purposes.

3. Assign privileges

After creating a user, the next step is to grant the user sufficient privileges to perform specific operations. There are two main types of privileges:

• System privileges: These privileges allow users to perform tasks that affect the entire database. Example: CREATE SESSION, CREATE TABLE.
• Object privileges: These privileges allow users to perform actions on specific database objects such as tables and views. Example: SELECT, INSERT.

To grant system privileges, use GRANT statement:

GRANT CREATE SESSION TO john_doe;

The above SQL command grants CREATE SESSION privilege, allowing the new user to log in to the database. You can also grant multiple privileges at once:

GRANT CREATE SESSION, CREATE TABLE TO john_doe;

4. Set quota

Quotas specify the amount of space a user can use in a specific tablespace. It is a good practice to set a quota for each user to avoid excessive space usage. Here is how you can set a quota:

ALTER USER john_doe QUOTA 100M ON users_tablespace;

The above command allows john_doe to use up to 100 megabytes of space in users_tablespace. If you want the user to have unlimited space in the tablespace, you can use the following command:

ALTER USER john_doe QUOTA UNLIMITED ON users_tablespace;

5. Assign roles

Roles in Oracle are named groups of related privileges that can be assigned to users. This simplifies privilege management. For example, you can create a role with select privileges on multiple tables and then assign that role to a user:

GRANT <role_name> TO john_doe;

Here is an example to assign the DBA role to the user john_doe:

GRANT DBA TO john_doe;

Note that the DBA role is powerful and should only be granted to trusted users.

6. Verify the user

It is prudent to ensure that your operations succeed. To verify the creation of a new user and privilege assignment, you can query the system's data dictionary views. For example, to list users, execute:

SELECT username FROM all_users ORDER BY username;

You can query USER_SYS_PRIVS or USER_ROLE_PRIVS views to check which privileges or roles have been granted to the new user:

SELECT * FROM USER_SYS_PRIVS WHERE username = 'JOHN_DOE';

Security considerations

Always maintain strong security practices when managing users in an Oracle database:

• Use strong and complex passwords for all users.
• Avoid using default user accounts for production activities.
• Regularly review and audit user privileges.
• Enforce password policies to ensure passwords are changed regularly and maintain complexity.

By following these security best practices, you help protect the Oracle database environment from unauthorized access and potential breaches.

Conclusion

Creating an Oracle Database user involves a series of clear and structured steps, from connecting to the database, defining the user, setting privileges, quotas, and roles. By following the steps outlined in this guide, administrators can effectively manage users, ensuring they have the right access while maintaining the security and integrity of the database. A thorough understanding and careful implementation of each step contributes to efficient database administration and the security of the Oracle Database environment.

  Share This Article

If you find anything wrong with the article content, you can request an update