Oracle TNS Listener password

[ckarnbach]

Where do I specify the tns listener password in CF MX. I have added a password to the oracle TNS listener service. I need to get MX to pass this password to the oracle server. Is there a place to do this.

cfk

[paross1]

What the heck is a tns listener password? Are you referring to the administrative password for the TNS listener? Why would you need that on the client (ColdFusion) end? The administrative password that is established for the listener on the Oracle server side is for administrative functions, not for client access.

Or, are you referring to the password associated with the username that you would use for the DSN that you are establishing in ColdFusion Administrator? What version of Oracle are you running, and what kind of connection? JDBC? ODBC socket? Are you using CFMX Enterprise or Standard? Are you attempting to use an Oracle thin client? It isn't clear to me exactly what it is you are asking for.

Phil

[ckarnbach]

Here is what I was given from our security group here at USDOJ:
We are using 9i,
Server Product ColdFusion MX
Version 7,0,2,142559
Edition Enterprise
Serial Number
Operating System Windows 2003
OS Version 5.2

"
Description:
A Oracle TNS Listener has been detected on the host with login security disabled (SECURITY=OFF).
Observation:
Oracle is an enterprise level database which is available on many different platforms.
A configuration vulnerability exists within the Oracle TNS Listener which allows remote unauthenticated access. The TNS Listener accepts a clients request and establishes a TNS (Transparent Network Substrate) data connection between the client and the service. A TNS connection allows clients and servers to communicate over a network via a common API, regardless of the network protocol used on either end (TCP/IP, IPX, etc). A default installation of the TNS listens on TCP port 1521.
Vulnerable Systems:
Oracle 8i
Oracle 9i
Recommendation:
It is recommended to only allow certain IP's or subnet ranges to access the TNS listener. This can be done by adding a rule in the firewall. We also recommend that you enable a password for the TNS listener within Oracle

[paross1]

Has adding a listener admin password on your Oracle server affected client access to your database?

Phil

[ckarnbach]

Yes the data source now comes up with the following:Connection verification failed for data source: budget_survey
java.sql.SQLException: [Macromedia][Oracle JDBC Driver][Oracle]ORA-01045: user BUDGET_SURVEY lacks CREATE SESSION privilege; logon denied
The root cause was that: java.sql.SQLException: [Macromedia][Oracle JDBC Driver][Oracle]ORA-01045: user BUDGET_SURVEY lacks CREATE SESSION privilege; logon denied

[paross1]

What happens when you access the same database via SQL*Plus using the same schema? It looks to me like your schema (username) lacks the appropriate privilege(s) for connecting to the database. Have you discussed your problem with your DBA? Your DBA may need to GRANT the BUDGET_SURVEY user the CREATE SESSION privilege, among others. I'm not sure why setting a TNS listener password would have affected the CREATE SESSION privilege for your schema.

Phil

[ckarnbach]

It worked fine BEFORE putting a tns password on the oracle server.... thats was the only change made.

[paross1]

Are you sure that this was the only security “hole” that was plugged by your “security group”? It sure seems possible that somebody revoked the CREATE SESSION privilege for public, or at least your BUDGET_SURVEY schema.

Phil

[ckarnbach]

They did not plug it I worked with the DBA on a test server. All that was done was to add the password I can remove it and show you the results if thats what you require?

[paross1]

What do you get when you run this query?

SELECT dsp.privilege
FROM dba_sys_privs dsp
LEFT JOIN dba_role_privs drp ON dsp.grantee = drp.granted_role
WHERE drp.grantee = 'BUDGET_SURVEY'
OR dsp.grantee = 'BUDGET_SURVEY'

Phil