Database Administrator Authentication


As a DBA, you often perform special operations such as shutting down or starting up a database. Because only a DBA should perform these operations, the database administrator usernames require a secure authentication scheme.

This section contains the following topics:

Administrative Privileges

Selecting an Authentication Method

Using Operating System Authentication

Using Password File Authentication



Administrative Privileges

Administrative privileges that are required for an administrator to perform basic database operations are granted through two special system privileges, SYSDBA and SYSOPER. You must have one of these privileges granted to you, depending upon the level of authorization you require.

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.

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

SYSDBA AND SYSOPER

The following operations are authorized by the SYSDBA and SYSOPER system privileges:

System Privilege

Operations Authorized

SYSDBA

Perform STARTUP and SHUTDOWN operations

ALTER DATABASE: open, mount, back up, or change character set CREATE DATABASE

DROP DATABASE

CREATE SPFILE

ALTER DATABASE ARCHIVELOG

ALTER DATABASE RECOVER

Includes the RESTRICTED SESSION privilege

Effectively, this system privilege allows a user to connect as user SYS.

SYSOPER

Perform STARTUP and SHUTDOWN operations

CREATE SPFILE

ALTER DATABASE OPEN/MOUNT/BACKUP

ALTER DATABASE ARCHIVELOG

ALTER DATABASE RECOVER (Complete recovery only. Any form of incomplete recovery, such as UNTIL TIMECHANGECANCELCONTROLFILE requires connecting as SYSDBA.)

Includes the RESTRICTED SESSION privilege.This privilege allows a user to perform basic operational tasks, but without the ability to look at user data.

The manner in which you are authorized to use these privileges depends upon the method of authentication that you use.

When you connect with SYSDBA or SYSOPER privileges, you connect with a default schema, not with the schema that is generally associated with your username. For SYSDBA this schema is SYS; for SYSOPER the schema is PUBLIC.

Connecting with Administrative Privileges: Example

This example illustrates that a user is assigned another schema (SYS) when connecting with the SYSDBA system privilege. Assume that the sample user oe has been granted the SYSDBA system privilege and has issued the following statements:CONNECT oe/oe
CREATE TABLE admin_test(name VARCHAR2(20));
Later, user oe issues these statements:CONNECT oe/oe AS SYSDBA
SELECT * FROM admin_test;
User oe now receives the following error:ORA-00942: table or view does not exist
Having connected as SYSDBA, user oe now references the SYS schema, but the table was created in the oe schema.

See Also:

"Using Operating System Authentication"

"Using Password File Authentication"


Selecting an Authentication Method

The following methods are available for authenticating database administrators:

.Operating system (OS) authentication

.A password file

Notes:

These methods replace the CONNECT INTERNAL syntax provided with earlier versions of Oracle Database. CONNECT INTERNAL is no longer supported.

Operating system authentication takes precedence over password file authentication. If you meet the requirements for operating system authentication, then even if you use a password file, you will be authenticated by operating system authentication.

Your choice will be influenced by whether you intend to administer your database locally on the same machine where the database resides, or whether you intend to administer many different databases from a single remote client. Figure 1-2 illustrates the choices you have for database administrator authentication schemes.

Figure 1-2 Database Administrator Authentication MethodsDescription of "Figure 1-2 Database Administrator Authentication Methods"

If you are performing remote database administration, consult your Oracle Net documentation to determine whether you are using a secure connection. Most popular connection protocols, such as TCP/IP and DECnet, are not secure.

See Also:Oracle Database Net Services Administrator's Guide

Nonsecure Remote Connections

To connect to Oracle Database as a privileged user over a nonsecure connection, you must be authenticated by a password file. When using password file authentication, the database uses a password file to keep track of database usernames that have been granted the SYSDBA or SYSOPER system privilege.

This form of authentication is discussed in "Using Password File Authentication".


Local Connections and Secure Remote Connections

You can connect to Oracle Database as a privileged user over a local connection or a secure remote connection in two ways:

1.If the database has a password file and you have been granted the SYSDBA or SYSOPER system privilege, then you can connect and be authenticated by a password file.

2.If the server is not using a password file, or if you have not been granted SYSDBA or SYSOPER privileges and are therefore not in the password file, you can use operating system authentication. On most operating systems, authentication for database administrators involves placing the operating system username of the database administrator in a special group, generically referred to as OSDBA. Users in that group are granted SYSDBA privileges. A similar group, OSOPER, is used to grant SYSOPER privileges to users.

Using Operating System Authentication

This section describes how to authenticate an administrator using the operating system.

OSDBA AND OSOPER

Two special operating system groups control database administrator connections when using operating system authentication. These groups are generically referred to as OSDBA and OSOPER. The groups are created and assigned specific names as part of the database installation process. The specific names vary depending upon your operating system and are listed in the following table:

Operating System Group UNIX user Group Windows User Group
OSDBA DBA ORA_DBA
OSOPER OPER ORA_OPER

The default names assumed by the Oracle Universal Installer can be overridden. How you create the OSDBA and OSOPER groups is operating system specific.

