10g Password file Authentication PASSWORD FILE Authentication Password files are created with the orapwd tool. Password files are created with the
orapwd
command line utility.
Remote SYSDBA connections
attempted with a user name and password uses password file authentication Password file authentication syntax
$ sqlplus
/ as sysdba $ sqlplus / @ as sysdba Password file authentication is enabled by setting the database parameter remote_login_password file to "shared" or "exclusive". If the connection to the instance is local (single SYSDBA/ SYSOPER privileged user) In this case no password is required. The syntax to connect using operating system authentication is SQL>CONNECT / AS SYSDBA or SQL>CONNECT / AS SYSOPER Above mentioned case , We don't need a password to connect to Oracle DB. conn user /any password as sysdba we can connect to the Database. This method is “ not workable for remote access” If we want to administrate our database remotely , must need a password file for SYSDBA privileged users. Connection to the instance is considered remotely and must use a password to connect with SYSDBA/SYSOPER users. When the password file is initially created with the uility orapwd it holds the password for user SYS. DB users can be added to the password file with the 'GRANT SYSDBA to USERNAME'. Exploring the Oracle DBA Technology by Thiyagu Gunasekaran
10g Password file Authentication
SQL>CONNECT username/password AS SYSDBA SQL>CONNECT username/password AS SYSOPER
Local database connection
Remote database connection
CONNECT / AS SYSDBA
CONNECT /@net_service_name AS SYSDBA
CONNECT / AS SYSOPER
CONNECT /@net_service_name AS SYSOPER
ORAPWD ORAPWD is a utility used to create a password file for an Oracle Database. The ORAPWD utility
to grant
SYSDBA and SYSOPER privileges to other
database users. By default, the user SYS is the only user that has these privileges (SYSDBA/SYSOPER). The default location of the password file , on Linux machine
$ORACLE_HOME\dbs
and name orapw$ORACLE _SID.
Creating password file via orapwd enables remote users to connect with administrative privileges through SQL*Net . SYNTAX : $ orapwd orapwd file= password= entries= force= orapwd file = file_name password = password for SYS [entries = number_of_users] [ force = Y/N ] [ ignorecase = Y/N ] [ nosysdba =Y/N ] Exploring the Oracle DBA Technology by Thiyagu Gunasekaran
10g Password file Authentication FILE Password file name. PASSWORD Password for the SYS user. IGNORE CASE Password will be case insensitive. ENTRIES =
Maximum number of
database users
that can be granted
SYSDBA/ SYSOPER privileges in the password file.
FORCE = if the value of this parameter is Y then the existing password file will be overwritten. FORCE parameter is available starting from Oracle 10g. SYSDBA/SYSOPER privileges are granted to a user, When we grant "SYSDBA" or "SYSOPER" privileges to a user, that user's name and privilege
information are added to the password file. Lets check how it works. SQL> select * from v$pwfile_users; USERNAME
SYSDB
SYS
TRUE
SYSOP
TRUE
SQL> create user rose identified by rose; User created.
SQL>grant sysdba to rose; Grant succeeded. Confirm
the user is listed in the Password file.
SQL> select * from v$pwfile_users; USERNAME
SYSDB
SYSOP
SYS
TRUE
TRUE
ROSE
TRUE
FALSE
Exploring the Oracle DBA Technology by Thiyagu Gunasekaran
10g Password file Authentication Now the user ROSE can connect as SYSDBA. 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.
If revoke the privilege , Oracle DB removes the user
from the password file . ALTER USER statement to change the password for the SYS user after who connect to
the database, then both
the password stored in the data
dictionary and the password stored in the password file are updated. PASSWORDFILE AS : orapwd file=orapwdtest password=oracledb entries=15 This command creates a password file as orapwtest that allows up to 15 privileged users can use different passwords. This number (15) corresponds to the number of distinct users allowed to connect to the database as SYSDBA/ SYSOPER. Creating sample Password file : $ orapwd file=orapwSID password=xxxxxx entries=5 (or) $ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=xxxxxx If Password file exists: $ orapwd file=orapwSID password=oracle entries=5 FORCE=Y Password=xxxxxx This is the password the privileged users should enter while connecting as SYSDBA/ SYSOPER.
Exploring the Oracle DBA Technology by Thiyagu Gunasekaran
10g Password file Authentication Two options for "SYS" Password authentication. 1) OS authentication 2) Password file authentication Password file authentication options are NONE, EXCLUSIVE, SHARED. Lets discuss about password file authentication briefly. Password file creating for SYS user, by default SYS having SYSDBA privilege. Setting Remote_Login_PasswordFile Parameter We should set the initialization parameter Remote_Login_Passwordfile to an appropriate value. Options are none, exclusive, and shared. This parameter specifies whether Oracle checks for a password file. NONE Here oracle means password file doesn’t exist.
i.e. Oracle ignores any
password file. No privileged connections will be allowed over nonsecure connections. Privileged users must be authenticated by the operating system. Remote_Login_Passwordfile changed to NONE SQL> alter system set remote_login_passwordfile=none scope=spfile; SQL>startup force;
SQL> select * from v$pwfile_users ; no rows selected If we tried to grant SYSDBA/SYSOPER privilege to
user , Oracle Database
issues an error (ORA-01994) if we attempt to grant those privileges. Exploring the Oracle DBA Technology by Thiyagu Gunasekaran
10g Password file Authentication Parameter Setting can be confirmed by SQL> SHOW PARAMETER REMOTE_LOGIN_PASSWORDFILE ; SQL> SHOW PARAMETER PASSWORD ; SQL> SHOW PARAMETER PASS ; NAME
TYPE
remote_login_passwordfile
VALUE
string
NONE
SQL> grant sysdba to rose; grant sysdba to rose ORA-01994: GRANT failed: password file missing or disabled
EXCLUSIVE Exclusive is the default value. The password file can be used by only one database. The password file can contain SYS as well as NON SYS users. Password file is used by only one (instance) of the database. An EXCLUSIVE file can contain the names of users other than SYS. (Any user can be added to the password file). We can add, modify, and delete users. This option enables to change the SYS password with the ALTER USER command. EXCLUSIVE password file allows
to grant SYSDBA and SYSOPER system
privileges to individual users and have them connect. Remote_Login_Passwordfile changed to EXCLUSIVE
SQL> alter system set remote_login_passwordfile=none scope=spfile; SQL> startup force;
Exploring the Oracle DBA Technology by Thiyagu Gunasekaran
10g Password file Authentication Parameter Setting can be Confirmed by SQL>SHOW PARAMETER REMOTE_LOGIN_PASSWORDFILE ; SQL> SHOW PARAMETER PASSWORD ; SQL>SHOW PARAMETER PASS; NAME
TYPE
remote_login_passwordfile
string
VALUE
EXCLUSIVE
SQL> grant sysdba to rose ; grant succeeded.
SQL>SELECT * FROM V$PWFILE_USERS ; USERNAME
SYSDB
SYSOP
SYS
TRUE
TRUE
ROSE
TRUE
FALSE
POINTS TO NOTE :
We can’t change the password for SYS , if REMOTE_LOGIN_PASSWORDFILE is set to SHARED. Setting shared (value) banns changing the password file. If we try to change the password file generates the error (ORA-01999). To modify this file, need to change this parameter value to EXCLUSIVE. SQL> alter system set remote_login_passwordfile=shared scope=spfile; System altered. SQL> startup force;
Exploring the Oracle DBA Technology by Thiyagu Gunasekaran
10g Password file Authentication When remote_login_password=shared , if we try to update password file , we could encounter following error. SQL> revoke SYSDBA from rose; revoke sysdba from rose * ERROR at line 1: ORA-01999: password file cannot be updated in SHARED mode
SHARED This option is useful for single DBA administering multiple databases. A SHARED password file can be used
by multiple
databases running on
the same server, or multiple instances of an Oracle Real Application Clusters (RAC) database. A SHARED password file cannot be modified. This means
that we can’t add users to a SHARED password file. The only user recognized by a SHARED password file is
SYS. All users
needing SYSDBA/ SYSOPER system privileges must connect using the same name, SYS, and password. See ORA -01999 error. However, the only user that can be added/authenticated is SYS. Deleting Password file To remove the password file, first delete it and then set the initialization parameter remote_login_passwordfile to none. Now the users that can authenticate by the OS will be able to connect to the database as SYSDBA or SYSOPER.
Exploring the Oracle DBA Technology by Thiyagu Gunasekaran
10g Password file Authentication Checking no of users added in the password file When SYSDBA or SYSOPER privileges are granted to a user, that user's name and privilege information are added to the password file.
SQL>select * from v$pwfile_users; POINTS TO REMEMBER whether SYSDBA/SYSOPER privilege users list can query through v$pwfile_users.
File holds the password information. The file location will default to the current directory. Contents are encrypted and are unreadable. The password required is the one for the SYS user of the database. The privileges assigned to
SYSDBA correspond to
OSDBA. SYSOPER
correspond to OSOPER. Operating System verifies the password provided using an external operating system file. This external file is generated using the ORAPWD utility. When the password for the INTERNAL or SYS accounts are changed with the ALTER USER command, the changes are mapped to the operating system password file. Password file is not present , remote_login_password= EXCLUSIVE If remote_login_passwordfile is NONE or EXCLUSIVE but the password file is not present , password file authentication is disabled and the only way to connect as SYSDBA is OS authentication. we can't grant a SYSDBA privilege to any user because password file is missing. When we exceed the allocated number of password entries, we must create a new password file. To avoid this necessity, allocate
actual number of
allowable entries can be higher than the number of users. Exploring the Oracle DBA Technology by Thiyagu Gunasekaran
10g Password file Authentication
Exploring the Oracle DBA Technology by Thiyagu Gunasekaran
