We need to have a solution to securely pass the
password to the program to make it automated. Here are a few ways.
One option is to create an environmental
variable, say, USERPASS passed to script as
This is still vulnerable to attack via the /usr/ucb/ps
command. The problem with environmental variables is they are
visible to all users on the system, without the user having to hack
into the environment. Executing
shows all the environmental variables and the
values used by all the users currently logged in to the system,
including root's. The /usr/ucb/ps call is present for compatibility
This variable may be set in a file that could
be hidden. It's simple but not very secure. Another option is to
create a file of passwords named .passlist . Note the period at the
beginning. This makes the file invisible in a routine examination.
This file has a permission set as 600, i.e. no privileges to anyone
other than the owner. Here are the contents of the file
and so on. As you see, it has the usernames and
passwords of all users. Next we will create a shell script to use
this file named .retrieve_password.sh. Note the period at the
beginning of the file. It makes it invisible in a regular ls –l
command, too. Here is how the script looks.
$HOME/.passlist | cut –d " " –f2
When the user issues sqlplus, he or she would
| sqlplus –s judy @report
The program will retrieve the password and feed
it to the sqlplus executable. An execution of ps –aef on the UNIX
prompt will not show the password.
Tip: Make sure that no one types
any kind of password in the command line. If passwords need to be
passed from the command line, use a secured file to store the
password and then use redirection to feed it to the program.
User Access Control
Once the machine is physically secured and the
firewalls are protected, the next security vulnerability comes in
the area of database access. The users need access to the database
to perform their job functions.
When users wish to connect, the database makes
sure that they are indeed authorized to access, a process known as
Authentication. This can occur in several ways – the users could be
defined as users in the database and then authenticated, or they may
have been authenticated elsewhere and their credentials are passed
on to the database as valid.
There are two ways the database authenticates
By OS authentication
Let us discuss how they are different.
A user is created in the database with a
password as in the following SQL command.
create user judy
This creates a user in the database,
specifically in the data dictionary table USER$ owned by the user
SYS. When the user wishes to connect to the database, he or she can
do that with the user ID and the password as defined. The method of
connection will vary from tool to tool. For instance in SQL*Plus,
the user would connect as
This is the most common use of authentication.
Download your Oracle scripts now:
definitive Oracle Script collection for every Oracle professional DBA