CODEWITHSUNDEEP
oracle-databasesql-loadercontrol-m
alhambra
alhambra

Reputation: 13

Control-M: Importing Data (.csv) to OracleDB using SQLLDR without exposing credentials

So, in the Control-M for Database, as far as I know. It purely run queries/statements that is valid as SQL statement. However, the one that I am using which is SQLLDR is an independent cli tool for import the data(.csv) to a oracledb table.

So, I have already done importing data(.csv) to my oracle database(table). However, in the OS Command (using sqlldr) my credentials are exposed, in the OS command it looks like this.

"sqlldr username/password@smthng"

Is there any way to implement this import (.csv data) job successfully using the Control-M for Database or to an OS Command (using sqlldr) without exposing the credentials?

ps: I am currently just learning. So, the environment is also for testing, however I want to practice the best practices. I am open to any other ideas as well to improve my current method.

Upvotes: 0

Views: 99

Answers (3)

Connor McDonald
Connor McDonald

Reputation: 11586

Other options. One most platforms the password can be taken from standard input, so you can do:

echo your_password | sqlldr userid=username@db ....

A more robust way is to use a wallet to store the credential, which then makes your sqlldr command like:

sqlldr userid=/@db ....

Examples of creating a credential wallet are at

https://connor-mcdonald.com/2015/09/21/connection-shortcuts-with-a-wallet/

Upvotes: 0

rand'Chris
rand'Chris

Reputation: 1330

According to the documentation if you omit the password it will prompt you for it. This way it won't be visible from the command line and you don't have to store it in a file. Does that work for your application?

Upvotes: 0

Paul W
Paul W

Reputation: 11264

I can't speak for Control-M, but with SQL*Loader you have the option of putting any command line arguments into a parameter file. You can place your credentials there so they don't show up on the command line and aren't visible to anybody logged into the same host doing a ps:

myparam.par contents:

userid=myuser/mypass@"myTNSstring"

mycontrol.ctl contents:

LOAD APPEND ... [whatever]

command line:

sqlldr parfile=myparam.par control=mycontrol.ctl

You can reuse the same parameter file for multiple jobs, and vary only the control file for each job. Just be sure to make the parameter file readable only by owner (e.g., chmod 600 in Unix) so you don't expose the password to anyone not logged in with your credentials.

Upvotes: 0

Related Questions