Solved

Host string to connect to remote Oracle 9i server from limited user account

Posted on 2006-10-23
3
5,710 Views
Last Modified: 2011-08-18
I have an Oracle 9i server setup on a private network.  My users have to VPN into this client network to get to my server.  That part is working fine.  However, because this is for a department at a University, all of my users will be connecting from lab machines operated by our Computer Services department, which has file permissions locked down.  Specifically, neither I nor my users have permissions to edit the tnsnames.ora file, which does not include any references to my oracle instance.

Can I setup my listener.ora file on the server so that users can connect using username/password@192.168.0.60 and be connected to the "OracleDB" database instance?

Here is my current listener.ora file:

# LISTENER.ORA Network Configuration File: c:\oracle\ora92\network\admin\listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = blade10)(PORT = 1521))
      )
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = c:\oracle\ora92)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = OracleDB)
      (ORACLE_HOME = c:\oracle\ora92)
      (SID_NAME = OracleDB)
    )
  )
0
Comment
Question by:smidgie82
  • 2
3 Comments
 
LVL 34

Accepted Solution

by:
Mark Geerlings earned 250 total points
Comment Utility
No, you need to modify one (or more) tnsnames.ora file(s) to allow a client to connect to the "OracleDB" alias in the listener.ora file.  Depending on how the client machines are set up, this could be just one common, shared tnsnames.ora file, or each of the lab machines could have their own tnsnames.ora file.

It is probably easiest, simplest and most-secure to use just one, shared tnsnames.ora file from a network location that the lab machines have read (but not write) access to.  Either you or someone must have write permissions on this networked tnsnames.ora file to add an entry pointing to your Oracle database.  Then you just add a registry entry to each of the lab machines pointing to this file.  The registry entry must be named: "TNS_ADMIN" and be located in: "Hkey-Local-Machine, Software, Oracle".  It must contain the full path and filename of the networked tnsnames.ora file.
0
 
LVL 9

Author Comment

by:smidgie82
Comment Utility
Unfortunately, I don't have write access to that registry key, and Computer Services isn't willing to either A) give me write access or B) create that value and push it out on a domain-wide basis.  So, that solution doesn't work.  However, while searching on your TNS_ADMIN reg key, I came across another solution: the TNS_ADMIN environment variable.  Just set the user's TNS_ADMIN environment variable to point to a folder holding custom tnsnames.ora and sqlnet.ora files for my server, and everything works great.

Thanks for the pointer!
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
Yes, if you can set an environment variable, that ought to work fine.  I'm glad I was able to help get you going in the direction you needed.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now