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]

Post by 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.
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

[Mladen Gogala]

Post by Hans Forbrich
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.

He is asking for a way to hide username and password, which implies
that he needs to do more then just execute a simple PL/SQL script.

Post by Hans Forbrich
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.

Yup, that can be done, but it is a major pain in the neck or lower. It is
also very insecure thing to do, which is clearly visible from many
external procedure related security on the metalink. That "unbreakable"
phrase was just a marketing ploy.


There are two ways to do that. One involves advanced security and
so called global login, which is established externally to the database.
That will cost you a buck or two and is likely to require purchase of
additional inexpensive products like RADIUS server or a Kerberos server.
The other way is to use a little bit deprecated, but still supported and
necessary OPS$ feature. Create an additional user named "mladen"
("orabatch" would, actually, be a better solution) and create oracle
user called OPS$ORABATCH. When jobs are launched from the ORABATCH
Windoze username, user simply connects as "/". It works like this:
$ sqlplus /

SQL*Plus: Release 10.1.0.2.0 - Production on Sun Jul 4 01:22:18 2004

Copyright (c) 1982, 2004, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> show user
USER is "OPS$MGOGALA"
SQL>

As you can see, no password information is revealed and your database is
as secure as your OS, which is not very comforting and reassuring having
in mind that the database is on Windoze.

[Sybrand Bakker]

Post by Mladen Gogala

Post by Hans Forbrich
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.

He is asking for a way to hide username and password, which implies
that he needs to do more then just execute a simple PL/SQL script.

Post by Hans Forbrich
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.

Yup, that can be done, but it is a major pain in the neck or lower. It is
also very insecure thing to do, which is clearly visible from many
external procedure related security on the metalink. That "unbreakable"
phrase was just a marketing ploy.
There are two ways to do that. One involves advanced security and
so called global login, which is established externally to the database.
That will cost you a buck or two and is likely to require purchase of
additional inexpensive products like RADIUS server or a Kerberos server.
The other way is to use a little bit deprecated, but still supported and
necessary OPS$ feature. Create an additional user named "mladen"
("orabatch" would, actually, be a better solution) and create oracle
user called OPS$ORABATCH. When jobs are launched from the ORABATCH
$ sqlplus /
SQL*Plus: Release 10.1.0.2.0 - Production on Sun Jul 4 01:22:18 2004
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> show user
USER is "OPS$MGOGALA"
SQL>
As you can see, no password information is revealed and your database is
as secure as your OS, which is not very comforting and reassuring having
in mind that the database is on Windoze.

This only works if you use "<COMPUTER NAME>"\<LOCAL NT ACCOUNT>"
all in uppercase. The default os_authentication_prefix is currently ""
and Oracle doesn't recommend using the OPS$ prefix anymore.


--
Sybrand Bakker, Senior Oracle DBA