O'Reilly logo

Oracle Distributed Systems by Charles Dye

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Authentication Methods

One of the DBA’sobjectives in a distributed environment is to provide easy database access to valid users, while thwarting (or at least discouraging) unauthorized access to the database and network traffic to it (which may contain sensitive information such as passwords). There are three distinct means of authenticating users of an Oracle database, corresponding to three different types of accounts:

Database authentication

This method corresponds to accounts made with the CREATE USER command. Users must provide a valid username/password, which the database validates with information stored in the data dictionary.

Operating system authentication

These are Oracle accounts that correspond to operating system accounts. If a user can log in to the operating system, she is permitted to log in to the database. We often refer to these accounts as OPS$ (pronounced “ops dollar”) because the corresponding database usernames are in the form OPS$os_username by default.

External authentication

These are accounts that are validated by some external means, such as a fingerprint scanner or a network authentication mechanism such as Kerberos.

The sections that follow examine the considerations for each of these methods in a distributed environment and discuss implementation options.

Database Authentication

Database authenticated accounts are the type with which we are most familiar. Every Oracle database has at least two such accounts: SYS and SYSTEM. In an ideal world, you can also create an account for each user, just as the administrators of your operating system(s) do: one individual, one account in the database(s) he needs to use. This seems reasonably straightforward, but some perils do exist, for example, compromised passwords.

Most multiuser operating systems allow users to report on all of the processes running on a machine; typically, this listing displays a process ID, username, program name, and other information about CPU utilization and so on. Sometimes the listing shows the arguments that a user passed to a program. If users passed their username and password, that information may be available to one and all. The SVR4 variant of the ps command, found on operating systems such as Solaris, is a classic example. Here is how you can obtain passwords on a Solaris machine:

cdye@socrates% ps -ef | grep sql
cdye 12174 10822  0 16:03:23 pts/8  0:00 grep sql
cdye 12168 10901  0 16:01:00 pts/9  0:00 sqlplus system/twinkletoes@hr_prod

So, the system password for hr_prod is “twinkletoes.” This problem has fueled considerable dialogue in Oracle user groups, and the consensus is that you can choose one of three remedies for it, described the following sections.

Write a wrapper command around sqlplus

In this way, the arguments are not displayed. Oracle Support has written (but does not support) a program called hide.c which masks arguments from the ps command. The program is described in Oracle Bulletin 1009091.6, which is included here:

Oracle Corporate Support
	                                 Problem Repository

	1. Prob# 1009091.6  HOW DO YOU HIDE USERNAME/PASSWORD IN PS?
	2. Soln# 2057042.6  USE THE HIDE.C PROGRAM

	1. Prob# 1009091.6  HOW DO YOU HIDE USERNAME/PASSWORD IN PS?

	Problem ID          : 1009091.6
	Affected Platforms  : NCR Unix SVR4
	Affected Products   : SQL*Forms
	Affected Components : IAD V03.00.XX
	Affected Oracle Vsn : V07.00.13.XX

	Summary:
	HOW DO YOU HIDE USERNAME/PASSWORD IN PS?

	+=+

	Problem Description:
	====================

	ps shows username/password.  How can I keep this from happening?

	Search words:
	hide.c hide

	+==+

	Diagnostics and References:

	 * {5038.6,Y,100}     PS SHOWS USERID AND PASSWORD

	2. Soln# 2057042.6  USE THE HIDE.C PROGRAM

	Solution ID         : 2057042.6
	For Problem         : 1009091.6
	Affected Platforms  : NCR Unix SVR4
	Affected Products   : SQL*Forms
	Affected Components : IAD V03.00.XX
	Affected Oracle Vsn : V07.00.13.XX

	Summary:
	USE THE HIDE.C PROGRAM

	+=+

	Solution Description:
	====================

	Use the program hide.c:
