7 Administering Users and Security

Sample Schemas

Most of the included accounts are administrative accounts, but Sample Schema accounts are also present. The Oracle Database Sample Schemas are a set of interlinked schemas that enable Oracle documentation, and Oracle by Example Series, to illustrate common database tasks:

• The human resources (hr) schema is useful for introducing basic topics. An extension to this schema supports Oracle Internet Directory demos.

• The order entry (oe) schema is useful for dealing with matters of intermediate complexity. Many datatypes are available in this schema, including nonscalar datatypes.

• The online catalog (oc) subschema is a collection of object-relational database objects built inside the oe schema.

• The product media (pm) schema is dedicated to multimedia datatypes.

• The information exchange (ix) schemas demonstrate Oracle Advanced Queuing capabilities.

• The sales history (sh) schema is designed for demos with large amounts of data. An extension to this schema provides support for advanced analytic processing.

About Privileges and Roles

Roles are groupings of privileges that you can use to create different levels of database access. For example, you can create a role for application developers that enable users to create tables and programs.

You can grant privileges and roles to other users only when you 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.

About Administrative Accounts

The following administrative 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. In the Users & Privileges section of the Administration home page, click Roles.

   Figure 7-1 Users & Privileges

   
   Description of "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. In the Name field, enter the name of the new role. For instance, enter APPDEV to create a new role for application developers.

4. 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, 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.

   A confirmation message indicates 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 "Figure 7-3 Create User General Page"
   
   

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

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

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.

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.