Membership in the OSDBA or OSOPER group affects your connection to the database in the following ways:

1.If you are a member of the OSDBA group and you specify AS SYSDBA when you connect to the database, then you connect to the database with the SYSDBA system privilege.

2.If you are a member of the OSOPER group and you specify AS SYSOPER when you connect to the database, then you connect to the database with the SYSOPER system privilege.

3.If you are not a member of either of these operating system groups and you attempt to connect as SYSDBA or SYSOPER, the CONNECT command fails.

See Also:

Your operating system specific Oracle documentation for information about creating the OSDBA and OSOPER groups

Preparing to Use Operating System Authentication

To enable operating system authentication of an administrative user:

1.Create an operating system account for the user.

2.Add the account to the OSDBA or OSOPER operating system defined groups.

Connecting Using Operating System Authentication

A user can be authenticated, enabled as an administrative user, and connected to a local database by typing one of the following SQL*Plus commands:

CONNECT / AS SYSDBA
CONNECT / AS SYSOPER


For a remote database connection over a secure connection, the user must also specify the net service name of the remote database:

CONNECT /@net_service_name AS SYSDBA
CONNECT /@net_service_name AS SYSOPER

See Also:

*Plus User's Guide and Reference for syntax of the CONNECT command

Using Password File Authentication

This section describes how to authenticate an administrative user using password file authentication.

Preparing to Use Password File Authentication

To enable authentication of an administrative user using password file authentication you must do the following:

1.If not already created, create the password file using the ORAPWD utility:

ORAPWD FILE=filename PASSWORD=password ENTRIES=max_users

2.Set the REMOTE_LOGIN_PASSWORDFILE initialization parameter to EXCLUSIVE.(This is the default).

Note:
REMOTE_LOGIN_PASSWORDFILE is a static initialization parameter and therefore cannot be changed without restarting the database.

3.Connect to the database as user SYS (or as another user with the administrative privileges).

4.If the user does not already exist in the database, create the user.

5.Grant the SYSDBA or SYSOPER system privilege to the user:
GRANT SYSDBA to oe;

This statement adds the user to the password file, thereby enabling connection AS SYSDBA.

See Also:

"Creating and Maintaining a Password File" for instructions for creating and maintaining a password file.

Connecting Using Password File Authentication

Administrative users can be connected and authenticated to a local or remote database by using the SQL*Plus CONNECT command. They must connect using their username and password and the AS SYSDBA or AS SYSOPER clause. For example, user oe has been granted the SYSDBA privilege, so oe can connect as follows:

CONNECT oe/oe AS SYSDBA

However, user oe has not been granted the SYSOPER privilege, so the following command will fail:

CONNECT oe/oe AS SYSOPER

Note:

Operating system authentication takes precedence over password file authentication. Specifically, if you are a member of the OSDBA or OSOPER group for the operating system, and you connect as SYSDBA or SYSOPER, you will be connected with associated administrative privileges regardless of the username/password that you specify.

If you are not in the OSDBA or OSOPER groups, and you are not in the password file, then attempting to connect as SYSDBA or as SYSOPER fails.

See Also:

SQL*Plus User's Guide and Reference for syntax of the CONNECT command

Database Administrator Security and Privileges

Database Administrator Security and Privileges

To perform the administrative tasks of an Oracle Database DBA, you need specific privileges within the database and possibly in the operating system of the server on which the database runs. Access to a database administrator's account should be tightly controlled.
This section contains the following topics:

The Database Administrator's Operating System Account
Database Administrator Usernames

The Database Administrator's Operating System Account

To perform many of the administrative duties for a database, you must be able to execute operating system commands. Depending on the operating system on which Oracle Database is running, you might need an operating system account or ID to gain access to the operating system. If so, your operating system account might require operating system privileges or access rights that other database users do not require (for example, to perform Oracle Database software installation). Although you do not need the Oracle Database files to be stored in your account, you should have access to them.

See Also:Your operating system specific Oracle documentation. The method of creating the account of the database administrator is specific to the operating system.

Database Administrator Usernames

Two user accounts are automatically created when Oracle Database is installed:
SYS (default password: CHANGE_ON_INSTALL)
SYSTEM (default password: MANAGER)

Note:Both Oracle Universal Installer (OUI) and Database Configuration Assistant (DBCA) now prompt for SYS and SYSTEM passwords and do not accept the default passwords "change_on_install" or "manager", respectively.

If you create the database manually, Oracle strongly recommends that you specify passwords for SYS and SYSTEM at database creation time, rather than using these default passwords. Please refer to "Protecting Your Database: Specifying Passwords for Users SYS and SYSTEM" for more information.

Create at least one additional administrative user and grant to that user an appropriate administrative role to use when performing daily administrative tasks. Do not use SYS and SYSTEM for these purposes.

Note Regarding Security Enhancements:In this release of Oracle Database and in subsequent releases, several enhancements are being made to ensure the security of default database user accounts. You can find a security checklist for this release in Oracle Database Security Guide. Oracle recommends that you read this checklist and configure your database accordingly.

SYS

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

