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

Hi.

The scripts are simply a list of SQL / PL/SQL statements. I run them using sqlplus and redirect their output to log files that I can parse for errors.

To keep the code secure from manipulation, should I encapsulate each script in a stored procedure and then call the stored procedure from a Windows account that has no right but execute that procedure? Is there any way to turn on trace on stored procedures to get equivalent of a log for parsing purposes?

Thanks.

"Hans Forbrich" <forbrich_at_yahoo.net> wrote in message news:_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 ...
>
> 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 Mon Jul 05 2004 - 12:29:09 CEST