2020年10月7日 22:25:27go评论117阅读模式

英文:

How to retrieve host, port, sid, user and password informations in java.sql.Connection

问题

我有一个Oracle连接（存储在java.sql.Connection对象中），这个连接是从一个属性文件（ApplicationResources.properties）中获取的。

我该如何获取关于主机、端口、SID、用户名和密码的信息？（我需要这些信息来通过我的Java应用程序调用特定的数据库函数）

以下是我的代码：

private void connectToDatabase() {
    ServiceLocator sl = new ServiceLocator();
    InitialContext ic = null;
    DataSource dataSource = null;
    try {
        if (conn == null) {
            ic = new InitialContext();
            dataSource = (DataSource) ic.lookup(sl.getInfo().getString("JNDI"));                
            conn = dataSource.getConnection();
            conn.setAutoCommit(true);
                                
            // 我需要主机、端口、SID和密码信息
            System.out.println("USER " + conn.getMetaData().getUserName());                                                
        }
    } catch (SQLException e) {
        logger.error("Error while trying to open database connection.", e);
    } catch (NamingException e) {
        logger.error("Error while trying to open database connection.", e);
    }
        
    if (conn != null)
        rastrear.definePredicate(conn);
}

英文:

I have an Oracle connection (in a java.sql.Connection object) that is mounted from a properties file (ApplicationResources.properties).

How do I capture information about host, port, sid, user and password? (I need this information to call a specific database function through my java application)

Below my code:

private void conectaBanco() {
    ServiceLocator sl = new ServiceLocator();
    InitialContext ic = null;
    DataSource dataSource = null;
    try {
        if (conn == null) {
            ic = new InitialContext();
            dataSource = (DataSource) ic.lookup(sl.getInfo().getString(&quot;JNDI&quot;));                
            conn = dataSource.getConnection();
            conn.setAutoCommit( true );
                            
            // I need host, port, sid and password information
            System.out.println(&quot;USER &quot; + conn.getMetaData().getUserName());                                                
        }
    } catch (SQLException e) {
        logger.error(&quot;Erro ao tentar abrir conexao com o banco de dados.&quot;, e);
    } catch (NamingException e) {
        logger.error(&quot;Erro ao tentar abrir conexao com o banco de dados.&quot;, e);
    }
    
    if(conn != null)
        rastrear.definePredicate(conn);
}

答案1

得分: 0

