473,554 Members | 2,194 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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
3 6700
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
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.8698 3$HS3.28374@edt nps84...
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
"John" <Jo**@nospam.co mwrote in message news:<44aGc.686 7$pY2.6190@lake read01>...
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.s et_sql_trace(TR UE);".

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@O RCL_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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
1658
by: Neil Davidson | last post by:
We have a component without a user interface which we want to license at design time. If someone has a design-time license then they should be able to compile applications using the component, but if they do not have a design time license then they should not be able to do this. The component does not have a user interface so its...
3
2128
by: bill | last post by:
I need to open a asp.net web form from a classic asp page, and pass a username and password to the asp.net page. The username and password exist as session variables in the classic asp application. I can't put the password in the classic asp page form as a hidden field and submit it, because someone can view source and see the password. ...
2
1645
by: Stephen | last post by:
Hi all, I want to create an app that runs a batch file which inturn runs applications. question: if I start the batch file from app1, how can app1 know that the batch file ran successfully and there was no errors.. (is there a return type) we run a lot of sql jobs using batch files, so we want to create a user interface to run batch...
4
6419
by: Shiraz | last post by:
Hi I'm using Visual Studio Installer to make my installer, and have not as yet figured out a straightforward way to use it to set environmental variables. Amongst the various things I tried, I'm thinking the following might help. I would appreciate if someone could comment on this idea and possibly suggest a better one: The environement...
0
2001
by: sandyschiru | last post by:
Hi, I just started learning DB2. I need to write Batch Jobs in DB2. Aby one let me know how to start with and any materials to refer. Please let me know the materials to refer.
3
2149
by: psbasha | last post by:
Hi, I would like to call the same aplication executable with and without Graphical User Interface. Requirement: With Tkinter User interface,user can give the inputs to run the application ( Interactive). Instead user will have this inputs in the file and it will be read by the batch utility or exe for the application For example:
3
1097
by: mista852003 | last post by:
Help?!!! How to delete the row in datagrid without delete from data source?
3
3276
by: =?Utf-8?B?QmlsbHkgWmhhbmc=?= | last post by:
I have a asp.net page, it uses js to print in IE. It always has the prompting user window appear. I have tried: <input onclick="document.all.WebBrowser.ExecWB(6,6)" type="button" value="Print directly"> <input onclick="document.all.WebBrowser.ExecWB(6,2)" type="button" value="Print directly">
0
7812
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
1
7574
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7894
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5442
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3579
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3561
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2026
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1145
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
850
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.