/*-------------------------------------------------------------------------+
|      Can be used as a program prefix: hide program arguments             |
|      or as a symbolic link.  If this program is not invoked as hide, it  |
|      will hide its arguments and invoke the program name.hide            |
|      The best way to use this is to rename your critical programs to     |
|      program.hide, and create a symbolic link program to hide.           |
|      mv sqlplus sqlplus.hide; ln -s hide sqlplus                         |
|      Thus when sqlplus is invoked, its arguments will be hidden          |
|                                                                          |
|   NOTES                                                                  |
|                                                                          |
|   This program works by padding 3000 '/' chars in argv[0].  This fools   |
|   all known ps's.  This will reduce the argument capacity of your        |
|   program by 3000 chars.  A good enhancement would be to reduce the      |
|   padding if needed so that no arguments are lost - would require a      |
|   method of determining the max argument size on the system.  Some       |
|   system's provide the E2BIG error on exec.                              |
|   There is some performace penalty for using this program, but it is     |
|   minimal because this program is so small - the biggest cost is the     |
|   extra exec required to get this program started.                       |
| HISTORY                                                                  |
|       09/17/92  D Beusee     Fixed to compile on any system              |
+------------------------------------------------------------------------*/
/*
* $Header: /work/isbns/1565924320/safarixml/RCS/ch04.xml,v 1.12 2004/04/16 19:36:09 chodacki Exp $
*
* $Log: ch04.xml,v $
* Revision 1.12  2004/04/16 19:36:09  chodacki
* sfcleanup fixes
*
* Revision 1.11  2002/09/04 21:18:23  chodacki
* R3
*
* Revision 1.10  2002/08/27 21:36:03  chodacki
* R3
*
* Revision 1.9  2001/08/07 21:49:45  chodacki
* notes have role=ORA attribute
*
* Revision 1.8  2000/11/10 19:27:13  jliggett
* final prep for bvd
*
* Revision 1.7  2000/10/26 20:07:50  jliggett
* minor edits
*
* Revision 1.6  2000/09/05 21:06:55  jliggett
* renumbered
*
* Revision 1.5  2000/07/18 19:18:44  jliggett
* added number attribute
*
* Revision 1.4  2000/06/19 14:56:21  jliggett
* final checklist
*
* Revision 1.3  2000/05/11 17:57:53  jliggett
* entered proof edits
*
* Revision 1.2  2000/05/04 16:35:00  jliggett
* validation
*
* Revision 1.1  2000/04/26 19:14:02  bsalter
* Initial revision
*
* Revision 1.6  1992/09/22  22:37:17  dbeusee
* Added exit(1) when cannot execvp the program.
*
* Revision 1.5  1992/09/22  11:28:44  dbeusee
* Some BSD systems have memset(), so add a #define memset MEMSET to fix
* compilation errors (like on ultrix).
*
* Revision 1.4  1992/09/22  06:34:57  dbeusee
* BSD systems need memset routine.
*
* Revision 1.3  1992/09/22  06:05:13  dbeusee
* Set JUNK_CHAR to ' ' but force last junk char to '/'. This looks prettier
* when doing 'ps'.  Also do not show full path of the program.  Also do not
* show .hide if prog is a symlink to hide.
*
* Revision 1.2  1992/09/22  05:52:26  dbeusee
* If hide could not execvp the program, give an error message.
* if hide was invoked with a full path (e.g. /usr/local/bin/hide),
* do not try to invoke PATH/hide.hide.
*
*
*/
	#include <stdio.h>
	#ifdef SYS5
	#include <string.h>
	#else
	#include <strings.h>
	#define strrchr rindex
	#define memset MEMSET /* some BSD systems have a memset() */
	char *memset();
	#endif
	#define JUNK_SIZE 3000
	#define JUNK_CHAR ' '
	char arg0buf[4096];
	char progbuf[4096];
	char errbuf[4096];
	int main(argc, argv)
	int argc;
	char *argv[];
	{
	    char *name, *base;
	    int firstarg;
	    if (!(name = strrchr(argv[0], '/')))
	        name = argv[0];
	    else
	        name ++; /* get past '/' */
	    firstarg = (!strcmp(name, "hide")) ? 1 : 0;
	    if (firstarg && (argc == 1))
	    {
	        fprintf(stderr, "Usage: hide program arguments\n");
	        fprintf(stderr, "   ie: hide sqlplus username/password\n");
	        fprintf(stderr, "if hide is not named hide, \
	it will execute name.hide (useful as a symbolic link)\n");
	        exit(1);
	    }
	 /* Build program name.  If symbolic link mode, use argv[0] || .hide */
	    strcpy(progbuf, argv[firstarg]);
	    if (!(base = strrchr(argv[firstarg], '/')))
	        base = argv[firstarg];
	    else
	        base ++; /* get past '/' */
	    if (!firstarg) strcat(progbuf, ".hide");
	    /* Build arg0 buffer.  First, fill it with junk */
	    memset((void *)arg0buf, JUNK_CHAR, JUNK_SIZE);
	    arg0buf[JUNK_SIZE-1] = '/'; /* set last char to '/' */
	 /* Prepend real program name - so ps can see what prog is running */
	    strncpy(arg0buf, base, strlen(base));
	 /* Append real program name - so prog can see what prog is running */
	    strcpy(arg0buf + JUNK_SIZE, argv[firstarg]);
	    /* Assign new arg0 buffer to the argv array */
	    argv[firstarg] = arg0buf;
	    /* Start the new program with the shifted arguments */
	    execvp(progbuf, argv + firstarg);
	    sprintf(errbuf, "Could not execvp '%s'", progbuf);
	    perror(errbuf);
	    exit(1);
	}
	#ifndef SYS5
	char *
	memset(s, c, n)
	        register char *s;
	        register c, n;
	{
	        register char *p = s;
	        while (n-- > 0)
	                *s++ = c;
	        return (p);
	}
	#endif /* ifndef SYS5 */

