Friday, November 03, 2006

Permitting a user on UNIX / Linux to use sqlplus

We encountered an interesting scenario worth mentioning.

Scenario:
  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
    ld.so.1: sqlplus: fatal: /app/oracle/product/10.2/lib/libsqlplus.so: Permission
    denied
    Killed
  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/libsqlplus.so
  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`;
    do
    echo "Dir or file : $i";
    echo "change ?";
    read ans;
    if [ $ans -eq '1' ];
    then chmod -R o-rx $i;
    else echo "Didn't change";
    fi;
    done
  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) :
    1. ORACLE_HOME,
    2. LD_LIBRARY_PATH,
    3. ORACLE_SID,
    4. PATH

9 comments:

  1. Another tip :
    To be able to run "sqlldr" (SQL Loader), you need to grant permission on "$ORACLE_HOME/rdbms" directory .

    ReplyDelete
  2. In some instances inspite of giving permissions on specified folders still we will get the following message::

    $ tnsping CTRD

    TNS Ping Utility for Solaris: Version 10.2.0.1.0 - Production on 12-MAR-2007 14:15:20

    Copyright (c) 1997, 2005, Oracle. All rights reserved.

    Message 3511 not found; No message file for product=network, facility=TNSMessage 3512 not found; No message file for product=network, facility=TNSAttempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = MOPPGPPCMD1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = CTRD)))
    Message 3509 not found; No message file for product=network, facility=TNS

    In this case give the same permissions on network.after this the problem should get resolved.

    ReplyDelete
  3. Thanks for the comments. I'll add these to appropriate place here.

    ReplyDelete
  4. After following these instructions, we are still getting the error

    SP2-1503: Unable to initialize Oracle call interface
    SP2-0152: ORACLE may not be functioning properly

    What do we do?

    ReplyDelete
  5. If you have access to Oracle's support site, have a look at the Note 443638.1 - Permission Denied Errors for users other than "oracle". Basically it tells you that for certain versions of Oracle you can run $ORACLE_HOME/install/changePerm.sh and get around the permissions issues (albeit with reduced security)

    ReplyDelete
  6. Thanks Guys, it helped me a lot.
    How do we give unix user to use EXP/IMP and EXPDP/IMPDP

    Thanks
    srh

    ReplyDelete
  7. Thanks, I've saved a lot of time because of your experience :)

    ReplyDelete
  8. thank you,
    chmod 755 on $ORACLE_HOME/rdbms worked fine for me.

    Thanks again,

    Davide

    ReplyDelete
  9. Your info saved me grief as for the first time ever a user was allowed direct access to a unix server to do sqlplus in my company.......normally they come in through a client which doesnt exhibit these issues. Thanks again for sharing.....

    ReplyDelete