Re: OPS$USER and SYSDBA privilege

On 30 Aug 2001 14:49:09 -0700, gspk_at_yahoo.com (Prince Kumar) wrote:

>I have defined an externally define user with prefix "ops$" . I am
>able to connect from the local server and remote clients. I am not
>able to connect as sysdba from the remote clients. How do I fix this?
>
>(I have the remote_os_authent = true)
>
>At local server:
>----------------
>Now, I granted the user "sysdba" privilege.
>
>usr_at_dev01$ sqlplus dbausr/pwd ;
>
>dbausr_at_dev01> create user ops$usr identified externally ;
>
>User created.
>
>dbausr_at_dev01> grant sysdba to ops$usr ;
>
>Grant succeeded.
>
>
>At the remote client:
>---------------------
>[usr_at_dev03]$ sqlplus "/@dev04 as sysdba"
>
>SQL*Plus: Release 8.1.6.0.0 - Production on Thu Aug 30 14:31:18 2001
>
>(c) Copyright 1999 Oracle Corporation. All rights reserved.
>
>ERROR:
>ORA-01031: insufficient privileges
>
>
>Enter user-name:
>
>
>But, If I try, as a normal user (no sysdba) it connects perfect.
>Anyidea, how to get this work?
>
>[usr_at_dev03]$ sqlplus "/@dev04"
>
>SQL*Plus: Release 8.1.6.0.0 - Production on Thu Aug 30 14:40:23 2001
>
>(c) Copyright 1999 Oracle Corporation. All rights reserved.
>
>
>Connected to:
>Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production
>JServer Release 8.1.7.2.0 - Production
>
>
>Note: I am testing various scenarios.
>Listed below the init.ora parameter,
>
>remote_login_passwordfile = exclusive

this enables 'privilleged users' to login remotely.

>
>remote_os_authent = true #vulnerable to security breach, used
>for testing only

this enables 'non-privilleged users' to login remotely with OS authentication.

>
>os_authent_prefix = "ops$"

this tells Oracle how to match OS user account and Oracle user account when OS authentication is used (locally or remotely).

Some distictions have to be made:
1.Log-in process can be divided into 'privilleged users' and 'non-privilleged users' based on whethere 'as sysdba' is used. 2.Log-in processes can happen 'locally' or 'remotely' based on whether the client and the server are on the same machine. 3.Authentication methods can be either 'database authentication' or 'OS authentifcation'. 'Database authentication' for 'non-privilleged users' uses the 'data dictionary', while that for 'privilleged users' uses the 'password file'.

As for your login tests, the rules are (yes/no means applicability): 1.locally, non-privilleged, dictionary authentication, yes 2.locally, non-privilleged, OS authentication, yes: create user ... identified externally
3.locally, privilleged, password file authentication, yes: grant sysdba to user
4.locally, privilleged, OS authentication, yes: users in group ORA_DBA for NT, OSDBA for Unix 5.remotely, non-privilleged, dictionary authentication, yes 6.remotely, non-privilleged, OS authentication, yes: create user ... identified externally + same (username,password) for client/server or NT domain prefex is used 7.remotely, privilleged, password file authentication, yes: remote_login_password_file
8.remotely, privilleged, OS authentication, no

The last scenario is the test you mention, remotely logging in as privilleged user with OS authentication is not supposed to be applicable. If you can do it, you should ask Oracle to pay you bucks for this terrible information. :-)

I hope I have covered all scenarios, please correct me, if any.

Dino Received on Fri Aug 31 2001 - 08:38:07 CDT