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
Comments inline:<br><br><div><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><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><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> <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. <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><div><br> This is the expected result.<br> <br></div><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><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><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><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"> 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>
