OS Authentication

• Once authenticated by the operating system, users can connect to Oracle more conveniently, without specifying a user name or password. For example, an operating-system-authenticated user can invoke SQL*Plus and skip the user name and password prompts by entering the following:
SQLPLUS /
• With control over user authentication centralized in the operating system, Oracle need not store or manage user passwords, though it still maintains user names in the database.
• Audit trails in the database and operating system use the same user names.


Note: When an operating system is used to authenticate database users, managing distributed database environments and database links requires special care.


OS Authentication Configuration Steps

1. Create OS user
2. Set below parameters.
alter system set os_authent_prefix = ‘OPS$’ scope=spfile;
Note: Default parameter value is “OPS$”

If OS user is member of domain group then also set below parameter.

alter system set remote_os_authent=TRUE scope=spfile;
Note: Default parameter value is FALSE,

3. Edit sqlnet.ora file and set below line to NTS
Sqlnet.authentication_services=(NTS)

4. Bounce database
shutdown immediate
startup

5. Create Oracle User for OS authentication

First check OS username
select sys_context(‘USERENV’,’OS_USER’) from dual;

create user “OPS$USERNAME” identified by EXTERNALLY;

Note: Oracle username is must be same as OS user name and must be started with “OPS$”.

6. Test to connect through OS authentication

sqlplus /

REMOTE_LOGIN_ PASSWORDFILE

remote_login_passwordfile parameter is use for Password Authentication Method.


There are three value for remote_login_passwordfile

1. None

With this setting "NONE" oracle behave passwordfile doesn't exists.


2. Exclusize ===>Default value.


An EXCLUSIVE password file can be used with only one instance of one database. Only an EXCLUSIVE file can be modified. Using an EXCLUSIVE password file enables you to add, modify, and delete users. It also enables you to change the SYS password with the ALTER USER command.


3. Shared


A SHARED password file can be used by multiple databases running on the same server, or multiple instances of a Real Application Clusters (RAC) database. A SHARED password file cannot be modified. This means that you cannot add users to a SHARED password file. Any attempt to do so or to change the password of SYS or other users with the SYSDBA or SYSOPER privileges generates an error. All users needing SYSDBA or SYSOPER system privileges must be added to the password file when REMOTE_LOGIN_PASSWORDFILE is set to EXCLUSIVE. After all users are added, you can change REMOTE_LOGIN_PASSWORDFILE to SHARED, and then share the file.


remote_login_passwordfile ====> is non modifiable parameters

this parameter is non modifiable. mean you have to restart oracle server to make any changes.

1. alter system set remote_login_passwordfile = [value]
2. shutdown
3. startup

or

1. shutdown
2. edit pfile and make changes in remote_login_passwordfile
3. create spfile from pfile
4. startup

Creating and Maintaining a Password File

Password File Creation.

You can create password file through ORAPWD utility

> orapwd
Usage: orapwd file= password= entries= force=

where
file - name of password file (mand),
password - password for SYS (mand),
entries - maximum number of distinct DBAs and OPERs (opt),
force - whether to overwrite existing file (opt)
There are no spaces around the equal-to (=) character.



Note: ORAPWD is invoke on OS command prompt. Windows "C:" , *nix "$"


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.


Note:

You cannot change the password for SYS if REMOTE_LOGIN_PASSWORDFILE is set to SHARED. An error message is issued if you attempt to do so.

Adding Users to a Password File

When you grant SYSDBA, SYSOPER privs to a user, that user's name and privs infomation are added to the password file.

but if the server doesn't have an EXCLUSIVE password file (that is, if the initialization parameter REMOTE_LOGIN_PASSWORDFILE is NONE or SHARED, or the password file is missing), Oracle Database issues an error if you attempt to grant these privileges.


SQL> grant SYSDBA,SYSOPER to [username];

Viewing Password File Members

SQL> select * from v$pwfile_users;

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



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.

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. If not already created, create the password file using the ORAPWD utility:

ORAPWD FILE=filename PASSWORD=password ENTRIES=max_users

