Creating password-authenticated users

In this task, you will create several users.

Getting ready

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.

How to do it...

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
   userprofile;
   
   

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.

There's more...

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.

How to create a user using EM Express

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 sys or system users, but that isn't recommended):
   
2. Select Users from the Security drop-down menu:
   
3. Click on the Create User tab:
   
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:
   
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:
   
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:
   
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.

See also

• Creating and using OS-authenticated users