Section 9.5. Configuring the PDBA Toolkit

9.5 Configuring the PDBA Toolkit

PDBA Toolkit configuration works the same way for both Unix and Win32. As we mentioned earlier, it's based upon a flat-file system.

From this point on, we'll refer to the file locations as PDBA_HOME/file_name.conf , regardless of platform.

We'll begin with the actual PDBA module and then continue with Connection Manager, the Password Server, and then the Password Client, each with its own configuration file, as shown in Figure 9-2.

9.5.1 PDBA Module Configuration

The PDBA module requires little configuration. It needs to know just two things:

Which mail server to use
Where it should say the emails are being sent from

The configuration file is PDBA_HOME/pdba.conf . The default contents are shown in Example 9-12.

Example 9-12. pdba.conf ” Default values

 package pdbaparms;    # who should mail be from?    # does not need to be a valid address    fromAddress =>  'oracle@jks.com',  );

Simply adjust the values to those for your own site. The mailServer parameter needs to be a valid mail server, but fromAddress can be a purely informational FROM: address. For example, if your domain is mydomain.com , you may want to simply set it to oracle@mydomain.com . Or you can set it to a real address, if you prefer, to make potential replies easier. For instance, you could change the two key lines like this:

 mailServer =>  'mail.mydomain.com',  ... fromAddress =>  'oracle@mydomain.com'  ,

When you're finished, check the file for syntactic correctness with the -cw switches:

 $ perl  -cw  pdba.conf

9.5.2 PDBA::CM Module Configuration

We've included the PDBA::CM (Connection Manager) module in the toolkit as an optional convenience, although we hope you'll want to set it up. The default PDBA_HOME/cm.conf is shown in Example 9-13.

Example 9-13. cm.conf ” CM configuration file

 package cmconf;       ORACLE_BASE => '/u02/app/oracle',       TNS_ADMIN => '/u02/app/oracle/product/8.1.7/network/admin'    },       ts99 => {       ORACLE_HOME => '/u02/app/oracle/product/8.1.7',       ORACLE_BASE => '/u02/app/oracle',       TNS_ADMIN => '/u02/app/oracle/product/8.1.7/network/admin'    }, );

New database connection attempts made via PDBA::CM check for this file as follows :

If it exists, the contents are checked for the target database.
If the database entry exists, cm.conf values are used to set ORACLE_HOME , ORACLE_BASE , TNS_ADMIN , and any other required environment variables .
If a database entry is not specified, the default values are used.

Setting the environment variables in this way means you get them right every time you connect to a target database ” and you don't have to remember them. This is useful for scripts running from a system scheduler. The usual method used to run an ordinary Perl script is to wrap it. The wrapper sets the environment and then executes the script. Example 9-14 may look familiar to cron users. When running a script via the Unix cron scheduler, you normally must explicitly set all Oracle environment variables in the script. This is because scripts that run via cron do not inherit the environment variables that are normally set when logged into an interactive Unix account.

Example 9-14. mybatch.sh ” Setting up a Perl script in a wrapper script

 #!/bin/ksh    # Set the environment export ORACLE_SID=ts01 export ORAENV_ASK=NO . /usr/local/bin/oraenv $ORACLE_SID    # Execute the script  mybatch.pl -database -username ayn -password rand

Using PDBA::CM 's configuration file eliminates the need for this kind of logic. You simply run the Perl script mybatch.pl directly via the system scheduler. For most situations, you set up cm.conf with just the default values and it works fine. You need only add specific database parameters as necessary. For Win32 users who need a simple default, edit the supplied cm.conf file to make it look like Example 9-15:

Example 9-15. cm.conf ” Basic CM configuration file

 package cmconf; use vars qw(%env); %env = (   default => {       ORACLE_BASE => 'c:/oracle',       TNS_ADMIN => 'c:/oracle/ora81/network/admin'    }, );

