Forum Stats

  • 3,767,981 Users
  • 2,252,736 Discussions
  • 7,874,399 Comments

Discussions

Issue with sqlcl connecting to database due to long user name

dherzhau
dherzhau Member Posts: 21 Red Ribbon

Hi,

we are on the way to move to a new linux box and we are experience problems with new, very long usernames on the linux box.

If I try to connect to an existing oracle box with sqlcl

I get the following error:

SQLcl: Release 21.1 Production on Tue Nov 09 10:14:20 2021

Copyright (c) 1982, 2021, Oracle. All rights reserved.

 USER         = TEST01

 URL          = jdbc:oracle:thin:@desamp01db:1521/ETEST01

 Error Message = Connection property: format error: Property is 'v$session.osuser' and value is 'e_mustermann_extern_ohne_vollmac'

Username? (RETRYING) ('TEST01/*********@dtest01db:1521/ETEST01'?)

Version:

Version sql -v

SQLcl: Release 21.1.1.0 Production Build: 21.1.1.113.1704

Environment LINUX 64 bit

I tried to shorten the username, but somehow it dit not work.

$JAVA -Doracle.jdbc.v\$session.osuser={USER:0:29} -Duser.name={USER:0:29} $CUSTOM_JDBC $CYGWIN "${APP_VM_OPTS[@]}" -client $SQLCL_DEBUG -cp "$CPLIST" oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli "[email protected]"

Any ideas appreciated.

Thanks

Dirk

Answers

  • User_H3J7U
    User_H3J7U Member Posts: 632 Silver Trophy

    You can set the connection property with EZ Connect Plus syntax:

    windows> sql us/[email protected]:1521/db.domain?v$session.osuser=xxx

    Or using environment variable JAVA_TOOLS_OPTIONS.

    windows> set JAVA_TOOL_OPTIONS=-Doracle.jdbc.v$session.osuser=xyz

  • dherzhau
    dherzhau Member Posts: 21 Red Ribbon

    Hi,

    Thank you for your support, unfortunatley the EZ connect property, did not work

    sql [email protected]:1521/test01?v$session.osuser=abc

    I user for testing purpose an old linux box.

    select osuser from v$session where osuser='abc';

    no Rows

    Unfortunatley the second method did not work, too. We are on Linux, so I had to modify it slightly

    EXPORT JAVA_TOOL_OPTIONS=-Doracle.jdbc.v$session.osuser=xyz

    I wasn't not aware that I could use the EZ Connect to set connection properties.

    But again, thanks a lot.

  • User_H3J7U
    User_H3J7U Member Posts: 632 Silver Trophy
    edited Nov 9, 2021 6:15PM

    EZ Connect Plus appears in the 19c. It should work with actual jdbc version. Check the content of ojdbc8.jar. But in unix you need escape the dollar symbol.

    To set an environment variable in unix, try: export JAVA_TOOL_OPTIONS='-Doracle.jdbc.v$session.osuser=xyz' and check it contains an exact value echo $JAVA_TOOL_OPTIONS

    To check osuser in database, use sys_context('userenv', 'os_user') in connected session.

  • User_H3J7U
    User_H3J7U Member Posts: 632 Silver Trophy
    edited Nov 10, 2021 3:20AM

    Both methods do not work if sqlcl uses oci (show connection gives jdbc:oracle:oci8:@...). On my Linux i unset the ORACLE_HOME environment variable. After that sqlcl started using jdbc thin:

    [email protected]:~> unset ORACLE_HOME
    [email protected]:~> export JAVA_TOOL_OPTIONS='-Doracle.jdbc.v$session.osuser=xyz789'
    [email protected]:~> sql ustas/[email protected]:1521/pdb213a.vm
    Picked up JAVA_TOOL_OPTIONS: -Doracle.jdbc.v$session.osuser=xyz789
    
    SQLcl: Release 21.2 Production on Wed Nov 10 03:51:29 2021
    Copyright (c) 1982, 2021, Oracle.  All rights reserved.
    Last Successful login time: Wed Nov 10 2021 03:51:30 +03:00
    
    Connected to:
    Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
    Version 21.3.0.0.0
    
    SQL> select sys_context('userenv', 'os_user') from dual;
    
       SYS_CONTEXT('USERENV','OS_USER')
    ___________________________________
    xyz789                              
    
    SQL> connect ustas/[email protected]:1521/pdb213a.vm?oracle.jdbc.v$session.osuser=abc123
    Connected.
    SQL> select sys_context('userenv', 'os_user') from dual;
    
       SYS_CONTEXT('USERENV','OS_USER')
    ___________________________________
    abc123                              
    
    SQL>
    

    -thin option does not work because ORACLE_HOME overwrites its value:

    public class SQLCliOptions {
    ...
      public static ScriptRunnerContext populateContextWithOptions(ScriptRunnerContext context) {
    ...
        if (options.contains("-oci"))
          context.putProperty("DBConfig.USE_THICK_DRIVER", Boolean.valueOf(true));
        if (options.contains("-thin"))
          context.putProperty("DBConfig.USE_THICK_DRIVER", Boolean.valueOf(false));
    ...
        if (JDBCHelper.doesOHJDBCExist())
          context.putProperty("DBConfig.USE_THICK_DRIVER", Boolean.valueOf(true));
    ...
      }
    ...