7 Managing Users and Security

Users access Oracle Database Express Edition through database user accounts. Some of these accounts are automatically created administrative accounts—accounts with database administration privileges. You log in to these administrative accounts to create and manage other user accounts, maintain database security, and perform other database administration tasks.

This section contains the following topics:

About User Accounts

A user account is identified by a user name and defines the user's attributes, including the following:

  • Password for database authentication

  • Privileges and roles

  • Default tablespace for database objects

  • Default temporary tablespace for query processing work space

When you create a user, you are also implicitly creating a schema for that user. A schema is a logical container for the database objects (such as tables, views, triggers, and so on) that the user creates. The schema name is the same as the user name, and can be used to unambiguously refer to objects owned by the user. For example, HR.EMPLOYEES refers to the table named EMPLOYEES in the HR schema. (The EMPLOYEES table is owned by HR.) The terms database object and schema object are used interchangeably.

When you drop (delete) a user, you must either first drop all the user's schema objects, or use the cascade feature of the drop operation, which simultaneously drops a user and all of that user's schema objects.

This section contains these topics:

See Also:

"Predefined User Accounts"

User Privileges and Roles

When creating a user, you grant privileges to enable the user to connect to the database, to run queries and make updates, and to create schema objects. There are two main types of user privileges:

  • System privileges—A system privilege is the right to perform a particular action, or to perform an action on any schema objects of a particular type. For example, the privileges to create tables and to delete the rows of any table in a database are system privileges.

  • Object privileges—An object privilege is a right to perform a particular action on a specific schema object. Different object privileges are available for different types of schema objects. The privilege to delete rows from the DEPARTMENTS table is an example of an object privilege.

Managing and controlling privileges is made easier by using roles, which are named groups of related privileges. You create roles, grant system and object privileges to the roles, and then grant roles to users. Unlike schema objects, roles are not contained in any schema.

Oracle Database Express Edition comes with some predefined roles:

  • The DBA role enables a user to perform most administrative functions, including creating users and granting privileges; creating and granting roles; creating and dropping schema objects in other users' schemas; and more. It grants all system privileges, but does not include the privileges to start up or shut down the database. It is by default granted to user SYSTEM. You should be very cautious about assigning the DBA role to any other database users.

  • Use of the CONNECT and RESOURCE roles is discouraged. Instead, grant only those privileges that the specific user will need. For example:

    grant CREATE SESSION, ALTER SESSION, CREATE DATABASE LINK, -
  CREATE MATERIALIZED VIEW, CREATE PROCEDURE, CREATE PUBLIC SYNONYM, -
  CREATE ROLE, CREATE SEQUENCE, CREATE SYNONYM, CREATE TABLE, - 
  CREATE TRIGGER, CREATE TYPE, CREATE VIEW, UNLIMITED TABLESPACE -
  to chris;

See Also:

Internal User Accounts

Certain user accounts are created automatically for database administration. Examples are SYS and SYSTEM. Other accounts are automatically created just so that individual Oracle Database XE features or products can have their own schemas. An example is the CTXSYS account, which is used by the Oracle Text product. Oracle Text is used to index the Oracle Database XE online Help. The Help index is stored in the CTXSYS schema in the database.

These automatically created accounts are called internal user accounts, and their schemas are called internal schemas.

The only internal accounts that you may log in with are the SYS and SYSTEM accounts, although it is recommended that you avoid logging in with the SYS account. Do not attempt to log in with other internal accounts. See "The SYS and SYSTEM Users" for more information.

About Administrative Accounts and Privileges

Administrative accounts and privileges enable you to perform administrative functions like managing users, managing database memory, and starting up and shutting down the database.

This section contains the following topics:

See Also:

The SYS and SYSTEM Users

The following administrative user accounts are automatically created when you install Oracle Database Express Edition (Oracle Database XE). They are both created with the password that you supplied upon installation (Windows operating systems) or configuration (Linux operating systems).

  • SYSTEM

    This is the user account that you log in with to perform all administrative functions other than starting up and shutting down the database.

  • SYS

    All base tables and views for the database data dictionary are stored in the SYS schema. These base tables and views are critical for the operation of Oracle Database XE. 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. You must not create any tables in the SYS schema.

    There is typically no reason to log in as user SYS. User SYSTEM is preferred for all administrative tasks except starting up and shutting down. See "Starting Up and Shutting Down" for more information.

See Also:

The SYSDBA System Privilege

SYSDBA is a system privilege that is assigned only to user SYS. It enables SYS to perform high-level administrative tasks such as starting up and shutting down the database.

