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

On Sun, 04 Jul 2004 02:44:10 +0000, Hans Forbrich wrote:

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

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

-- 
Well-behaved women seldom make history