Retrieving current username within table function

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Retrieving current username within table function

Nigel Jones-2
Newbie warning ...

I'm trying to figure out how I could determine the current username when in a table function (in fact I'm writing some java code that plugs into someone else's table function ...)

I was originally thinking of something like the snippet below. However this doesn't work (I get an SQL Transient Exception). 

I don't know derby well but saw some discussion around not exposing the language context in any case (due to security concerns)

Given this can anyone suggest the simplest way to retrieve the authorised user? 
ContextManager contextMgr = ((EmbedConnection) DriverManager.getConnection("jdbc:default:connection")).getContextManager();

LanguageConnectionContext languageContext = (LanguageConnectionContext)contextMgr.getContext("LanguageConnectionContext");
StatementContext derbyStatementContext = languageContext.getStatementContext();
String localDerbyContextCurrentUser = derbyStatementContext.getSQLSessionContext().getCurrentUser();

Many thanks
Nigel.
Reply | Threaded
Open this post in threaded view
|

Re: Retrieving current username within table function

Rick Hillegas-3
On 2/8/18 10:57 AM, Nigel Jones wrote:
Newbie warning ...

I'm trying to figure out how I could determine the current username when in a table function (in fact I'm writing some java code that plugs into someone else's table function ...)

I was originally thinking of something like the snippet below. However this doesn't work (I get an SQL Transient Exception). 

I don't know derby well but saw some discussion around not exposing the language context in any case (due to security concerns)

Given this can anyone suggest the simplest way to retrieve the authorised user? 
ContextManager contextMgr = ((EmbedConnection) DriverManager.getConnection("jdbc:default:connection")).getContextManager();

LanguageConnectionContext languageContext = (LanguageConnectionContext)contextMgr.getContext("LanguageConnectionContext");
StatementContext derbyStatementContext = languageContext.getStatementContext();
String localDerbyContextCurrentUser = derbyStatementContext.getSQLSessionContext().getCurrentUser();

Many thanks
Nigel.

Hi Nigel,

You should be able to call current_user from inside a user-defined function. Here's an example of how to do this via a scalar function. Should work the same for a table function:

Run the following ij script...

connect 'jdbc:derby:memory:db1;create=true';

create function findUser() returns varchar(128)
language java
parameter style java
reads sql data
external name 'UserFinder.findUser';

values findUser();

...after compiling the following class...

import java.sql.*;

public class UserFinder
{
  public static String findUser() throws SQLException
  {
    try (Connection conn = DriverManager.getConnection("jdbc:default:connection"))
    {
      try (PreparedStatement ps = conn.prepareStatement("values current_user"))
      {
        try (ResultSet rs = ps.executeQuery())
        {
          rs.next();
          return rs.getString(1);
        }
      }
    }
  }
}

Reply | Threaded
Open this post in threaded view
|

Re: Retrieving current username within table function

Nigel Jones-2
Many thanks for that suggestion.

I subsequently found another path through the code I'm using where the user id had already been set and gives me what I need for now, but the above fragment is really useful in case I need to do this somewhere else. Thanks ...

On Fri, 9 Feb 2018 at 02:42 Rick Hillegas <[hidden email]> wrote:
On 2/8/18 10:57 AM, Nigel Jones wrote:
Newbie warning ...

I'm trying to figure out how I could determine the current username when in a table function (in fact I'm writing some java code that plugs into someone else's table function ...)

I was originally thinking of something like the snippet below. However this doesn't work (I get an SQL Transient Exception). 

I don't know derby well but saw some discussion around not exposing the language context in any case (due to security concerns)

Given this can anyone suggest the simplest way to retrieve the authorised user? 
ContextManager contextMgr = ((EmbedConnection) DriverManager.getConnection("jdbc:default:connection")).getContextManager();

LanguageConnectionContext languageContext = (LanguageConnectionContext)contextMgr.getContext("LanguageConnectionContext");
StatementContext derbyStatementContext = languageContext.getStatementContext();
String localDerbyContextCurrentUser = derbyStatementContext.getSQLSessionContext().getCurrentUser();

Many thanks
Nigel.

Hi Nigel,

You should be able to call current_user from inside a user-defined function. Here's an example of how to do this via a scalar function. Should work the same for a table function:

Run the following ij script...

connect 'jdbc:derby:memory:db1;create=true';

create function findUser() returns varchar(128)
language java
parameter style java
reads sql data
external name 'UserFinder.findUser';

values findUser();

...after compiling the following class...

import java.sql.*;

public class UserFinder
{
  public static String findUser() throws SQLException
  {
    try (Connection conn = DriverManager.getConnection("jdbc:default:connection"))
    {
      try (PreparedStatement ps = conn.prepareStatement("values current_user"))
      {
        try (ResultSet rs = ps.executeQuery())
        {
          rs.next();
          return rs.getString(1);
        }
      }
    }
  }
}