Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: os_authent_prefix

Re: os_authent_prefix

From: Amir Gheibi <gheibia_at_gmail.com>
Date: Wed, 26 Jul 2006 12:20:03 +0800
Message-ID: <c906cd460607252120v6c4338c6kdb2cd7b8b688c2f4@mail.gmail.com>


Jared,

I want to thank you for your perfect guidance. There were two things that made it clear for me:

1- The ops$ prefix is required for users that authenticate externally. 2- The username/password are ignored for sysdba access when logging on locally.

Here is the result:

(obviously I've logged in as oracle to the Linux Server) create user ops$oracle identified externally; grant create session, dba to ops$oracle; exit

and

sqlplus /
show user (output is ops$oracle)
select * from session_roles; (result includes the DBA)

You know, I don't understand what is it with some experts that when you ask a question - that you have looked everywhere to find the answer and you couldn't or you could but didn't understand it - instead of helping you, make you more confused by asking other questions like "why do you want to do this?".

Maybe I'm not an expert in Oracle. But if I was I would never answer somebody's question like that.

Thanks again Jared,
Amir

On 7/26/06, Jared Still <jkstill_at_gmail.com> wrote:
>
> Comments inline:
>
> Hello everyone,
> >
> > I use Oracle 10g R2 on Fedora Core 4, and I use password file.
> >
> > The value of "os_authent_prefix" is "ops$", (default) and the os user
> > that I'm able to login with it as sysdba using os authentication is named
> > "oracle". (connect "/ as sysdba")
> >
> > I've created a user in my database named ops$oracle with the code
> > bellow:
> >
> > create user ops$oracle identified by secret;
> > grant create session, dba to ops$oracle;
> >
>
> I've created the same user on 10g R1 on a Linux server.
> The os_authent_prefix = ops$. Close, but not quite the same environment.
>
> I can connect as sysdba from a remote windows client like this:
> > sqlplus "ops$oracle/secret_at_testDb as sysdba"
> >
>
> I cannot. I would not expect to be able to unless SYSDBA were granted.
>
> Perhaps you should run the following query to see if sysdba was granted to
> ops$oracle:
>
> select * from v$pwfile_users;
>
> The fact that you can logon as sysdba from a windows client suggests that
> indeed
> there is an entry for ops$oracle in v$pwfile_users. The fact that you
> cannot do
> so through sqlnet on the server suggests otherwise.
>
> Does testdb resolve to the same database on both client and server?
>
> In addition, the ops$ prefix is required for users that authenticate
> externally.
> The ops$oracle account you have created is not such an account. To create
>
> an externally identified account requires this:
>
> create user ops$oracle identified externally;
>
> The only way to login to that account would be to logon to the server as
> 'oracle'
> and using this command:
>
> sqlplus /
>
> Unless of course remote_os_authent=true, in which case anyone from any
> workstation on the network with admin privileges on the workstation
> could then logon as ops$oracle. Probably not what you want.
>
>
> sqlplus "ops$oracle/secret_at_testDb as sysdba"
> >
>
> This is the expected result.
>
>
> sqlplus "ops$oracle/secret as sysdba".
> >
>
> The linux account you are starting the session with is in the dba group.
> It doesn't matter what user you login as, or even if the user exists.
>
> Try this:
>
> sqlplus "bugsbunny/daffyduck as sysdba"
>
>
> My underestanding is if I want to connect locally and I use tnsname in the
> > connection command, oracle will interpret it differently.
> >
> > Could anyone make this clear for me that why oracle acts differently,
> > please?
> >
>
>
> In a nutshell, the username/password are ignored for sysdba access
> when logging on locally.
>
> The user on the linux server has sysdba authentication enabled through
> inclusion in the dba group.
>
> Further explanation would require reading the docs.
> I will let you do that. :)
>
> http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14220/security.htm#i12336
>
>
> --
> Jared Still
> Certifiable Oracle DBA and Part Time Perl Evangelist
>

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Jul 25 2006 - 23:20:03 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US