4 Replies Latest reply: Apr 28, 2016 1:27 AM by Namrata Shah RSS

    Do we have any way to set and use dynamic JNDI connection.

    Namrata Shah

      Hi,

       

      My requirement over here is bit different then normal scenario. Generally we define one JNDI connection and use that under workbook connect section.

      From pure data security reason we are going with different approach at oracle level.

       

      Under Oracle database we have created multiple user id's

      eg

      Usr1

      Usr2

       

      We have say profile and loss data captured in table name "PnL_Details"

      Table data is like below

      Client                 Date                PnLAmt

        Usr1                 31-Jan-2016        1000000

        Usr2                 31-Jan-2016        3000000

        Usr1                 29-Feb-2016        1000000

        Usr3                 29-Feb-2016        1000000


      Now using Oracle utility (VPD) I am restricting data access based on User id through which database connection is taking place.

      That means when we are connecting to database using 'Usr1' user id and run query "Select * from PnL_Details" , we will get only Usr1 related rows i.e. row-1 and 3 from above table.

      and same way when we connect to database using 'Usr2' user id and  run query "Select * from PnL_Details" , we  gets only Usr2 related rows i.e. row-2 and 4 from the above table.

       

      This entire setup is done at database level. Now when Usr1 is connecting to application and running datatwatch workbook we want JNDI connection to use Usr1 for Oracle database connection and when Usr2 is connecting workbook should automatically use JNDI connection which is created with Usr2 id..

       

      So to summarize at runtime I want workbook to decide which JNDI connection to be used.

       

      Is there a way to achieve it? We are using datawatch designer & server 13.3

        • Re: Do we have any way to set and use dynamic JNDI connection.
          Edrun Yuen

          Hi Namrata,

           

          The JNDI name setting of the Database Connector can be parameterized. Just a reminder, it is of course important to understand that each JNDI name that they set must be pointing to a database where there is a table named like in their query, with columns named like in their query. Also, the different databases behind each JNDI should be of the same brand in order to avoid SQL dialect errors.

           

          Please take note that this is unofficial, untested and we cannot give any guarantees.

            • Re: Do we have any way to set and use dynamic JNDI connection.
              Namrata Shah

              Hi Edrun,

               

              Thanks for the revert. Yes we have same Oracle brand so all other conditions which you had mentioned are matching. Could you please help me on how to achieve "The JNDI name setting of the Database Connector can be parameterized."

               

              As I had mentioned in above example we want to connect to same database but to a different - different schema depending upon who is the user of that workbook which gets decided at the run time.

              For example

              If Usr1 has connected to the application and using datawatch workbook which is part of that application then workbook should internally connect to Oracle using Usr1 and if

              Usr2 has connected to the application and using datawatch workbook which is part of that application then workbook should internally connect to Oracle using Usr2.

               

              - Regards

              Namrata Shah

                • Re: Do we have any way to set and use dynamic JNDI connection.
                  Theo Klemming

                  Hi

                   

                  For this purpose, you should not parameterize the JNDI connection.

                  Instead, you should include the user id in your SQL query.

                   

                  The user ID of an authenticated user is automatically assigned as the value to an internal parameter in Datawatch Panopticon called {userid}.

                   

                  By including a WHERE clause in your SQL query, and using the {userid} as the value to match, you will get different rows back in the query result set depending on who the authenticated user is.

                   

                  A requirement here is that the exact string matching the user id:s exist in the database.