Re: ops$ logins from both secure and nonsecure clients with same username

Bill Manry - Oracle Corporation wrote:
> The setting of OS_AUTHENT_PREFIX (to "" or "ops$") only determines
> how the Oracle userid is formed from whatever Oracle thinks the
> client's OS userid is. It sounds like you are asking for Oracle's
> authentication behavior to vary depending on the type of system on
> which the client runs. That isn't possible in the current product
> as far as I know, but you might want to investigate Oracle Secure
> Network Services to see if it has facilities that might meet your
> needs.
>
> /b
> --
> Bill Manry - Mainframe and Integration Technologies - Oracle Corporation
> These are my opinions, not necessarily Oracle's.

Actually, if you set OS_AUTHENT_PREFIX to anything other than "OPS$", you have to set the account to be IDENTIFIED EXTERNALLY. This will prevent anyone from a client machine from logging into that account (unless you have REMOTE_OS_AUTHENT=TRUE, which is BAD!).

For example, if you set OS_AUTHENT_PREFIX="", then created a user scott like this:

	create user scott identified by tiger;
	grant connect to scott; 

You can connect using scott/tiger from anywhere. However, you can not connect to the oracle account scott from the server account scott with just a /.

Username: SCOTT
Password:

$ sqlplus /

SQL*Plus: Release 3.1.3.1.3 - Production on Fri Aug 2 07:14:23 1996 Copyright (c) Oracle Corporation 1979, 1994. All rights reserved. ERROR: ORA-01017: invalid username/password; logon denied Enter user-name:

To enable proxy logins in this case, you must set the account to be identified externally.

        alter user scott identified externally;

Now, the 'sqlplus /' command will work. However, 'sqlplus scott/tiger' will not, either from the server or from a client.

The only way to 'have your cake and eat it too...' is to use 'OPS$' as OS_AUTHENT_PREFIX. You then create the oracle account with the prefix.

	create user ops$scott identified by tiger;
	grant connect to ops$scott;

Now, you can log into ops$scott with 'sqlplus /' from the server, or using 'sqlplus ops$scott/tiger' from a client.

Note: This restrictive behavior was introduced into Oracle7 after its

       original release, maybe in 7.0.16 or in 7.1.  If you have
       the original 7.0.14 release, you can actually set
       OS_AUTHENT_PREFIX="" and allow both proxy logins and password
       logins.

-- 
Bob Swisshelm
Eli Lilly and Company
swisshelm_at_lilly.com