search

darold / ora2pg

Ora2Pg is a free tool used to migrate an Oracle database to a PostgreSQL compatible schema. It connects your Oracle database, scan it automatically and extracts its structure or data, it then generates SQL scripts that you can load into PostgreSQL.
http://www.ora2pg.com/
GNU General Public License v3.0
991 stars 342 forks source link

Fix for Issue #1381 #1621

Closed simonpane closed 1 year ago

simonpane commented 1 year ago

Adding additional functionality related to an old Issue #1381.

Sometimes it is desirable to keep the Oracle password out of plain text OS files including the Ora2Pg configuration file. Instead it is a little safer to keep the Oracle credentials in an Oracle Wallet file and then make a password-less connection based on having that Wallet file. Essentially a little bit similar to using ssh with a private key file.

This version of an Oracle Wallet file is called a Secure External Password Store or SEPS. Oracle documentation reference: https://docs.oracle.com/en/database/oracle/oracle-database/19/dbseg/configuring-authentication.html#GUID-803496D2-19C7-4F02-94EC-C13EDD8FB17B

With an Oracle Wallet/SEPS connection, no username or password is required. For example with SQL Plus the connection simply becomes:

sqlplus /@ORCL

In this example, a specific username and password was used. It was just not provided at the command line and instead it was obtained from the Oracle Wallet file.

With Perl, the functionality is similar - the username and password is removed so the database handle connection line would change from this:

my $dbh = DBI->connect($self->{oracle_dsn}, $self->{oracle_user}, $self->{oracle_pwd},

To this:

my $dbh = DBI->connect($self->{oracle_dsn}, '', '',

But I don't want to change any existing functionality in any way.

So to make this solution dynamic and to not break any other functionality, and allowing it to still prompt for a username and password if one is not included in the configuration file (as it currently does), this PR includes new capabilities.

Specifically: if the the ORACLE_USER and ORACLE_PWD fields are set to the special string of __SEPS__ then the database handle connection changes.

Example in the configuration file:

$ grep '^ORACLE_USER\|ORACLE_PWD' config/ora2pg.conf
ORACLE_USER      __SEPS__
ORACLE_PWD       __SEPS__

And the Oracle.pm logic changes to make the database username and password fields dynamic in the database handle connection:

       my $dbh = DBI->connect($self->{oracle_dsn},
               ( $self->{oracle_user} eq "__SEPS__" ? "" : $self->{oracle_user} ),
               ( $self->{oracle_pwd}  eq "__SEPS__" ? "" : $self->{oracle_pwd}  ),

Meaning: if the ORACLE_USER/PWD field is __SEPS__, then use an empty string for the connection. Otherwise, use the set value.

Documentation updated accordingly in this PR.

darold commented 1 year ago

Thanks Simon!