Oracle Create User – Detailed Guide

In this Oracle tutorial, we will learn how to create a user in the oracle database 21c & 19c. Also, we will cover these topics:

• Oracle create user
• Oracle create a user with all privileges
• Oracle create a user and grant all privileges
• Oracle create a user with dba privileges
• Oracle create a user with same privileges
• Oracle create a user with sysdba privileges
• Oracle create a user with select privileges only
• Oracle create a user with admin priviliges
• Oracle create a user with root privileges
• Oracle create a user in pdb
• Oracle create user schema
• Oracle create user profile
• Oracle create dblink identified by values

Oracle create user

Each organization has a different set of departments handled by professionals. These professionals require access to the oracle database to perform assigned responsibilities. The username is created for each individual who will be accountable for their activities on the database.

Oracle provides common users who can access the Container Database (CDB) and Portable Database (PDB). Common users have C## or c## has prefix followed by any name. example: C##SCOTT

Below is the syntax for creating a common user in Oracle Database 19c and 21c:

CREATE USER C##<USER> IDENTIFIED BY <password>;

Other than the common user you can also create normal users that do not require any prefixes but these users can be created only inside the Portable database and their access is limited to the current PDB.

Below syntax shows how to connect to PDB and then create a new user in oracle database 19c & 21c:

-- LOGIN TO PDB
ALTER SESSION SET CONTAINER = orclpdb;

-- CREATE USER
CREATE USER James IDENTIFIED BY "James@123"; 

After a user is created, they will require permission to perform the task on the oracle database. But, without permission, they won’t be able to connect, create sessions, perform any activity with that user. In the next section, we will discuss privileges or permissions.

Read: Oracle check database status

Oracle create user with all privileges,

Privileges are the permission given to users to perform a particular task in the oracle database. Privileges vary from task to task as each employee deals with a different part of the database.

Operational employees have access to the part of a database that holds information about their profile and manager, whereas the HR team has access to all the employees department-wise, Admin has access to all the users in that organization.

Root database administrator (DBA) has all the privileges for all the groups and users of that organization. It has access to all the branches of that organization as well. A root database administrator is the most powerful team in the organization.

Privileges are required for all types of activities like creating, connecting, manipulating databases, groups, and users. All these Privileges are further grouped into two parts:

• System Privileges
• Object Privileges

System Privileges

System privileges are powerful special rights granted to a user to perform administrative tasks in the oracle database 19c & 21c. Create and drop a user or tablespace, rollback, lock table, import, export, etc. These are a few examples of actions on any schema object.

In nutshell, system privileges provide permissions to handle activities outside the table.

Here is the list of all the System Privileges

Object Privileges

Object privileges are the rights given to a user to access and perform some actions on the database objects or the objects owned by some other user in the database.

Objects are tables, views, sequences, procedures, etc. Action means Alter, delete, execute, select, update, reference, insert. Sys and Owner of the object have all the privileges on its object.

Object privilege is the permission to deal with the activities within the table.

Here is the list of all the Object Privileges

Any user with Grant All Privileges has access to system privileges in the oracle database 19c and 21c.
In the below example, we have created a user with all privileges, James is the username and pwd is the password for a user.

GRANT ALL PRIVILEGES TO James IDENTIFIED BY pwd;

In the next section, we will throw more light on granting all privileges in oracle databases 21c and 19c.

Read: Connect to oracle database

Oracle create user and grant all privileges

Grant All privileges provide access to all the System Privileges to the user. Now users can perform all the administrative tasks like creating and dropping the user, rollback and locking the table, import and exporting the databases.

It is not advised to create a user with all privileges because it a too much power and if used unethically can create serious troubles for the company.

Using GRANT ALL PRIVILEGES keyword followed by username and password we can create a new user with all the privileges in oracle database 19c and 21c.

Below is the syntax to grant all the privileges to a user:

GRANT ALL PRIVILEGES TO <username> IDENTIFIED BY <password>;

In the below example, we have created a user PythonGuides with all the privileges.

GRANT ALL PRIVILEGES TO PythonGuides IDENTIFIED BY "Python@Guides";

Now user PythonGuides can connect, Select, Create, Delete and Update any table in the oracle database. Moving forward we will learn about dba privileges.

Read: Oracle get database name

Oracle create user with dba privileges

Oracle Database Administrator (DBA) has all system privileges with root options. which means he can create user, drop, change passwords for users, groups, and tablespace, lock & unlock accounts, etc.