SESSION SID:
SELECT sys_context('USERENV', 'SID') FROM DUAL;
ORACLE_SID:
SELECT sys_context('userenv','instance_name') FROM dual;
HOST (Database machine) :
SELECT UTL_INADDR.get_host_name FROM dual;
Password: See [http://www.dba-oracle.com/t_password_storage.htm][1]
CLIENT SIDE Port:
Select port from v$session;
SERVER OS PID of Oracle server process connected to client process
SELECT p.spid
FROM   v$process p, v$session s
WHERE s.paddr = p.addr and 
sys_context('USERENV', 'SID') = s.sid;
Server side port:
This is really difficult since the server process port is mapped to a different port then the initial - for example port 12102 in listener.ora/tnsnames.ora get mapped by listener to a arbitrary free one (by the way: thats the reason why often firewalls need to be shutdown and while connections cannot get after mapping through firewall. This can be fixed but this is another story)
Below code
- grants required privileges
- defines a java stored function "Util.RunThis" which executes on database server a OS command passed into function and return string.
- "Util.RunThis" is mapped to PL/SQL function "RUN_CMD"
- "GET_PORT" is a PL/SQL function returning a numeric value of the used Port of the database server process connected to client session. Inside GET_PORT the SELECT is used to determine database server process pid replacing [SPID] in command below       
        /usr/sbin/lsof -Pan -p [SPID] -i
- finally invoking a simple select we get the Port of database server process attached to current session
        connect / as sysdba
        grant select on sys.v_$process to scott;        
        grant select on sys.v_$session to scott;
        begin
          dbms_java.grant_permission
              ('SCOTT',
               'SYS:java.io.FilePermission',
               '<<ALL FILES>>',
               'execute'); 
        end;
        /
        connect scott/tiger@foo.com
        create or replace and compile java source named "Util"
        as
         import java.io.*;
         import java.lang.*;
         import java.nio.charset.Charset;
         import java.nio.*;
         public class Util extends Object
         {
           public static String RunThis(String args)
           {
             Runtime rt = Runtime.getRuntime();
             String rc = args;
             try
             {
               Process p = rt.exec(args);
               int bufSize = 4096;
               BufferedInputStream bis =
               new BufferedInputStream(p.getInputStream(), bufSize);
               int len;
               byte buffer[] = new byte[bufSize];
               // Echo back what the program spit out
               while ((len = bis.read(buffer, 0, bufSize)) != -1)
               {
                 String xxx = new String(buffer, Charset.forName("UTF-8"));
                 rc = rc + xxx;
               }
               p.waitFor();
               rc = rc + "ABC";
             }
             catch (Exception e)
             {
               e.printStackTrace();
               rc = "Exception!!!";
             }
             finally
             {
               return rc;
             }
           }
         }
        /
        create or replace function RUN_CMD( p_cmd in varchar2) return VARCHAR2
        as language java
        name 'Util.RunThis(java.lang.String) return java.lang.String';
        /
        create or replace function GET_PORT return number
        as
          SPID NUMBER;
          retval varchar2(32000);
          y varchar2(1024);
          cmd VARCHAR2(256);
        begin
          SELECT 
            p.spid 
          INTO 
            SPID
          FROM   
            sys.v_$process p, 
            sys.v_$session s
          WHERE 
            s.paddr = p.addr and 
            sys_context('USERENV', 'SID') = s.sid;
          cmd :=  '/usr/sbin/lsof -Pan -p [SPID] -i';
          /* raw string data returned from Shell executing cmd */
          retval := run_cmd(replace(cmd,'[SPID]', SPID));
          /* get last occurance of : marking redirected port */
          y := substr(retval,INSTR(retval,':', -1)+1,1024);
          /* return the numeric port by stripping info like " (ESTABLISHED)" */
          return to_number(substr(y,1,INSTR(y, ' ')-1));
        end;
        /
        show errors
        select get_port from dual;
        /*-------------------- OUTPUT -------------------------- */
        SQL> connect / as sysdba
        grant select on sys.v_$process to scott;
        grant select on sys.v_$session to scott;
        begin
          dbms_java.grant_permission
              ('SCOTT',
               'SYS:java.io.FilePermission',
               '<<ALL FILES>>',
               'execute');
        end;
        /Connected.
        SQL> SQL>
        Grant succeeded.
        SQL> SQL>
        Grant succeeded.
        SQL> SQL>   2    3    4    5    6    7    8
        PL/SQL procedure successfully completed.
        SQL> connect scott/tiger@foo.com
        Connected.
        SQL> create or replace and compile java source named "Util"
          2  as
         import java.io.*;
         import java.lang.*;
         import java.nio.charset.Charset;
          3    4    5    6   import java.nio.*;
          7   public class Util extends Object
          8   {
          9     public static String RunThis(String args)
           {
             Runtime rt = Runtime.getRuntime();
         10   11   12       String rc = args;
         13
         14       try
         15       {
         16
         17         Process p = rt.exec(args);
         18   19         int bufSize = 4096;
         20         BufferedInputStream bis =
         21         new BufferedInputStream(p.getInputStream(), bufSize);
         22         int len;
               byte buffer[] = new byte[bufSize];
         23   24
               // Echo back what the program spit out
               while ((len = bis.read(buffer, 0, bufSize)) != -1)
         25   26   27         {
         28           String xxx = new String(buffer, Charset.forName("UTF-8"));
         29           rc = rc + xxx;
         30         }
               p.waitFor();
         31   32
               rc = rc + "ABC";
         33   34   35       }
         36       catch (Exception e)
         37       {
         38         e.printStackTrace();
         39         rc = "Exception!!!" ;
         40       }
         41
         42       finally
         43       {
         44         return rc;
         45       }
           }
         46   47   }
        / 48
        Java created.
        SQL> create or replace function RUN_CMD( p_cmd in varchar2) return VARCHAR2
        as language java
        name 'Util.RunThis(java.lang.String) return java.lang.String';
        /  2    3    4
        Function created.
<details>
<summary>英文:</summary>
SESSION SID: 
    SELECT sys_context(&#39;USERENV&#39;, &#39;SID&#39;) FROM DUAL;
ORACLE_SID:  
    SELECT sys_context(&#39;userenv&#39;,&#39;instance_name&#39;) FROM dual;
HOST (Database machine) :  
    SELECT UTL_INADDR.get_host_name FROM dual;
Password: See [http://www.dba-oracle.com/t_password_storage.htm][1]
CLIENT SIDE Port:
    Select port from v$session;
SERVER OS PID of Oracle server process connected to client process 
    SELECT p.spid
    FROM   v$process p, v$session s
    WHERE s.paddr = p.addr and 
    sys_context(&#39;USERENV&#39;, &#39;SID&#39;) = s.sid;
Server side port: 
This is really difficult since the server process port is mapped to a different port then the initial - for example port 12102 in listener.ora/tnsnames.ora get mapped by listener to a arbitrary free one (by the way: thats the reason why often firewalls need to be shutdown and while connections cannot get after mapping through firewall. This can be fixed but this is another story)
Below code 
- grants required privileges
- defines a java stored function &quot;Util.RunThis&quot; which executes on database server a OS command passed into function and return string.
- &quot;Util.RunThis&quot; is mapped to PL/SQL function &quot;RUN_CMD&quot; 
- &quot;GET_PORT&quot; is a PL/SQL function returning a numeric value of the used Port of the database server process connected to client session. Inside GET_PORT the SELECT is used to determine database server process pid replacing [SPID] in command below       
        /usr/sbin/lsof -Pan -p [SPID] -i
- finally invoking a simple select we get the Port of database server process attached to current session
        connect / as sysdba
        
        grant select on sys.v_$process to scott;        
        grant select on sys.v_$session to scott;
        
        begin
          dbms_java.grant_permission
              (&#39;SCOTT&#39;,
               &#39;SYS:java.io.FilePermission&#39;,
               &#39;&lt;&lt;ALL FILES&gt;&gt;&#39;,
               &#39;execute&#39;); 
        end;
        /
        
        connect scott/tiger@foo.com
        
        create or replace and compile java source named &quot;Util&quot;
        as
         import java.io.*;
         import java.lang.*;
         import java.nio.charset.Charset;
         import java.nio.*;
         public class Util extends Object
         {
           public static String RunThis(String args)
           {
             Runtime rt = Runtime.getRuntime();
             String rc = args;
            
             try
             {
        
               Process p = rt.exec(args);
              
               int bufSize = 4096;
               BufferedInputStream bis =
               new BufferedInputStream(p.getInputStream(), bufSize);
               int len;
               byte buffer[] = new byte[bufSize];
               
               // Echo back what the program spit out
               while ((len = bis.read(buffer, 0, bufSize)) != -1)
               {
                 String xxx = new String(buffer, Charset.forName(&quot;UTF-8&quot;));
                 rc = rc + xxx;
               }
               p.waitFor();
               
               rc = rc + &quot;ABC&quot;;
        
             }
             catch (Exception e)
             {
               e.printStackTrace();
               rc = &quot;Exception!!!&quot; ;
             }
            
             finally
             {
               return rc;
             }
           }
         }
        /
        
        create or replace function RUN_CMD( p_cmd in varchar2) return VARCHAR2
        as language java
        name &#39;Util.RunThis(java.lang.String) return java.lang.String&#39;;
        /
        
        create or replace function GET_PORT return number
        as
          SPID NUMBER;
          retval varchar2(32000);
          y varchar2(1024);
          cmd VARCHAR2(256);
        begin
          SELECT 
            p.spid 
          INTO 
            SPID
          FROM   
            sys.v_$process p, 
            sys.v_$session s
          WHERE 
            s.paddr = p.addr and 
            sys_context(&#39;USERENV&#39;, &#39;SID&#39;) = s.sid;
            
          cmd :=  &#39;/usr/sbin/lsof -Pan -p [SPID] -i&#39;;
        
          /* raw string data returned from Shell executing cmd */
          retval := run_cmd(replace(cmd,&#39;[SPID]&#39;, SPID));
          
          /* get last occurance of : marking redirected port */
          y := substr(retval,INSTR(retval,&#39;:&#39;, -1)+1,1024);
          
          /* return the numeric port by stripping info like &quot; (ESTABLISHED)&quot; */
          return to_number(substr(y,1,INSTR(y, &#39; &#39;)-1));
        end;
        /
        show errors
        
        select get_port from dual;
       
        /*-------------------- OUTPUT -------------------------- */
        SQL&gt; connect / as sysdba
        
        grant select on sys.v_$process to scott;
        
        grant select on sys.v_$session to scott;
        
        begin
          dbms_java.grant_permission
              (&#39;SCOTT&#39;,
               &#39;SYS:java.io.FilePermission&#39;,
               &#39;&lt;&lt;ALL FILES&gt;&gt;&#39;,
               &#39;execute&#39;);
        end;
        /Connected.
        SQL&gt; SQL&gt;
        Grant succeeded.
        
        SQL&gt; SQL&gt;
        Grant succeeded.
        
        SQL&gt; SQL&gt;   2    3    4    5    6    7    8
        
        PL/SQL procedure successfully completed.
        
        SQL&gt; connect scott/tiger@foo.com
        
        Connected.
        SQL&gt; create or replace and compile java source named &quot;Util&quot;
          2  as
         import java.io.*;
         import java.lang.*;
         import java.nio.charset.Charset;
          3    4    5    6   import java.nio.*;
          7   public class Util extends Object
          8   {
          9     public static String RunThis(String args)
           {
             Runtime rt = Runtime.getRuntime();
         10   11   12       String rc = args;
         13
         14       try
         15       {
         16
         17         Process p = rt.exec(args);
        
         18   19         int bufSize = 4096;
         20         BufferedInputStream bis =
         21         new BufferedInputStream(p.getInputStream(), bufSize);
         22         int len;
               byte buffer[] = new byte[bufSize];
         23   24
               // Echo back what the program spit out
               while ((len = bis.read(buffer, 0, bufSize)) != -1)
         25   26   27         {
         28           String xxx = new String(buffer, Charset.forName(&quot;UTF-8&quot;));
         29           rc = rc + xxx;
         30         }
               p.waitFor();
         31   32
               rc = rc + &quot;ABC&quot;;
        
         33   34   35       }
         36       catch (Exception e)
         37       {
         38         e.printStackTrace();
         39         rc = &quot;Exception!!!&quot; ;
         40       }
         41
         42       finally
         43       {
         44         return rc;
         45       }
           }
         46   47   }
        / 48
        
        Java created.
        
        SQL&gt; create or replace function RUN_CMD( p_cmd in varchar2) return VARCHAR2
        as language java
        name &#39;Util.RunThis(java.lang.String) return java.lang.String&#39;;
        /  2    3    4
        
        Function created.
        
        SQL&gt; create or replace function GET_PORT return number
        as
          SPID NUMBER;
          retval varchar2(32000);
          2    3    4    5    y varchar2(1024);
          cmd VARCHAR2(256);
        begin
          6    7    8    SELECT
            p.spid
          INTO
          9   10   11      SPID
          FROM
            sys.v_$process p,
            sys.v_$session s
          WHERE
         12   13   14   15   16      s.paddr = p.addr and
            sys_context(&#39;USERENV&#39;, &#39;SID&#39;) = s.sid;
        
          cmd :=  &#39;/usr/sbin/lsof -Pan -p [SPID] -i&#39;;
        
         17   18   19   20   21    /* raw string data returned from Shell executing cmd */
          retval := run_cmd(replace(cmd,&#39;[SPID]&#39;, SPID));
        
         22   23   24    /* get last occurance of : marking redirected port */
          y := substr(retval,INSTR(retval,&#39;:&#39;, -1)+1,1024);
        
          /* return the numeric port by stripping info like &quot; (ESTABLISHED)&quot; */
         25   26   27   28    return to_number(substr(y,1,INSTR(y, &#39; &#39;)-1));
        end;
        /
        show errors 29   30
        Function created.
        
        SQL&gt;
        No errors.
        SQL&gt; select get_port from dual;
        
          GET_PORT
        ----------
             36586
        
  [1]: http://www.dba-oracle.com/t_password_storage.htm
</details>
# 答案2
**得分**: 0
我通过在我的Weblogic配置JDBC上添加附加参数（主机、用户、端口、SID - 密码会自动返回）来解决了这个问题。我通过下面的代码获得了这些值：
```java
import weblogic.jdbc.wrapper.PoolConnection;
import java.util.Properties;
(...)
public Properties retornaPropriedadesConexao(){
    PoolConnection pc = (PoolConnection)conn;
    Properties p = pc.getConnectionEnv().getDriverProperties();                             
    return p;
}

英文:

I solve this problem adding on my Weblogic configuration JDBC the additional parameters (host, user, port, sid - the password is returned automaticaly).
And I got this values throw this code below:

import weblogic.jdbc.wrapper.PoolConnection;
import java.util.Properties;
(...)
public Properties retornaPropriedadesConexao(){
PoolConnection pc = (PoolConnection)conn;
Properties p = pc.getConnectionEnv().getDriverProperties();                            
return p;
}

通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。

如何在java.sql.Connection中检索主机、端口、SID、用户和密码信息

问题

答案1

避免在检查默认响应时出现代码重复。

并发问题在ISORequestListener中

Gradle无法将sysout写入文件 – java.io.IOException：无法删除文件

mapstruct, Java 和 Spring 的问题：目标未添加

如何在Playwright视觉比较中屏蔽多个定位器？

在C++中，可以使用可变模板参数来检索类型的内部类型。

selenium.common.exceptions.StaleElementReferenceException: Message: stale element reference: stale element not found

Creating and opening a URL to log in to Website via Basic Auth with Robot Framework/Selenium (Python)

AG Grid 在上下文菜单中以大文本形式打开

What's the correct way to type hint an empty list as a literal in python?

如何在Highcharts Gantt中更改本地化的星期名称

如何在同一个流中使用多个过滤器和映射函数？

如何使用Map/Set来将代码优化到O(n)？

.NET MAUI Android在GitHub Actions上构建失败，错误代码为1。