Administering Database Users

You can use Enterprise Manager to administer database users. You can create specific users to enable access to the database based on roles and privileges. For example, you might create a specific user MYUSER with the necessary privileges (which you can assign to roles) to develop applications. You might create other users specifically to administer the database.

In the Users & Privileges section of the Administration home page, click Users. The Users page appears, as shown in Figure 7-2. From this page you can view, add, edit, or delete database users.

Figure 7-2 Users Page

Description of "Figure 7-2 Users Page"

Creating Users

In this section, you create a user named MYUSER, set the password, and assign MYUSER to the USERS tablespace. This series of tasks enables myuser to log in to the database. Later, you learn how to edit and assign roles to this user.

To create a database user, perform the following tasks as user SYS or SYSTEM:

On the Users page, click Create.

The Create Users General page shown in Figure 7-3 is displayed. The General page is one of a series of pages in the Users property page.

Figure 7-3 Create User General Page

Description of "Figure 7-3 Create User General Page"
In the Name box, enter a user name. For example, enter MYUSER.
In the Profile list, accept the value DEFAULT. The profile specifies the resource limit.

Note that this profile does not allow the user to log in to Enterprise Manager, which would require the user to be assigned the DBA role. For more information, see "Administering Roles".
In the Authentication list, accept the value Password. For advanced authentication schemes, see Oracle Database Security Guide.
In the Enter Password and Confirm Password boxes, enter your password.
Do not check Expire Password now. If the account status to set to expired, then the user or the database administrator must change the password before the user can log in to the database.
For the Default Tablespace field, click the flashlight icon and select the USERS tablespace. While users can specify different defaults for each user, it is easiest to define a default permanent tablespace and a default temporary tablespace at the database level, instead of the user level. For more information on the USERS tablespace, see "Some Tablespaces in the Database".
For the Temporary Tablespace field, click the flashlight icon and select the TEMP tablespace. For more information on the TEMP tablespace, see "Some Tablespaces in the Database".
In Status, select Unlocked.

You can later lock an account to keep a user out of the database. Locking an account is preferable to deleting a user, which removes all associated tables and data.
Click OK.

The Users page appears. You can now see an entry for the new user myuser.

The Users General page has links to several other property pages. For example, you can specify user roles, privileges, quotas, consumer groups, and proxy users. Consumer groups are groups of users, or sessions, that are grouped together based on their processing needs.

Note:

This task creates a regular database user. To grant the user system administrator privileges, complete additional steps described in Chapter 3, "Getting Started with Oracle Enterprise Manager".

Using a Shortcut to Create Users

If you later want to create other users similar to user MYUSER, or another existing user, Oracle provides a shortcut.

To use a shortcut to create a new user:

Select the user that you want to duplicate. For example, select MYUSER.
From the Actions menu, select Create Like.
Click Go.

The Create User General page appears. This page displays a new user with the same attributes as the duplicated user.

The Actions list also provides shortcuts for other actions, as well as providing a means to display the SQL DDL used to create a user.

Editing Users

You can change the user attributes by navigating to the Users page (Figure 7-2) and clicking Edit. You can then select the properties page with the attributes that you want to change.

Changing the Tablespace Quota for a User

When you create a new user, the user is not able to save data until given a tablespace quota. This procedure enables user MYUSER to save data.

To change the tablespace quotas assigned to user MYUSER:

From the Users page, select MYUSER in the results list, then click Edit.

The Edit User General page appears.
Click Quotas to display the Quotas property page.

This user has 0 MB quota on all tablespaces, which means that MYUSER has no quota in any tablespace. Because MYUSER belongs to the USERS tablespace, he must have quota to create tables and other schema objects in this tablespace.
In the Quota list for tablespace USERS, select Value.
In the corresponding Value column, enter 100.
Click Apply.

User MYUSER can now create objects in the USERS tablespace.
Click the Database tab to return to the Administration home page.

Unlocking Accounts and Resetting Passwords

Locked accounts cannot be accessed by the user. During installation, you can unlock and reset the Oracle-supplied database user accounts. If you did not choose to unlock those accounts at that time, then you can do so now.

To unlock database account:

From the Users page shown in Figure 7-2, select a user whose Account Status is shown as EXPIRED AND LOCKED. For example, select DMSYS.
From the Actions list, select Unlock User and click Go.
Click Yes to the confirm that you want to unlock the user.

This action unlocks the user account. The Account Status is now EXPIRED. This action does not reset the password, so the user is still unable to log in to the database.
From the Users page, select the same user and click Edit.

The Edit User General page appears.
Enter a password for the account and click Apply.

You must follow the preceding steps individually for each account that you want to unlock and reset.

Locking an account is similar to unlocking it. You select the user and then choose Lock User from the Actions list. Locking an account denies access to the account.

To better understand the ramifications of unlocking and resetting accounts, see the other property pages available on the Edit user page. Specifically, click the Roles, System Privileges, and Object Privileges links to see the privileges of the user whose account you are enabling.

Granting Roles

You can use Enterprise Manager to grant roles to users. For example, you can grant a user the DBA role, which allows the user to administer the database. You can also grant user-created roles, such as the APPDEV role you created in "Creating Roles".

To grant the DBA and APPDEV role to MYUSER:

In the Users page, select MYUSER and click Edit.

The Edit User: MYUSER page appears.
Click Roles.

A page appears with the list of roles for MYUSER. The only role should be CONNECT.
Click Edit List.

The Modify Roles page appears.
In the Available Roles list, select the DBA and click Move to add it to the Selected Roles list. Do the same for the APPDEV role. You can also move the role by double-clicking.
Click OK.

The Roles property page appears.
Click Apply to save your changes.

Revoking Roles

You can also revoke roles from users.

To revoke the DBA role from MYUSER:

In the Users page, select MYUSER and click Edit.

The Edit User: MYUSER page appears.
Click Roles.

A page appears with the list of roles for MYUSER.
Click Edit List.

The Modify Roles page appears.
In the Selected Roles list, select the DBA role and click Remove to make it part of the Available Roles list.
Click OK.

The Roles property page.
Click Apply to save your changes.

Dropping Users

Enterprise Manager enables you to drop users. You must exercise caution when dropping users, however, because this action drops all schema objects owned by the user including tables and indexes.To deny user access to the database, it is better to lock the user account or expire the user password. See "Unlocking Accounts and Resetting Passwords".

To drop MYUSER:

In the Users page, select MYUSER.
Click Delete.

A confirmation page appears.
Click Yes to confirm the deletion.