In oracle, a set of privileges are bundled under one role name and this role name can be assigned to a user. In this case, DBA is the role name and it can be assigned to a user using the Grant DBA statement in oracle database 21c & 19c.

Below is the syntax to create a user with dba privileges in oracle database 21c and 19c:

GRANT DBA to <user> IDENTIFIED BY <password>;

The role is the collection of privileges in the oracle database and all the roles are present inside role_role_privs . DBA role privileges can be viewed using the below command:

SELECT *
FROM role_role_privs
WHERE ROLE = 'DBA';

Here is the output of the above script, in this output, the DBA role has granted the below-mentioned roles in oracle database 21c and 19c.

----------------------------------------------------------------------
ROLE  |   GRANTED_ROLE           |  ADMIN_OPTION | COMMON | INHERITED
----------------------------------------------------------------------
DBA	SELECT_CATALOG_ROLE	           NO	   YES	    YES
DBA	EXECUTE_CATALOG_ROLE	           NO	   YES	    YES
DBA	CAPTURE_ADMIN	                   NO	   YES	    YES
DBA	EXP_FULL_DATABASE	           NO	   YES	    YES
DBA	IMP_FULL_DATABASE	           NO	   YES	    YES
DBA	ACCHK_READ	                  YES	   YES	    YES
DBA	DATAPUMP_EXP_FULL_DATABASE	   NO	   YES	    YES
DBA	DATAPUMP_IMP_FULL_DATABASE	   NO	   YES	    YES
DBA	GATHER_SYSTEM_STATISTICS	   NO	   YES	    YES
DBA	OPTIMIZER_PROCESSING_RATE	   NO	   YES	    YES
DBA	EM_EXPRESS_ALL	                   NO	   YES	    YES
DBA	SCHEDULER_ADMIN	                   NO	   YES	    YES
DBA	PPLB_ROLE	                   NO	   YES	    YES
DBA	XDBADMIN	                   NO	   YES	    YES
DBA	XDB_SET_INVOKER	                   NO	   YES	    YES
DBA	WM_ADMIN_ROLE	                   NO	   YES	    YES
DBA	JAVA_ADMIN	                   NO	   YES	    YES
DBA	OLAP_XS_ADMIN	                   NO	   YES	    YES
DBA	OLAP_DBA	                   NO	   YES	    YES

Privileges in the DBA role can be viewed from DBA_SYS_PRIVS. Use the below script to get the list of privileges gathered under the DBA role in oracle database 19c and 21c.

SELECT PRIVILEGE
FROM DBA_SYS_PRIVS;

Below is the output of the above query, we have displayed a few records only. Use the Count() aggregate function to get the exact number of privileges in the oracle database.

CREATE ANY ANALYTIC VIEW
INSERT ANY MEASURE FOLDER
UPDATE ANY CUBE
ALTER ANY MINING MODEL
DROP ANY MINING MODEL
DROP ANY ASSEMBLY
MANAGE FILE GROUP
CREATE ANY RULE
IMPORT FULL DATABASE
ALTER ANY RULE SET
DROP ANY EVALUATION CONTEXT
ALTER ANY EVALUATION CONTEXT
DROP ANY OUTLINE
ALTER ANY OUTLINE
CREATE ANY OUTLINE
DEQUEUE ANY QUEUE
DROP ANY OPERATOR
ALTER ANY LIBRARY
CREATE ANY DIRECTORY
ALTER ANY MATERIALIZED VIEW
CREATE MATERIALIZED VIEW
ANALYZE ANY
ALTER ANY TRIGGER
FORCE TRANSACTION
ALTER ANY ROLE
SELECT ANY SEQUENCE
CREATE SEQUENCE
DROP ANY VIEW
CREATE ANY VIEW
CREATE ANY SYNONYM
SELECT ANY TABLE
BECOME USER
ALTER TABLESPACE
DROP ANY HIERARCHY
ALTER LOCKDOWN PROFILE
EM EXPRESS CONNECT
UPDATE ANY CUBE BUILD PROCESS
CREATE ANY CUBE BUILD PROCESS
SELECT ANY CUBE

Example:

The below example shows, how to create a user with dba privileges in oracle database 21c & 19c. Here PythonGuides is the name of the user and python@guides is the password.

-- CREATE USER WITH DBA PRIVILEGES
GRANT DBA TO PythonGuides IDENTIFIED BY "python@guides";

-- VIEW GRANTED ROLES

SELECT * 
FROM DBA_ROLE_PRIVS
WHERE GRANTEE='PYTHONGUIDES';

