Note that the most recent version of this document can be found here.
What versions of Oracle will Oracletool work with?
Does Oracletool work with Windows NT as the web server?
Does the web server need to be running on the machine that Oracletool is installed on?
Can Oracletool connect to databases on other machines?
Can't you make Oracletool look nicer? Why aren't there any graphics?
Required privileges for a connecting user
The short version, if you already have the DBI and DBD::Oracle Perl modules installed
The long version, if the above does not pertain..
Setting parameters in the oracletool.ini file
Executing Oracletool for the first time.
Troubleshooting Oracletool installation problems
Oracletool is provided as is, without warranty of any kind. The author takes no responsibility for events which may be caused by the use or misuse of this software.
Copyright (c) 1998 – 2014 Adam vonNieda, Kansas USA
You may distribute under the terms of either the GNU General Public License or the Artistic License, as specified in the Perl README file, with the exception that it cannot be placed on a CD-ROM or similar media for commercial distribution without the prior approval of the author.
Oracletool can be a challenge to install the first time, especially if you are not familiar with how web based apps, particularly CGI, work. So why bother? Well, once installed, Oracletool can be a very handy addition to your toolbox. Most tools limit you to your personal workstation, meaning that’s where they are installed. If you have to leave your desk to work a problem, your tools stay there. Because Oracletool is server based, it doesn’t matter where you are; If you have a web browser, you can connect to Oracletool and use it to help you debug problems. Also, while Oracletool may be challenging to some on the first installation, that’s the only one you have to do. Oracletool is installed once, in one place, and everyone who can access your web server (and has the proper Oracle accounts) can use it from that point. In addition, any scripts or information you store in “MyOracletool” are accessible from anywhere. The fact that Oracletool is not client based is one of its greatest strengths, and that’s why it’s written this way.
Oracletool is written more from a DBA standpoint than developer. In the past, the tool checked for the explicit "SELECT ANY TABLE" privilege upon a user logging in. This is no longer true. But, because Oracletool depends on so many 'DBA%', 'V$%' views, and system level tables, a user needs some degree of authority, albeit select only, on these data dictionary objects. In order to make the tool available to both DBA and developer, you may want to run the included 'create_role.sql' script, which will prompt you for a role name, which will be created, and may be granted to users who are allowed to access data dictionary views, but not rows from all tables in the database (SELECT ANY TABLE). The exception is that it will not grant privileges to any '%LINK%' views or tables, thus preventing said user from obtaining passwords for existing database links. Having said this, Oracletool is targeted more to the Oracle DBA than the developer, and always will be.
There are better tools than Oracletool for developers. As a DBA, however, after learning about all of the sections within Oracletool, you will be pleasantly surprised at just how handy it can be.
Oracletool has functionality that may not be readily apparent until you use it on a variety of databases. If a particular function is not configured on your database, you will not see menu items for it. Here are some examples..
· If your database is not RAC, that section will not appear.
· If you are not using replication, it will not appear.
· If your database is not backed up via RMAN, that will not appear.
· You will only see RMAN catalog repository information if you are connected to a database where a catalog exists.
· You will not see Oracle statspack information unless it’s being used.
A: All versions 7.3 and above. As of 2.3, only 10g and above are "supported".
A: Yes. In fact, using Activestate Perl (http://www.activestate.com) and the Perl Package Manager, DBI and DBD::Oracle are very easy to install on Windows platforms.
A: Yes. Oracletool, Perl (with required modules), and web server must all be on the same machine.
A: Yes, Oracletool only needs to be installed in one place, and all of your users can use it to connect to all of your Oracle databases.
A: Oracletool is designed to get the job done in the easiest way possible. I’ve not found a good reason to bloat it with graphics. You can set the background image for your theme in the oracletool.ini file, however. There are several "themes" to choose from to make the tool more to your preferences as far as colors etc.
A: This is generally a permissions problem. Most
likely your
web server cannot access the libraries it needs to in order to load the
DBD::Oracle module. There is a more detailed explanation here.
Please go to symbolstone.org for more information about installing / using the DBI and DBD::Oracle modules.
A web server that allows CGI. I use the Apache web server, and so do millions of others.
The user that you choose to connect to each of the databases you use Oralcetool with needs to have authority to see the underlying data dictionary views, and of course permission to connect to the database. Granting “CREATE SESSION” to these users will fill the connecting requirement, and granting the following privileges will allow the user to select the data dictionary views.
Oracle7, Oracle8, Oracle8i: SELECT ANY TABLE
Oracle9i and > SELECT ANY TABLE, SELECT ANY DICTIONARY
Granting the privileges above will give you the capability to see all of the information that Oracletool selects from the database. It does not give you permission to use the DBA functions of Oracletool. You may or may not want to grant the DBA role to your connecting user.
Instructions for those who already have DBI and DBD::Oracle
compiled into their perl.
1) Copy oracletool.pl to your CGI directory.
2) Copy oracletool.sam to oracletool.ini, in the same
directory as Oracletool.
3) Set a few parameters at the top of the ini file.
4) Set the path to perl correctly at the top of oracletool.pl
5) Execute it like so...
http://yourhost/yourcgidir/oracletool.pl
Installation of Oracletool is pretty straightforward, especially if you have installed stuff on your web server before. The main thing is that you need to have the DBI and DBD::Oracle modules compiled into your Perl BEFORE you attempt to install Oracletool. These instructions will assume that you have already done so. If you have not done that yet, download the required Perl modules and follow the instructions to install them. I have compiled the DBI and DBD::Oracle modules many times and have had very few problems. If you do encounter problems with DBI or the DBD::Oracle module, there are mailing lists devoted to the subject, please use them.
OK, let's get to it! At this point, you should have a recent version of Perl with the DBI and DBD::Oracle modules compiled in and tested. You also have a functional web server in place, right? If not, back up and come back here when you've got those things in place. Download Oracletool if you have not already.
Make sure you change this to the path to your
perl binary
with
DBI and DBD::Oracle compiled in!
AND make sure you do a chmod
755 oracletool.pl !!
#!/usr/bin/perl
OK, on to the less obvious stuff
Copy oracletool.sam to oracletool.ini, and put it in the same directory as oracletool.pl.
There are several variables in oracletool.ini that may need to be set. These are at the very top of the script and there is an explanation of each variable above it.
You will see a section at the top of the oracletool.ini that looks like this...
#=============================================================================
# Begin user editable section. You may need to change some things in this
# section
#=============================================================================
The first variable you may want to set is for your $ORACLE_HOME.
Set this in the line that looks like this.
ORACLE_HOME = /apps/oracle/product/10.2
Next, set your $TNS_ADMIN variable, if you use one that is not default.
The default is $ORACLE_HOME/network/admin.
TNS_ADMIN = /home/oracle/admin/sqlnet
The rest of the variables in this section are self explanatory, and you
can mess with them if you choose to. The default colors work OK for
me.
When you see the following...
#========================================================================
# End user editable section. Do not edit below this point.
#========================================================================
Then you're almost ready to go!.
Copy oracletool.pl to a directory that is defined to the web server as a
cgi directory. For instance, under Redhat Linux, the default CGI directory
is /home/httpd/cgi-bin.
Make sure that oracletool.pl is executable by the web server, meaning that,
the web server may not run as the same user id that installed oracletool.pl.
Doing a chmod 755 oracletool.pl is safe. There is no info stored in the script
that people shouldn't see for any reason. When you are finished with all of the
above, you should be able to execute oracletool.pl by putting something like the
following into your browser address window. Let's assume that your web server
is named 'webserver' and that you copied oracletool.pl into the default CGI
directory on your web server.
http://webserver/cgi-bin/oracletool.pl
And you should get back a window with entries from your tnsnames.ora file in a drop down list.
If you have problems, I can be reached at adam@oracletool.com, but please read the following first..
It’s important to understand the way Oracletool will be executed by the web server. The web server is executing at the OS level as a user that is probably not the same user that installed Oracletool. For instance, if you log into a UNIX server as “adam”, and put the oracletool.pl and oracletool.ini files into your web servers CGI directory, say. “/var/www/cgi-bin”, those files may be owned by you. That’s fine, but they will be executed by the user that the web server runs as. This user is often named “nobody”, or “apache”, or something else, but it is likely NOT running as “adam”. So, you need to make sure that “apache”, for instance, has permission not only to read oracletool.ini, and execute oracletool.pl, but also to access the required Oracle libraries that are installed on the server which make DBD::Oracle use possible.
If you have done all the prerequisites prior to executing Oracletool and are still having problems, try running the “example.cgi” script which came with the Oracletool distribution. Edit the variables at the top of the script and run it by hand. It should display some raw HTML text, and SYSDATE, which was selected from your database. If this script works, then DBI and DBD::Oracle are installed and working correctly. Next, try running it as a CGI script from your web browser. If it does not work, look at your web server.s error log and try to determine the problem. You might also want to try logging in to the server as the same user that the web server runs as, and try executing the script
If you are getting an error that looks similar to the following..
Can't load
'/usr/lib/perl5/site_perl/5.6.0/i386-linux/auto/DBD/Oracle/Oracle.so'
for
module DBD::Oracle: libclntsh.so.9.0: cannot open shared object file:
No such
file or directory at /usr/lib/perl5/5.6.0/i386-linux/DynaLoader.pm
This is generally a permissions problem. Most likely your web
server
cannot access the libraries it needs to in order to load the
DBD::Oracle
module. Sometimes, on Linux, the full path to $ORACLE_HOME/lib (not the
variable),
needs to be added to the /etc/ld.so.conf file and then
“ldconfig” run as root.
If you’ve tried everything above, and are still having
problems, gather
up as much error information as you can and send it to me at adam@oracletool.com.
Web based applications are hard to secure. I’ve made the best effort I can to make Oracletool secure by adding the option to encrypt the cookies that are stored on your PC, and transferred back and forth from Oracletool.
I decided to provide some information about cookies because many people do not want to use a cookie based application, like Oracletool. Let me know if I have left anything out.
A: Cookies are files that are stored on your PC. Your PC being the machine that you are running your browser on. When you start your browser it reads these files and puts the information in memory.
A: They come from web servers, or sites. When you access a site, it will often write a cookie to your browser, which writes the file to your PC when you close the browser. By writing a cookie, the site can access it later, and that's how sites seem to know who you are when you return. It goes like this...
You access yahoo.com
Yahoo.com askes you for your name.
You type it in.
Yahoo.com sends a cookie to your browser with that information.
Your browser saves it.
One week later, you access Yahoo.com again.
Yahoo.com reads the cookie, and knows who you are. It says “Welcome back Mr. Smuckatelly
A: It depends on how they are used, and your views on security. Cookies can only be as secure as your PC. If someone walks up to your PC, starts your browser, and accesses Yahoo.com, the steps above occur and that person in essentially you now. Cookies are stored in text files, so they can be read by any text editor on your PC if you know where to look for them. In versions prior to 1.05, Oracletool stored usernames and passwords in plain text which was very unsecure. These cookies can now be encrypted by adding a few Perl modules to your system. I feel that this is a very secure method of storing this information provided there is no security loopholes in your browser. Internet Explorer (4,5) has one such loophole.
The description of the problem is at (As of May 26, 2000)
http://peacefire.org/security/iecookies
and the fix is at http://www.microsoft.com/technet/security/bulletin/ms00-033.asp
Keep in mind that not only do they have to have your information, they need to have access to your network. This means that most databases are already protected from everyone but your co-workers.
If someone was to break into your computer and read your cookie file, a typical entry will look like this if you are using the Oracletool encryption...
toolbox.sessionid52616e646f6d4956d503c1af9bca21e8338a2a8786cc25f74fc098e88047fc3c
And will look like this if you are not...
toolbox.sessionidsystem~manager
That's a big difference! I will always suggest using the encryption if possible. The only way that someone can decode the top string is if they know what type of encryption is being used, and if they know the encryption key, which is a parameter in the oracletool.ini that is set by the administrator.
Some people are extremely security concious, and will never trust a cookie based application. That's just the way it is. The fact is, your cookies are but one of many things that a malicous person will have access to if he or she breaks into your computer. I hope this has helped to explain how Oracletool protects your passwords.
Thanks to Richard Chen for code contributions pertaining to encryption
========================================================
Usernames and passwords for the database instances you access can now be encrypted before they are sent to your PC as a cookie. These were being written in plain text prior to v1.2.0. There are 3 different levels of security available in Oracletool now. These levels will be enabled automatically depending on which Perl modules you have installed. To find out what level you are at, click on the "About" menu item after installing Oracletool. Here are the differences between the different levels, and the dependencies for each...
This is the level that Oracletool is at prior to v1.2.0. If you are at level 0 then there is no encryption of any kind taking place. If you do not have any of the modules discussed below installed, this will be your level. The cookie which stores the username and password for the databases you access will be stored in plain text on your PC.
This level uses the MIME::Base64 method of encoding. With this level, the username, password, and encryption string are encoded into a single string. The encryption string is set in the oracletool.ini, and is MD5 encoded before being added to the string. This level requires the following Perl modules... These modules are available at CPAN (http://www.cpan.org) and Activestate (http://www.activestate.com)
1) Digest-MD5
2) Mime-Base64
This level uses the cipher block chaining mode with the IDEA or Blowfish block cipher, and is extremely secure. It encodes the username and password into a single encrypted string based on a keyword stored in the oracletool.ini. This level requires the following Perl modules... These modules are available at CPAN (http://www.cpan.org), but not all are on the Activestate site when I checked prior to this release. This is the only reason that Level 1 encryption exists at all. Oracletool defaults to the IDEA method. If you would like to use Blowfish, set the ENCRYPTION_METHOD parameter accordingly in the oracletool.ini.
1) Digest-MD5
2) Crypt-IDEA or Crypt-Blowfish
3) Crypt-CBC
If you choose to use these security features, simply install the required Perl modules. Oracletool will see the modules and start using them for each subsequent cookie that is created. See your browser documentation if you want to remove the cookies currently on your hard drive.
__END__