Skip to main content

Permitting a user on UNIX / Linux to use sqlplus

By
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
Labels:

Comments

  1. AnonymousFriday, March 09, 2007 2:52:00 PM

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

    ReplyDelete
  2. AnonymousMonday, March 12, 2007 2:19:00 PM

    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. UnknownThursday, September 13, 2007 5:59:00 PM

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

    ReplyDelete
  4. AnonymousThursday, March 26, 2009 6:11:00 PM

    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. AnonymousWednesday, July 01, 2009 6:07:00 PM

    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. AnonymousMonday, October 11, 2010 6:20:00 AM

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

    Thanks
    srh

    ReplyDelete
  7. AnonymousMonday, February 28, 2011 8:45:00 AM

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

    ReplyDelete
  8. AnonymousWednesday, September 14, 2011 9:30:00 AM

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

    Thanks again,

    Davide

    ReplyDelete
  9. AnonymousFriday, March 23, 2012 2:27:00 PM

    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

Post a Comment

Please add your comment here

Popular posts from this blog

HWM (high watermark) of a tablespace ?

By
How many times have you thought of calculating HWM (high watermark) of a tablespace as a DBA ? Have you ever found out that a tablespace is overallocated in a database, as compared to the data being used in it ? In such a case, you'd want to reduce the wasted space & resize the tablespace to a smaller size, although greater than the current usage. HWM is at the maximum blocks ever occupied by a SEGMENT (unless we perform a truncate on it). Segments span over datafiles however remain within a tablespace. All segments are physically allocated by way of extents, which are contiguous blocks of data. Now, when we talk about contiguous blocks, 2 datafiles (even from same tablespace) are highly unlikely to have contiguguos data blocks. Hence, theoretically, it's possible to calculate high watermark within a datafile, not a tablespace directly, as the latter is only a logical unit. Although, we can resize a tablespace, however the resize value needs to be based on exact calculati...
7 comments
Read more

Oracle Virtualbox - UUID conflict resolved for VM

By
Now-a-days, we use virtual machines (VMs) a lot, to test operating systems, software, databases etc. as sandboxes - test, play, destroy ... repeat ! I had an interesting learning with Oracle virtualbox (issue & then solution discovery), which I'm sharing here ... it might help someone with similar situation : I had a VM in virtualbox, which I copied into a separate folder, while it was powered off. Now, after the original VM was gone, I wanted to add it as a separate VM with a different name. However, I was seeing this UUID conflict. (UUID already exists) What I did : 1) Created a new empty VM with the desired NAME. 2) Located the new folder, where copy of the VM was there. 3) Ran the commands to change the UUID for each disk-file (.vmdk or others) I had there : "C:\Program Files\Oracle\VirtualBox\vboxmanage.exe" internalcommands sethduuid disk1.vmdk (you have to be in the same directory as the disk files, to run this command. Easy, whereve...
6 comments
Read more