sysdba privilege and OS Authentication in oracle local database and password file for remotely

what are sysdba and sysoper privileges in oracle ?
why/how sys user can connect to the stop oracle database ?
when you need a password file in oracle database ?
what is OS Authentication in oracle database and How maintain it ?
How can prevent sys user to accessing oracle database without password ?
( like connect / as sysdba )
Why need DBA(unix) / ORA_DBA(windows) group in oracle database server?


All Answer:-
==============

There are two main administrative privileges in Oracle: SYSOPER and SYSDBA .
SYSDBA and SYSOPER are special privileges as they allow access to a database instance
even when it is not running and so control of these privileges is totally outside of
the database itself.

(such as
The 'OSDBA' and 'OSOPER' groups are chosen at installation time and usually both default
to the group 'dba'. These groups are compiled into the 'oracle' executable and so are the
same for all databases running from a given ORACLE_HOME directory.
The actual groups being used for OSDBA and OSOPER can be checked thus:

cd $ORACLE_HOME/rdbms/lib
cat config.[cs]

The line '#define SS_DBA_GRP "group"' should name the chosen OSDBA group.
The line '#define SS_OPER_GRP "group"' should name the chosen OSOPER group.

If you want to change the OSDBA or OSOPER groups this file needs to be modified
either directly or using the installer. )



So SYSDBA privilegesed user password can not be stored in the database, because Oracle can not access the database before the instance is started up. Therefore, the authentication of the DBA must happen outside of the database. There are two distinct mechanisms to authenticate the DBA: using the password file or through the operating system.


( SYSOPER privilege allows operations such as:
Instance startup, mount & database open ;
Instance shutdown, dismount & database close ;
Alter database BACKUP, ARCHIVE LOG, and RECOVER.
This privilege allows the user to perform basic operational tasks without the ability to look at user data.

SYSDBA privilege includes all SYSOPER privileges plus full system privileges
(with the ADMIN option), plus 'CREATE DATABASE' etc..
This is effectively the same set of privileges available when previously connected INTERNAL.)


when you want to administrate your database remotely then you must need a password file for SYSDBA privileged users.

If the connection to the instance is local or 'secure' then it is possible to use the operating system to determine if a user is allowed SYSDBA or SYSOPER access. In this case no password is required.
The syntax to connect using operating system authentication is:

CONNECT / AS SYSDBA
or
CONNECT / AS SYSOPER

Oracle determines if you can connect thus:

On UNIX the Oracle executable has two group names compiled into it,
one for SYSOPER and one for SYSDBA.These are known as the OSOPER and OSDBA groups.
Typically these can be set when the Oracle software is installed.

(means for locally authentication no need for any password file. but locally need OS Authentication by making the connected OS user in the member of DBA(unix) / ORA_DBA(windows) group )


connection to the instance is considered remotelly and 'secure' then you MUST use a password to connect with SYSDBA or SYSOPER privilege. When the passwordfile is initially created with the uility orapwd it holds the password for user SYS, other users can be added to the password file with the 'GRANT SYSDBA to USER' command.
Such a user can then connect to the instance for administrative purposes using the syntax:

CONNECT username/password AS SYSDBA
or
CONNECT username/password AS SYSOPER



The init parameter remote_login_passwordfile specifies if a password file is used to authenticate the DBA or not. If it set either to shared or exclusive a password file will be used. Default location and file name is
$ORACLE_HOME/dbs/orapw$ORACLE_SID on Unix and %ORACLE_HOME%\database\PWD%ORACLE_SID%.ora on Windows.

How to show ,add and remove user from password file ? see below :-


SQL*Plus: Release 11.1.0.6.0 - Production on Sun Jun 12 14:47:40 2011

Copyright (c) 1982, 2007, Oracle. All rights reserved.

SQL> conn sys@test123 as sysdba
Enter password:
Connected.
SQL>
SQL>
SQL> select * from v$pwfile_users;

USERNAME SYSDB SYSOP
------------------------------ ----- -----
SYS TRUE TRUE

SQL> grant sysdba to halim identified by halim ;

Grant succeeded.

SQL>
SQL> select * from v$pwfile_users;

USERNAME SYSDB SYSOP
------------------------------ ----- -----
SYS TRUE TRUE
HALIM TRUE FALSE

SQL>
SQL> grant sysoper to halim ;

Grant succeeded.

SQL> select * from v$pwfile_users;

USERNAME SYSDB SYSOP
------------------------------ ----- -----
SYS TRUE TRUE
HALIM TRUE TRUE

SQL> revoke sysoper from halim ;

Revoke succeeded.

SQL> select * from v$pwfile_users;

USERNAME SYSDB SYSOP
------------------------------ ----- -----
SYS TRUE TRUE
HALIM TRUE FALSE

SQL>
SQL>