Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2014
    Posts
    2

    Unanswered: DB2 JDBC and special register

    Hopefully this is just a glitch but I am banging my head against a wall.

    The issue is that I have a client who are moving from Sybase ASE to DB2. While it is not a train smash, they would like to avoid a plethora of OS users. To my way of thinking what they want to do in this regard is achievable through using (setting)the SESSION_USER special register. I have tested this on my SLES DB2 10.5 installation and it works fine at the console level

    i.e. connect from OS(DB2inst1) user and then just GRANT SELECT to a user TEST and change the SESSION_USER to that. This really does give me what I want.

    However, I have tried to do this in a Java package (while I am not really a Java person, the client will use it to set up the external user) and tried to use the JDBC connection string as follows:

    try{
    String url =
    "jdbc:db2://192.168.56.101:50001/SAMPLE" +
    ":user=db2inst1;password=slesdb2;" +
    "specialRegisters=SESSION_USER=test" + ";";


    Class.forName("com.ibm.db2.jcc.DB2Driver");
    myConnection=DriverManager.getConnection(url);

    The connection works, but when I do 'SELECT SESSION_USER FROM SYSIBM.SYSDUMMY1', it returns DB2INST1 which is still the SYSTEM USER. I am sure that I am doing something stupid just cannot see it.

    Any help appreciated.

    Regards

    Regards

  2. #2
    Join Date
    Sep 2013
    Location
    India
    Posts
    246
    String url =
    "jdbc:db2://192.168.56.101:50001/SAMPLE" +
    ":user=db2inst1;password=slesdb2;" +

    In the above connection string user = db2inst1. Thats why sysibm.sysdummy1 will return "db2inst1" only. Through java i dont think the code you are using is actually changing Session_user. We may think of some other way to do it.

    please post how you "change the SESSION_USER to to test" at console level.

    ssumit
    ssumit

  3. #3
    Join Date
    Sep 2013
    Location
    India
    Posts
    246
    When a special register is referenced in a routine, the value of the special register in the routine depends on whether the special register is updatable or not. For non-updatable special registers, the value is set to the default value for the special register. For updatable special registers, the initial value is inherited from the invoker of the routine and can be changed with a subsequent SET statement inside the routine.

    follow url :

    IBM Knowledge Center

    so i think in java program it was not SET.

    ssumit
    ssumit

  4. #4
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    To allow a user to change his SESSION_USER special registry you have to grant him the SETSESSIONUSER privilege, for example:
    Code:
    grant setsessionuser on user test to user db2inst1
    Then you can either run the following in your session:
    Code:
    set session_user test
    or create a connect procedure to automate the process like:
    Code:
    create or replace procedure conn_proc
    begin
      if (USER='DB2INST1') then 
        commit;
        set session_user TEST;
      end if;
    end@
    
    grant execute on procedure conn_proc to public@
    update db cfg using connect_proc conn_proc@
    Regards,
    Mark.

  5. #5
    Join Date
    Aug 2014
    Posts
    2
    Hi Mark, Ssumit,

    Firstly Ssumit, here is the text from the console set as you requested. I thought that I had sent it but cannot find it in the thread so that is probably to my being new to the forum Excuse the formatting..

    Directory: /home/db2inst1/Desktop
    Wed Aug 20 03:58:24 EDT 2014
    db2inst1@db2server:~/Desktop> db2 connect to sample

    Database Connection Information

    Database server = DB2/LINUXX8664 10.5.3
    SQL authorization ID = DB2INST1
    Local database alias = SAMPLE

    db2inst1@db2server:~/Desktop> db2 select 'session_user from sysibm.sysdummy1'

    1
    --------------------------------------------------------------------------------------------------------------------------------
    DB2INST1

    1 record(s) selected.

    db2inst1@db2server:~/Desktop> db2 set session_user=test
    DB20000I The SQL command completed successfully.
    db2inst1@db2server:~/Desktop> db2 select 'session_user from sysibm.sysdummy1'

    1
    --------------------------------------------------------------------------------------------------------------------------------
    TEST

    1 record(s) selected.

    db2inst1@db2server:~/Desktop>

    As you can see it does work and the user is DB2INST1. Mark, I had GRANTED the authority.

    However, one thing I did do was to change the part of the JDBC connect string to rubbish and the connect still worked. This probably means that the part in question is not fully validated and therefore it could be a syntax error on my part.

    Once again thank you for the responses.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •