|Oracle® Database Administrator's Guide
11g Release 2 (11.2)
|PDF · Mobi · ePub|
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 that are required for an administrator to perform basic database operations are granted through two special system privileges,
SYSOPER. You must have one of these privileges granted to you, depending upon the level of authorization you require.
SYSOPERsystem 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.
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, if you have the
SYSDBA privilege, you can connect to the database by specifying
CONNECT AS SYSDBA.
|System Privilege||Operations Authorized|
Effectively, this system privilege allows a user to connect as user
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
SYSOPER privileges, you connect with a default schema, not with the schema that is generally associated with your username. For
SYSDBA this schema is
SYSOPER the schema is
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 CREATE TABLE admin_test(name VARCHAR2(20));
oe issues these statements:
CONNECT oe AS SYSDBA SELECT * FROM admin_test;
oe now receives the following error:
ORA-00942: table or view does not exist
Having connected as
oe now references the
SYS schema, but the table was created in the
Database Administrators can authenticate database administrators through the data dictionary, (using an account password) like other users. Keep in mind that beginning with Oracle Database 11g Release 1, database passwords are case-sensitive. (You can disable case sensitivity and return to pre–Release 11g behavior by setting the
SEC_CASE_SENSITIVE_LOGON initialization parameter to
Operating system (OS) authentication
Strong authentication with a network-based authentication service, such as Oracle Internet Directory
These methods are required to authenticate a database administrator when the database is not started or otherwise unavailable. (They can also be used when the database is available.)
The remainder of this section focuses on operating system authentication and password file authentication. See Oracle Database Security Guide for information about authenticating database administrators with network-based authentication services.
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 system 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.
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.
Oracle Database Security Guide for information about authenticating database administrators with network-based authentication services.
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 user names that have been granted the
SYSOPER system privilege. This form of authentication is discussed in "Using Password File Authentication".
You can connect to Oracle Database as a privileged user over a local connection or a secure remote connection in two ways:
If the database has a password file and you have been granted the
SYSOPER system privilege, then you can connect and be authenticated by a password file.
If the server is not using a password file, or if you have not been granted
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.
This section describes how to authenticate an administrator using the operating system.
Membership in one of two special operating system groups enables a DBA to authenticate to the database through the operating system rather than with a database user name and password. This is known as operating system authentication. These operating system 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 default names vary depending upon your operating system, and are listed in the following table:
|Operating System Group||UNIX User Group||Windows User Group|
Oracle Universal Installer uses these default names, but you can override them. One reason to override them is if you have multiple instances running on the same host computer. If each instance is to have a different person as the principal DBA, you can improve the security of each instance by creating a different OSDBA group for each instance. For example, for two instances on the same host, the OSDBA group for the first instance could be named
dba1, and OSDBA for the second instance could be named
dba2. The first DBA would be a member of
dba1 only, and the second DBA would be a member of
dba2 only. Thus, when using operating system authentication, each DBA would be able to connect only to his assigned instance.
Membership in the OSDBA or OSOPER group affects your connection to the database in the following ways:
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.
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.
If you are not a member of either of these operating system groups and you attempt to connect as
CONNECT command fails.
See Also:Your operating system specific Oracle documentation for information about creating the OSDBA and OSOPER groups
Create an operating system account for the user.
Add the account to the
OSOPER operating system defined groups.
CONNECT / AS SYSDBA CONNECT / AS SYSOPER
For the Windows platform only, remote operating system authentication over a secure connection is supported. You must specify the net service name for the remote database:
CONNECT /@net_service_name AS SYSDBA CONNECT /@net_service_name AS SYSOPER
Both the client computer and database host computer must be on a Windows domain.
This section describes how to authenticate an administrative user using password file authentication.
If not already created, create the password file using the
ORAPWD FILE=filename ENTRIES=max_users
See "Creating and Maintaining a Password File" for details.
When you invoke Database Configuration Assistant (DBCA) as part of the Oracle Database installation process, DBCA creates a password file.
Beginning with Oracle Database 11g Release 1, passwords in the password file are case-sensitive unless you include the
Y command-line argument.
REMOTE_LOGIN_PASSWORDFILE initialization parameter to
EXCLUSIVE. (This is the default).
REMOTE_LOGIN_PASSWORDFILEis a static initialization parameter and therefore cannot be changed without restarting the database.
Connect to the database as user
SYS (or as another user with the administrative privileges).
If the user does not already exist in the database, create the user and assign a password.
Keep in mind that beginning with Oracle Database 11g Release 1, database passwords are case-sensitive. (You can disable case sensitivity and return to pre–Release 11g behavior by setting the
SEC_CASE_SENSITIVE_LOGON initialization parameter to
SYSOPER system privilege to the user:
GRANT SYSDBA to oe;
This statement adds the user to the password file, thereby enabling connection
See Also:"Creating and Maintaining a Password File" for instructions for creating and maintaining a password file.
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. Note that beginning with Oracle Database 11g Release 1, passwords are case-sensitive unless the password file was created with the
For example, user
oe has been granted the
SYSDBA privilege, so
oe can connect as follows:
CONNECT oe AS SYSDBA
oe has not been granted the
SYSOPER privilege, so the following command will fail:
CONNECT 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