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

On Sun, 04 Jul 2004 05:26:20 GMT, Mladen Gogala <gogala_at_sbcglobal.net> wrote:

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

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