DISCLAIMER:
The hide.c code is not supported by Oracle. It is provided as a courtesy, 
as a workaround for SVR4 machines. BSD already hides the ps arguments.

Use operating system authenticated (OPS$) accounts

These accounts do not require a username or password:

cdye@socrates% sqlplus /@hr_prod
SQL*Plus: Release 8.0.4.0.0 - Production on Mon Dec 28 21:43:57 1998

(c) Copyright 1997 Oracle Corporation.  All rights reserved.

Connected to:
Oracle8 Enterprise Edition Release 8.0.4.1.0 - Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 - Production

SQL> show user
user is "OPS$CDYE"
SQL>

Those who attempt to obtain a password for these accounts will be disappointed, as you can see here:

cdye@socrates% ps -ef | grep sqlp
   cdye 12214 10822  1 18:14:05 pts/8    0:00 sqlplus /@hr_prod
    cdye 12216 10901  0 18:14:22 pts/9    0:00 grep sqlp

Don’t invoke programs with username and password on command line

Instruct users not to enter their usernames and passwords on the command line. Let the program prompt for the password instead:

cdye@socrates% sqlplus cdye@hr_prod

SQL*Plus: Release 8.0.4.0.0 - Production on Mon Dec 28 21:49:14 1998

(c) Copyright 1997 Oracle Corporation.  All rights reserved.


Connected to:
Oracle8 Enterprise Edition Release 8.0.4.1.0 - Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 - Production

SQL> show user
user is "CDYE"
SQL>

Personally, I have not been overly successful with enforcing this approach.

Even if you use an operating system that does not display program arguments when processes are listed (such as the VMS show system command), passwords may still be available in network trace files.

Operating System Authentication

As mentioned earlier, you can use operating system authenticated accounts to avoid the issues of compromised database passwords. In effect, OPS$ accounts do not have passwords; their encrypted version, stored in the data dictionary view DBA_USERS, is EXTERNAL:

system@dc18 SQL>  SELECT username, password
  2  FROM dba_users
  3  WHERE username like 'OPS$%'
  4  /

Username          Password
---------------   ------------------
OPS$AKALIDIN      EXTERNAL
OPS$AKAPO         EXTERNAL
OPS$BONO          EXTERNAL
OPS$CDYE          EXTERNAL
OPS$CHATSINT      EXTERNAL
OPS$CHERNOVI      EXTERNAL
OPS$CKER          EXTERNAL
OPS$DEASLEY       EXTERNAL
OPS$DWEB          EXTERNAL
OPS$EDD           EXTERNAL
OPS$GKRISHNA      EXTERNAL
OPS$GWANG         EXTERNAL
OPS$IASAAD        EXTERNAL
OPS$IBALKHI       EXTERNAL
OPS$IHAB          EXTERNAL

Note

You can set the password for OPS$ accounts to whatever value you like, and they will still work.

One argument for using OPS$ accounts is that database passwords are no longer an issue: they cannot be stolen or compromised. Another reason is that these accounts are generally much more convenient for users—one less password to remember and, of course, less typing. OPS$ accounts also allow a centralized approach to account administration. Finally, auditing is simplified because trace files and audit trails containing Oracle user IDs are easy to map to operating system accounts.

If you decide to use OPS$ accounts, you are in effect telling your database that if a user can log in to the operating system successfully, then she should be able to log in to the database, too, connecting to the Oracle account corresponding to her operating system account. The Unix account cdye, for example, connects to the Oracle account OPS$CDYE:

SQL> CREATE USER ops$cdye
2    IDENTIFIED EXTERNALLY;

User created.

SQL> SELECT username, password
  2  FROM dba_users
  3  WHERE username = 'OPS$CDYE';

