External OS User Authentication in Oracle

Oracle users can be authenticated in different ways,We generally login into our database with two ways . i.e, either as

C:\> sqlplus / as sysdba        or
C:\> sqlplus user/password@SID as sysdba.

This is only valid when we are the member of  "ORA_DBA"  OS  group(window) . If we are not the member of the "ORA_DBA" group,then we cannot login into database . Let have a look :  Here i have created a osuser  "oraext" and login with this user and try to connect with database.

Step 1 : Create OSuser 

C:\>net user oraext orapass /add
The command completed successfully.

Check the domain
C:\> echo %userdomain%
TECH-199

Step 2:  Login with "oraext"  user in  window machine and try to connect the database as:

c:\> sqlplus sys/sys@noida as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Sep 30 14:46:39 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR: ORA-01017:  invalid username/password; logon denied
Enter user-name:

Here,we are getting invalid username error,this is because the os user "oraext" is not the member of  the ORA_DBA group. Therefore to handle such case Oracle provide OS authentication method to connect database without authenticating any database username and password. Let's check 

In order to create the external user authentication we perform the following as :

1.) Check the values of the parameter "os_authentication_prefix"

SQL> SHOW PARAMETER os_authent_prefix
NAME                                 TYPE                       VALUE
---------------------             -----------                 -------------
os_authent_prefix            string                         OPS$

As we can see, the default value is "ops$". If this is not appropriate it can be changed using the alter system command.

2.) Create a database user with same name as the OS user which is prefixed by os_authent_prefix values followed by domain name. On Windows platforms we would expect an Oracle username of   "OPS$DOMAIN\xxxx"  for the Windows user "xxxx".

Now we know the OS authentication prefix, we can create a database user to allow an OS authenticated  connection. To do this, we create an Oracle user in the normal way, but the username must be the prefix value concatenated to the domain-name and OS username . Therefore the username seems like "ops$tech-199\oraext"

SQL> create user "ops$tech-199\oraext" identified externally;

3.) Grant connect privileges to them .

SQL> grant connect to "ops$tech-199\oraext";

Now Login as user "oraext" in window  and open the cmd and connect as  :

C:\>sqlplus / 
SQL*Plus: Release 11.2.0.1.0 Production on Fri Sep 30 17:27:31 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

Note: The parameter in sqlnet.ora file be SQLNET.AUTHENTICATION_SERVICES= (NTS)

Advantage of the OS authentication:

1.) Without OS Authentication applications must store passwords in a variety of applications each with their own security model and vulnerabilities.

2.) Domain authentication already has to be secure because if it is not then database security just slows down access to the database, but cannot prevent it.

3.) Users that only have to remember one domain password can be made to create more secure domain passwords more easily than they can be made to create even less secure database passwords as the number of different databases they must connect to increases.

Enjoy  :-) 

Oracle Session Information From SYS_CONTEXT

SYS_CONTEXT is a standard Oracle Database function used to retrieve session-level information. SYS_CONTEXT  allow us to retrieve a set of session parameters via the namespace parameter ‘USERENV’.Basically, these are global variables that Oracle stores on the current session/user. Here is a sample of some session and user-specific information that can be obtained in one function call:

SQL> select sys_context('USERENV','AUTHENTICATION_TYPE') 
          ,sys_context('USERENV','CURRENT_SCHEMA') 
          ,sys_context('USERENV','CURRENT_SCHEMAID') 
          ,sys_context('USERENV','CURRENT_USER') 
          ,sys_context('USERENV','CURRENT_USERID') 
          ,sys_context('USERENV','DB_DOMAIN') 
          ,sys_context('USERENV','DB_NAME') 
          ,sys_context('USERENV','INSTANCE') 
         ,sys_context('USERENV','IP_ADDRESS') 
         ,sys_context('USERENV','ISDBA') 
         ,sys_context('USERENV','LANG') 
         ,sys_context('USERENV','LANGUAGE') 
         ,sys_context('USERENV','NETWORK_PROTOCOL') 
         ,sys_context('USERENV','NLS_CALENDAR') 
         ,sys_context('USERENV','NLS_CURRENCY') 
         ,sys_context('USERENV','NLS_DATE_FORMAT') 
        ,sys_context('USERENV','NLS_DATE_LANGUAGE') 
        ,sys_context('USERENV','NLS_TERRITORY') 
        ,sys_context('USERENV','OS_USER') 
        ,sys_context('USERENV','SESSION_USER') 
        ,sys_context('USERENV','SESSION_USERID') 
       ,sys_context('USERENV','SESSIONID') 
       ,sys_context('USERENV','TERMINAL') from dual ;

The output of the above sample is :

Enjoy      :-) 

Identify IP Addresses and Host Names

We can identify the IP addresses of all the client connect to server. Below the command to identified  the IP address ,machine name,sid  .

SQL>   select   sid,   machine,     UTL_INADDR.get_host_address (substr(machine,instr(machine,'\')+1))  ip  from   v$session   where   type='USER'   and    username is not null   order  by   sid;

Oracle provides the method to identifying and relating to the IP addresses and host names  for Oracle clients and servers. The few methods  are  : 

1.) UTL_INADDR
2.) SYS_CONTEXT
3.) V$INSTANCE
4.) V$SESSION

1.) UTL_INADDR  :  The UTL_INADDR package provides a PL/SQL procedures to support internet addressing. It provides an API to retrieve host names and IP addresses of local and remote hosts.The utl_inaddr like the two function  : 

1.) get_host_address and 
2.) get_host_name.

Now we check the description of UTL_INADDR 

SQL> desc UTL_INADDR

FUNCTION  GET_HOST_ADDRESS  RETURNS  VARCHAR2
 Argument Name                Type                In/Out         Default?
 -----------------          ------------           --------      -------------
 HOST                         VARCHAR2            IN           DEFAULT
FUNCTION GET_HOST_NAME RETURNS VARCHAR2
 Argument Name               Type                    In/Out             Default?
 ----------------           ---------------        --------          ------------
 IP                                 VARCHAR2            IN                DEFAULT

I.) Get_host_address: The get_host_address function like the argument name "HOST" , data types "varchar2" and by default NULL. The get_host_address get local IP address and remote host given its IP address.

II.) Get_host_name: The get_host_name function like the argument name "IP" , data types "varchar2" and by default NULL. The get_host_name get local host name and remote host given its name.

Let see some example : 

The GET_HOST_ADDRESS function returns the IP address of the specified host name

SQL>select  UTL_INADDR.get_host_address('TECH-284') from  dual;

UTL_INADDR.GET_HOST_ADDRESS('TECH-284')
-----------------------------------------------------------
192.100.0.85

The GET_HOST_NAME function returns the host name of the specified IP address.

SQL> SELECT UTL_INADDR.get_host_name('192.168.52.127') FROM dual;

UTL_INADDR.GET_HOST_NAME('192.168.52.127')
----------------------------------------------------------
tech-199

The host name of the database server is returned if the specified IP address is NULL or omitted.

2.) SYS_CONTEXT : Oracle has a very useful built-in function called SYS_CONTEXT. The SYS_CONTEXT function is able to return the following host and IP address information for the current session.SYS_CONTENT has the following options as 

TERMINAL - An operating system identifier for the current session. This is often the client machine name.
HOST - The host name of the client machine.
IP_ADDRESS - The IP address of the client machine.
SERVER_HOST - The host name of the server running the database instance.

What makes this function more interesting is the fact that Oracle provides a built-in namespace called USERENV with predefined parameters, which describes the current session.

The syntax of this function goes like this:
SYS_CONTEXT ( 'namespace' , 'parameter' [, length] )

Let see some example :

SQL> SELECT SYS_CONTEXT('USERENV','TERMINAL') FROM dual;
SYS_CONTEXT('USERENV','TERMINAL')
---------------------------------------------------
TECH-199

SQL> SELECT SYS_CONTEXT('USERENV','HOST') FROM dual;
SYS_CONTEXT('USERENV','HOST')
------------------------------------------
TECH\TECH-199

SQL> SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') FROM dual;
SYS_CONTEXT('USERENV','IP_ADDRESS')
--------------------------------------------------
192.100.0.112

SQL> SELECT SYS_CONTEXT('USERENV','SERVER_HOST') FROM dual;
SYS_CONTEXT('USERENV','SERVER_HOST')
-----------------------------------------------------
tech-199

For more about sys_context check the below link .
http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/functions150.htm
http://www.adp-gmbh.ch/ora/sql/sys_context.html

3.) V$INSTANCE :  The host_name column of the V$INSTANCE view contains the host name of the server running the instance.

SQL> SELECT host_name FROM v$instance;
HOST_NAME
-----------------
TECH-199

4.) V$SESSION  :  V$SESSION displays session host information for each current session. The column such as terminal and machine give the following details.
TERMINAL: The operating system terminal name for the client.This is often set to the client machine name.
MACHINE :The operating system name for the client machine.This may include the domain name if present.

SQL> SELECT terminal, machine FROM v$session WHERE username = 'HR';
TERMINAL           MACHINE
-------------       --------------------
TECH-199          TECH\TECH-199


Enjoy    :-)