Sunday, May 16, 2010

Secure sqlplus connection?

  sqlplus command is used to connect to Oracle from Unix and we saw the different ways to connect to sqlplus from UNIX and retrieve data in one of our earlier articles. Whenever we connect to sqlplus, the username, password and instance are provided in the command line itself. This way of establishing a connection to sqlplus is not considered secure since the database user details are accessible to any UNIX user from the process table.

  To illustrate this, let us open two Unix terminals.

  1. In Terminal 1, connect to an sqlplus session as shown below:
#echo $USER
# sqlplus blogger/Secret!@myinst
      The sqlplus command used above will establish a sqlplus connection.

  2. In the Terminal 2, let us list all the process for the user 'guru'.
#ps -ef | grep sqlplus
guru 2716 29208  0 20:43:05 pts/10  0:01 sqlplus blogger/Secret!@myinst
  As shown above, sqlplus connection is shown as one of the processes in which all the credentials are easily viewable. These credentials are not only visible to the user 'guru' and the 'root', but also to any user in the UNIX box. And hence this is not a secure way of connecting to sqlplus in very sensitive environments.


   sqlplus connection can be made from the shell in a different way in which no user information needs to be given as part of command line arguments. All the credentials are given only after getting into the sqlplus session:

1. In the 1st terminal, we will establish a sqlplus connection in the way shown below:
#sqlplus /nolog
>connect blogger/Secret!@myinst
>show user
USER is "blogger"
    As shown above, the username and password details are provided using the sqlplus connect command. And hence the Unix shell is not aware of the user details.

  2. In the 2nd terminal, lets list again all the processes run by the user 'guru':
#ps -ef | grep sqlplus
guru 3261 29208 23 20:44:07 pts/10 0:01 sqlplus /nolog
   The user/password details are no longer visible and hence the connection is secure.


  1. hi,

    In the Terminal 2, i am not getting the username/password

    #ps -ef | grep sqlplus
    guru 2716 29208 0 20:43:05 pts/10 0:01 sqlplus

    What can be the reason for this?