Re: Running Batch Jobs without Exposing User ID/Passwords or Source of SQL statements?!
Date: Sun, 04 Jul 2004 02:44:10 GMT
Message-ID: <_9KFc.86983$HS3.28374_at_edtnps84>
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 ...
- Oracle database comes with a built-in job scheduler, DBMS_JOBS, that can submit any PL/SQL procedure as a job.
- Oracle database has ability to call DLLs using 'External Procedures', so if you scheduled jobs are DLLs, you can run them under DB control.
- 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 Sun Jul 04 2004 - 04:44:10 CEST