Although under typical circumstances it is not necessary to log in to the database as user SYS, if you want to log in as SYS with the SQL Command Line (SQL*Plus), you must connect to the database "AS SYSDBA." Connecting AS SYSDBA invokes the SYSDBA privilege. If you omit the AS SYSDBA clause when logging in as user SYS, the SQL Command Line rejects the login attempt.

The following example illustrates how to connect to the database with the SYSDBA privilege from the SQL Command Line:

SQL > connect sys/password as sysdba

password is the password for the SYS user account.

Caution:

When you connect as user SYS, you have unlimited privileges on data dictionary tables. Be certain that you do not modify any data dictionary tables.

See Also:

Operating System Authentication

Operating system authentication (OS authentication) is a way of using operating system login credentials to authenticate database users. One aspect of OS authentication can be used to authenticate database administrators. If you log in to the Oracle Database XE host computer with a user name that is in a special operating system user group, you are then permitted to connect to the database with the SYSDBA privilege. An administrator who is authenticated through OS authentication does not need to know the SYS or SYSTEM account password.

OS authentication is needed because there must be a way to identify administrative users even if the database is shut down. A user authenticated in this way can then start up the database. (See "Starting Up and Shutting Down" for more information.)

Table 7-1 lists the operating system user groups whose member users can connect to the database with the SYSDBA privilege.

Table 7-1 Operating System User Groups for OS Authentication

Platform Operating System User Group Name

Linux

dba

Windows

ORA_DBA

On each platform, if the OS authentication user group does not already exist, it is automatically created when you install Oracle Database XE. In addition, upon installation on the Linux platform, the user account oracle is automatically created and placed in the dba group. Upon installation on the Windows platform, the user performing the installation is automatically added to the ORA_DBA group. On both platforms, you can add other host users to the OS authentication user group to enable them to connect to the database with the SYSDBA privilege.

Caution:

Adding other users to the OS authentication user group has security implications, because these users can modify any database object.

See Also:

Logging In as an Administrator

There are three ways to log in to Oracle Database Express Edition (Oracle Database XE) to perform administrative tasks:

  • Log in as user SYSTEM

  • Log in as a user who has been granted the DBA role

  • Log in and connect to the database as SYSDBA

Table 7-2 provides information about each of these login methods.

Table 7-2 Database Administrator Login Methods

Login Method Permitted In Notes See

Log in to the database as user SYSTEM

The Oracle Database XE graphical user interface and the SQL Command Line

For routine administrative tasks like managing memory and managing users. You must supply the password for the SYSTEM user.

"Logging In as User SYSTEM"

Log in to the database as a user who has been granted the DBA role

The Oracle Database XE graphical user interface and the SQL Command Line

For routine administrative tasks like managing users. An administrator must first grant the DBA role to the user.

"Logging In as a User with the DBA Role"

Log in and connect to the database as SYSDBA

the SQL Command Line

For high-level administrative tasks like starting up and shutting down the database, and changing the SYS password. You can connect as SYSDBA using the SYS user name and password, or using operating system authentication.

"Logging In and Connecting to the Database as SYSDBA"

See Also:

Logging In as User SYSTEM

You can log in as user SYSTEM using either of the following methods:

  • Using SQL Developer, open a database connection to the SYSTEM user.

  • Using the SQL Command Line, enter the following statement:

    SQL> CONNECT SYSTEM/<password>;

Logging In as a User with the DBA Role

The procedures for logging in as a user who has been granted the DBA role are the same as those for logging in as user SYSTEM, with the following exceptions:

  • When logging in, you must supply the user name and password for this user account.

  • An administrator must have previously logged in and granted the DBA role to this user.

    See "User Privileges and Roles" for more information.

Logging In and Connecting to the Database as SYSDBA

You can log in and connect as SYSDBA using either of the following methods:

  • Using SQL Developer, open a database connection to the SYS user AS SYSDBA.

  • Using the SQL Command Line, enter one the following statements.

    To use database authentication:

    SQL> CONNECT SYS/<password> AS SYSDBA;

    To use operating system (OS) authentication:

    SQL> CONNECT / AS SYSDBA;

    The slash (/) indicates that the database should authenticate you with operating system (OS) authentication. Remember that when you connect with OS authentication, you are effectively logging in to the database as user SYS.

See Also:

Changing Administrative User Passwords

To change the password for user SYS or SYSTEM:

  1. Using the SQL Command Line, connect to the database as SYSDBA.

    See "Logging In and Connecting to the Database as SYSDBA" for instructions.

  2. Enter one of the following commands:

    ALTER USER SYS IDENTIFIED BY newpassword;
