7 Administering Users and Security

Database Administrative Accounts

Two user accounts are automatically created when Oracle Database is installed:

• SYS

• SYSTEM

Viewing Roles

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

1. From the Administration home page shown in Figure 5-2, click the Roles link shown under the security heading.

   
   Description of the illustration security_crop_roles.gif
   
   

2. The Roles page is displayed. 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-1.

3. Select the CONNECT role in the Select column. Click View. The View page appears and you can see all of the privileges and roles associated with the CONNECT role.

Creating a New Role

You can create a secure application role with all privileges necessary to run an application. You can then grant the role to other roles or users. A user can require several different roles, each granted a different set of privileges that allow for more or less data access while using the application.

To create a new role:

1. From the Administration Page, click Roles under the Security heading.

2. Click Create.

3. On the Create Role page, enter the name of the new role. For instance, enter APDEV to create a new role for application developers in your organization. Click OK. A page appears with a list of all roles, including the one 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.

For example, to modify the APDEV role:

1. From the Administration Page, under the Security heading, click Roles.

2. Click APDEV or any role you want to modify. The Edit Role: APDEV page appears. This has links to the following property pages: Roles, System Privileges, Object Privileges, and Consumer Groups. Click Roles.

3. Click Modify.

4. In the Available Roles list, double click CONNECT to add the CONNECT role to the APDEV role. Click OK.

5. You are returned to the Edit Role: APDEV page. Click System Privileges.

6. Click Modify.

7. In the Available System Privileges list, double click EXECUTE ANY PROCEDURE and SELECT ANY TABLE to add these privileges to the APDEV role. Click OK.

8. You are returned to the Edit Role: APDEV page. Click Object Privileges.

9. From the Select Object Type pulldown list, select Table. Click Add.

10. From the Select Table Objects box, click the browse button (flashlight). Select the HR schema in the pulldown list. To see a list of all tables in the selected schema, clear the search box and click Go. Select the JOBS table. You are returned to the Add Table Object Privileges page.

11. Double click DELETE to add the DELETE privilege to the APDEV role for the HR.JOBS table. Click OK. To learn how to grant this role to a user, see "Granting Roles" in this chapter.

Dropping Roles

To drop the APDEV role:

1. From the Administration page, click Roles under the Security heading.

2. Select the APDEV role, and click Delete. At the confirmation page, click Yes.

Creating Users

In this section, you create a user named MYUSER, set the password, and assign MYUSER to the USERS tablespace. This enables MYUSER to log in to the database

Later, you will learn how to edit and assign roles to this user.

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

1. Click Create on the right hand side of the Users page. The Create Users General page shown in Figure 7-2 is displayed.

   The General page is one of a series of pages in the Users property page.

   Figure 7-2 Create User General Page

   
   Description of the illustration user_create.gif
   
   

2. Enter a user Name. For example: MYUSER

3. Leave Profile set to DEFAULT. The profile specifies the resource limit

   Note that this profile does not allow the user to log in to Enterprise Manager. To do so, the user needs to be assigned the DBA role. For more information, see "Administering Roles".

4. Leave Authentication set to password. For advanced authentication schemes, see Oracle Database Security Guide.

5. Enter the password of your choice in the Enter Password and Confirm Password boxes.

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. For Status, select Unlocked. Note that you can later lock an account to keep a user out of the database. Locking an account is preferable to deleting a user which deletes all associated tables and data.

10. Click OK. The Users page is displayed, and 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 for doing so. On the Users page, you can create a user with the same attributes as another user as follows:

1. Select the user that you want to duplicate.

2. From the Actions menu, select Create Like.

3. Click Go. The Create User General page appears for a new user with the same attributes as the duplicated user.

The Actions box 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 attributes of users by clicking Edit on the Users page (Figure 7-1) and selecting the properties page with the attributes that you want to change. The following procedure changes the tablespace quotas assigned to user MYUSER.

1. From the Users page, select MYUSER in the results list, then click Edit. The Edit User General page is displayed.

2. Click the Quotas property page to display the Quotas page.

   You can see that this user has 0 Mbytes quota on all tablespaces. This means that MYUSER has no quota in any tablespace. Since MYUSER belongs to the users tablespace, he must have quota to create tables and other schema objects in this tablespace.

3. In the Quota column for tablespace users, click the drop-down list and select Value.

4. In the corresponding Value column, enter 100.

5. Click Apply.

   User MYUSER can now create objects in the users tablespace.

6. Click the Database property page to return to the Administration home page.

Dropping Users

Enterprise Manager enables you to drop users. However, you must exercise caution when doing so, since dropping user will drop all schema objects owned by the user including tables and indexes. If you want 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 a user such as MYUSER:

1. From the Administration page, under the Security heading, click Users.

2. Select the MYUSER user, and click Delete. At the confirmation page, click Yes.