-- VIEW SYSTEM PRIVILEGES 
SELECT *
FROM DBA_SYS_PRIVS
WHERE GRANTEE='PYTHONGUIDES';

In the below output, user ‘pythonguides‘ is created with DBA privileges in oracle databases 19c and 21c.

Read: Oracle vs MySQL Database

Oracle create user with same privileges

Oracle allows creating a role which is the collection of multiple privileges in the oracle database. Any user with this role will have all the privileges immediately.

Suppose, in a company new employees join so they need access to the oracle database. Creating users and then assigning multiple privileges manually becomes an inefficient task.

So the role is created with the name let’s say newemp in this case and all the required privileges are provided on this role so that in future when a new employee will join then using Grant newemp on <username> same privileges can be assigned to that new user.

Moving forward we will create multiple users with the same privileges and the entire process is breakdown into 3 simple steps:

• Create Role
• Assign Privileges to role
• create user and assign that role

Read: Oracle Create Sequence Tutorial

Create Role in Oracle database 19c & 21c

Role in oracle is the collection of multiple privileges that can be assigned to any user in the oracle database 19c & 21c. Below is the syntax to create a role in oracle:

CREATE ROLE <Role Name>
IDENTIFIED BY [password | Global | External]

• Password will add layer of security, user need to enable Role using password.
• Global
• External

Below is an example to create a role in the oracle database. Here, we are creating a role with the name NEWEMP. Moving forward we will give a few privileges to this role.

-- CREATE ROLE
CREATE ROLE NEWEMP;

-- CREATE ROLE WITH PASSOWRD
CREATE ROLE NEWEMP IDENTIFIED BY pwd;

Assign Privileges to Role in Oracle database 19c & 21c

In this section, we will continue the role created in the previous section. Here we will add a few privileges to the EMPNEW role in the oracle database.

System privileges like creating a session, creating a table, etc can be provided directly but for Object privileges like select, update, alter, etc. Object name or Table name is required in oracle database 19c.

Here is the syntax to assign privileges to roles in oracle database 21c and 19c:

-- CREATE ROLE
GRANT CREATE SESSION, CREATE TABLE TO NEWEMP WITH ADMIN OPTION;

-- VIEW ROLE
SELECT *
FROM DBA_SYS_PRIVS
WHERE GRANTEE = 'NEWEMP';

Read: How to create table in Oracle

Create User and Assign Role

In the previous section, the NEWEMP role has been assigned with privileges to create tables and sessions. This means any user with this role can connect to the session and then create a new table within that session.

-- CREATE NEW USER WITH ROLE
GRANT NEWEMP TO John IDENTIFIED BY root;

-- CONNECT TO NEW USER 
CONNECT John/root@orclpdb

-- CREATE TABLE
CREATE TABLE PROFILE(
    ID  INTEGER,
    NAME  VARCHAR2(100),
    GENDER CHAR(5),
    COUNTRY VARCHAR2(100) DEFAULT 'United States of America'
);

So far everything is good and we have not received any errors. Now moving forward, since we don’t have insert privileges so the error will appear while inserting data in the above table.

You can assign this role to n number of users and all of them will have the same privileges. This way we have completed the explanation on how to create a new user with the same privileges in oracle database 19c & 21c.

Read: How to create table in Oracle

Oracle create user with sysdba privileges

Sys or system is the most powerful user in oracle database 21c & 19c. It is created by default when oracle is installed with an instance on the operating system.

The source of power for sys is the role which is sysdba in oracle. Sysdba role has all the possible privileges.

The below example shows, how to create a user with sysdba privileges. In this script, USA_Market is the user name and strongpass is the password for the user.

GRANT SYSDBA TO USA_Market IDENTIFIED BY strongpass;

Oracle create user with select privileges only

Select privilege in oracle database 21c & 19c allows users to only view the data. Users can access the database in read-only mode. He/She can fetch the details but won’t be able to change or manipulate the database.

Using GRANT SELECT statement in the oracle database you can grant the read-only permission to the user. The below example shows, the creation of user – USA_MART with password secure@usa.

GRANT SELECT TO USA_MART IDENTIFIED BY "secure@usa";

Oracle create user with admin priviliges

Database Administrator (DBA) has all system privileges with the admin option. After sysdba, it is the most powerful user in oracle database 19c and 21c.

DBA role is referred to as admin in oracle database as it holds most of the privileges in oracle database 21c and 19c. DBA is a powerful user so make sure to grant this permission to responsible hands.

Use the below syntax to create a user with admin privileges.

