is there a way to code a windows batch file that runs sqlplus in such a way that the username and password are not exposed?
We have several dos scripts that call sqlplus to produce spool files or run sql scripts and the password is coded within the dos script for all to see.
Is there a clever way around it? - cant log on with windows authentication because the database is not set up that way.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
davo, I will give another point of view : see, "the database is not set up that way" does NOT make sense, OS authentication do NOT need any special setup, itīs just a CREATE USER !!! Sorry, but a db where you canīt create new users is something weird - possible, but weird, and very rare.... Please check this before you give up the OS-ayth option....
We do this using external authentication. Our computer operators who run our batch jobs all have ID's in Oracle which match their Windows ID's with a prefix of "OPS$" added. Then our batch jobs run SQLPlus as follows:
plus33w.exe / mysqlfile.sql
Note the forward slash where the oracle ID and password would normally be. So if windows user RPERRY is running this job, and if there exists oracle user OPS$RPERRY then the login is automatic without having to specify an ID or password for Oracle.
In order to make this work you also need these lines in your init.ora file:
remote_os_authent = true
os_authent_prefix = OPS$
Also when you setup the "OPS$...." Oracle ID's you must specify 'external authentication'. You will not need a password for these ID's - all the security falls on Windows and there are no exposed passwords in the batch files.
Most any Oracle utility can use external authentication. SQLPlus, Export, Import, Schema Manager, Developer 2000, etc. etc. We have even been able to modify applications written in COBOL and Microsoft Access to take advantage of this.
Another alternative is to specify the oracle connect string by a runtime argument. Not very convenient for the user - they need to keyin the connect string as part of the run command for the job - but at least it keeps the ID and password out of the batch file. The batch file would then contain the following line:
plus33w.exe %1 mysqlfile.sql
Or, for clarification, do it like this:
Set OraConnect = %1
plus33w.exe %OraConnect% mysqlfile.sql