Creating password-authenticated users in Oracle 12c

Creating password user in Oracle 12cIn this task, you will create several users. To complete this recipe, you'll need an existing user who has create user privilege (you may use the OS-authenticated user who has the DBA role).

You'll use Oracle Enterprise Manager Database Express 12c (EM Express). To learn more about it (for example, how to configure an HTTPS port for EM Express and how to start it), see the third chapter of the official Oracle guide -Oracle Database 2 Day DBA, 12c Release 1.


Create password-authenticated user in SQL command line

1. Connect to the database as a user who has create user privilege:

   $ sqlplus /

2. Create a password-authenticated user (for example, username: jessica, password: oracle_1) as follows:

   SQL> create user jessica identified by oracle_1;

3. Create a password-authenticated user with a more complex password:

   SQL> create user tom identified by "Qax7UnP!123*";

4. Create a user that uses a specific password profile:

   SQL> create user mike identified by test1 profile

5. Create a user and force it to change password upon the first login:

   SQL> create user john identified by password1
password expire;

6. Create a user richard, whose default tablespace is users, temporary tablespace is temp, and who has their quota set to unlimited on the users tablespace:

   SQL> create user richard identified by oracle_2 default
tablespace users temporary tablespace temp quota unlimited
on users; 


How it works...

In step 1, you used OS authentication to connect to the database.

In step 2, you created a password-authenticated user jessica with simpler password.

In step 3, you created a password-authenticated user tom with more complex password. In this case (because a password contains special characters), you are using quotation marks (") to enclose the password.

Both of these users are using the default password profile.

In step 4, you created a password-authenticated user with the assigned password profile userprofile.

In step 5, you created user john. This user has to change his password at the first database login.

In step 6, you created the user richard. In the create user statement, quota unlimited on users means that you want to let the user allocate space in the tablespace without bound. The quota clause lets you define the maximum amount of space the user can allocate in the tablespace. You can have multiple quota clauses for multiple tablespaces within one  create user statement. The unlimited tablespace system privilege enables users to have an unlimited quota on all tablespaces in the database.

Note If you grant unlimited tablespace system privilege to a user and afterwards you revoke it, all explicitly granted quotas will also be revoked.

How to create a user using Oracle EM Express

You can also create users using Oracle Enterprise Manager Cloud Control 12c or Oracle Enterprise Manager Database Express 12c (EM Express). Oracle Enterprise Manager Database Control is no longer available in Oracle Database 12c.


  1. Start EM Express and log in to it using the user that has either EM_EXPRESS_BASIC or EM_EXPRESS_ALL role (you can use sysor system users, but that isn't recommended):

 Start OEM Express and log

  2. Select Users from the Security drop-down menu:

Users Security menu in OEM

  3. Click on the Create User tab:

Create user in OEM

  4. Enter user details in the pop-up dialog (for example, username: ted, password: oracle_123, here you can also choose the authentication method, password profile, lock account, expire password) leave the default values and click on the Nextbutton (see image here) as follows:

User Detail for registration in OEM

   5. In this step, you can choose default tablespace and temporary tablespace from the drop-down lists. Leave the default values, as shown in the following screenshot:

default tablespace and temporary tablespace for user in OEM

  6. In this step, you can grant privileges to user ted by selecting them in the left pane and moving them to the right pane (use > button). If you want to revoke privileges, do the opposite (select them in right pane and use < button). When you are satisfied with the list of privileges in the right pane (the ones you are going to grant to user ted), click on the OK button as follows:

 grant privileges to user in OEM

  7. A pop-up window confirmation should appear with the following message: SQL statement has been processed successfully.
Click on the OK button to close the window.


Вас заинтересует / Intresting for you:

Cloning Oracle Database 12c fr...
Cloning Oracle Database 12c fr... 4370 views Stepan Ushakov Tue, 05 Jun 2018, 11:28:55
RMAN: Specifying the Backup Us...
RMAN: Specifying the Backup Us... 551 views Андрей Волков Sat, 29 Feb 2020, 10:14:03
Getting Started with Oracle Da...
Getting Started with Oracle Da... 3048 views Akmaral Tue, 27 Feb 2018, 18:50:54
What Does Java Mean to an Orac...
What Does Java Mean to an Orac... 1627 views Александров Попков Tue, 27 Feb 2018, 18:52:19