os_authent_prefix 2006-07-26 - By Amir Gheibi
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@(protected)> 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@(protected) 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@(protected) 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
>
Jared,<br><br>I want to thank you for your perfect guidance.<br>There were two
things that made it clear for me:<br><br>1- The ops$ prefix is required for
users that authenticate externally.<br>2- The username/password are ignored for
sysdba access when logging on locally.
<br><br>Here is the result:<br><br>(obviously I've logged in as oracle to the
Linux Server)<br>create user ops$oracle identified externally;<br>grant create
session, dba to ops$oracle;<br>exit<br><br>and<br><br>sqlplus /<br>
show user (output is ops$oracle)<br>select * from session_roles; (result
includes the DBA)<br><br>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?".
<br><br>Maybe I'm not an expert in Oracle. But if I was I would never answer
somebody's question like that.<br><br>Thanks again Jared,<br>Amir<br><br><div>
<span class="gmail_quote">On 7/26/06, <b class="gmail_sendername">
Jared Still</b> <<a href="mailto:jkstill@(protected)">jkstill@(protected)</a>>
; wrote:</span><blockquote class="gmail_quote" style="border-left: 1px solid rgb
(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div>Comments inline:<br><br><div></div><div><span class="q"><span class="gmail
_quote"></span><blockquote class="gmail_quote" style="border-left: 1px solid rgb
(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div>Hello everyone,<br><br>I use Oracle 10g R2 on Fedora Core 4, and I use
password file.
<br><br>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")
<br><br>I've created a user in my database named ops$oracle with the code
bellow:<br><br>create user ops$oracle identified by secret;<br>grant create
session, dba to ops$oracle;</div></blockquote></span></div><div><div><br>
I've created the same user on 10g R1 on a Linux server.
<br>The os_authent_prefix = ops$. Close, but not quite the same environment.<br
></div></div><div><span class="q"><br><blockquote class="gmail_quote" style=
"border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding
-left: 1ex;">
<div>
I can connect as sysdba from a remote windows client like this:
<br>sqlplus "ops$oracle/secret@(protected) as sysdba"</div></blockquote><
/span></div><div><div><br>I cannot. I would not expect to be able to
unless SYSDBA were granted.<br><br>Perhaps you should run the following query
to see if sysdba was granted to ops$oracle:
<br><br> select * from v$pwfile_users;<br><br>The fact that you can logon
as sysdba from a windows client suggests that indeed<br>there is an entry for
ops$oracle in v$pwfile_users. The fact that you cannot do<br>so through
sqlnet on the server suggests otherwise.
<br><br>Does testdb resolve to the same database on both client and server?<br>
<br>In addition, the ops$ prefix is required for users that authenticate
externally.<br>The ops$oracle account you have created is not such an account.
To create
<br>an externally identified account requires this:<br><br> create user
ops$oracle identified externally;<br></div><br>The only way to login to that
account would be to logon to the server as 'oracle'<br>and using this command:
<br><br> sqlplus /<br><br>Unless of course remote_os_authent=true, in
which case anyone from any<br>workstation on the network with admin privileges
on the workstation <br>could then logon as ops$oracle. Probably not what
you want.
</div><div><span class="q"><br><br><blockquote class="gmail_quote" style=
"border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding
-left: 1ex;"><div>sqlplus "ops$oracle/secret@(protected) as sysdba"
</div></blockquote></span></div><div><div><br>
This is the expected result.<br> <br></div></div><div><span class="q"><br>
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204)
; margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><div>sqlplus "ops$oracle
/secret as sysdba".
</div></blockquote></span></div><div><div><br>The linux account you are
starting the session with is in the dba group.<br>It doesn't matter what user
you login as, or even if the user exists.<br><br>Try this:<br><br>
sqlplus "bugsbunny/daffyduck as sysdba"
<br><br><br></div></div><div><span class="q"><blockquote class="gmail_quote"
style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex;
padding-left: 1ex;"><div>My underestanding is if I want to connect locally and
I use tnsname in the connection command, oracle will interpret it differently.
<br><br>Could anyone make this clear for me that why oracle acts differently,
please?</div></blockquote></span></div><div><div><br><br>In a nutshell, the
username/password are ignored for sysdba access<br>when logging on locally.
<br><br>The user on the linux server has sysdba authentication enabled through
<br>inclusion in the dba group.<br><br>Further explanation would require
reading the docs.<br>I will let you do that. :)<br><a href="http:/
/download-west.oracle.com/docs/cd/B19306_01/server.102/b14220/security.htm
#i12336" title="http://download-west.oracle.com/docs/cd/B19306_01/server.102
/b14220/security.htm#i12336" target="_blank" onclick="return top.js.OpenExtLink
(window,event,this)">
http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14220/security
.htm#i12336</a><br><br></div><br></div>-- <br>Jared Still<br>Certifiable Oracle
DBA and Part Time Perl Evangelist<br>
</div></blockquote></div><br>
|
|
