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>
