Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2731
  • Last Modified:

Cannot tnsping or connect locally except from root and oracle Linux accounts, but remote connections work

I just installed Oracle 11g on a new Fedora 9 Linux box.  Everything installed fine.  However, I am seeing some odd behavior that I can't figure out: I can tnsping and sqlplus (connect) perfectly from *remote* machines and from the root and oracle user accounts on the local box.  However, whenever I try to tnsping or sqlplus to the database from any "normal" Linux user account it throws a "TNS-03505: Failed to resolve name" error.  At first I thought it was invalid data in tnsnames.ora, but I have triple-checked it and plus I can connect just fine from the 'root' and 'oracle' accounts.

To summarize, here is what I am seeing ('shanadl' is the oracle service name):
1. Log in as 'dougb' on the local Linux box.
2. tnsping shanadl  << ERROR: "TNS-03505: Failed to resolve name"
3.  Log in as 'test' (which is another normal account) on the local Linux box.
4. tnsping shanadl << ERROR: "TNS-03505: Failed to resolve name"
5.  Log in as 'oracle' on the local Linux box.
6. tnsping shanadl << WORKS!
7.  Log in as 'oracle' on the local Linux box.
8. tnsping shanadl << WORKS!
9. Log in to any remote box (e.g., Windows or a different Linux box).
10. tnsping shanadl << WORKS!  (granted, this uses a different tnsnames.ora file local to the client box)

In the past I have installed Oracle 8, 9i, and 10g instances and I have never seen this behavior.  The fact that I can connect to the Oracle instance as root and the 'oracle' accounts but not from normal accounts makes me think this is some new security feature in Oracle 11g.  However, Googling hasn't turned up anything.  Why can't I connect from non-privileged Linux accounts?  I'm really stumped here!  Any advice is appreciated.
0
dbeachy1
Asked:
dbeachy1
  • 2
1 Solution
 
dbeachy1Author Commented:
Correction, step 7 should read:
7. Log in as 'root' on the local Linux box.
 
0
 
DavidSenior Oracle Database AdministratorCommented:
Weird.  Problems like this are commonly solved when we methodically examine our assumptions, right?  Although you triple-checked a tnsnames file, it's possible that your box has more than one tnsnames.ora.  Check with executing "find / -name "tnsnames.ora" -type f -print" and see whether or not you have duplicates.
Secondly, are the .profiles for test and dougb identical to the others?  I'd connect as test, and eyeball my "env|sort" results to confirm the Oracle homes, and TNS_ADMIN variable are correct.
Thirdly, your symptom suggests that the test accounts simply don't have execute permission on $ORACLE_HOME/bin/sqlplus.  Check for group priviledges, and let me know what comes back.
Regards,
dvz
0
 
dbeachy1Author Commented:
Sure enough, it turned out to be a permissions problem on tnsnames.ora! Oracle 11g created the tnsnames.ora file with 0640 permissions (rw owner, r group, NO ACCESS world), and so only root and users in the 'dba' group could connect locally.  I assume this is part of the security ehnancements talked about in Oracle 11g.  In any case, the fix was this:
1. Log in as oracle.
2. chmod a+r $ORACLE_HOME/network/admin/tnsnames.ora

And presto, I can connect from my local account now!  That little change from previous Oracle versions sure caused me a lot of grief.  :)  
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now