All of the 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, and no one should create any tables in the schema of user SYS. (However, 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 using the SYS account.

SYSTEM

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

The SYSTEM username is used to create additional tables and views that display administrative information, and internal tables and views used by various Oracle Database options and tools. Never use the SYSTEM schema to store tables of interest to non-administrative users.

The DBA Role

A predefined DBA role is automatically created with every Oracle Database installation. This role contains most database system privileges. Therefore, the DBA role should be granted only to actual database administrators.

Note:The DBA role does not include the SYSDBA or SYSOPER system privileges. These are special administrative privileges that allow an administrator to perform basic database administration tasks, such as creating the database and instance startup and shutdown. These system privileges are discussed in "Administrative Privileges".

Identifying Your Oracle Database Software Release

Identifying Your Oracle Database Software Release

Because Oracle Database continues to evolve and can require maintenance, Oracle periodically produces new releases. Not all customers initially subscribe to a new release or require specific maintenance for their existing release. As a result, multiple releases of the product exist simultaneously.
As many as five numbers may be required to fully identify a release. The significance of these numbers is discussed in the sections that follow.

Release Number Format

To understand the release nomenclature used by Oracle, examine the following example of an Oracle Database server labeled "Release 10.1.0.1.0".

Figure 1-1 Example of an Oracle Database Release NumberDescription of "Figure 1-1 Example of an Oracle Database Release Number"
Note:Starting with release 9.2, maintenance releases of Oracle Database are denoted by a change to the second digit of a release number. In previous releases, the third digit indicated a particular maintenance release.

Major Database Release Number

The first digit is the most general identifier. It represents a major new version of the software that contains significant new functionality.

Database Maintenance Release Number

The second digit represents a maintenance release level. Some new features may also be included.

Application Server Release Number

The third digit reflects the release level of the Oracle Application Server (OracleAS).

Component-Specific Release Number

The fourth digit identifies a release level specific to a component. Different components can have different numbers in this position depending upon, for example, component patch sets or interim releases.

Platform-Specific Release Number

The fifth digit identifies a platform-specific release. Usually this is a patch set. When different platforms require the equivalent patch set, this digit will be the same across the affected platforms.

Checking Your Current Release Number

To identify the release of Oracle Database that is currently installed and to see the release levels of other database components you are using, query the data dictionary view PRODUCT_COMPONENT_VERSION. A sample query follows. (You can also query the V$VERSION view to see component-level information.) Other product release levels may increment independent of the database server.COL PRODUCT FORMAT A35
COL VERSION FORMAT A15
COL STATUS FORMAT A15
SELECT * FROM PRODUCT_COMPONENT_VERSION;
PRODUCT VERSION STATUS
---------------------------------------- ----------- -----------
NLSRTL 10.2.0.1.0 Production
Oracle Database 10g Enterprise Edition 10.2.0.1.0 Prod
PL/SQL 10.2.0.1.0 Production
...
It is important to convey to Oracle the results of this query when you report problems with the software.

Today we will discuss the third topic in oracle which is called Selecting an Instance with Environment variables.In this topic we will learn about various kinds of instance and there variables.How they work and in which environment they execute properly.


Selecting an Instance with Environment Variables

Before you attempt to use SQL*Plus to connect locally to an Oracle instance, you must ensure that environment variables are set properly. When multiple database instances exist on one server, or when an Automatic Storage Management (ASM) instance exists on the same server as one or more database instances, the environment variables determine which instance SQL*Plus connects to. (This is also true when there is only one Oracle instance on a server.)
For example, each Oracle instance (database or ASM) has a unique system identifier (SID). To connect to an instance, you must at a minimum set the ORACLE_SID environment variable to the SID of that instance. Depending on the operating system, you may need to set other environment variables to properly change from one instance to another.
Refer to the Oracle Database Installation Guide or administration guide for your operating system for details on environment variables and for information on switching instances.
Note:This discussion applies only when you make a local connection—that is, when you initiate a SQL*Plus connection from the same machine on which the target instance resides, without specifying an Oracle Net Services connect identifier. When you make a connection through Oracle Net Services, either with SQL*Plus on the local or a remote machine, or with Enterprise Manager, the environment is automatically set for you.
For more information on connect identifiers, see Oracle Database Net Services Administrator's Guide.
Solaris Example
The following Solaris example sets the environment variables that are required for selecting an instance. When switching between instances with different Oracle homes, the ORACLE_HOME environment variable must be changed.% setenv ORACLE_SID SAL1
% setenv ORACLE_HOME /u01/app/oracle/product/10.1.0/db_1
% setenv LD_LIBRARY_PATH /usr/lib:/usr/dt/lib:/usr/openwin/lib:/usr/ccs/lib
Most UNIX installations come with two scripts, oraenv and coraenv, that can be used to easily set these environment variables. For more information, see Administrator's Reference for UNIX Systems.
Windows Example
On Windows, you must set only the ORACLE_SID environment variable to select an instance before starting SQL*Plus.SET ORACLE_SID=SAL1


This concludes our topic i hope you have read it in detail and understand the concept of the topic.Our next topic is Identifying Your Oracle Database Software Release.Which i will post soon