Re: oracle authentication from windows

Arup,

thanks for your detailed feedback

Comments inline

>
>(1) The use of remote_os_authent is false, then it simply means that users
>from another machine can't log in using OPS$ accounts. Needless to say, this
>reduces the security and must be weighed a bit more carefully than usual.
>

Here I think you meant to say remote_os_authent =TRUE (not false) is a security
risk to ponder carefully

>(2) Should you ever have accounts externally identified? I would say, yes,
>as long as you know the privileges are not sweeping.

Agreed, particularly useful to avoid passwords in batch jobs

>(3) Having said that, the next question is - should you leave the value of
>os_authent_prefix at NULL (""). I say absolutely not. Here is the probelm
>with an externally idefoaable user with os_authent_prefix set to null. A
>user OPS$SCOTT identified externally is no the same as SCOTT, if the
>os_authent_prefix is set to OPS$. If that parameter is null, then database
>user SCOTT is the same as the OS user SCOTT. If someone creates a user in
>the OS called "system" and os_authent_prefix is null, what is the database
>user it connects to? SYSTEM!!!! Would you want that? Of couse not. That is
>why you never, ever, want to set the os_authent_prefix to null. Always set
>it to a value. You may have a different value such as UNSAFE$, if you wish,
>to fool the hackers.
>

That's where I don't follow you :
It connects to SYSTEM ONLY if I have declared SYSTEM identified EXTERNALLY in the database.
So the security problem resides in the choice of EXTERNALLY identified users and we are back to (2) above.

>(4) Thank you for the example. I guess I was not very clear on that issue. I
>think the original question from Bulbul was how to prevent the OPS$ useers
>from connecting to the database using user id, isn't that so, Bulbul? The
>solution is rather simple. Just use either of the two forms of the command
>"create user OPS$SCOTT identified externally" or create user SCOTT
>identified by tiger", not "create user OPS$SCOTT identified by tiger". And
>always set your os_authent_prefix to OPS$ or some non-null value. This way
>the accounts will be authenticated in only one way, not both.
>
>Another trick I use to foll th ehackers using the brute force attack is to
>use the construct
>
>alter user ops$scott identified by values 'somestringinascii'
>

In fact the syntax "create user ops$scott identified externally" already translates to
create user ops$scott identified by values 'EXTERNAL'

>Since the password is hashed and digestedm this will not match any password
>and therefore will not correspond to a value.
>

As an aside, if you do this for sys password AND have a password file (i.e orapw<SID>) use a value of exactly 16 characters - the hash is stored internally in varchar2(16) - or you will get some ORA-00600 when using the password file
with a user not member of the OSDBA group. For this case, I suggest to use the value ' ****No Login****' (notice the 16c)

>By the way, Gilles, the connection will work fine with the userid as a
>parameter to the sqlplus command. Are you using unix? If so, use
>
>sqlplus ops\$arup/nanda (note the backslash before the $ sign)
>>instead of
>>sqlplus ops$arup/nanda
>
>$ sign being a unix special character may have confused sqlplus, thinking
>$arup/nanda being an environmental variable.
>

Correct that was it

>HTH.
>
>Arup
>
>
>
>----- Original Message -----
>To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
>Sent: Tuesday, June 24, 2003 5:19 PM
>
>
>> Hi Arup,
>>
>> At 21:59 21/06/2003 -0800, you wrote:
>> <Snip>...
>>
>> >An OS user called scott will be able to connect as the database user
>> >OPS$SCOTT, not SCOTT - a big difference. This is why the
>os_authent_prefix
>> >parameter is so important to set; don't leave it as null. If it is null,
>> >then the OS user scott can connect to database user scott.
>> >
>> Maybe I miss the obvious..
>> Considering remote_os_authent=false, why for LOCAL connections
>> os_authent_prefix='' is inherently less secure than os_authent_prefix=OPS$
>> or whatever string you choose.
>> In each case, the prerequisite is to create the user "identified
>externally"
>> (that's where you MUST be cautious)
>> But if scott is created with a password (i.e create user scott identified
>> by tiger)
>> then no OS user scott can log on whatever the os_authent_prefix is.
>> At least that's how I understand the feature.
>> Can you please elaborate on the security issue ?
>>
>> >> Any ideas how to restrict the externally identified users so that they
>> >> have to log in to the database server to access their oracle schemas.?
>> >
>> >Well, they are mutually exclusive. A user is authenticated by either the
>> >database or externally, not both. So if you create user scott identified
>> >externally, you are allowing him to bypass database authentication. If
>you
>> >don't want that, then you would create user SCOTT identified by a
>password.
>> >
>> In fact if os_authent_prefix=OPS$ and ONLY in this case,
>> you can still do this (it's inherited from V6 days but still working
>> with Oracle 9i R2) :
>>
>> create user ops$arup identified by nanda;
>> grant create session to ops$arup;
>> And know you can either connect with
>> sqlplus /
>> or
>> sqlplus /nolog
>> connect ops$arup/nanda
>> or
>> sqlplus /nolog
>> connect ops$arup
>> password : nanda
>>
>> Although it doesn't work directly from the command line
>> like sqlplus ops$arup/nanda
>> or sqlplus ops$arup
>> password : nanda
>> (But works again after you get
>> Enter username for a 2nd try)
>>
>> Regards
>>
>>
>> Gilles Parc
>>
>> carpe diem !!
>> --
>> Please see the official ORACLE-L FAQ: http://www.orafaq.net
>> --
>> Author: Gilles PARC
>> INET: [EMAIL PROTECTED]
>>
>> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
>> San Diego, California -- Mailing list and web hosting services
>> ---------------------------------------------------------------------
>> To REMOVE yourself from this mailing list, send an E-Mail message
>> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
>> the message BODY, include a line containing: UNSUB ORACLE-L
>> (or the name of mailing list you want to be removed from). You may
>> also send the HELP command for other information (like subscribing).
>>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: Arup Nanda
> INET: [EMAIL PROTECTED]
>
>Fat City Network Services -- 858-538-5051 http://www.fatcity.com
>San Diego, California -- Mailing list and web hosting services
>---------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).
>
>
>

Gilles Parc

carpe diem !!

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Gilles PARC
  INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).