Skip Headers
Oracle® Database 2 Day DBA
10g Release 2 (10.2)

Part Number B14196-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

7 Administering Users and Security

This chapter describes how to create and manage user accounts. It contains the following topics:

Overview of Users and Security

For users to access your database, you the database administrator must create user accounts and grant appropriate database access privileges to those accounts. A user account is identified by a username and defines the user's security attributes, including the following:

User accounts are usually created by a database administrator or a person specifically assigned to create accounts and administer security. After you create a user account, you must grant basic privileges to the account to enable the user to connect to the database and to view and create database objects, known as schema objects. The name of the schema is identical to the name of the user.

All databases include the SYS, SYSTEM, SYSMAN, and DBSNMP administrative accounts. Other accounts are included depending upon what features or options are installed.

The administrative accounts provided by Oracle Database should be used only by authorized individuals. To protect these accounts from unauthorized access, these accounts are initially locked with their passwords expired. As the database administrator, you are responsible for the unlocking and resetting of these accounts as described in "Unlocking Accounts and Resetting Passwords". Table 7-1 contains descriptions of some of these accounts.

Table 7-1 Administrative User Accounts Provided by Oracle Database

Username Password Description See Also

CTXSYS

CTXSYS

The Oracle Text account

Oracle Text Reference

DBSNMP

DBSNMP

The account used by the Management Agent component of Oracle Enterprise Manager to monitor and manage the database

Oracle Enterprise Manager Grid Control Installation and Basic Configuration

MDDATA

MDDATA

The schema used by Oracle Spatial for storing Geocoder and router data

Oracle Spatial User's Guide and Reference

MDSYS

MDSYS

The Oracle Spatial and Oracle interMedia Locator administrator account

Oracle Spatial User's Guide and Reference

DMSYS

DMSYS

The data mining account. DMSYS performs data mining operations.

Oracle Data Mining Administrator's Guide

Oracle Data Mining Concepts

OLAPSYS

MANAGER

The account used to create OLAP metadata structures. This account owns the OLAP Catalog (CWMLite).

Oracle OLAP Application Developer's Guide

ORDPLUGINS

ORDPLUGINS

The Oracle interMedia user. Plugins supplied by Oracle and third party format plugins are installed in this schema.

Oracle interMedia User's Guide

ORDSYS

ORDSYS

The Oracle interMedia administrator account

Oracle interMedia User's Guide

OUTLN

OUTLN

The account that supports plan stability. Plan stability enables you to maintain the same execution plans for the same SQL statements. OUTLN acts as a role to centrally manage metadata associated with stored outlines.

Oracle Database Performance Tuning Guide

SI_INFORMTN_SCHEMA

SI_INFORMTN_SCHEMA

The account that stores the information views for the SQL/MM Still Image Standard

Oracle interMedia User's Guide

SYS

CHANGE_ON_INSTALL

The account used to perform database administration tasks

"About Administrative Accounts"

SYSMAN

CHANGE_ON_INSTALL

The account used to perform Oracle Enterprise Manager database administration tasks. Note that SYS and SYSTEM can also perform these tasks.

Oracle Enterprise Manager Grid Control Installation and Basic Configuration

SYSTEM

MANAGER

Another account used to perform database administration tasks

"About Administrative Accounts"


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.

See Also:

Oracle Database Sample Schemas for a description of the Sample Schemas that are used for examples in Oracle Database documentation and educational materials.

Administering Roles

System privileges, object privileges, and roles provide a basic level of database security. They are designed to control user access to data and to limit the kinds of SQL statements that users can execute.

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.

Table 7-2 Privileges and Roles

Privilege or Role Description Examples

System privilege

An Oracle-defined privilege usually granted only to and by administrators. System privileges enable users to perform specific database operations.

The following are examples of system privileges that can be granted to users:

  • CREATE TABLE allows grantee to create tables in the grantee's schema.

  • CREATE USER allows grantee to create users in the database.

  • CREATE SESSION allows grantee to connect to an Oracle database to create a user session.

Object privilege

A privilege that controls access to a specific object.

The following examples are object privileges that can be granted to users:

  • SELECT ON hr.employees TO myuser

  • INSERT ON hr.employees TO myuser

Role

A group of privileges or other roles

The following examples are Oracle-defined roles:

  • CONNECT is a role that Enterprise Manager automatically grants to a user when you create a user as shown in "Creating Users". This role has the CREATE SESSION privilege.

  • RESOURCE extends the privileges of a user beyond those granted by the CONNECT role. It includes CREATE PROCEDURE, CREATE TRIGGER, and other system privileges.

  • DBA is the standard role that can be granted by an administrator to another administrator. It includes all system privileges and should only be granted to the most trusted and qualified of users. Assigning this role to a user enables the user to administer the database.

You can create your own roles if you have been granted this privilege.


