Hi,

I have a UNIX Shell script which has a SQL written inside it.
The Shell Script is called by a Concurrent Programme and when executed, the shell script runs the SQL Inside it and sends the output via mail.
The script runs fine when I put the login credentials i.e. user id and password of Oracle Database within the shell script.
But this procedure might create security issues in PRODUCTION because anyone might open the shell script and see the user id and pwd.
Can somebody suggest a better option to connect to data base.

Thanks

External authentication is the answer.

Can't you use the Default parameters sent from Oracle Apps to Unix Shell Script run thru Concurrent Program ....
Example :$1 is the Request id and $2 is the User Name / pass word of the Database you are connected in ....

Hi,

I am new to shell scripting and hence need some help.
The read somewhere that $1 and $2 are reserved for request id and User id/pwd and hence I did tried a situation whereby I did not put the user id / pwd and ran the shell script. I ensured that I have not used $1 and $2 as any of my parameter. But the script failed
Can you please let me know whether I need to write something specific in the shell script to ensure that the shell picks $1 as Request ID and $2 as User ID and PWD

Thanks

Hi,
why shouldn't you execute the SQL through a stored procedure? The database frontend application languages I know all have means to invoke a stored procedure through the regular database connection and this way the authentication is performed by establishing the connection with the application.

I'm only familiar with Informix stored procedures but they are capable of dynamicly building strings and run OS commands in SPL and I can't imagine Oracle doesn't provide a similar functionality. If necessary it even can be done in Java stored procedures.

So my way of handling this problem would be creating a stored procedure which performes the SQL statement, putting the results in a string and finally run the UNIX mail command with the string as parameter.
Another approach could be using the script as it is but invoked by a stored procedure. Because the executer of the shell script is an authenticated (at application logon), privileged user of the database the Informix database server is not concerned with authentication anymore when the SQL in the shell script is performed.

Regards.

@ksundararajan, @Tyveleyn: OP's requirement is related to Oracle database.

In order to login to Oracle using sqlplus and NOT supply an id/password, you need to create a user in the database with the same name as the Unix account name, with whatever prefix is set by the os_auth_prefix initialization parameter. Example, suppose your unix id is: UNIXID, then:

Code:

Create User OPS$UNIXID Identified Externally
Default Tablespace USERS Temporary Tablespace TEMP;

Grant CONNECT, SYSOPER To OPS$UNIXID;

Then you invoke sqlplus like this:

Code:

#!/bin/ksh
. /path/to/oraenv
sqlplus / @/path/to/scripts/MyScript.sql >/path/to/report/MyReport.txt
mailx -s"`date` - MyReport" theUser@mydomain.com </path/to/report/MyReport.txt

PS: Make sure the following initialization parameters are set:

remote_login_passwordfile='exclusive'
remote_os_authent=true