GRANT DBA TO <user> IDENTIFIED BY <password> WITH ADMIN OPTION;

Read: How to Fetch Data from Oracle Database in Python

Oracle create user with root privileges

In Oracle, root users are the same as the admin user, they are the most powerful users in the Oracle database 21c and 19c. They have all the system and object privileges. The root user has the power to create or remove any user from any object of the oracle database.

Multiple Privileges are put under one role and that role is assigned to a user. In oracle, the Sysdba role has root privileges and is advised not to assign this role to any user in the oracle database.

Senior database administrators and founders of the company have root privileges so that they can monitor and manipulate any user or data in the oracle database.

Below is the syntax to create a user with root privileges in oracle database 21c & 19c:

GRANT SYSDBA TO <user> IDENTIFIED BY <password>;

Read: How to Check Oracle Database Version

Oracle create user in pdb

Portable databases (PDB) are the portable form of database that can be connected and removed as per the requirement. They are lightweight and allow to create a new user and perform all the activities.

It is recommended to use PDBs instead of working directly on Container Databases (CDB). To create new users use CREATE USER the statement followed by the username of choice and then IDENTIFIED BY and password for this user.

Below syntax shows in Oracle how to create a user in PDB, we assume that you are connected to PDBs:

CREATE USER <username> IDENTIFIED BY <password>;

In the below example, we have connected to PDB:- orclpdb and then created a new user with the name pythonguides_admin with a password as pwdAdmin.

- CONNECT TO PDB
ALTER SESSION SET CONTAINER = orclpdb;

-- CREATE USER
CREATE USER pythonguides_admin IDENTIFIED BY pwdAdmin;

Possible error messages and their Solution :-

If you are seeing an error message – invalid common user or role name that means you are not connected to your PDBs. This error appears when you try to create a normal user on the container database (CDB). Log in to your PDB and try again.

ERROR at line 1:
ORA-65096: invalid common user or role name

You cannot create a common user inside the portable database (PDB) in oracle databases 19c & 21c. To fix the error message remove the prefix C## or c## from the username.

ERROR at line 1:
ORA-65094: invalid local user or role name

Read: How to Get List all Tables in Oracle Database

Oracle create user schema

The schema in oracle is used to maintain the database in separate folders. Like on our desktop we keep music files in the Music folder, pictures in an image folder, data in document folders so here these folders are called schemas in terms of database.

It is advised to create schema as and when required in the database because it keeps the database clean and schema name must refer to the data. for example, the music folder will have music files.

Oracle create user schema involves two steps and those steps are:

• Create new user or schema name
• Grant permission to that user or schema

Create a new user in oracle database

User can be created by using CREATE USER statements in oracle database 21c and 19c followed by the password for that user.

Below syntax shows how to create user or schema in oracle database 19c & 21c.

CREATE USER <user> IDENTIFIED BY <password>;

Grant permission to user in oracle database

Without permission or privilege oracle user/schema won’t be able to connect and create a table inside it. So Grant permission to the user using Grant keyword.

GRANT <permission name> TO <user>;

In the below example, we have created a new user with the name bookshelf.

CREATE USER bookshelf IDENTIFIED BY pwd;

In the below example, we have granted all the permission to the user

GRANT ALL to bookshelf;

So in this way we have created a schema with the name bookshelf in oracle databases 21c and 19c. Moving forward, new tables can be created within this user and they can be accessed using bookshelf.bookname. Here, the bookshelf is the schema and bookname is the table.

Read: How to Install Oracle on Docker

Oracle create user profile

In Oracle, the user profile is created to set a limit on resources and passwords. Every organization focuses on optimum utilization of resources and Security. All of this is possible by adding constraints or restrictions on the usage timing of the resource.

Oracle provides resource and password parameters, the resource provides restrictions like CPU per session, session per user, connect time, idle time, etc. Whereas password parameter makes sure the user changes the password after a certain period of time, login attempts, password lock time, etc.

Below table shows Resource parameters in oracle database 19c & 21c

SESSIONS_PER_USER	Number of sessions user can connect at the same time
CPU_PER_SESSION	CPU time limited per session, represented in 100th of a second.
CPU_PER_CALL	The CPU time limit for a call
CONNECT_TIME	the time limit in minutes to connect with the user session
IDLE_TIME	Allowed time in minutes for continuous inactivity. Any operation happening won’t be considered in inactivity.
LOGICAL_READS_PER_CALL	A number of data blocks are read for a call to process SQL query.
LOGICAL_READS_PER_SESSION	allowed a number of data blocks to read ( from memory and disk) in a user session.
PRIVATE_SGA	amount of private memory allowed for a session to allocate in the shared pool of SGA.
COMPOSITE_LIMIT	Total resource cost for a session in-service units. Total_Service_Units = CPU_PER_SESSION + PRIVATE_SGA + LOGICAL_READS_PER_SESSION + CONNECT_TIME

