Friday, November 03, 2006

Permitting a user on UNIX / Linux to use sqlplus

We encountered an interesting scenario worth mentioning.

  1. Oracle is installed on a Linux server newly & another ftp user wants to just use sqlplus also.
  2. Just setting the ORACLE_HOME, LD_LIBRARY_PATH, ORACLE_SID, PATH in the user's profile did not let him run the "sqlplus" command at all. The error shown was :
  3. $ sqlplus sqlplus: fatal: /app/oracle/product/10.2/lib/ Permission
  4. The files under "lib" directory under home didn't have permission for anybody other than "oracle" user.
  5. -rwxr-x--- 1 oracle oinstall 1320016 Jun 28 2005 /app/oracle/product/10.2/lib/
  6. When we permit the other users (other than oracle) to read/execute on the lib directory (chmod -R o+rx $ORACLE_HOME/lib), the error changed.
  7. $ sqlplus
    Error 6 initializing SQL*Plus
    Message file sp1.msb not found
    SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory
  8. Now, sqlplus is looking for sp1us.lang file under $ORACLE_HOME/sqlplus/mesg directory as per the information available on Oracle forums. Hence, the error. I allowed the others to "rx" here too.
  9. The error changed again :
  10. $ sqlplus
    SP2-1503: Unable to initialize Oracle call interface
    SP2-0152: ORACLE may not be functioning properly
  11. The actual procedure followed was to allow "rx" on all directories after "sqlplus" & then removing "rx" privilege one-by-one from each unimportant directory & testing sqlplus command at each step, from another session.
  12. I used a little script to change all the directory permissions interactively, only if I want to (by looking at the name & thinking as an Oracle DBA ;-) ) and ran this from inside ORACLE_HOME .
  13. $ for i in `ls $ORACLE_HOME`;
    echo "Dir or file : $i";
    echo "change ?";
    read ans;
    if [ $ans -eq '1' ];
    then chmod -R o-rx $i;
    else echo "Didn't change";
  14. The new information I discovered was on allowing "rx" to "oracore" directory in ORACLE_HOME, "sqlplus" started working. There's a mesg directory there too, which none of the errors pointed to :-( !

Conclusion :
  1. For any normal user (not a part of "oinstall" / "dba" groups ) to be able to run sqlplus and access an ORACLE database , read/execute permissions are required for these 4 directories :
    1. $ORACLE_HOME/bin
    2. $ORACLE_HOME/lib
    3. $ORACLE_HOME/oracore
    4. $ORACLE_HOME/sqlplus
  2. In addition, these 4 parameters should also be set in the user's environment (.profile) :
    3. ORACLE_SID,
    4. PATH