Note that by default PDBA::CM ignores the cm.conf file if ORACLE_HOME is set. You can override that behavior with the FORCE_CONFIG attribute. You can also tell PDBA::CM to look beyond PDBA_HOME for a configuration file via PATH and FILE :

 my $dbh = new PDBA::CM (     DATABASE => $db,     USERNAME => $username,     PASSWORD => $password,  FORCE_CONFIG=> 1,                  # Use the config file! :-)   PATH => '/u02/app/oracle/config  ',  FILE => 'oracle_cm.conf  ' );

9.5.3 Password Server Configuration

The password server configuration file, PDBA_HOME/pwd.conf , contains five data structures:

$port: Sets the TCP port for the password server
%pwd: Sets the passwords for the password server
%users: Sets up password server users
%encryption: Encrypts passwords
%instanceAuth: Sets up per-account authorization

We'll describe these in the following sections.

9.5.3.1 $port: Setting the TCP port for the password server

First you need to set the TCP port to be used by the password server. The setting in the file is currently 1579. You can change this to any other setting as follows:

 package pwd; use vars qw($port %pwd %instanceAuth %users %encryption );  $port=1579;

Ports < 1024 will require special OS or root permissions.

9.5.3.2 %pwd: Setting the passwords for password server

This is where the passwords for each account are specified by machine or database server name, Oracle instance name, and account name , in a manner similar to the tnsnames.ora file structure. Example 9-16 is taken directly from the pwd.conf file included in the PDBA distribution. It contains passwords for the sys and system users, for the databases ts98 and ts99 on the watson server, and for database ts01 on the sherlock server.

Example 9-16. pwd.conf

 %pwd = (         sherlock => {               ts01 => { system   => 'hoser',                  sys      => 'hosehead' }    },    watson => {       ts99 => { system   => 'wazzup',                  sys      =>  'wizard'  },          ts98 => { system   => 'whyn0t',                  sys      => 'bcuz' }    } );

This type of data structure is known as a hash of hashes (see Appendix A). The %pwd Perl hash contains a list of hash keys, in this case sherlock and watson . Each of these contains another Perl hash. Inside these hashes are the actual accounts and passwords. Here's what you need to do:

If you're ready to edit your pwd.conf file, go ahead and change the server names , accounts, and passwords to those appropriate for your site.
If you do edit the file, be sure to check it with perl -cw pwd.conf when done.

The method used to access this data may appear initially to be somewhat daunting, but scrutiny reveals that it's only a loop extracting servers, instances, usernames, and passwords, as Example 9-17 shows.

Example 9-17. pwd.pl ” Accessing data elements

 1   use PDBA::ConfigFile; 2   3   unless ( new PDBA::ConfigLoad( FILE => 'pwd.conf', PATH => './' ) )  4      die "could not load pwd.conf\n"; 5   } 6 7   $t=0; 8  for my $server ( keys %pwd::pwd ) {  9      print "\t" x $t, "server: $server\n"; 10     $t++; 11  for my $instance ( keys %{$pwd::pwd{$server}} ) {  12        print "\t" x $t, "instance: $instance\n"; 13        $t++; 14  for my $user ( keys %{$pwd::pwd{$server}->{$instance}} ) {  15  print "\t" x $t, "username: $user"  , 16  "password: $pwd::pwd{$server}->{$instance}{$user}\n";  17         } 18         $t--; 19     } 20     $t--; 21  }

Here's what is going on.

Line 8 extracts the server names from the %pwd hash via the built-in Perl function keys . (Recall how each server has its own hash inside %pwd .)
Line 11 extracts instances.
Line 14 uses %{} to de-reference yet another nested hash, this time for each server.
The account names and passwords are finally revealed in lines 15 and 16.

Fortunately, PDBA::PWD takes care of all these technical difficulties. The only place this password structure is used is within the PDBA::PWD module. Retrieving a single password in a real-life script is actually fairly simple, as you can see here:

 my $password = $pwd::pwd{$server}->{$instance}{$user};

9.5.3.3 %users: Setting up password server users

