Tuesday, 20 October 2015

Types of Oracle Users

The Oracle Database Administrator
This chapter describes your responsibilities as a database administrator (DBA) who administers the Oracle database server.

The following topics are discussed:

Types of Oracle Users


The types of users and their roles and responsibilities at a site can vary. A small site can have one database administrator who administers the database for application developers and users. A very large site can find it necessary to divide the duties of a database administrator among several people, and among several areas of specialization.

This section contains the following topics:

Database Administrators


Each database requires at least one database administrator (DBA) to administer it. Because an Oracle database system can be large and can have many users, often this is not a one person job. In such cases, there is a group of DBAs who share responsibility.

A database administrator's responsibilities can include the following tasks:
  • Installing and upgrading the Oracle server and application tools
  • Allocating system storage and planning future storage requirements for the database system
  • Creating primary database storage structures (tablespaces) after application developers have designed an application
  • Creating primary objects (tables, views, indexes) once application developers have designed an application
  • Modifying the database structure, as necessary, from information given by application developers
  • Enrolling users and maintaining system security
  • Ensuring compliance with your Oracle license agreement
  • Controlling and monitoring user access to the database
  • Monitoring and optimizing the performance of the database
  • Planning for backup and recovery of database information
  • Maintaining archived data on tape
  • Backing up and restoring the database
  • Contacting Oracle Corporation for technical support

Security Officers


In some cases, a site assigns one or more security officers to a database. A security officer enrolls users, controls and monitors user access to the database, and maintains system security. As a DBA, you might not be responsible for these duties if your site has a separate security officer.

Network Administrators


Some sites have one or more network administrators. A network administrator can administer Oracle networking products, such as Oracle Net Services.

See Also:
Part VI, "Distributed Database Management" for information on network administration in a distributed environment

Application Developers


Application developers design and implement database applications. Their responsibilities include the following tasks:
  • Designing and developing the database application
  • Designing the database structure for an application
  • Estimating storage requirements for an application
  • Specifying modifications of the database structure for an application
  • Relaying the above information to a database administrator
  • Tuning the application during development
  • Establishing an application's security measures during development

Application developers can perform some of these tasks in collaboration with DBAs.

Application Administrators


An Oracle site can assign one or more application administrators to administrate a particular application. Each application can have its own administrator.

Database Users


Database users interact with the database through applications or utilities. A typical user's responsibilities include the following tasks:
  • Entering, modifying, and deleting data, where permitted
  • Generating reports from the data

Tasks of a Database Administrator


The following tasks present a prioritized approach for designing, implementing, and maintaining an Oracle Database:

These tasks are discussed in succeeding sections.

Note:
If upgrading to a new release, back up your existing production database before installation. For information on preserving your existing production database, see Oracle9i Database Migration.

Task 1: Evaluate the Database Server Hardware