Username      PASSWORD
------------- ------------------------------
OPS$CDYE      EXTERNAL

If you keep the default encrypted password EXTERNAL for these accounts, nobody else will be able to use the OPS$ account because it is not possible to supply a password that encrypts to the string EXTERNAL. Oracle identifies users, but the operating system authenticates them.

Creating OPS$ accounts

Besides creating the database and operating system accounts themselves, there are a couple of other steps required to configure OPS$ accounts. Table 4.1 describes the relevant initialization parameters.

Table 4-1. Initialization Parameters Associated with OPS$ Logins

Parameter Name

Default Value

Description

OS_AUTHENT_PREFIX

OPS$

This is the string prepended to the name of the operating system account to form the database account.

REMOTE_OS_AUTHENT

FALSE

If TRUE, then the database will accept users who have been validated by a machine other than the one on which the database is running.

You must restart the database in order for changes to these values to take effect.

There may be additional requirements depending on your database’s platform, as shown in Table 4.2.

Table 4-2. Operating-System Specific Requirements for Using OPS$ Logins

Operating System

Remarks

Unix

No additional requirements.

Microsoft NT

Operating system must exist on the NT server on which database resides; it must either share a directory from NT server to clients or use named pipes. NT clients must run 32-bit versions of Oracle client software (e.g., Forms), and the applications themselves must have been compiled with the 32-bit versions.

NetWare

For a secure OPS$ account with SQL*Net 2, the NetWare user must also be associated with an Oracle user by using the Oracle Snap-In for NetWare Administrator utility. This requires NetWare 4.1 or higher and the installation of Oracle’s Novel Directory Service Authentication Adapter at the server and client.

Once you have configured your system, users can connect to the database using a connect string of the form:

/@sqlnet_alias

Note that there is no username or password.

Note

Not all client tools can take advantage of OPS$ logins. For example, the login screen for Designer 2000 does not accept “/” as a username.

The assumed risks of OPS$ accounts

If you set the initialization parameter REMOTE_OS_AUTHENT to TRUE, you are instructing your database to trust the authentication methods of every client on your network. As a general rule, your clients are not trustworthy. Why not? Because some operating systems permit users to masquerade as whomever they wish. Clients running Windows 3.x can set the CONFIG.ORA file parameter USERNAME to identify themselves to a remote Oracle database, while Windows 95 users can set the following registry subkey:

My Computer\HKEY_LOCAL_MACHINE\System\CurrentControlSet\control\CurrentUser

Because of these security weaknesses, you should consider OPS$ accounts to be publicly available if PCs have access to your network and you have set REMOTE_OS_AUTHENT to TRUE. And even if PCs are not on your network, if people have physical access to a machine running most other operating systems, they can become whomever they want on that machine. Remember that setting REMOTE_OS_AUTHENT to TRUE means that you accept the authentication methods of all clients.

External Authentication

Oracle’s advanced networking option include interfaces (known as adapters) to a variety of third-party security services for authenticating users. You can configure these services so that users can use a single password to connect to any database on your network. The single sign-on architecture works by storing username and password information in a database or file system residing on a single server, called the authentication server. Oracle currently includes adapters for the following authentication services:

  • Kerberos

  • ICL Access Manager/SESAME

  • CyberSAFE Challenger

  • Bull ISM

  • SecurID

  • DCE Security Service (GSSAPI)

  • Banyan

  • Biometric (Identix)

The authentication server acts as an intermediary between client computers and database servers, as depicted in Figure 4.1.

Authentication server

Figure 4-1. Authentication server

The sequence of events is as follows:

  1. When a user on the client machine initiates a database connection, the client requests authentication credentials from the authentication server. This information is typically in the form of an encrypted key.

  2. The authentication server verifies the client and sends the required credentials back.

  3. The client makes a connection request to the database server, using the credentials obtained from the authentication server.

  4. The database server sends the credentials to the authentication server for validation.

  5. The authentication server sends verification back to the database server, which then accepts the connection request.

This architecture offers several advantages over conventional database authentication or OPS$ accounts:

  • The authentication server on which passwords reside is under centralized administration, and access can (and should) be extremely limited. No interactive logins should be permitted.

  • Passwords never travel over the network; instead, they are used as a key to encrypt and decrypt information during the login process.

  • Users can use the same password for every database they access, with little risk that this password can be compromised.

  • Besides associating passwords with users, you can associate usernames with client machines so that a given user can only connect from a given client.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required