ALTER USER SYSTEM IDENTIFIED BY newpassword;

    where newpassword is the desired new password.

See Also:

"About Administrative Accounts and Privileges"

Managing Database Users

You can use SQL Developer or the SQL Command Line (SQL*Plus) to manage database users. This section discusses using SQL Developer, and contains the following topics:

To perform these operations, in the SQL Developer Connections navigator, open a connection

  1. In the SQL Developer Connections navigator, open a connection to the SYSTEM user.

  2. In the nodes under this SYSTEM connection, expand Other Users.

    This displays nodes for all database users, including several Oracle-supplied internal users. The Connections navigator hierarchy may look like this:

    Connections
  . . .
  SYSTEM
    Views
    Editioning Views
    . . .
    Other Users
      ANONYMOUS
      APEX_040000
      APEX_PUBLIC_USER
      APQQOSSYS
      CHRIS
      CTXSYS
      . . .
      HR
      MDSYS

  3. To create a new database user, right-click the Other Users node in the Connections navigator and select Create User.

  4. To perform an action on a database user, right-click that user in the hierarchy and select the appropriate command (Edit User or Drop User).

See Also:

Oracle Database SQL Language Reference and Oracle Database Security Guide for information on managing users with the SQL Command Line (SQL*Plus).

Creating Users

To create a new database user, right-click the Other Users node in the SQL Developer Connections navigator and select Create User. Before creating a user, determine the following:

  • Whether or not you want to permit the user to create database objects in that user's own schema.

    If so, on the Create Database User page, grant individual session-related and create object system privileges. See the following topics for more information:

  • Whether or not you want to grant the user DBA privileges.

    If so, on the Create Database User page, grant the DBA role. See "User Privileges and Roles" for details on the DBA role.

    Because DBA privileges include the ability to create database objects in any schema, if you grant the DBA role, you do not need to grant individual create object system privileges.

    Caution:

    Granting the DBA role to a user has security implications, because the user can modify objects in other users' schemas.

  • Whether or not to create the user with an expired password.

    When you do this, the password that you assign the user is used only for the user's first login. Upon first login, the user is prompted to select a new password.

See Also:

"About User Accounts"

Example: Creating a User

Suppose you want to create a user account for a database application developer named Nick. Because Nick is a developer, you want to grant him all CREATE system privileges so that he can create the schema objects that his applications require. In addition, you want to create his account with the password firesign.

To create the user Nick:

  1. Right-click the Other Users node in the SQL Developer Connections navigator and select Create User.

  2. In the Create/Edit User dialog box, for the User tab, enter the information shown in the following figure:

    User tab. Entries are explained after the illustration.

    User Name: NICK

    New Password and Confirm Password: Desired password for the user.

    Password expired (user must change): Select or not, as desired. (It is not selected in the figure.)

    Account is Locked: Select or not, as desired. (It is not selected in the figure.)

    Edition Enabled: Select or not, as desired. (It is not selected in the figure.)

    Default Tablespace: USERS

    Temporary Tablespace: TEMP

  3. In the Create/Edit User dialog box, click the System Privileges tab, and under Granted select the following privileges because you want to be sure that NICK will have them):

    • ALTER SESSION

    • CREATE SESSION

    • CREATE DATABASE LINK

    • CREATE MATERIALIZED VIEW

    • CREATE PROCEDURE

    • CREATE PUBLIC SYNONYM

    • CREATE ROLE

    • CREATE SEQUENCE

    • CREATE SYNONYM

    • CREATE TABLE

    • CREATE TRIGGER

    • CREATE TYPE

    • CREATE VIEW

    • UNLIMITED TABLESPACE

  4. In the Create/Edit User dialog box, click Apply, then click Close.

Altering Users

You can use the Manage Database Users page to alter a user. Altering a user means changing some of that user's attributes. You can change all user attributes except the user name, default tablespace, and temporary tablespace. If you want to change the user name, you must drop the user and re-create that user with a different name. (Before you drop the user, ensure that the user's schema objects are either no longer needed or are backed up (for example, by exporting them). See "Dropping Users" for more information.)

One of the attributes that you can alter is the user password. If you do this, you must either communicate the new password to the user, or request the new password from the user and then enter it. An easier and more secure way to cause a password change is to expire the password. When you expire a password, the user is prompted to change the password at the next login. See "Expiring a User Password" for more information.

See Also:

Example: Altering a User

Suppose Nick is promoted to senior developer, and he has shown an interest in helping with routine database administration tasks. You decide to grant the DBA role to Nick.

