You can translate the question and the replies:

Denodo support for something like SQL EXECUTE AS function?

Hello, Does Denodo support some kind of impersonation feature like EXECUTE AS in SQL Server? This would allow a functional user to execute a query on behalf of another user. If Denodo doesn't support it through this kind of statement, does Denodo support some kind of alternative?
user
16-08-2018 04:02:08 -0400

7 Answers

Hi, For this scenario, I would create a JAVA based custom function using Denodo4E extension available in the directory *<DENODOHOME>/tools/denodo4e* and deploy the developed Custom function in Virtual DataPort. You could find samples for Virtual DataPort custom function in the directory, *<DENODOHOME>\samples\vdp\customFunctions*. For more information on creating custom functions, you can have a look at [Developing Custom Functions](https://community.denodo.com/docs/html/browse/7.0/vdp/developer/developing_extensions/developing_custom_functions/developing_custom_functions) section of Virtual DataPort Developer Guide. Hope this helps!
Denodo Team
17-08-2018 05:56:34 -0400
Hello, I'm not sure if this really helps, the "EXECUTE AS" function in SQL Server is not a function that operates on a single row or value (custom function) or even multiple rows or values (custom aggregate function). It is a function that modifies the context of the query. So in fact I think it should behave as a custom [context](https://community.denodo.com/docs/html/browse/6.0/vdp/vql/queries_select_statement/context_clause/context_clause) function in Denodo. So doing something like (if at all possible); a query executed by a functional user (with sufficient/admin rights)= ```SQL SELECT * FROM my_view CONTEXT('user' = 'some_other_user') ``` or even better: ```SQL SELECT * FROM my_view EXECUTE AS 'some_other_user' ``` How could this be implemented?
user
20-08-2018 08:39:16 -0400
Hi, If your view has been created over a JDBC data source which supports ‘pass-through credentials’, you can apply the CONTEXT clause with USERNAME and PASSWORD parameters to use other credentials to execute the statement. You can achieve this with the following steps: * In the ‘Configuration’ > ‘Connection’ tab of the datasource, change the Authentication method to ‘User Pass-through session credentials’ * Open VQL Shell, execute statements with the following syntax: ``` SELECT * FROM view1 CONTEXT(USERNAME = 'admin', PASSWORD = 'd4GvpKA5BiwoGUFrnH92DNq5TTNKWw58I86PVH2tQIs/q1RH9CkCoJj57NnQUlmvgvvVnBvlaH8NFSDM0x5fWCJiAvyia70oxiUWbToKkHl3ztgH1hZLcQiqkpXT/oYd' ENCRYPTED) ``` The server will connect to the database of the view with the username ‘admin’ and the password, given that ‘admin’ has the proper privileges granted to the underlying data source. You can execute the ENCRYPT_PASSWORD stored procedure to encrypt your password. `ENCRYPT_PASSWORD 'my_secret_password';` You may refer to the [CONTEXT Clause](https://community.denodo.com/docs/html/browse/7.0/vdp/vql/queries_select_statement/context_clause/context_clause) section of the Virtual DataPort VQL Guide and [Considerations When Configuring Data Sources with Pass-Through Credentials](https://community.denodo.com/docs/html/browse/7.0/vdp/administration/appendix/considerations_when_configuring_data_sources_with_pass-through_credentials/considerations_when_configuring_data_sources_with_pass-through_credentials) section of the Virtual DataPort Administration Guide for more information. Hope this helps!
Denodo Team
29-08-2018 01:04:13 -0400
Thank you for the clarification. This is indeed a useful funcionality which might help us. however, we would like to this from an external client, so when Denodo is the JDBC source and we want to change the user in the query that is querying denodo and have denodo check the roles and permissions with the user parsed in the context.
user
30-08-2018 05:18:26 -0400
Hi, I tried accessing Denodo using a JDBC client and was successfully able to execute the same statement through the external client. You may refer to [Connecting from your application - JDBC Clients](https://community.denodo.com/tutorials/browse/basics/4connect1jdbcclient) of the Tutorials Section and [Access Through JDBC](https://community.denodo.com/docs/html/browse/7.0/vdp/developer/access_through_jdbc/access_through_jdbc#access-through-jdbc) section of the Virtual DataPort Developer Guide for more information. Hope this helps!
Denodo Team
24-09-2018 03:02:37 -0400
I have the same question. I think the answers have misunderstood the question. The request is to connect to Denodo via JDBC using a functional account from an application but to execute the query in Denodo as the user connecting to the calling application i.e. how to impersonate a user via JDBC in a similar pattern as can be done via rest services https://community.denodo.com/docs/html/browse/7.0/vdp/administration/publication_of_web_services/web_services_authentication/web_services_authentication#impersonating-a-user
user
17-08-2021 12:28:57 -0400
Hi, In order to impersonate the user through the JDBC connection, I would make use of the **'Initial SQL sentences'** option in the Configuration tab of the [JDBC Data source](https://community.denodo.com/docs/html/browse/8.0/en/vdp/administration/creating_data_sources_and_base_views/jdbc_sources/jdbc_sources#jdbc-sources) in Denodo 8.0 to execute an initial sentence before the actual query. By providing the sentence **"EXECUTE AS USER = '@USER_NAME'"** as Initial SQL Sentence, the user credentials used to login to the VDP will be passed to the JDBC Data Source. By interpolating the user-name, the user logged into Virtual DataPort will be the user accessing the JDBC Data Source as impersonation. Hope this helps!
Denodo Team
14-09-2021 09:17:16 -0400
You must sign in to add an answer. If you do not have an account, you can register here