Evaluate how Oracle and its applications can best use the available computer resources. This evaluation should reveal the following information:
  • How many disk drives are available to Oracle and its databases
  • How many, if any, dedicated tape drives are available to Oracle and its databases
  • How much memory is available to the instances of Oracle you will run (see your system's configuration documentation)

Task 2: Install the Oracle Software


As the database administrator, you install the Oracle database server software and any front-end tools and database applications that access the database. In some distributed processing installations, the database is controlled by a central computer (database server) and the database tools and applications are executed on remote computers (clients). In this case, you must also install the Oracle Net components necessary to connect the remote machines to the computer that executes Oracle.

For more information on what software to install, see "Identifying Your Oracle Database Software Release".

See Also:
For specific requirements and instructions for installation, refer to the following documentation:
  • Your operating system specific Oracle documentation
  • Your installation guides for your front-end tools and Oracle Net drivers.

Task 3: Plan the Database


As the database administrator, you must plan:
  • The logical storage structure of the database
  • The overall database design
  • A backup strategy for the database

It is important to plan how the logical storage structure of the database will affect system performance and various database management operations. For example, before creating any tablespaces for your database, you should know how many datafiles will make up the tablespace, what type of information will be stored in each tablespace, and on which disk drives the datafiles will be physically stored. When planning the overall logical storage of the database structure, take into account the effects that this structure will have when the database is actually created and running. Such considerations include how the logical storage structure database will affect the following:
  • The performance of the computer executing Oracle
  • The performance of the database during data access operations
  • The efficiency of backup and recovery procedures for the database

Plan the relational design of the database objects and the storage characteristics for each of these objects. By planning the relationship between each object and its physical storage before creating it, you can directly affect the performance of the database as a unit. Be sure to plan for the growth of the database.

In distributed database environments, this planning stage is extremely important. The physical location of frequently accessed data dramatically affects application performance.

During the planning stage, develop a backup strategy for the database. You can alter the logical storage structure or design of the database to improve backup efficiency.

It is beyond the scope of this book to discuss relational and distributed database design. If you are not familiar with such design issues, refer to accepted industry-standard documentation.

Part II, "Oracle Server Processes and Storage Structure" and Part III, "Schema Objects" provide specific information on creating logical storage structures, objects, and integrity constraints for your database.

Task 4: Create and Open the Database


When you complete the database design, you can create the database and open it for normal use. You can create a database at installation time, using the Database Configuration Assistant, or you can supply your own scripts for creating a database.

Either way, refer to Chapter 2, "Creating an Oracle Database", for information on creating a database and Chapter 4, "Starting Up and Shutting Down" for guidance in starting up the database.

Task 5: Back Up the Database


After you create the database structure, carry out the backup strategy you planned for the database. Create any additional redo log files, take the first full database backup (online or offline), and schedule future database backups at regular intervals.

See Also:
For instructions on customizing your backup operations and performing recovery procedures see either of the following:

Task 6: Enroll System Users


After you back up the database structure, you can enroll the users of the database in accordance with your Oracle license agreement, create appropriate roles for these users, and grant these roles.

The following chapters will help you in this endeavor:

Task 7: Implement the Database Design


After you create and start the database, and enroll the system users, you can implement the planned logical structure database by creating all necessary tablespaces. When you complete this, you can create the objects for the database.

Part II, "Oracle Server Processes and Storage Structure" and Part III, "Schema Objects" contain information which can help you create logical storage structures and objects for your database.

Task 8: Back Up the Fully Functional Database


Now that the database is fully implemented, again back up the database. In addition to regularly scheduled backups, you should always back up your database immediately after implementing changes to the database structure.

Task 9: Tune Database Performance


Optimizing the performance of the database is one of your ongoing responsibilities as a DBA. Additionally, Oracle provides a database resource management feature that enables you to control the allocation of resources to various user groups.

The database resource manager is described in Chapter 27, "Using the Database Resource Manager".

See Also:
Oracle9i Database Performance Tuning Guide and Reference contains information about tuning your database and applications.

Identifying Your Oracle Database Software Release


Because the Oracle database server continues to evolve and can require maintenance, Oracle periodically produces new releases. Because only some users initially subscribe to a new release or require specific maintenance, multiple releases of the product can exist simultaneously.

As many as five numbers may be required to fully identify a release. The significance of these numbers is discussed below.

Release Number Format


To understand the release level nomenclature used by Oracle, examine the following example of an Oracle database server labeled "Release 9.2.0.1.0."

Figure 1-1 Example of an Oracle Release Number

Text description of admin002.gif follows
Text description of the illustration admin002.gif


Note:
Starting with release 9.2, maintenance releases of Oracle 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


This is the most general identifier. It represents a major new edition (or version) of the software that contains significant new functionality.

Database Maintenance Release Number


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

Application Server Release Number


This digit reflects the release level of the Oracle9i Application Server (Oracle9iAS).

Component Specific Release Number


This 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


This digit identifies a platform specific release. Usually this is a patch set. Where different platforms require the equivalent patch set, this digit will be the same across the effected platforms.

Checking Your Current Release Number


To identify the release of the Oracle database server that is currently installed and to see the release levels of other Oracle components you are using, query the data dictionary view PRODUCT_COMPONENT_VERSION. A sample query is shown below. Other product release levels may increment independently 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                              9.2.0.1.0       Production        
Oracle9i Enterprise Edition         9.2.0.1.0       Production        
PL/SQL                              9.2.0.1.0       Production        
TNS for Solaris:                    9.2.0.1.0       Production

It's important to convey to Oracle the information displayed by this query when you report problems with the software.

Optionally, you can query the V$VERSION view to see component-level information.

Database Administrator Security and Privileges


To accomplish the administrative tasks of an Oracle DBA, you need extra privileges both 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


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

See Also:
Your operating system specific Oracle documentation. The method of distinguishing a database administrator's account is operating system specific.

Database Administrator Usernames


Two user accounts are automatically created with the database:
  • SYS (default password: CHANGE_ON_INSTALL)
  • SYSTEM (default password: MANAGER)
    Note:
    Oracle recommends that you specify passwords for SYS and SYSTEM at database creation time, rather that using these default passwords. This is explained in "Protecting Your Database: Specifying Passwords for Users SYS and SYSTEM".
    If you use the default passwords, to prevent inappropriate access to the data dictionary tables or other tampering with the database, it is important that you change the passwords for the SYS and SYSTEM usernames immediately after creating an Oracle database.

It is suggested that you create at least one additional administrator user, and grant that user the DBA role, to use when performing daily administrative tasks. It is recommended that you do not use SYS and SYSTEM for these purposes.

Note Regarding Security Enhancements:
In this release of Oracle and in subsequent releases, several enhancements are being made to ensure the security of default database user accounts.
  • During initial installation with the Database Configuration Assistant (DCBA), all default database user accounts except SYS, SYSTEM, SCOTT, DBSNMP, OUTLN, AURORA$JIS$UTILITY$, AURORA$ORB$UNAUTHENTICATED and OSE$HTTP$ADMIN are locked and expired. To activate a locked account, the DBA must manually unlock it and reassign it a new password.
  • In addition, the DBCA prompts for passwords for users SYS and SYSTEM during initial installation of the database rather than assigning default passwords to them. A CREATE DATABASE statement issued manually also lets you supply passwords for these two users.

SYS


When any database is created, the user SYS is automatically created and granted the DBA role.

All of the base tables and views for the database's data dictionary are stored in the schema SYS. These base tables and views are critical for the operation of Oracle. To maintain the integrity of the data dictionary, tables in the SYS schema are manipulated only by Oracle. 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 using the SYS account.

SYSTEM


When a database is created, 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 options and tools. Never create in the SYSTEM schema tables of interest to individual users.

The DBA Role


A predefined role, named DBA, is automatically created with every Oracle database. This role contains most database system privileges. Therefore, it is very powerful and should be granted only to fully functional 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".

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


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.
While referred to as system privileges, SYSDBA and SYSOPER, can also be thought of as types of connections (for example, you specify: CONNECT AS SYSDBA) that enable you to perform certain database operations for which privileges cannot be granted in any other fashion.

SYSDBA and SYSOPER


The following are the operations that 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
  • CREATE SPFILE
  • ARCHIVELOG and RECOVERY
  • 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
  • ARCHIVELOG and RECOVERY
  • 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 manor in which you are authorized to use these privileges depends upon the method of authentication that you choose to 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 user scott has issued the following statements:
CONNECT scott/password
CREATE TABLE admin_test(name VARCHAR2(20));

Later, scott issues these statements:
CONNECT scott/password AS SYSDBA
SELECT * FROM admin_test;

User scott now receives the following error:
ORA-00942: table or view does not exist

This is because scott now references the SYS schema by default. The table was created in the scott schema.


Selecting an Authentication Method


The following methods are available for authenticating database administrators:
  • Operating system (OS) authentication
  • Password files
    Note:
    These methods replace the CONNECT INTERNAL syntax provided with earlier versions of Oracle. CONNECT INTERNAL is no longer allowed.

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 Methods

Text description of admin001.gif follows
Text description of the illustration admin001.gif


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


Non-Secure Remote Connections


To connect to Oracle as a privileged user over a non-secure 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


To connect to Oracle as a privileged user over a local connection or a secure remote connection, you have the following options:
  • You can connect and be authenticated by a password file, provided the database has a password file and you have been granted the SYSDBA or SYSOPER system privilege.
  • 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 OS authentication. On most operating systems, OS authentication for database administrators involves placing the OS username of the database administrator in a special group, generically referred to as OSDBA.

Using Operating System (OS) Authentication


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

Preparing to Use OS Authentication


To enable authentication of an administrative user using the operating system you must do the following:
  1. Create an operating system account for the user.
  2. Add the user to the OSDBA or OSOPER operating system defined groups.
  3. Ensure that the initialization parameter, REMOTE_LOGIN_PASSWORDFILE, is set to NONE. This is the default value for this parameter.

Connecting Using OS 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:
SQL*Plus User's Guide and Reference for syntax of the CONNECT command

OSDBA and OSOPER


Two special operating system groups control database administrator connections when using OS 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 Windows
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.

The following describes how membership in the OSDBA or OSOPER group affects your connection to Oracle:
  • If you are a member of the OSDBA group, and specify AS SYSDBA when you connect to the database, you are granted the SYSDBA system privilege.
  • If you are a member of the OSOPER group, and specify AS SYSOPER when you connect to the database, you are granted the SYSOPER system privilege.
  • If you are not a member of the associated operating system group for SYSDBA or SYSOPER system privileges, the CONNECT command fails.
    See Also:
    Your operating system specific Oracle documentation for information about creating the OSDBA and OSOPER groups

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. Create an operating system account for the user.
  2. If not already created, Create the password file using the ORAPWD utility: 
    ORAPWD FILE=filename PASSWORD=password ENTRIES=max_users
  3. Set the REMOTE_LOGIN_PASSWORDFILE initialization parameter to EXCLUSIVE.
  4. Connect to the database as user SYS (or as another user with the administrative privilege).
  5. If the user does not already exist in the database, create the user. Grant the SYSDBA or SYSOPER system privilege to the user: 
    GRANT SYSDBA to scott;
    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 with the AS SYSDBA or AS SYSOPER clause. For example, user scott has been granted the SYSDBA privilege, so he can connect as follows:
CONNECT scott/tiger AS SYSDBA

However, since scott has not been granted the SYSOPER privilege, the following command will fail:
CONNECT scott/tiger 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 the connection will fail.

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

Creating and Maintaining a Password File


You can create a password file using the password file creation utility, ORAPWD. For some operating systems, you can create this file as part of your standard installation.

This section contains the following topics:

Using ORAPWD


When you invoke the password file creation utility without supplying any parameters, you receive a message indicating the proper use of the command as shown in the following sample output:
orapwd
Usage: orapwd file=<fname> password=<password> entries=<users>
where
file - name of password file (mand),
password - password for SYS (mand),
entries - maximum number of distinct DBAs and OPERs (opt),
There are no spaces around the equal-to (=) character.

The following command creates a password file named acct.pwd that allows up to 30 privileged users with different passwords. In this example, the file is initially created with the password secret for users connecting as SYS.
ORAPWD FILE=acct.pwd PASSWORD=secret ENTRIES=30

Following are descriptions of the parameters in the ORAPWD utility.
FILE

This parameter sets the name of the password file being created. You must specify the full path name for the file. The contents of this file are encrypted, and the file cannot be read directly. This parameter is mandatory.

The types of filenames allowed for the password file are operating system specific. Some operating systems require the password file to be a specific format and located in a specific directory. Other operating systems allow the use of environment variables to specify the name and location of the password file. See your operating system specific Oracle documentation for the names and locations allowed on your platform.

If you are running multiple instances of Oracle using Oracle9i Real Application Clusters, the environment variable for each instance should point to the same password file.

Caution:
It is critically important to the security of your system that you protect your password file and the environment variables that identify the location of the password file. Any user with access to these could potentially compromise the security of the connection.
PASSWORD

This parameter sets the password for user SYS. If you issue the ALTER USER statement to change the password for SYS after connecting to the database, both the password stored in the data dictionary and the password stored in the password file are updated. This parameter is mandatory.
ENTRIES

This parameter specifies the number of entries that you require the password file to accept. This number corresponds to the number of distinct users allowed to connect to the database as SYSDBA or SYSOPER. The actual number of allowable entries can be higher than the number of users because the ORAPWD utility continues to assign password entries until an operating system block is filled. For example, if your operating system block size is 512 bytes, it holds four password entries. The number of password entries allocated is always multiple of four.

Entries can be reused as users are added to and removed from the password file. If you intend to specify REMOTE_LOGON_PASSWORDFILE=EXCLUSIVE, and to allow the granting of SYSDBA and SYSOPER privileges to users, this parameter is required.

Caution:
When you exceed the allocated number of password entries, you must create a new password file. To avoid this necessity, allocate a number of entries that is larger than you think you will ever need.

Setting REMOTE_LOGIN_ PASSWORDFILE


In addition to creating the password file, you must also set the initialization parameter REMOTE_LOGIN_PASSWORDFILE to the appropriate value. The values recognized are described as follows:

Value Description
NONE
Setting this parameter to NONE causes Oracle to behave as if the password file does not exist. That is, no privileged connections are allowed over non-secure connections. NONE is the default value for this parameter.
EXCLUSIVE
An EXCLUSIVE password file can be used with only one database. Only an EXCLUSIVE file can contain the names of users other than SYS. Using an EXCLUSIVE password file allows you to grant SYSDBA and SYSOPER system privileges to individual users and have them connect as themselves.
SHARED
A SHARED password file can be used by multiple databases. However, the only user recognized by a SHARED password file is SYS. You cannot add users to a SHARED password file. All users needing SYSDBA or SYSOPER system privileges must connect using the same name, SYS, and password. This option is useful if you have a single DBA administering multiple databases.

Suggestion:
To achieve the greatest level of security, you should set the REMOTE_LOGIN_PASSWORDFILE initialization parameter to EXCLUSIVE immediately after creating the password file.

Adding Users to a Password File


When you grant SYSDBA or SYSOPER privileges to a user, that user's name and privilege information are added to the password file. If the server does not have an EXCLUSIVE password file (that is, if the initialization parameter REMOTE_LOGIN_PASSWORDFILE is NONE or SHARED) you receive an error message if you attempt to grant these privileges.

A user's name remains in the password file only as long as that user has at least one of these two privileges. If you revoke both of these privileges, the user is removed from the password file.

To Create a Password File and Add New Users to It
  1. Follow the instructions for creating a password file as explained in "Using ORAPWD".
  2. Set the REMOTE_LOGIN_PASSWORDFILE initialization parameter to EXCLUSIVE.
  3. Connect with SYSDBA privileges as shown in the following example: 
    CONNECT SYS/password AS SYSDBA
  4. Start up the instance and create the database if necessary, or mount and open an existing database.
  5. Create users as necessary. Grant SYSDBA or SYSOPER privileges to yourself and other users as appropriate. See "Granting and Revoking SYSDBA and SYSOPER Privileges".

Granting the SYSDBA or SYSOPER privilege to a user causes their username to be added to the password file. This enables the user to connect to the database as SYSDBA or SYSOPER by specifying username and password (instead of using SYS). The use of a password file does not prevent OS authenticated users from connecting if they meet the criteria for OS authentication.

Granting and Revoking SYSDBA and SYSOPER Privileges


If your server is using an EXCLUSIVE password file, use the GRANT statement to grant the SYSDBA or SYSOPER system privilege to a user, as shown in the following example:
GRANT SYSDBA TO scott;

Use the REVOKE statement to revoke the SYSDBA or SYSOPER system privilege from a user, as shown in the following example:
REVOKE SYSDBA FROM scott;

Because SYSDBA and SYSOPER are the most powerful database privileges, the ADMIN OPTION is not used. Only a user currently connected as SYSDBA (or INTERNAL) can grant or revoke another user's SYSDBA or SYSOPER system privileges. These privileges cannot be granted to roles, because roles are only available after database startup. Do not confuse the SYSDBA and SYSOPER database privileges with operating system roles, which are a completely independent feature.

See Also:
Chapter 25, "Managing User Privileges and Roles" for more information on system privileges

Viewing Password File Members


Use the V$PWFILE_USERS view to see the users who have been granted SYSDBA or SYSOPER system privileges for a database. The columns displayed by this view are as follows:

Column Description
USERNAME
This column contains the name of the user that is recognized by the password file.
SYSDBA
If the value of this column is TRUE, then the user can log on with SYSDBA system privileges.
SYSOPER
If the value of this column is TRUE, then the user can log on with SYSOPER system privileges.

Maintaining a Password File


This section describes how to:
  • Expand the number of password file users if the password file becomes full
  • Remove the password file
  • Avoid changing the state of the password file

Expanding the Number of Password File Users


If you receive the file full error (ORA-1996) when you try to grant SYSDBA or SYSOPER system privileges to a user, you must create a larger password file and re-grant the privileges to the users.

To Replace a Password File

  1. Note the users who have SYSDBA or SYSOPER privileges by querying the V$PWFILE_USERS view.
  2. Shut down the database.
  3. Delete the existing password file.
  4. Follow the instructions for creating a new password file using the ORAPWD utility in "Using ORAPWD". Ensure that the ENTRIES parameter is set to a number larger than you think you will ever need.
  5. Follow the instructions in "Adding Users to a Password File".

Removing a Password File


If you determine that you no longer require a password file to authenticate users, you can delete the password file and reset the REMOTE_LOGIN_PASSWORDFILE initialization parameter to NONE. After you remove this file, only those users who can be authenticated by the operating system can perform database administration operations.

Caution:
Do not remove or modify the password file if you have a database or instance mounted using REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE (or SHARED). If you do, you will be unable to reconnect remotely using the password file. Even if you replace it, you cannot use the new password file, because the timestamps and checksums will be wrong.

Changing the Password File State


The password file state is stored in the password file. When you first create a password file, its default state is SHARED. You can change the state of the password file by setting the initialization parameter REMOTE_LOGIN_PASSWORDFILE. When you start up an instance, Oracle retrieves the value of this parameter from the parameter file stored on your client machine. When you mount the database, Oracle compares the value of this parameter to the value stored in the password file. If the values do not match, Oracle overwrites the value stored in the file.

Caution:
Use caution to ensure that an EXCLUSIVE password file is not accidentally changed to SHARED. If you plan to allow instance start up from multiple clients, each of those clients must have an initialization parameter file, and the value of the parameter REMOTE_LOGIN_PASSWORDFILE must be the same in each of these files. Otherwise, the state of the password file could change depending upon where the instance was started.

Database Administrator Utilities


Several utilities are available to help you maintain the data in your Oracle database. This section introduces two of these utilities:

SQL*Loader


SQL*Loader is used both by database administrators and by other users of Oracle. It loads data from standard operating system files (such as, files in text or C data format) into Oracle database tables.

Export and Import


The Export and Import utilities enable you to move existing data in Oracle format to and from Oracle databases. For example, export files can archive database data or move data among different Oracle databases that run on the same or different operating systems.
 
Thanks
Srini
Posted by at
Labels:

No comments:

Post a Comment


No one has ever become poor by giving

Subscribe to: Post Comments (Atom)