The password server is only available for clients specified in the %users hash. These require a password. In case you're thinking "Oh great, another password to remember, let's use P4SSW0RDF1V3 or something," keep in mind that this one greatly reduces the number of other passwords you need to remember. Attempts to retrieve a password without a correct username and password will return no data. There are no informational messages declaring that the password or username is incorrect. Simply change the content for your own users in pwd.conf as follows:

 %users = ( andyd => 'perlgeek',    # Needs to get out more! :-)            jkstill => 'getalife',  # Gotta turn off that computer! 8-)            scott => 'tiger' );

9.5.3.4 %encryption: Encrypting passwords

Most folks have become increasingly security-conscious lately, and rightly so. Sending clear text passwords over a network is now considered unacceptable practice, because the routing of TCP/IP packets over a multiply-redundant network makes it easy for unauthorized persons to compromise security. With that in mind, we designed the PDBA::PWD module to ensure that passwords are transmitted in encrypted form. To assist in the encryption, we've chosen the RC4 encryption algorithm available via the Crypt::RC4 Perl module. It is fast, easy to install, and available for both Unix and Win32 platforms. ^[9] All you need do is specify a string to use as the encryption key. This is done via the %encryption hash in PDBA/pwd.conf , shown in Example 9-18.

^[9] Once you're used to Crypt::RC4 , you may wish to modify our toolkit to gain even greater security with ever more secure Perl modules; see http://search.cpan.org/Catalog/Security.

Example 9-18. pwd.conf ” The %encryption hash

 %encryption = (    level => 'simple',       # don't change this    maxKeyLen => 56 );

There are three pieces of data in the %encryption hash, and the only one you need to be concerned with for now is key . Here are the requirements for key:

It needs to be a phrase and can be practically anything you want.
Nonsense key phrases are best ” anything that is difficult to guess.
The key is used to encrypt passwords sent over the network.
If you make the key longer than 56 characters , it will be truncated to a length of 56.
It can be shorter than 56 characters, but for best results make it at least 56.

You may recognize our own key as being from J.R.R.Tolkien's The Lord of The Rings . It is too predictable for an actual encryption key, but demonstrates that the key can be anything you like. Change the key, and then check the configuration file with perl -cw pwd.conf .

9.5.3.5 %instanceAuth: Setting up per-account authorization

The %instanceAuth hash provides an optional security feature. It can be used to limit which users are able to retrieve passwords for a particular account. If you have some sensitive accounts, you can limit access to them with %instanceAuth . Any accounts unspecified will be available to all authorized users. If the %instanceAuth structure is missing completely, all users found in %users have access to all passwords for all accounts.

Consider Example 9-19: it's a subset of the servers found within Example 9-16. In the %pwd hash, there are two servers, with three Oracle instances, and two accounts in each instance, sys and system . This is a total of six passwords, and we want to filter their access as follows:

You want to allow andyd and jkstill to have access to the system account on ts01 .
Only andyd is to have access to the sys account on ts01 .
Only scott and andyd are to have access to the sys account on ts99 .
All authorized users of the password server, as found in the %users hash, are to have access to the supplied accounts sys and system in the ts98 instance, as well as to the system account in ts99 .

The entries in %instanceAuth shown in Example 9-19 create the required filter. The only entries needed are for accounts where you wish to limit the users who can retrieve passwords.

Example 9-19. pwd.conf ” Using the %instanceAuth structure

 %instanceAuth = (        sherlock => {        ts01 => { system  =>  [ qw(andyd jkstill) ]  ,                  sys     =>  [qw(andyd)]  }     },        watson => { ts99 => {                 sys  =>  [qw( scott andyd )]  }     } );

Let's see what's going on here.

The lowest -level entry in the %instanceAuth structure is something called an "anonymous array" ” that is, it exists in Perl memory as an array, but it has no name. (You can read up on anonymous arrays in Appendix A.)
You can tell it's an anonymous array because the data is in square brackets [] , indicating an array, but there is no name associated with it.
If this level of security is unnecessary, just delete all of the data inside the %instanceAuth hash so it looks like this:
```
 %instanceAuth = (  ); 
```
You must keep %instanceAuth , though, even if it is undefined. If you delete it entirely, a warning will be raised when you check the file via the command perl -cw pwd.conf .

9.5.3.6 Securing pwd.conf

The PDBA/pwd.conf file is sensitive, so you need to set Fort Knox file permissions enabling only authorized users to read or edit it. On Unix, this is done via chmod :

  $ chmod 640 pwd.conf  $ ls -la pwd.conf -rw-r-----    1 oramon  dba   8508 Apr 14  07:27 pwd.conf

Setting the permission to 0640 allows users belonging to the DBA group to view the file, while only the file owner can edit it. Win32 security is different. Setting the proper permissions on Win32 is a point-and-click operation that works as follows:

Find the target file in Windows Explorer and right-click on it.
If you have network security on your system, you'll see a Security tab. Click on it.
The File Permissions dialog should appear, like the one in Figure 9-3. In this example, everyone on the network has access to PDBA_HOME/pwd.conf .
Allowing all users on the network to have read access should be avoided for a file containing passwords. You may want to highlight users or groups that should no longer have access to the file (such as Everyone ). Click on Remove so they no longer have access to this file.

Figure 9-3. Setting file security on Win32

9.5.3.7 Running the password server on Unix

Those on Unix are now ready to run the password server via the command line as follows:

 $ pwd.pl

That's all there is to it. The password server has landed. You can verify its operation via a ps command, using either the -fea or -aux switches, depending on your own Unix flavor:

 $ ps -fea  grep pwd  grep -v grep oramon   25771     1  0 08:12 ?        00:00:00 perl  /usr/bin/pwd.pl  $ ps -aux  grep pwd  grep -v grep oramon   25771  0.0  0.8  5668 4540 ?     S   08:12 0:00 perl  /usr/bin/pwd.pl

9.5.3.8 Running the password server on Win32

Running the password server on Win32 is slightly more involved. You need to install the pwd.pl script as a service. Thanks to the Win32::Daemon module, this is straightforward.

The first thing to do is to locate the script pwd_service.pl . This is used to install pwd.pl as a service or to remove it. If Perl is installed on C: , the path will be:
```
 C:\Perl\site\lib\PDBA\util\  pwd_service.pl  
```
Once the script has been located, you open a command prompt window to execute the script and install the password server service. The following command will install the service.
```
 C:\Perl\site\lib\PDBA\util\  pwd_service.pl -install  
```
This will automatically detect where Perl.exe and pwd.pl are located and use them to install the service. Example 9-20 reproduces the relevant script portion.

Example 9-20. pwd_service.pl ” Install password server service on Win32

 use File::Basename; use Getopt::Long; use Win32::Daemon;     my %optctl=(  );    my $perlExe = $EXECUTABLE_NAME; # build a path to pwd.pl my @dirs = File::Spec->splitdir($perlPath);  %Hash = ( name    =>  'Oracle_PWD_Server  ',  display =>  'Oracle_PWD_Server  ',  path    =>  $perlExe  ,           user    =>  '',   # Unnecessary, for this particular application.           pwd     =>  '',   # Unnecessary, for this particular application.  parameters => $pwdPath );  unless ( GetOptions(\%optctl, "install!", "remove!" ) ) {    usage(1); } if ( $optctl{remove} ) {     if( Win32::Daemon::DeleteService( $Hash{name} ) ) {       print "Successfully removed.\n";    } else {       print "Failed to remove service: " . GetError(  ) . "\n";    } } print "finished.\n";    sub GetError {     return( Win32::FormatMessage( Win32::Daemon::GetLastError(  ) ) ); }

If you should happen to have a nonstandard installation of Perl, you can use Table 9-11 (password server parameters) as a guide in editing the attributes of the %Hash data structure in pwd.pl .

Table 9-11. Parameters for installing the password server on Win32

Attribute	Description
name	The name of the service. We've set it to Oracle_PWD_Server.
display	The name to display in the Win32 Service Manager.
path	The full path to the Perl executable ” for example, C:\Perl\bin\perl.exe.
user	Who the user is to run as (unnecessary for this application).
pwd	Password for user (unnecessary for this application).
parameters	The full pathname to the pwd.pl script ” for example, C: \Perl\bin\pwd.pl.

9.5.3.9 Starting the service

Now all you need do is run Win32 Service Manager to start the service. There are two different paths, shown in Table 9-12, depending on which Win32 platform you're on.

Table 9-12. Installing the password server on Win32

Win32 version	Start menu instructions
NT 4.0	Start->Settings->Control Panel. Then double-click on the Services icon. Highlight the Oracle_PWD_Server service and click Start .
Windows 2000	Start->Programs->Administrative Tools->Services. Highlight the Oracle_PWD_Server , right-click on it, and press Start .

9.5.4 Password Client Configuration

We're in the home stretch now. All that's left is to set up the password client pwc.pl and give it a whirl. As with the password server, the client program uses a configuration file. Unlike the server, the configuration file is optional. We hope you'll want to use it though, as it makes the client program considerably easier to use. Bring up PDBA_HOME/pwc.conf in your favorite editor and take a look at it. The contents of the file, as it appears in the PDBA distribution, are shown in Example 9-21.

Example 9-21. pwc.conf ” Password client configuration

  package pwc;  use vars qw(%optctl); %optctl = ( host => 'sherlock',             port => 1579,             machine => 'watson',             instance => 'ts98',             username => 'sys',             my_username => 'scott',             my_password => 'tiger',             key => 'One Ring to bring them all and in the darkness bind them');

All of the command-line options except -conf can be specified in this file, thus avoiding long command-line entries and the use of clear text passwords (which is what we were trying to avoid in the first place). It assumes that the configuration file name is PDBA_HOME/pwc.conf , unless another name is specified directly on the command line with the -conf filename option. Typing pwc.pl -help on the command line displays all of the options as a useful reminder of what you'll need to input. Table 9-13 summarizes these options.

Table 9-13. Command-line options for pwc.pl

Argument	Value
conf	Tells pwc.pl the name of the configuration file to use.
host	Win32 or Unix server on which the password server is running.
port	Host port being used by the password server. This will be the same value specified for port , in PDBA_HOME/pwd.conf .
machine	Name of the physical host the database instance is on.
instance	Database instance.
username	Username for which you are requesting the password.
my_username	Your password server username.
my_password	Your password server password.
key	Key used to encrypt/decrypt passwords sent over the network. Your password server authentication is encrypted as well. Include this value in quotes if spaces or special characters are included.

Note the following:

Any options not specified in this file will need to be included on the command line.
Arguments specified on the command line override those in the configuration file.

One common approach is to create a configuration file specifying the password server and port, your username and password, and the key used to encrypt data across the network. With those options present in the configuration file, you would only need to specify the configuration file, database server machine, database instance, and account name on the command line. For example, your command line would look something like this when you're retrieving the password for account system in instance vdr on database server elfenwood :

 $ pwc.pl -machine elfenwood -instance VDR -username system -conf pwc.conf

To connect to a different host's password server, you would modify the command line as follows:

 $ pwc.pl  -host mycroft  -machine elfenwood -instance VDR \    -username system -conf pwc.conf

Example 9-22 is an example of connecting to an alternate password server. In this case the password server on Unix server sherlock was unavailable, so a connection was made to the alternate password server on the Win32 server mycroft .

Example 9-22. Connecting to an alternate password server

 %oramon > pwc.pl %oramon >

Let's see what's happening in Example 9-22.

Notice in the example that the password whyn0t was returned for the sys user in database instance ts99 on the database server watson . You might find the password, originally found in Example 9-16, a little hard to pick out, because neither a line feed nor a carriage return is displayed, just the password. (Obviously, you can adapt your client scripts so passwords are not revealed in this way; we've done it only to demonstrate the concept.)
You can copy the configuration file to any file name you like, and use it that way. This would be useful if you wanted to keep different configuration files for each database server or to organize them by username.
Be sure that you avoid changing the password client package line near the top of the package, package pwc; , as that line is required for this configuration file to work properly.
Secure PDBA_HOME/pwc.conf to protect it from unauthorized users, as we showed with pwd.conf earlier.

The pwc.pl script is useful in demonstrating how to use the password server in your own scripts. It is also useful as a standalone script for retrieving passwords at the command line. On Unix systems, it may even be used to retrieve passwords and use them directly as input. This example demonstrates its use in logging in to SQL*Plus without any need to type the password:

 sqlplus system/$(pwc.pl -machine sherlock -database ts01 -username system)

This uses the Korn shell's $( ) subshell mechanism, returning the output of pwc.pl to the current shell.

Alas, we do not know of an equivalent subshell mechanism on the Win32 platform.

9.5.4.1 Using PDBA::PWC in your own Perl scripts

The password client module is available for use in your own Perl scripts as well. Here are the basic pieces you'll need to include in order to connect to the password server:

 use PDBA::PWC;    my $client = new PDBA::PWC(    host => $remote_host,    port => $remote_port ); $client->authenticate(     username => $myusername,    password => $mypassword,    key => $key,    debug => $optctl{debug} ); my $password = $client->getPassword(     machine => $machine,    instance => $instance,    username => $username,    key => $key );

This completes the toolkit installation. In the following chapters, we'll see what it can do for us.