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, SYSDBA and SYSOPER. You must have one of these privileges granted to you, depending upon the level of authorization you require.
SYSDBA and SYSOPER
The following operations are authorized by the SYSDBA and SYSOPER system privileges:
System Privilege Operations Authorized
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:
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.
Selecting an Authentication Method
The following methods are available for authenticating database administrators:
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. illustrates the choices you have for database administrator authentication schemes.
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.
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.
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:
Using Operating System Authentication
This section describes how to authenticate an administrator using the operating system.
Preparing to Use Operating System Authentication
To enable operating system authentication of an administrative user:
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:
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
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 Windows
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 onnection to the database in the following ways:
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:
This statement adds the user to the password file, thereby enabling connection AS SYSDBA.
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:
However, user oe has not been granted the SYSOPER privilege, so the following command will fail:CONNECT oe/oe AS SYSOPER
Oracle 10g Related Interview Questions
|Oracle 10g Interview Questions||Oracle 9i Interview Questions|
|Oracle 8i Interview Questions||Informatica Interview Questions|
|PL/SQL Interview Questions||Oracle 11g Interview Questions|
|SQL Interview Questions||Oracle apps Interview Questions|
|Sybase Interview Questions||Oracle Apps ERP Interview Questions|
|Oracle 7.3 Interview Questions||Oracle Access Manager Interview Questions|
|Oracle Application Framework Interview Questions||Oracle Apps DBA Interview Questions|
Oracle 10g Related Practice Tests
|Oracle 10g Practice Tests||Oracle 9i Practice Tests|
|Oracle 8i Practice Tests||Informatica Practice Tests|
|PL/SQL Practice Tests||Oracle 11g Practice Tests|
|SQL Practice Tests||Oracle apps Practice Tests|
|Sybase Practice Tests||Oracle Apps ERP Practice Tests|
|Oracle 7.3 Practice Tests|
Oracle 10g Tutorial
Overview Of Administering An Oracle Database
Creating An Oracle Database
Starting Up And Shutting Down
Managing Oracle Database Processes
Managing Control Files
Managing The Redo Log
Managing Archived Redo Logs
Managing Datafiles And Tempfiles
Managing The Undo Tablespace
Using Oracle-managed Files
Using Automatic Storage Management
Managing Space For Schema Objects
Managing Partitioned Tables And Indexes
Managing Hash Clusters
Managing Views, Sequences, And Synonyms
General Management Of Schema Objects
Detecting And Repairing Data Block Corruption
Managing Users And Securing The Database
Managing Automatic System Tasks Using The Maintenance Window
Using The Database Resource Manager
Moving From Dbms_job To Dbms_scheduler
Overview Of Scheduler Concepts
Using The Scheduler
Administering The Scheduler
Distributed Database Concepts
Managing A Distributed Database
Developing Applications For A Distributed Database System
Distributed Transactions Concepts
Managing Distributed Transactions
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.