-
1. Re: Passing User ID in sys_context from Teiid to Oracle with JDBC connection
rareddy Jun 11, 2015 8:31 AM (in response to abhilash_tcs)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..
-
2. Re: Passing User ID in sys_context from Teiid to Oracle with JDBC connection
abhilash_tcs Jun 11, 2015 8:58 AM (in response to rareddy)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
-
3. Re: Passing User ID in sys_context from Teiid to Oracle with JDBC connection
rareddy Jun 11, 2015 3:32 PM (in response to abhilash_tcs)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..
-
4. Re: Passing User ID in sys_context from Teiid to Oracle with JDBC connection
abhilash_tcs Jun 12, 2015 4:37 AM (in response to rareddy)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
-
5. Re: Passing User ID in sys_context from Teiid to Oracle with JDBC connection
rareddy Jun 12, 2015 9:56 AM (in response to abhilash_tcs)Is (3) working for you?
-
6. Re: Passing User ID in sys_context from Teiid to Oracle with JDBC connection
abhilash_tcs Jun 12, 2015 10:26 AM (in response to rareddy)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
-
7. Re: Passing User ID in sys_context from Teiid to Oracle with JDBC connection
rareddy Jun 12, 2015 6:54 PM (in response to abhilash_tcs)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.
-
8. Re: Passing User ID in sys_context from Teiid to Oracle with JDBC connection
rareddy Jun 12, 2015 6:55 PM (in response to abhilash_tcs)See here Archetype Template Translator Project - Teiid 8.11 (draft) - Project Documentation Editor for Eclipse based instructions for Archtypes
-
9. Re: Passing User ID in sys_context from Teiid to Oracle with JDBC connection
abhilash_tcs Jun 15, 2015 9:35 AM (in response to rareddy)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
-
10. Re: Passing User ID in sys_context from Teiid to Oracle with JDBC connection
rareddy Jun 15, 2015 10:04 AM (in response to abhilash_tcs)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.
-
11. Re: Passing User ID in sys_context from Teiid to Oracle with JDBC connection
abhilash_tcs Jun 16, 2015 4:33 AM (in response to rareddy)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
-
12. Re: Passing User ID in sys_context from Teiid to Oracle with JDBC connection
rareddy Jun 17, 2015 5:50 PM (in response to abhilash_tcs)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.
-
13. Re: Passing User ID in sys_context from Teiid to Oracle with JDBC connection
rareddy Aug 27, 2015 11:40 PM (in response to abhilash_tcs)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..