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

From: doug <change_on_install_at_yahoo.com>
Date: 5 Jul 2004 08:22:24 -0700
Message-ID: <8f428d3d.0407050722.110f4128@posting.google.com>

"John" <John_at_nospam.com> wrote in message news:<44aGc.6867$pY2.6190_at_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_at_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. Received on Mon Jul 05 2004 - 10:22:24 CDT