To alter Nick's user account:

  1. In the SQL Developer Connections navigator, expand the SYSTEM connection and right-click the Other Users node.

  2. Right-click NICK and select Edit User.

  3. In the Create/Edit User dialog box, click the Roles tab.

  4. Under Granted, select DBA.

  5. In the Create/Edit User dialog box, click Apply, then click Close.

Locking and Unlocking User Accounts

To temporarily deny access to the database for a particular user, you can lock the user account. If the user then attempts to connect, the database displays an error message and disallows the connection. You can unlock the user account when you want to allow database access again for that user.

Note:

Many internal user accounts are locked (or both expired and locked). You should not attempt to log in with these locked user accounts. See "Internal User Accounts" for more information.

The HR user account, which contains a sample schema, is initially expired and locked. You must log in as SYSTEM, unlock the account, and assign a password before you can log in as HR.

To lock or unlock a user account:

  1. In the SQL Developer Connections navigator, expand the SYSTEM connection and right-click the Other Users node.

  2. Right-click the desired user and select Edit User.

  3. In the Create/Edit User dialog box, in the User tab, select or deselect Account is Locked: selecting (checking) causes the account to be locked; deselecting (unchecking) causes the account to be unlocked.

  4. In the Create/Edit User dialog box, click Apply, then click Close.

Expiring a User Password

When you expire a user password, the user is prompted to change the password at the next login. Reasons to expire a password include the following:

  • A user password becomes compromised.

  • You have a security policy in place that requires users to change their passwords on a regular basis.

  • A user has forgotten his or her password.

    In this case, you alter the user account, assign a new temporary password, and expire the password. The user then logs in with the temporary password and is prompted to choose a new password.

    See "Altering Users" for more information.

Example: Expiring a Password

Suppose Nick's password becomes compromised, and you want to assign him a new one. The easiest way to do this is to expire his current password. The next time that Nick logs in with the compromised password, he is prompted to choose a new password.

To expire Nick's password:

  1. In the SQL Developer Connections navigator, expand the SYSTEM connection and right-click the Other Users node.

  2. Right-click NICK and select Edit User.

  3. In the Create/Edit User dialog box, in the User tab, select (check) Password expired (user must change next login).

  4. In the Create/Edit User dialog box, click Apply, then click Close.

Dropping Users

Dropping a user removes the user from the database. Before you can drop a user, you must first drop all the user's schema objects. Or, you can use the cascade feature of the drop operation, which simultaneously drops a user and also that user's schema objects. The following are two alternatives to dropping a user and losing all the user's schema objects:

Caution:

Under no circumstances should you attempt to drop the SYS or SYSTEM users, or any other internal user accounts. Doing so could cause Oracle Database XE to malfunction.

Example: Dropping a User

Suppose Nick's project is canceled and Nick takes a position in another department. You want to drop the user NICK and all associated schema objects.

To drop user NICK and all his owned schema objects:

  1. In the SQL Developer Connections navigator, expand the SYSTEM connection and right-click the Other Users node.

  2. Right-click NICK and select Drop User.

  3. In the Drop User dialog box, (check) Cascade

    This indicates that you want to drop the user's schema objects also. If the user has schema objects and you do not select this option, you receive an error message if you attempt to complete the drop operation.

  4. In the Drop User dialog box, click Apply.

User Accounts Reference

This section provides reference information for managing user accounts. It covers the following topics:

Predefined User Accounts

Table 7-3 lists the Oracle Database XE predefined user accounts. Many of these accounts are internal accounts. You must not drop internal accounts, and with the exception of the accounts SYS and SYSTEM, you must not attempt to log in with an internal account.

Table 7-3 Oracle Database Express Edition Predefined User Accounts

User Account Name Purpose

ANONYMOUS

Internal. Used for anonymous HTTP access to the database. Required by the Oracle Database XE graphical user interface. This account must remain unlocked. The account password is set upon installation (Windows) or configuration (Linux). For optimal security, avoid changing the password for this account.

CTXSYS

Internal.

DBSNMP

Internal.

DIP

Internal.

FLOWS_version

Internal.

FLOWS_FILES

Internal.

HR

For the HR sample schema. This account is initially expired and locked.

MDSYS

Internal.

OUTLN

Internal.

SYS

Owns the data dictionary base tables and views. The account password is set upon installation (Windows) or configuration (Linux).

SYSTEM

Log in with this account to perform routine database administration. The account password is set upon installation (Windows) or configuration (Linux).

TSMSYS

Internal.

XDB

Internal.

See Also: