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    :-)

Grant privileges on all tables in particular schema

In oracle, we cannot grant the privileges on schemas level .If we have to grant the privileges on all the tables of a particular schemas, then it is very tedious to grant privileges on all the tables one-by-one to a particular user. This task can be  performed by using a simple pl/sql procedure. Here is a Demo for this : 

Suppose we have to grant "select" privileges on all the tables to user  then we need to do something like this .

SQL>FOR x IN (SELECT * FROM user_tables)
          LOOP
          EXECUTE  IMMEDIATE  'GRANT  SELECT  ON  ' || your.table_names || '  TO <<user>>' ;
           END LOOP ;




Enjoy    :-)

Why and How to Drop Undo Tablespace ?

A condition may be occur when we have to  drop the Undo tablespace. Undo tablespace may be drop in various scenarios .In my case ,Once i have imported few tables with table_exists_action=append parameter in  database and these tables has created lots of undo's  i.e;  near about 102GB. So when we backup the database ,the backup size increases, i.e; backup consumes lots of space. Another scenario may be possible that while clonning if the undo tablespace get missed, then we can recover by just dropping the undo tablespace. Below is demo for drop and re-creating the undo tablespace. 

Step 1 : Shutdown immediate

SQL> shut immediate

Step 2 : Create pfile from spfile and edit pfile to set undo_management=manual (if it is set auto then set it to manual and if this parameter is not in pfile than set it i.e, undo_management=manual  otherwise it will consider it "auto"  management

Step 3 : Startup pfile=<modified pfile>

Step 4 : Drop undo tablespace as

SQL> drop  tablespace <undo_name> including contents and datafiles.

Step 5 : Create Undo tablespace

SQL> create undo tablespace undotbs1 datafile <location> size=100M;

Step 6 : Shutdown the database and edit pfile to reset  "undo_management=AUTO"

Step 7 : create spfile from pfile

SQL> create spfile from pfile=<pfile_location>

Step 8 : Startup the database

SQL> startup 


Enjoy   :-)