What is REMOTE_LOGIN_ PASSWORDFILE?

What is REMOTE_LOGIN_ PASSWORDFILE?

•The remote REMOTE_LOGIN_ PASSWORDFILE instance parameter controls whether it is possible to connect to the instance as a user with the SYSDBA privilege over the network.•This parameter is string type and can have two values either NONE or SHARED. The default value is shared. In fact the value EXCLUSIVE is supported for backward compatibility. It now has the same behavior as the value SHARED.•When this parameter is set to NONE the only way to get a SYSDBA connection is to log on to the operating system of the server machine as a member of the operating system group that owns the Oracle software.•Setting this parameter to either EXCLUSIVE or SHARED gives users another way in. They can log on to the server as SYSDBA even if they are not a member of the Oracle software owning group, or even if they are coming in across the network, they can still connect as SYSDBA.•The passwords are embedded, in encrypted form, in an operating system file in the location $ORACLE_HOME/ dbs/orapw on Unix, or %ORACLE_HOME% \pwd .ora database on Windows.•The V$PWFILE_USERS view shows you which users have their passwords entered in the password file, and whether they have the SYSOPER privilege, the SYSDBA privilege, or both.

Database Administrator Authentication
We sometimes need such special operation on database like, startup or shutdown the database or change the archival mode of the task. To do this, two special system privileges, SYSDBA and SYSOPER are required. You must have one of these privileges granted to you, depending upon the level of authorization you require.One thing we need to remember when you connect with SYSDBA or SYSOPER privileges, you connect with a default schema.For SYSDBA this schema is SYS and for SYSOPER the schema is PUBLIC.There are two methods are available for authenticating database administrators.1)Operating System(OS) Authentication. 2)Password File Authentication.Priority of Database Administrator Authentication Methods:------------------------------------------------------------Remote Database Administration ->If have Secure Connection?->If yes->Want to use OS authentication?->If yes then use OS Authentication.Remote Database Administration ->If have Secure Connection?->If yes->Want to use OS authentication?->If No then use Password file Authentication.Remote Database Administration ->If have Secure Connection?->If No then use Password file Authentication.Local Database Administration ->Want to use OS authentication?->If No then use Password file Authentication.Local Database Administration ->Want to use OS authentication?->If yes then use OS Authentication.Using Operating System Authentication-------------------------------------------Two special operating system groups control database administrator connections when using operating system authentication. These groups are generically referred to as OSDBA and OSOPER.On unix is is usually referred as dba/oper and on windows as ORA_DBA/ORA_OPER.If you are a member of the OSDBA/ORAOPER group and you specify AS SYSDBA/ AS SYSOPER when you connect to the database, then you connect to the database with the SYSDBA/SYSOPER system privilege. If you are not a member of OSDBA/ORAOPER group then OS authentication fails.How to Use Operating System AuthenticationTo 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.On unix the system is # id -a oracleuid=100(oracle) gid=100(oinstall) groups=101(dba)# useradd -g oinstall -G dba test# exitexitbash-3.00$ su testPassword:sh-3.00$ sqlplus / as sysdbaOn windows, click on my computer>Manage>Local Users and Groups>Add user and group.Using Password File Authentication--------------------------------------------1.If you don't have password file create one,2.Set the REMOTE_LOGIN_PASSWORDFILE initialization parameter to EXCLUSIVE. (This is the default).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:Here is the steps,1.orapwd FILE=newpwdfile.pwd PASSWORD=test ENTRIES=302.alter system set REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE scope=spfile3.SQL> conn / as sysdba4.SQL> create user test identified by t;5.SQL> !lsnrctl status and see the service name. Here my one is arju.therapbd.com6.grant sysdba to testAnd here is the test after step 5,SQL> grant create session to test;Grant succeeded.SQL> conn test/t as sysdbaConnected.SQL> conn test/t@neptune/arju.therapbd.com as sysdbaERROR:ORA-01031: insufficient privilegesWarning: You are no longer connected to ORACLE.SQL> conn / as sysdbaConnected.SQL> grant sysdba to test;Grant succeeded.SQL> conn test/t@neptune/arju.therapbd.com as sysdbaConnected.You can check who of you have password file authentication using,select username, SYSDBA,SYSOPER from v$pwfile_users;