1

I'd like to know how to set the CLIENT_IDENTIFIER parameter in Oracle in order to have the application user available during invocations to the database. I'm using the Spring framework with myIbatis.

I'll be very grateful if you can give me precise instructions on how to do that because I am not an expert using these technologies.

rjdkolb
  • 10,377
  • 11
  • 69
  • 89
Jose Carlos
  • 11
  • 1
  • 2

3 Answers3

2

For CLIENT_INFO

BEGIN DBMS_APPLICATION_INFO.SET_CLIENT_INFO('whatever'); END;

For CLIENT_IDENTIFIER

BEGIN DBMS_SESSION.SET_IDENTIFIER('whatever'); END;
Matthew McPeak
  • 17,705
  • 2
  • 27
  • 59
2

Check Oracle documentation for setEndToEndMetrics for older Versions; for 12c setClientInfo.

In either case you need a real connection, not the proxy.

The advantage of using this approach compared to a call of DBMS_APPLICATION_INFO is that it needs no roundtrip to the database. The information is transferred and set with the next JDBC call.

UPDATE

A simple example for iBatis follows.

1) You must unwind the connection to get the real Oracle connection

 Connection con = unwindConnection(sqlMapClient.getCurrentConnection());

2) define the E2E identifiers - as you correct stated after requesting the connection from pool or event before each action

  String[] metrics = new String[OracleConnection.END_TO_END_STATE_INDEX_MAX];
 metrics[OracleConnection.END_TO_END_ACTION_INDEX] = 'myAction3';
 metrics[OracleConnection.END_TO_END_CLIENTID_INDEX] = 'myClient3';
 metrics[OracleConnection.END_TO_END_MODULE_INDEX] = 'myModule3';

 con.setEndToEndMetrics(metrics, (short) 0);

3) after the next JDBC roundtrip to DB the identifier are set

 select    ACTION, CLIENT_IDENTIFIER, MODULE 
 from v$session where ....

 myAction3   myClient3   myModule3
Marmite Bomber
  • 19,886
  • 4
  • 26
  • 53
  • What I want to know is how to do this in Spring or myIbatis. I think I must set the value of the CLIENT_IDENTIFIER parameter every time I get an Oracle connection from the pool that is managed by Spring? or myIbatis?, but I don't know how and where to modify the code in order to make this work. My final goal is to get access to the name of the application user (not the database user) in my stored procedures and database triggers for auditing purposes. – Jose Carlos Sep 15 '15 at 15:38
  • Thanks for your answer, but I still don't know how to wire up this code with the code generated by myBatis which are essentially a collection of XML files and empty clases. – Jose Carlos Sep 17 '15 at 16:31
  • On the other hand, I found this page () which uses Spring AOP and describes exactly what I want to do, I did all the steps but it simply does not work. The prepare() method that should intercept every call to getConnection() is never called. – Jose Carlos Sep 17 '15 at 16:41
  • @Jose - your **Q doesn't match exactly your problem**. My suggestion. 1) close it and ask a new Q "How can I intercept getConnection in ...". 2) DBMS_SESSION is simple but costly way to set the client identifier. In case performance and scalability is your concern you should deploy the method described above. – Marmite Bomber Sep 19 '15 at 08:36
2

You can set using the Spring AOP. Write an aspect that will invoke whenever getConnection method called.

@Component
@Aspect
public class ClientIdentifierConnectionPreparer implements ConnectionPreparer {

    private String prepSql = "{ call DBMS_SESSION.SET_IDENTIFIER(?) }";
    @Autowired
    private UserService userService;

    @AfterReturning(value="execution(java.sql.Connection javax.sql.DataSource.getConnection(..))" returning="connection")
    public Connection prepare(Connection connection) {
        CallableStatement cs = connection.prepareCall(prepSql);
        cs.setString(1,userService.getUserId());
        cs.execute();
        cs.close();
        return connection;
    }
}
Sheekat
  • 21
  • 2