Passing User ID in sys_context from Teiid to Oracle with JDBC connection

[abhilash_tcs]

I have to pass User ID from Teiid to Oracle for VPD purpose. I have code written to set logged in user ID in sys_context's os_user. How can I implement to execute that piece of code while Teiid makes connection to Oracle.

[rareddy]

Abhilash,

When you create the data source in JBoss EAP for your Oracle data source, you need to configure a security-domain for data source to pass in logged in user's credentials. Here is documentation on it Data Source Security - Teiid 8.11 (draft) - Project Documentation Editor

Ramesh..

[abhilash_tcs]

Hi Ramesh,

Expected a reply from you. Thanks.

This doesn't solve the problem since it uses the credentials we use to login Teiid and connects to Oracle with same credentials.

But we have to connect to oracle with Global credentials, Teiid with Global Teiid crentials, and we have to get os_user of the client making connection to Teiid, and send it to Oracle where policies are defined.

Snippet of the User ID setting in os_user -

public static String setConnection(String userId) throws SQLException{

  String url = "jdbc:oracle:thin:username/password@host:port:SID";

  DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

  OracleConnection oConn = (OracleConnection)DriverManager.getConnection(url);

  Properties props = new Properties();

  props.setProperty(OracleConnection.CONNECTION_PROPERTY_THIN_VSESSION_OSUSER,userId);

  Connection conn = DriverManager.getConnection(url, props);

Thanks,

Abhilash

[rareddy]

Abhilash,

Where you get the "userId" in the above code snippet? note I am not asking "username" which is also in there? You need to do this when connection is created? or Is there any way to set this property on existing connection? also if you have any oracle specific document links please post.

Depending upon above answers, there may be ways to do this. As per Teiid and Oracle connections, you can remove the security-domain and just configure username, password in the data source configuration.

Ramesh..

[abhilash_tcs]

Ramesh,

1. Where you get the "userId" in the above code snippet? note I am not asking "username" which is also in there?

"userId" in the code snippet would be login user of the application that is communicating with VDB. The Application is likely to use system account to login to the VDB which is "username" and not the individual user's credentials "userId".

2. You need to do this when connection is created? or Is there any way to set this property on existing connection?

Yes, we have to do this when the connection is created. Once the client application connects to VDB using global system account, it should be sending logged in user's ID so that when VDB connects to oracle, it can set the property of OS_USER in default oracle function SYS_CONTEXT.

3. We have to implement kind of this - click here with ID getting passed from client to Teiid to Oracle.

Relevant link of oracle - OracleConnection (Oracle Database JDBC Java API Reference)

Thanks,

Abhilash

[rareddy]

Is (3) working for you?

[abhilash_tcs]

Ramesh,

When I tried connecting to Oracle Database with the code snippet by replacing OS_USER it was working. But implementing in Teiid translator, I'm unable to materialize it.

Custom Translator template archetype is not working for me. I have used the following repo as given in JBoss DV custom translator development document. Suggest can this be achieved using Teiid custom translator?

-Abhilash

[rareddy]

Try using Eclipse, that worked for me. But even then based on the (3) from other thread he was mentioning that he could not unwrap the oracle connection, you may have better luck. BTW, I have not tried it.

[rareddy]

See here Archetype Template Translator Project - Teiid 8.11 (draft) - Project Documentation Editor for Eclipse based instructions for Archtypes

[abhilash_tcs]

Ramesh,

I'm trying to do it, but do you mean that it did not work for previous trails? Because I'm also stuck up at a point from where I'm unable to proceed. May be I'm trying a solution which is not even feasible.

-Abhilash

[rareddy]

I am not saying that, there was not enough information in the other thread and I have not tried myself.

Because I'm also stuck up at a point from where I'm unable to proceed.

I can only help you guide if you tell me where and what point you are struck. Eclipse based artifact generation, I tried myself and it works as documented. Otherwise just use pattern from any other translator project to manually create the structure.

May be I'm trying a solution which is not even feasible.

Technically it sounds feasible, however I can not say about implementation hurdles. If you are a Redhat customer you can you can also seek professional services.

[abhilash_tcs]

Ramesh,

Yes I agree. I am also trying to get Professional Services from Redhat but in the mean time trying to resolve it by myself.

I'm actually stuck up in this part -

Even if I deploy the translator which sets "USER_ID" in "OracleConnection.CONNECTION_PROPERTY_THIN_VSESSION_OSUSER" (this would set up "os_user" in "userenv" of SYS_CONTEXT) while Teiid makes connection to Oracle. How can I pass the USER_ID from Teiid to Oracle since SYS_CONTEXT function will not be available in Teiid.

Is it feasible in Teiid to get application's logged in "user_id" and pass it to Translator/Resource-adaptor?

Please Note - The user_id is not connection credentials of Teiid VDB.

-Abhilash

[rareddy]

One can easily add a source function, if SYS_CONTEXT is Oracle function, that can be added on your source model of your VDB and it can then be used in any transformations.

Look at this Custom Translator where I describe how to pass payload from client to the translator. You can send the "user_id" value same way.

[rareddy]

Abhilash,

Sorry, it took little time to solution but, I have written solution for your issue with Oracle VPD through DV. See here for details teiid-marketplace/oracle-vpd · GitHub as to how to accomplish this, turned out to be simple than I thought. No custom translator required.

Let me know if you have any questions. Hopefully I will blog on this soon.

Ramesh..