Re: Running Batch Jobs without Exposing User ID/Passwords or Source of SQL statements?!

John wrote:

> Hi.
>
> I have a number of batch jobs that are ran nightly on our Windows 2000
> based
> Oracle 8.1.7 (soon to be 9i) server. I have these designed just right, so
> the Windows Scheduled Tasks runs them and then a parser goes through the
> output and, in case of errors, sends me a page...
>
> The database is our financial system which requires users to login using
> Oracle based user ID / Password.
>
> Here are two concerns:
>
> 1. Right now, the batch files used to schedule the processes, parse the
> output and generate Emails, have a set of Oracle User ID/Password
> hardcoded in them. Ideally, I would want to use Windows authentication to
> schedule the jobs so that passwords are not visible to others.
>
> 2. Of course, the source of the batch process is visible in SQL format and
> can be viewed and, worse yet, modified by malicious hackers... Ideally, I
> guess, most of the SQL processing could move to stored procedures...
>
> Dare I mention SQLServer 2000?! I had to recently pick that up to help
> manage several other projects and am quite impressed by the way one
> creates Jobs and processes that remain pretty much secure from these kind
> of issues. I know Oracle must have similar features, but I am not familiar
> enough with
> them and hope you can make appropriate suggestions... How do others
> approach these problems?
>
> Thanks.

Not knowing how bound you are to your code, or what kind of coding style you used, here are a few thoughts ...

1. Oracle database comes with a built-in job scheduler, DBMS_JOBS, that can submit any PL/SQL procedure as a job.
2. Oracle database has ability to call DLLs using 'External Procedures', so if you scheduled jobs are DLLs, you can run them under DB control.
3. Oracle database has native ability to send email using UTL_SMTP package. The contents can, of course, be generated dynamically.

All of the above are fairly easy to use and documented at http://docs.oracle.com. In particular, the DBMS_JOBS and UTL_SMTP are documeted in the "Supplied PL/SQL Packages" docco.

4) Oracle database comes with a WORKFLOW package that has ability to send out emails, and receive & parse emails as a response. (See Workflow docco)

5) Oracle database comes with a mesage queue mechanism called Advanced QUeueing which could receive the async request from an unqualifies batch job, through subscription, and use that to trigger a job or a workflow. (See Advanced QUeue docco)

These are a bit more involved but not terribly difficult to use either.

HTH
/Hans Received on Sat Jul 03 2004 - 21:44:10 CDT