7 Administering Users and Security

Database Administrative Accounts

The following administrative accounts are automatically created when Oracle Database is installed:

• SYS

• SYSTEM

About Privileges and Roles

Privileges and roles can be granted to other users by users who possess the necessary privilege. The granting of roles and privileges starts at the administrator level. At database creation, the administrative user SYS is created and granted all system privileges and predefined Oracle roles. User SYS can then grant privileges and roles to other users and also grant those users the right to grant specific privileges to others.

Table 7-2 provides descriptions and examples of privileges and roles.

Viewing Roles

You can use Enterprise Manager to view existing roles as follows:

1. In the Users & Privileges section of the Administration home page, click Roles.

   Figure 7-1 Users & Privileges

   

   The Roles page appears. From this page you can create, edit, view, or delete roles. The structure and functionality of the Roles page is similar to that of the Users page shown in Figure 7-2.

2. Select the CONNECT role.

3. Click View.

   The View page appears. In this page you can see all of the privileges and roles associated with the CONNECT role.

Creating Roles

You can create a secure role with the privileges necessary for application development. You can then grant the role to other roles or users depending on the level of data access required by the user.

In this exercise, you create an application developer role called APPDEV.

To create the APPDEV role:

1. In the Users & Privileges section of the Administration home page, click Roles.

   The Roles page appears.

2. Click Create.

   The Create Role General page appears.

3. Click OK.

   A page appears with a list of all roles, including the APPDEV role that you just created. You can now modify this new role by adding the required privileges.

Modifying Roles

You can add roles, privileges, and consumer groups to roles. In this exercise, you add the basic system privileges shown in Table 7-3, which allow the creation of various objects in this schema, to the APPDEV role that you created previously. These objects are described in Chapter 8, "Managing Schema Objects".

To modify the APPDEV role:

1. In the Users & Privileges section of the Administration home page, click Roles.

   The Roles page appears.

2. From the list of roles, select APPDEV and click Edit.

3. Click System Privileges to navigate to the System Privileges property page.

   The System Privilege column should display no items.

4. Click Edit List.

   The Modify System Privileges page appears.

5. In the Available System Privileges list, double-click the privileges listed in Table 7-3 to add them to the Selected System Privileges list.

6. Click OK.

   You are returned to the Edit Role: APPDEV page.

7. Click Apply.

   A confirmation message should appear saying that the role has been modified successfully.

Dropping Roles

In this exercise, you drop to the APPDEV role that you created in "Dropping Roles".

To drop the APPDEV role:

1. In the Users & Privileges section of the Administration home page, click Roles.

   The Roles page appears.

2. Select the APPDEV role and click Delete.

   A confirmation page appears.

3. Click Yes.

   You should get a confirmation message that the role has been deleted successfully.

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:

1. 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 the illustration create_user.gif

2. In the Name box, enter a user name. For example, enter MYUSER.

3. 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".

4. In the Authentication list, accept the value Password. For advanced authentication schemes, see Oracle Database Security Guide.

5. In the Enter Password and Confirm Password boxes, enter your password.

6. 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.

7. For the Default Tablespace field, click the browse button (flashlight) 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".

8. For the Temporary Tablespace field, click the browse button (flashlight) and select the TEMP tablespace. For more information on the TEMP tablespace, see "Some Tablespaces in the Database".

9. 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.

10. 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.

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. On the Users page, you can create a user with the same attributes.

To use a shortcut to create a new user:

1. Select the user that you want to duplicate. For example, select MYUSER.

2. From the Actions menu, select Create Like.

3. 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.

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:

1. In the Users page, select MYUSER.

2. Click Delete.

   A confirmation page appears.

3. Click Yes to confirm the deletion.