Chacham's Journal: Chronicle: Oracle, sqlldr, and security.

I'm a fan of Oracle. I think the DB works very well, and there is a focus on being "correct", and not just "what works". SQL Server is for programmers, Oracle is for DBAs.

The question of security is raised. Is Oracle secure? Well, probably as much as any other DB, and they definitely have embarrassing security fixes.

So, we're trying to load data via SQL Loader, which requires a username and password for login. All is fine and dandy, until we wrap it up for production. The username/password pair must be read in from a file, decrypted in memory, and passed to the program. OK, seems easy enough.

Sqlldr accepts the username/password pair in one of three ways, on the command line, in a parameter file, or interactively. Although the database can be stored in an environment variable, the username and password cannot. On the command line is not an option, as anyone can do a ps -ef and see it. Putting it in a parameter file means writing it unencrypted to disk, so that's also a big no-no. So try a named pipe? Testing shows sqlldr won't read a named pipe for the parameter file. Even though it does read it for the control file. Go figure.

That leaves us with the interactive approach. That would mean to create a here-document. But again, that would put the password unencrypted on disk. Until an NW poster gave me the obvious answer. Use a here-document that uses an environment variable. That way, the script can decrypt the password and store it in memory, and the here document will grab it. In memory, only root (besides the current user) can check out the environment. And that works well.

It does leave me wondering though. Oracle doesn't allow an easy secure way to pass passwords to its utilities. The DB may be secure, but forget the tools. And, SQL*Plus can only be run interactively. A quickie script to run just a statement or two would have to include a script-file with an "exit" at the end to do the same thing.

From a command line interface standpoint, i'd say DB2 takes the cake here.

It's really odd

[stoolpigeon]

that it seems that the care taken with the database itself doesn't seem to transfer over to all the tools surrounding the database. I can see why Qwest has done all right selling toad all these years.

Re:

[Chacham]

Though, i never liked toad. A few years ago, i seemed to have liked PL/SQL Developer [allroundautomations.nl], but this time, when i tried the trial version, it just didn't seem to "click".

So, i'm sticking with SQL*Plus in a DOS window, which can be quite good, once some scripts are learned and the appropriate system catalog views remembered.

Re:

[stoolpigeon]

I like sqlplus for certain things and I use sql developer a lot. I've never actually used toad myself, but a ton of people I work with use it.

Re:

[Chacham]

I've never actually used toad myself, but a ton of people I work with use it.

Probably coders as opposed to designers. Like Toad, coders care about getting it done, regardless of how.

I don't even remember *why* i feel that way about TOAD, other than it was clear to me a few years ago when i tried it.