C:\>set ORACLE_HOME=c:\oracle\product\10.1.0\db_1

C:\>ORAPWD file=%ORACLE_HOME%\database\pwdorcl.ora password=oracle entries=5

2. Set the REMOTE_LOGIN_PASSWORDFILE initialization parameter to EXCLUSIVE. (This is the default).


Note:

REMOTE_LOGIN_PASSWORDFILE is a static initialization parameter and therefore cannot be changed without restarting the database.


3. Grant the SYSDBA or SYSOPER system privilege to the user:


SQL>grant SYSDBA,SYSOPER to scott;

Grant succeeded.


This statement adds the user to the password file, thereby enabling connection AS SYSDBA.

You can check in v$pwfile_users views for which user having SYSDBA,SYSOPER privs.

SQL> select * from v$pwfile_users;

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

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 attempting to connect as SYSDBA or as SYSOPER fails.

Authentication Method

The following methods are available for authenticating database administrators:


1.Operating system (OS) authentication

2.A password file

Notes:

These methods replace the CONNECT INTERNAL syntax provided with earlier versions of Oracle Database. CONNECT INTERNAL is no longer supported.


Operating system authentication takes precedence over password file authentication. If you meet the requirements for operating system authentication, then even if you use a password file, you will be authenticated by operating system authentication.

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:

1. If the database has a password file and you have been granted the SYSDBA or SYSOPER system privilege, then you can connect and be authenticated by a password file.

2. 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 operating system authentication. On most operating systems, authentication for database administrators involves placing the operating system username of the database administrator in a special group, generically referred to as OSDBA. Users in that group are granted SYSDBA privileges. A similar group, OSOPER, is used to grant SYSOPER privileges to users.

SQL> select username , sysdba, sysoper from v$pwfile_users;

USERNAME SYSDB SYSOP
------------------------------ ----- -----
SYS TRUE TRUE
SCOTT FALSE TRUE
A TRUE FALSE

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

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.
The SYSDBA and SYSOPER privileges can also be thought of as types of connections that enable you to perform certain database operations for which privileges cannot be granted in any other fashion. For example, you if you have the SYSDBA privilege, you can connect to the database by specifying CONNECT AS SYSDBA.

SYSOPER Privileges

1.Perform STARTUP and SHUTDOWN operations

2.CREATE SPFILE

3.ALTER DATABASE OPEN/MOUNT/BACKUP

4.ALTER DATABASE ARCHIVELOG

5.ALTER DATABASE RECOVER (Complete recovery only. Any form of incomplete recovery, such as UNTIL TIME|CHANGE|CANCEL|CONTROLFILE requires connecting as SYSDBA.)

6.Includes the RESTRICTED SESSION privilege


This privilege allows a user to perform basic operational tasks, but without the ability to look at user data.


SYSDBA Privileges

1.Perform STARTUP and SHUTDOWN operations

2.ALTER DATABASE: open, mount, back up, or change character set

3.CREATE DATABASE

4.DROP DATABASE

5.CREATE SPFILE

6.ALTER DATABASE ARCHIVELOG

7.ALTER DATABASE RECOVER

8.Includes the RESTRICTED SESSION privilege

Effectively, this system privilege allows a user to connect as user SYS.


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.

SQL> conn scott/tiger
Connected.
SQL> drop table admin_test purge;

Table dropped.

SQL> create table admin_test ( no number);

Table created.

SQL> conn scott/tiger as sysdba
Connected.
SQL> sho user
USER is "SYS"
SQL> select * from admin_test;
select * from admin_test
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> grant SYSOPER to scott;

Grant succeeded.

SQL> conn scott/tiger as sysoper
Connected.
SQL> select * from admin_test;
select * from admin_test
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> show user
USER is "PUBLIC"

For connecting SYSDBA privs to database any user need

1. SQLNET.AUTHENTICATION_SERVICES= (NTS) in sqlnet.ora
2. OS user is member of ORA_DBA group
3. Explicit granted SYSDBA role to user

For connection SYSOPER privs to database any user need

1. Explicit granted SYSOPER role to user