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

[John]

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.

[Hans Forbrich]

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

[John]

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" <fo******@yahoo.netwrote in message
news:_9KFc.86983$HS3.28374@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

[doug]

"John" <Jo**@nospam.comwrote in message news:<44aGc.6867$pY2.6190@lakeread01>...

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.

That could work. You need to grant EXECUTE on the procs and CREATE
SESSION to the user, of course.

Oracle also comes with the "wrap" utility. You can use this to turn
your plain text source code into encrypted gibberish -- at least as
far as the average user is concerned. The database can of course read
this just fine. This is the utility that Oracle uses for its supplied
packages.

========
You can enable tracing in an SQL script by adding your alter session
statement. In PL/SQL, you can use
"dbms_session.set_sql_trace(TRUE);".

You could also add an autonomous transaction to simply write to a log
table whenever a procedure is executed. Using autonomous transaction
ensures the record remains whether or not the procedure or session
issues a ROLLBACK.

========
BTW, I use a simplier technique in UNIX: the "HEREFILE" format. Here
is an example:
sqlplus /nolog <<-HEREFILE
connect batch_oper/secret_passwd@ORCL_8a

@script1.sql
@script2.sql

exit
HEREFILE

The "HEREFILE" strings act as labels only and can be anything.
Everything between them is passed as standard input (i.e. as if you
were typing). Thus the internal commands are hidden from the process
list.