See Also:

Oracle Database SQL Reference for a list of Oracle-defined privileges and roles

About Administrative Accounts

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

SYS

When you create an Oracle database, the user SYS is automatically created and granted the DBA role.

All base tables and views for the database data dictionary are stored in the schema SYS. These base tables and views are critical for the operation of Oracle Database. To maintain the integrity of the data dictionary, tables in the SYS schema are manipulated only by the database. They should never be modified by any user or database administrator. Also, you should not create any tables in the schema of user SYS, although you can change the storage parameters of the data dictionary settings if necessary.

Ensure that most database users are never able to connect to Oracle Database with the SYS account.

SYSTEM

When you create an Oracle Database, the user SYSTEM is also automatically created and granted the DBA role.

The SYSTEM user can create additional tables and views that display administrative information as well as internal tables and views used by various Oracle Database options and tools. Never use the SYSTEM schema to store tables of interest to nonadministrative users.

A predefined DBA role is automatically created with every Oracle Database installation. This role contains most database system privileges. Therefore, you should grant the DBA role only to actual database administrators. The DBA role does not include the SYSDBA or SYSOPER system privileges.

Administrative Privileges

SYSDBA and SYSOPER are administrative privileges required to perform basic database operations such as creating the database and instance startup and shutdown. Depending upon the level of authorization you require, you must have one of these privileges granted to you.

Note:

The SYSDBA and SYSOPER system privileges allow access to a database instance even when the database is not open. Control of these privileges is totally outside of the database itself.

You can also think of the SYSDBA and SYSOPER privileges as types of connections that enable you to perform certain database operations for which privileges cannot be granted in any other way. For example, if you have the SYSDBA privilege, then you can connect to the database by specifying CONNECT AS SYSDBA.

See Also:

Oracle Database Administrator's Guide for more the operations authorized with each privilege and an example

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

    Pointer to the Roles link.
    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.

See Also:

Oracle Database Security Guide for more information on administering user security, roles, and privileges

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

Table 7-3 APPDEV Privileges

Privilege Description

CREATE TABLE

Enables user to create tables in his schema.

CREATE VIEW

Enables user to create views in his schema.

CREATE PROCEDURE

Enables user to create procedures in his schema.

CREATE TRIGGER

Enables user to create triggers in his schema.

CREATE SEQUENCE

Enables user to create sequences in his schema.

CREATE SYNONYM

Enables user to create synonyms in his schema.


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.

Administering Profiles

A user profile establishes the password management policy for a user and sets limits the user's access to certain database resources. When you create the user in "Creating Users", you assign the Oracle-supplied default profile. This default profile is liberal in its resource specifications and does not provide tight restrictions on password usage.

To display the attributes of the default profile:

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

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

  2. Select the DEFAULT profile and click View.

    The View page appears. In this page you can view all of the attributes associated with the DEFAULT profile.

Database resource usage and limits are managed by the Database Resource Manager. You can read about the Database Resource manager in online Help and view its pages when you click the links in the Resource Manager section of the Database Administration page.

See Also:

Oracle Database Administrator's Guide as well as the Database Resource Manager online Help for a description of the Database Resource Manager and its use

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.

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

    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.


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

See Also:

Oracle Database Administrator's Guide and Oracle Database Security Guide for more information on roles, privileges, quotas, consumer groups and proxy authentication

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.

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:

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

    The Edit User General page appears.

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

  3. In the Quota list for tablespace USERS, 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 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:

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

  2. From the Actions list, select Unlock User and click Go.

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

  4. From the Users page, select the same user and click Edit.

    The Edit User General page appears.

  5. 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:

  1. In the Users page, select MYUSER and click Edit.

    The Edit User: MYUSER page appears.

  2. Click Roles.

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

  3. Click Edit List.

    The Modify Roles page appears.

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

  5. Click OK.

    The Roles property page appears.

  6. Click Apply to save your changes.

Revoking Roles

You can also revoke roles from users.

To revoke the DBA role from MYUSER:

  1. In the Users page, select MYUSER and click Edit.

    The Edit User: MYUSER page appears.

  2. Click Roles.

    A page appears with the list of roles for MYUSER.

  3. Click Edit List.

    The Modify Roles page appears.

  4. In the Selected Roles list, select the DBA role and click Remove to make it part of the Available Roles list.

  5. Click OK.

    The Roles property page.

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

  1. In the Users page, select MYUSER.

  2. Click Delete.

    A confirmation page appears.

  3. Click Yes to confirm the deletion.

Users: Oracle by Example Series

Oracle by Example (OBE) has a series on the Oracle Database 2 Day DBA book. This OBE steps you through the tasks in this chapter and includes annotated screen shots.

To view the Users OBE, point your browser to the following location:

http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/10g/r2/2day_dba/users/users.htm