Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2006
    Posts
    6

    Unanswered: Connecting to a database via UNIX Shell Script

    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

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking



    External authentication is the answer.

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Nov 2006
    Posts
    9

    Use $2

    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 ....

  4. #4
    Join Date
    Dec 2006
    Posts
    6
    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

  5. #5
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    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.
    Last edited by Tyveleyn; 12-17-06 at 09:15.

  6. #6
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking


    @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




    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •