By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,213 Members | 2,172 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,213 IT Pros & Developers. It's quick & easy.

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

P: n/a
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.
Jun 27 '08 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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
Jun 27 '08 #2

P: n/a
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

Jun 27 '08 #3

P: n/a
"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.
Jun 27 '08 #4

This discussion thread is closed

Replies have been disabled for this discussion.