Re: Oracle Users/Shell scripts

You may need to do some research on Oracle documentation. Simply put, you already have an operation system account called "foobar", and you also have a oracle database user account called "foobar". You set the database user "foobar" to be identified externally, and hope foobar can login to the db from the operating system wihtout a password.

Normally that will NOT work. Why? Oracle says:

If a user with an operating system account named "TSMITH" is to connect to an Oracle database and be authenticated by the operating system, Oracle checks that there is a corresponding database user "OPS$TSMITH" and, if so, allows the user to connect. All references to a user authenticated by the operating system must include the prefix, as seen in "OPS$TSMITH".

So you may want to "create user ops$foobar identified externally", OR, change the default username prefix "OPS$" to null. It can be done with the init parameter: OS_AUTHENT_PREFIX="". Then oracle will not append OPS$ to the OS user account.

Another issue, you need to modify your script and replace "sqlplus foobar" with "sqlplus /", that's what you should tell oracle when you want to use OS authentication.

Finally, if you use OS auth, you CANNOT specify a "@database_specification".

Hope this helps.

Robert Xuequn XU
Bioinformatics Specialist
Caltech, Pasadena, CA

Neil Reardon (nfr_at_archimedes.demon.co.uk) wrote:
: Hi Folks...

: I'm running Oracle 7.3.4 on Solaris 2.5.1 and am trying to call an
: Oracle script (through sqlplus) from a csh script. I have created both a
: UNIX and Oracle user called "foobar". The Oracle user was created with
: the following SQL:

: create user foobar identified externally default tablespace appl_data_ts
: temporary tablespace temp_data_ts;

: i.e. the Oracle user has been identified externally.

: The csh script contains the following lines:

: -- BEGIN
: #!/bin/csh

: $PATH_TO_ORACLE_BIN_DIR/sqlplus foobar @users.sql
: -- END

: The SQL script contains the following:

: -- BEGIN
: select * from dba_users;
: -- END

: (Tough stuff, I know!) The scripts aren't working, i.e. I am asked to
: enter a password when "sqlplus" is fired up. When I enter the password,
: i.e. the password of user "foobar", I am given an "invalid
: username/password" message. I thought that identifying the user
: externally caused Oracle to obtain the information from the O/S's
: password file. Am I missing something?

: Thanks in advance for any help!

: Neil Reardon
: Technical Consultant
: Xyvision Ltd
Received on Tue Mar 30 1999 - 13:44:19 CST