Below table shows Parameters for Passwords in oracle database 19c and 21c

FAILED_LOGIN_ATTEMPTS	How many times a user can put an incorrect password consecutively.
PASSWORD_LIFE_TIME	Specify the number of days when the current password will expire.
PASSWORD_REUSE_TIME	Users cannot use the same password for a certain number of days. Specify the number of days
PASSWORD_REUSE_MAX	The user won’t be able to use the same password unless the password has been changed specified time.
PASSWORD_GRACE_TIME	Default extra 7 days are provided after the completion of the grace period of the password reset warning. specify your value as per the business norms.
PASSWORD_LOCK_TIME	The account is locked after consecutive incorrect passwords. Specify the number of days the account will be locked default is 1 day.

User profile can be created using below syntax:

-- PROFILE WITH RESOURCE PARAMETER
CREATE PROFILE <profile name> LIMIT
[RESOURCE PARAMETER]

-- PROFILE WITH PASSWORD PARAMETER
CREATE PROFILE <profile name> LIMIT 
[PASSWORD PARAMETER]

Example:

In the below example, we have created a profile with the name CHECKS.

• User with this profile can access 5 sessions concurrently,
• User can use CPU till any time,
• Each call cannot take more than 50 seconds to fetch, parse, execute the query.
• User cannot stay connected for more than 20 minutes per session.
• Computer cannot be left idle, there must be some activity performed by user within 10 minutes.
• Account will lock after 8 days of warning to change password.

-- CREATE PROFILE
CREATE PROFILE CHECKS LIMIT
    SESSIONS_PER_USER 5
    CPU_PER_SESSION UNLIMITED
    CPU_PER_CALL 5000
    CONNECT_TIME 20
    IDLE_TIME 10
    PASSWORD_GRACE_TIME 8;

-- CREATE USER WITH PROFILE
CREATE USER James IDENTIFIED BY pwd
PROFILE CHECK;

-- DELETE PROFILE
DROP PROFILE CHECKS;

Similarly, a password profile can be created as shown in the below script. Here we have named the profile as pwdcheck in the oracle database.

-- CREATE PROFILE
CREATE PROFILE PWDCHECK LIMIT
	FAILED_LOGIN_ATTEMPTS 5
	PASSWORD_LIFE_TIME 60
	PASSWORD_REUSE_TIME 30
	PASSWORD_REUSE_MAX 5
	PASSWORD_GRACE_TIME 7
	PASSWORD_LOCK_TIME 2;

-- CREATE USER WITH ROLE    
CREATE USER James IDENTIFIED BY pwd
PROFILE PWDCHECK;

-- DELETE PROFILE
DROP PROFILE CHECKS;

Read: Oracle Add Row Number

Oracle create dblink identified by values

Oracle creates a database link also known as dblink is the schema object in one database which allows you to access objects on another database.

Other objects can be accessed easily if they are on an oracle database system. Non-oracle databases can be accessed using Oracle Heterogeneous Service in oracle databases 19c and 21c.

Here is the syntax to create a database link on oracle database 21c and 19c:

 CREATE DATABASE LINK 

• System privilege CREATE SESSION on the remote oracle database 19c and 21c.
• System privileges CREATE DATABASE LINK are required to create a private database link
• System privileges CREATE PUBLIC DATABASE LINK are required to create a public database link.
• Oracle net must be installed on both remote and local oracle database 21c and 19c.

Also, take a look at some more Oracle tutorials.

• Oracle to_char Date Format
• Oracle Add a Column to Table
• Oracle Add Foreign Key
• Database Size in Oracle

In this tutorial, you have learned how to create users in oracle databases 21c & 19c. Also, we have covered these topics:

• Oracle create user
• Oracle create a user with all privileges
• Oracle create a user and grant all privileges
• Oracle create a user with dba privileges
• Oracle create a user with same privileges
• Oracle create a user with sysdba privileges
• Oracle create a user with select privileges only
• Oracle create a user with admin priviliges
• Oracle create a user with root privileges
• Oracle create a user in pdb
• Oracle create user schema
• Oracle create user profile
• Oracle create dblink identified by values